Wednesday, June 17, 2015

Display, Insert, Edit, Update and delete data with datalist control in asp.net

Introduction: In this article I am going to explain how to Display, Insert, Edit, Update and delete data using datalist control in asp.net

Description:
In this example I have insert the data into database table using FooterTemplate of datalist control. To make this example live use the code step wise.
Create a table:-
Create table Tb_Student
(
Id int not null identity,
Student_Name varchar(50),
Student_Address varchar(200),
RollNo int
)

Now create store procedures to Get, Insert, update and Delete from database.

Procedure to GET data:
Create Proc [dbo].[Sp_GetStudentData]

As begin
Select * from dbo.Tb_Student
end

Procedure to INSERT the data/records:
Create Proc Sp_InsertStudents
(
@sname varchar(50),
@saddress varchar(200),
@rollno int
)
As begin
Insert into dbo.Tb_Student values(@sname,@saddress,@rollno)
end

Procedure to UPDATE records:
Create Proc Sp_UpdateStudents
(
@id int,
@sname varchar(50),
@saddress varchar(200),
@rollno          int
)
As begin
Update Tb_Student set Student_Name=@sname,Student_Address=@saddress,RollNo=@rollno where id =@id
end

Procedure to DELETE the records:
Create Proc Sp_DeleteStudents
(
@id int
)
As begin
Delete from Tb_Student where id =@id
end

After that add a webform to website/project. Drag and drop the Datalist control from toolbox to webform. Design the datalist as mention below:
HTML markup of Page:
<asp:DataList ID="dlstudent" runat="server" DataKeyField="Id"
            oncancelcommand="dlstudent_CancelCommand" oneditcommand="dlstudent_EditCommand"
            onupdatecommand="dlstudent_UpdateCommand"
            ondeletecommand="dlstudent_DeleteCommand"
            onitemcommand="dlstudent_ItemCommand" ShowFooter="true" ShowHeader="true">
            <HeaderTemplate>
            <table><tr>
            <th>Student Name</th><th>Student Address</th><th>RollNo</th></tr>
            </HeaderTemplate>
            <ItemTemplate>
            <tr align="center">
            <td><%# DataBinder.Eval(Container.DataItem, "Student_Name")%></td>
             <td> <%# DataBinder.Eval(Container.DataItem,"Student_Address") %></td>
              <td> <%# DataBinder.Eval(Container.DataItem, "RollNo") %></td>
              <td><asp:ImageButton ID="imgbtnedit" runat="server" ImageUrl="~/images/btnedit.png" CommandName="edit" ToolTip="Edit" Width="32px" CausesValidation="false" /></td>
              <td><asp:ImageButton ID="imgbtndelete" runat="server" ImageUrl="~/images/btndelete.png" CommandName="Delete" ToolTip="Delete" Width="32px" CausesValidation="false"/></td>
            </tr>          
            </ItemTemplate>
            <EditItemTemplate>           
            <tr>
             <td><asp:TextBox ID="txtname" runat="server" Text='<%# Eval("Student_Name") %>'></asp:TextBox></td>
              <td><asp:TextBox ID="txtaddress" runat="server" Text='<%# Eval("Student_Address") %>'></asp:TextBox></td>
               <td><asp:TextBox ID="txtrollno" runat="server" Text='<%# Eval("RollNo") %>'></asp:TextBox></td>
               <td><asp:ImageButton ID="imgbtncancel" runat="server" ImageUrl="~/images/btncancel.png" CommandName="cancel" ToolTip="Cancel" Width="32px" CausesValidation="false"/></td>
               <td><asp:ImageButton ID="imgbtnupdate" runat="server" ImageUrl="~/images/btnupdate.png" CommandName="update" ToolTip="Update" Width="32px" CausesValidation="false"/></td>
            </tr>
                       </EditItemTemplate>
                       <FooterTemplate>
                       <tr>
                       <td><asp:TextBox ID="txtsname" runat="server"></asp:TextBox ><br />
                       <asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="txtsname" ErrorMessage="Enter Student name"></asp:RequiredFieldValidator></td>
                       <td><asp:TextBox ID="txtsaddress" runat="server"></asp:TextBox><br />
                       <asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="txtsaddress" ErrorMessage="Enter Student Address"></asp:RequiredFieldValidator></td>
                       <td><asp:TextBox ID="txtsrollno" runat="server"></asp:TextBox><br />
                       <asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="txtsrollno" ErrorMessage="Enter Roll No."></asp:RequiredFieldValidator>
                       <asp:CompareValidator ID="Comp1" runat="server" ErrorMessage="Enter numeric value only" ControlToValidate="txtsrollno" Operator="DataTypeCheck" Type="Integer" ></asp:CompareValidator></td>
                        <td><asp:ImageButton ID="imgbtnadd" runat="server" ImageUrl="~/images/btnadd.png" CommandName="Insert" ToolTip="Add New Record" Width="32px" CausesValidation="true"/></td>
                       </tr>                      
                       </FooterTemplate>
        </asp:DataList>

Now we have written the code. Firstly add the namespace.
C#:
using System.Data;
using System.Data.SqlClient;
using System.Configuration;

VB:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration

Create a connection:
C#:
SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connection"].ToString());

VB:
Private con As New SqlConnection(ConfigurationManager.ConnectionStrings("connection").ToString())

Write the code to bind the data to Datalist:
C#:
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            BindDatalist();
        }
    }
    public void BindDatalist()
    {
        try
        {
            SqlDataAdapter adp = new SqlDataAdapter("Sp_GetStudentData", con);
            adp.SelectCommand.CommandType = CommandType.StoredProcedure;
            DataTable dt = new DataTable();
            adp.Fill(dt);
            if (dt.Rows.Count > 0)
            {
                dlstudent.DataSource = dt;
                dlstudent.DataBind();
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

VB:
Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
        If Not IsPostBack Then
            BindDatalist()
        End If
    End Sub
    Public Sub BindDatalist()
        Try
            Dim adp As New SqlDataAdapter("Sp_GetStudentData", con)
            adp.SelectCommand.CommandType = CommandType.StoredProcedure
            Dim dt As New DataTable()
            adp.Fill(dt)
            If dt.Rows.Count > 0 Then
                dlstudent.DataSource = dt
                dlstudent.DataBind()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Write the below given code to Update the Record:
C#:
  protected void dlstudent_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        try
        {
            int id = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex].ToString());
            TextBox txtname = (TextBox)e.Item.FindControl("txtname");
            TextBox txtaddress = (TextBox)e.Item.FindControl("txtaddress");
            TextBox txtroll = (TextBox)e.Item.FindControl("txtrollno");
            SqlCommand cmd = new SqlCommand("Sp_UpdateStudents", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", id);
            cmd.Parameters.AddWithValue("@sname", txtname.Text);
            cmd.Parameters.AddWithValue("@saddress", txtaddress.Text);
            cmd.Parameters.AddWithValue("@rollno", Convert.ToInt32(txtroll.Text));
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
            Response.Write("<script type=\"text/javascript\">alert('Updated Successfully!!!');</script>");
            dlstudent.EditItemIndex = -1;
            BindDatalist();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

VB:
Protected Sub dlstudent_UpdateCommand(source As Object, e As DataListCommandEventArgs)
        Try
            Dim id As Integer = Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex).ToString())
            Dim txtname As TextBox = DirectCast(e.Item.FindControl("txtname"), TextBox)
            Dim txtaddress As TextBox = DirectCast(e.Item.FindControl("txtaddress"), TextBox)
            Dim txtroll As TextBox = DirectCast(e.Item.FindControl("txtrollno"), TextBox)
            Dim cmd As New SqlCommand("Sp_UpdateStudents", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", id)
            cmd.Parameters.AddWithValue("@sname", txtname.Text)
            cmd.Parameters.AddWithValue("@saddress", txtaddress.Text)
            cmd.Parameters.AddWithValue("@rollno", Convert.ToInt32(txtroll.Text))
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            cmd.Dispose()
            Response.Write("<script type=""text/javascript"">alert('Updated Successfully!!!');</script>")
            dlstudent.EditItemIndex = -1
            BindDatalist()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Code to Delete the records:
C#:
protected void dlstudent_DeleteCommand(object source, DataListCommandEventArgs e)
    {
        try
        {
            int id = Convert.ToInt32(dlstudent.DataKeys[e.Item.ItemIndex].ToString());
            SqlCommand cmd = new SqlCommand("Sp_DeleteStudents", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@id", id);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
            Response.Write("<script type=\"text/javascript\">alert('Deleted Successfully!!!');</script>");
            BindDatalist();
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

VB:
Protected Sub dlstudent_DeleteCommand(source As Object, e As DataListCommandEventArgs)
        Try
            Dim id As Integer = Convert.ToInt32(dlstudent.DataKeys(e.Item.ItemIndex).ToString())
            Dim cmd As New SqlCommand("Sp_DeleteStudents", con)
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@id", id)
            con.Open()
            cmd.ExecuteNonQuery()
            con.Close()
            cmd.Dispose()
            Response.Write("<script type=""text/javascript"">alert('Deleted Successfully!!!');</script>")
            BindDatalist()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Code to Insert the Data into database table:
C#:
protected void dlstudent_ItemCommand(object source, DataListCommandEventArgs e)
    {
      try
      {
          if (e.CommandName == "Insert")
        {
            TextBox txtname = (TextBox)e.Item.FindControl("txtsname");
            TextBox txtaddress = (TextBox)e.Item.FindControl("txtsaddress");
            TextBox txtrollno = (TextBox)e.Item.FindControl("txtsrollno");
            SqlCommand cmd = new SqlCommand("Sp_InsertStudents", con);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@sname",txtname.Text);
            cmd.Parameters.AddWithValue("@saddress",txtaddress.Text);
            cmd.Parameters.AddWithValue("@rollno",txtrollno.Text);
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            cmd.Dispose();
            Response.Write("<script type=\"text/javascript\">alert('Inserted Successfully!!!');</script>");
            BindDatalist();
        }
      }
      catch (Exception ex)
      {
          throw ex;
      }
    }

VB:
  Protected Sub dlstudent_ItemCommand(source As Object, e As DataListCommandEventArgs)
        Try
            If e.CommandName = "Insert" Then
                Dim txtname As TextBox = DirectCast(e.Item.FindControl("txtsname"), TextBox)
                Dim txtaddress As TextBox = DirectCast(e.Item.FindControl("txtsaddress"), TextBox)
                Dim txtrollno As TextBox = DirectCast(e.Item.FindControl("txtsrollno"), TextBox)
                Dim cmd As New SqlCommand("Sp_InsertStudents", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.AddWithValue("@sname", txtname.Text)
                cmd.Parameters.AddWithValue("@saddress", txtaddress.Text)
                cmd.Parameters.AddWithValue("@rollno", txtrollno.Text)
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
                cmd.Dispose()
                Response.Write("<script type=""text/javascript"">alert('Inserted Successfully!!!');</script>")
                BindDatalist()
            End If
        Catch ex As Exception
            Throw ex
        End Try
    End Sub

Code to enable the edit items of datalist:
C#:
protected void dlstudent_EditCommand(object source, DataListCommandEventArgs e)
    {
        dlstudent.EditItemIndex = e.Item.ItemIndex;
        BindDatalist();
    }

VB:
Protected Sub dlstudent_EditCommand(source As Object, e As DataListCommandEventArgs)
        dlstudent.EditItemIndex = e.Item.ItemIndex
        BindDatalist()
    End Sub

Code to cancel the edit intms of datalist control:
C#:
  protected void dlstudent_CancelCommand(object source, DataListCommandEventArgs e)
    {
        dlstudent.EditItemIndex = -1;
        BindDatalist();
    }
VB:
Protected Sub dlstudent_CancelCommand(source As Object, e As DataListCommandEventArgs)
        dlstudent.EditItemIndex = -1
        BindDatalist()
    End Sub

Build, run the project/website and check the result.
Display, Insert, Edit, Update and delete data with datalist control in asp.net

In this article we have learn display, edit, update and delete data with datalist control in asp.net. I hope you enjoyed this article. 

No comments:

Post a Comment