asp.net Datalist insert update delete example

I want to show you how to use datalist for CRUD operation in asp.net.

I created one table 'customer' with CustomerID(Primary Key) and CustomerName as two fields in database.

Following is the HTML source
-------------------------------

<h2>Datalist Example Application:</h2>
        <asp:DataList ID="DataList1" runat="server" CellPadding="4"
            onitemcommand="DataList1_ItemCommand"
            oncancelcommand="DataList1_CancelCommand" oneditcommand="DataList1_EditCommand"
            onupdatecommand="DataList1_UpdateCommand"
            ondeletecommand="DataList1_DeleteCommand" ForeColor="#333333">
            <FooterStyle BackColor="#1C5E55" ForeColor="White" Font-Bold="True" />
            <HeaderStyle BackColor="#1C5E55" Font-Bold="True" ForeColor="White" />
            <ItemStyle BackColor="#E3EAEB" />
        <ItemTemplate>
        <table border="0" cellpadding="0" cellspacing="0">
        <tr>
        <th>Customer ID:</th>
        <td><asp:Label ID="lblCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:Label></td>
        </tr>
        <tr>
        <th>Customer Name:</th>
        <td><asp:Label ID="lblCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:Label></td>
        </tr>
        <tr><td><asp:Image ID="Image1" Height="20px" Width="20px" ImageUrl="~/Images/edit-icon.png" runat="server" />
        <asp:Button ID="btnEdit" runat="server" Text="Edit" CommandName="Edit" OnClientClick="return confirm('Are you sure you want to Edit this record?');" /></td>
        <td>
        <asp:Image ID="Image2" Height="20px" Width="20px" ImageUrl="~/Images/delete.png" runat="server" />
        <asp:Button ID="btnDelete" runat="server" Text="Delete" CommandName="Delete"  OnClientClick="return confirm('Are you sure you want to delete this record?');" /></td>
        </tr>
         </table>
        </ItemTemplate>
         <FooterTemplate>
          <table border="0" cellpadding="0" cellspacing="0">
        <tr>
        <th>Customer ID:</th>
        <th>Customer Name:</th>
        </tr>
        <tr>
        <td><asp:TextBox ID="txtCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:TextBox></td>
        <td><asp:TextBox ID="txtCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:TextBox></td>
        <td>
        <asp:Image ID="Image1" Height="20px" Width="20px" ImageUrl="~/Images/add.png" runat="server" />
        <asp:Button ID="btnInsert" runat="server" Text="Add New Customer" CommandName="Insert" /></td>
        </tr>
         </table>
         </FooterTemplate>
            <AlternatingItemStyle BackColor="White" />
         <EditItemTemplate>
          <table border="0" cellpadding="0" cellspacing="0">
            <tr>
            <td>Customer ID:</td>
            <td><asp:TextBox ID="etxtCustNo" runat="server" Text='<%#Bind("CustomerID") %>'></asp:TextBox></td>
            <//tr>
             <tr>
            <td>Customer Name:</td>
            <td><asp:TextBox ID="etxtCustName" runat="server" Text='<%#Bind("CustomerName") %>'></asp:TextBox></td>
            <//tr>
            <tr>
            <td><asp:Button ID="btnUpdate" runat="server" Text="Update" CommandName="Update" /></td>
            <td><asp:Button ID="btnCancel" runat="server" Text="Cancel" CommandName="Cancel" /></td>
            </tr>
         </EditItemTemplate>
            <SelectedItemStyle BackColor="#C5BBAF" Font-Bold="True" ForeColor="#333333" />
        </asp:DataList>
---------------------------------
Now on code behind please write following code:


public partial class WebForm1 : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!Page.IsPostBack)
            {
                Bind();
            }
        }

        private void Bind()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            SqlConnection conn = new SqlConnection(connectionString);
            SqlDataAdapter ad = new SqlDataAdapter("select * from Emp", conn);
            DataSet ds = new DataSet();
            conn.Open();
            ad.Fill(ds);
            conn.Close();
            DataList1.DataSource = ds.Tables[0];
            DataList1.DataBind();

        }

        protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
        {
           
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Insert"))
            {
                TextBox txtCustNo = (TextBox)e.Item.FindControl("txtCustNo");
                TextBox txtCustName = (TextBox)e.Item.FindControl("txtCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Insert into Emp(CustomerID,CustomerName) values(@1,@2)";
                cmd.Parameters.AddWithValue("@1", txtCustNo.Text);
                cmd.Parameters.AddWithValue("@2", txtCustName.Text);
                conn.Open();
                cmd.ExecuteNonQuery();
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Added Succesfully');", true);
                conn.Close();
                Bind();
            }
        }

        protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
        {

            DataList1.EditItemIndex = e.Item.ItemIndex;
            Bind();
        }

        protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
        {
            DataList1.EditItemIndex = -1;
            Bind();
        }

        protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Update"))
            {
                TextBox txtCustNo = (TextBox)e.Item.FindControl("etxtCustNo");
                TextBox txtCustName = (TextBox)e.Item.FindControl("etxtCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Update Emp set CustomerName=@1 where CustomerID=@2";
                if (txtCustName != null)
                    cmd.Parameters.Add("@1", SqlDbType.VarChar, 50).Value = txtCustName.Text;
                if (txtCustNo != null)
                    cmd.Parameters.Add("@2", SqlDbType.Int, 20).Value = txtCustNo.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                DataList1.EditItemIndex = -1;
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Updated Succesfully');", true);
                conn.Close();
                Bind();
            }
        }

        protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
        {
           
            string connectionString = ConfigurationManager.ConnectionStrings["mystring"].ConnectionString;
            if (e.CommandName.Equals("Delete"))
            {
                Label txtCustNo = (Label)e.Item.FindControl("lblCustNo");
                Label txtCustName = (Label)e.Item.FindControl("lblCustName");
                SqlConnection conn = new SqlConnection(connectionString);
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "Delete from Emp where CustomerID=@1";
                if(txtCustNo!=null)
                cmd.Parameters.Add("@1", SqlDbType.Int, 20).Value = txtCustNo.Text;
                conn.Open();
                cmd.ExecuteNonQuery();
                ClientScript.RegisterStartupScript(GetType(), "ShowAlert", "alert('Record Deleted Succesfully');", true);
                conn.Close();
                Bind();

            }
        }
    }

Run it now by Pressing F5.