Using DataList Insert,Update & Delete Data with SQLServer

DataList Manupulation

Insert, Update And Delete Using Datalist..

Introduction

DataList is used to display a repeated list of items like repeater control. DataList provide data grouping and flexible layout as ListView but it does not provide some feature like update, delete, paging, insert and sorting. It is drawback of DataList. 

 

DataList must define Several Optional templates can be used to customize the appearance of the list as per Following:-

AlternatingItemTemplate: If defined, provides the content and layout for alternating items in the DataList. If not defined, ItemTemplate is used.

EditItemTemplate: If defined, provides the content and layout for the item currently being edited in the DataList. If not defined, ItemTemplate is used.

FooterTemplate: If defined, provides the content and layout for the footer section of the DataList. If not defined, a footer section will not be displayed.

HeaderTemplate: If defined, provides the content and layout for the header section of the DataList. If not defined, a header section will not be displayed.

ItemTemplate: Required template that provides the content and layout for items in the DataList.

SelectedItemTemplate: If defined, provides the content and layout for the currently selected item in the DataList. If not defined, ItemTemplate is used.

SeparatorTemplate: If defined, provides the content and layout for the separator between items in the DataList. If not defined, a separator will not be displayed.

.aspx Code 

<asp:DataList ID="DataList1" runat="server" DataKeyField="Dept_id"
            oneditcommand="DataList1_EditCommand" RepeatColumns="2"
            RepeatDirection="Horizontal" oncancelcommand="DataList1_CancelCommand"
            ondeletecommand="DataList1_DeleteCommand" onitemcommand="DataList1_ItemCommand"
            onupdatecommand="DataList1_UpdateCommand">
            <FooterTemplate>
                <table>
                    <tr>
                        <td>
                            Name :</td>
                        <td>
                            <asp:TextBox ID="txtName" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            Age :</td>
                        <td>
                            <asp:TextBox ID="txtAge" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                            City :</td>
                        <td>
                            <asp:TextBox ID="txtCity" runat="server"></asp:TextBox>
                        </td>
                    </tr>
                    <tr>
                        <td>
                        </td>
                        <td>
                            <asp:LinkButton ID="lbInsert" runat="server" CommandName="Insert">Insert</asp:LinkButton>
                            |</td>
                    </tr>
                </table>
            </FooterTemplate>
        <ItemTemplate>
        <table>
        <asp:Label ID="lblID" runat="server" Visible="false" Text='<%#bind("Dept_id") %>'></asp:Label>
        <tr><td>Name :</td><td><asp:Label ID="lblName" runat="server"
                Text='<%# bind("sName") %>'></asp:Label></td></tr>
        <tr><td>Age :</td><td><asp:Label ID="lblAge" runat="server"
                Text='<%# bind("iAge") %>'></asp:Label></td></tr>
         <tr><td>City :</td><td><asp:Label ID="lblCity" runat="server"
                 Text='<%# bind("sCity") %>'></asp:Label></td></tr>
         <tr><td></td><td>
         <asp:LinkButton ID="lbEdit" runat="server" CommandName="Edit">Edit</asp:LinkButton>|<asp:LinkButton
             ID="lbDelete" runat="server" CommandName="Delete">Delete</asp:LinkButton></td></tr>
        </table>
        </ItemTemplate>
        <EditItemTemplate>
        <table>
        <tr><td>Name :</td><td><asp:TextBox ID="txtName" runat="server" Text='<%#bind("sName") %>'></asp:TextBox></td></tr>
        <tr><td>Age :</td><td><asp:TextBox ID="txtAge" runat="server" Text='<%#bind("iAge") %>'></asp:TextBox></td></tr>
        <tr><td>City :</td><td><asp:TextBox ID="txtCity" runat="server" Text='<%#bind("sCity") %>'></asp:TextBox></td></tr>
        <tr><td></td><td>
         <asp:LinkButton ID="lbUpdate" runat="server" CommandName="Update">Update</asp:LinkButton>|<asp:LinkButton
             ID="lbCancel" runat="server" CommandName="Cancel">Cancel</asp:LinkButton></td></tr>
        </table>
        </EditItemTemplate>
        </asp:DataList>

 

.aspx.cs coding

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;

public partial class _Default : System.Web.UI.Page
{
    SqlConnection con = new SqlConnection();
    protected void Page_Load(object sender, EventArgs e)
    {
        con.ConnectionString = "server=LOVE-PC;database=practice;integrated security=true;";
        if (!IsPostBack)
        {
            BindDatalist();
        }
    }
    public void BindDatalist()
    {
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "select * from tbDepartment";
        cmd.Connection = con;
        SqlDataAdapter adp = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        adp.Fill(ds);
        DataList1.DataSource = ds;
        DataList1.DataBind();
    }

    protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
    {
        DataList1.EditItemIndex = e.Item.ItemIndex;
        BindDatalist();
    }

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

    protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)
    {
        int DeptId = Convert.ToInt16(DataList1.DataKeys[e.Item.ItemIndex]);
        Label lblID = (Label)e.Item.FindControl("lblID");
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "delete from tbDepartment where Dept_id=" + DeptId.ToString();
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        BindDatalist();
    }

    protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
    {
        int DeptId = Convert.ToInt16(DataList1.DataKeys[e.Item.ItemIndex]);
        Label lblID = (Label)e.Item.FindControl("lblID");
        TextBox txtName = (TextBox)e.Item.FindControl("txtName");
        TextBox txtAge = (TextBox)e.Item.FindControl("txtAge");
        TextBox txtCity = (TextBox)e.Item.FindControl("txtCity");
        SqlCommand cmd = new SqlCommand();
        cmd.CommandText = "update tbDepartment set sName=@sName, iAge=@iAge, sCity=@sCity where Dept_id=" + DeptId.ToString();
        cmd.Parameters.AddWithValue("@Dept_id",DeptId);
        cmd.Parameters.AddWithValue("@sName",txtName.Text);
        cmd.Parameters.AddWithValue("@iAge", txtAge.Text);
        cmd.Parameters.AddWithValue("@sCity", txtCity.Text);
        cmd.Connection = con;
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();
        DataList1.EditItemIndex = -1;
        BindDatalist();
    }

    protected void DataList1_ItemCommand(object source, DataListCommandEventArgs e)
    {
        if (e.CommandName == "Insert")
        {
            TextBox txtName = (TextBox)e.Item.FindControl("txtName");
            TextBox txtAge = (TextBox)e.Item.FindControl("txtAge");
            TextBox txtCity = (TextBox)e.Item.FindControl("txtCity");
            SqlCommand cmd = new SqlCommand();
            cmd.CommandText = "insert into tbDepartment (sName, iAge, sCity) values(@sName, @iAge, @sCity)";
            cmd.Parameters.AddWithValue("@sName", txtName.Text);
            cmd.Parameters.AddWithValue("@iAge", txtAge.Text);
            cmd.Parameters.AddWithValue("@sCity", txtCity.Text);
            cmd.Connection = con;
            con.Open();
            cmd.ExecuteNonQuery();
            con.Close();
            DataList1.EditItemIndex = -1;
            BindDatalist();
        }
    }
}

Set Some properties of Datalist:-