Display,Edit ,Update and delete data using DataList in Asp.Net

Display, Edit , Update and delete data using DataList

in Asp.Net and C#

DataList control is a data bound control which display repeated list of item of database.Manupulation of data in DataList is possible. We can edit and delete data in DataList. DataList control can be bound with database tables, xml file or other list of item (Array,ArrayList,Collections,Hash Table etc.).

Unlike GridView ,DataList does not have any default template to display data. So Developers has to use templates like ItemTemplate or EditItemTemplate to display data in DataList control.

DataList support some command name for button control for the manupulation of data.

DataList Support following command name-
Edit—call edit item event of DataList
Update—call update item event of DataList
Delete—call item deleted event of DataList
Cancel – call cancel edit event of DataList.

You can also give your own command name in DataList.

Here I am giving an example of how we can display ,edit , update and delete data using DataList control of Asp.Net.

DataList uses templates to display ,edit and update data. Common templates are given below:

ItemTemplate- Item Template define how to display data in DataList control
EditItemTemplate-- Edit Item Template define how to display data for editing




I have used followin database table in my example. Use following script to create table in your sql server databae

CREATE TABLE tbbook
(
bookid int NULL ,
booktit varchar (50) NULL ,
bookaut varchar (50) NULL ,
bookpub varchar (50) NULL ,
bookprc int NULL
)
GO

Use following code in your .aspx page

<asp:DataList ID="DataList1" runat="server" BackColor="White" BorderColor="#FF8080" BorderStyle="None" BorderWidth="1px" CellPadding="4" GridLines="Both" RepeatColumns="2" OnCancelCommand="DataList1_CancelCommand" OnEditCommand="DataList1_EditCommand" OnUpdateCommand="DataList1_UpdateCommand" DataKeyField="bookid">
<ItemTemplate>
<b>Title:</b><%#Eval("booktit") %><br />
<b>Author:</b><%#Eval("bookaut") %><br />
<b>Publisher:</b><%#Eval("bookpub") %><br />
<b>Price:</b><%#Eval("bookprc") %><br />
<asp:LinkButton ID="b1" Text="Edit" CommandName="edit" runat="server" />
</ItemTemplate>
<EditItemTemplate>
<b>Title:</b><asp:TextBox ID="tb1" Text='<%#Eval("booktit") %>' runat="server"></asp:TextBox><br />
<b>Author:</b><asp:TextBox ID="tb2" Text='<%#Eval("bookaut") %>' runat="server"></asp:TextBox><br />
<b>Publisher:</b><asp:TextBox ID="tb3" Text='<%#Eval("bookpub") %>' runat="server"></asp:TextBox><br />
<b>Price:</b><asp:TextBox ID="tb4" Text='<%#Eval("bookprc") %>' runat="server"></asp:TextBox><br />
<asp:LinkButton ID="b2" Text="Update" CommandName="update" runat="server" />
<asp:LinkButton ID="b3" Text="Cancel" CommandName="cancel" runat="server" />
</EditItemTemplate>
<FooterStyle BackColor="#99CCCC" ForeColor="#003399" />
<SelectedItemStyle BackColor="#009999" Font-Bold="True" ForeColor="#CCFF99" />
<ItemStyle BackColor="White" ForeColor="#003399" />
<HeaderStyle BackColor="#003399" Font-Bold="True" ForeColor="#CCCCFF" />
</asp:DataList>

Write followin code in your page_load event

if (Page.IsPostBack == false)
{
listbind();
}

Following method will bind DataList to database

private void listbind()
{
SqlDataAdapter adp = new SqlDataAdapter("select * from tbbook", "server=tiger;database=database1;uid=sa");
DataSet ds = new DataSet();
adp.Fill(ds);
DataList1.DataSource = ds;
DataList1.DataBind();
}

Below is edit event of grid view which will fire when user click on edit button

protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)
{
//call edit item template for selected index
DataList1.EditItemIndex = e.Item.ItemIndex;
listbind();
}

Now here is update event which will fire when user click on update button

protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)
{
Int32 bid, bprc;
String btit, baut, bpub;

bid = Convert.ToInt32(DataList1.DataKeys[e.Item.ItemIndex]);
btit = ((TextBox)(e.Item.FindControl("tb1"))).Text;
baut = ((TextBox)(e.Item.FindControl("tb2"))).Text;
bpub = ((TextBox)(e.Item.FindControl("tb3"))).Text;
bprc = Convert.ToInt32(((TextBox)(e.Item.FindControl("tb4"))).Text);

SqlConnection con = new SqlConnection("server=tiger;database=database1;uid=sa");
con.Open();
SqlCommand cmd = new SqlCommand("update tbbook set booktit=@btit,bookaut=@baut,bookpub=@bpub,bookprc=@bprc where bookid=@bid", con);
cmd.Parameters.Add("@bid", SqlDbType.Int).Value = bid;
cmd.Parameters.Add("@btit", SqlDbType.VarChar, 50).Value = btit;
cmd.Parameters.Add("@baut", SqlDbType.VarChar, 50).Value = baut;
cmd.Parameters.Add("@bpub", SqlDbType.VarChar, 50).Value = bpub;
cmd.Parameters.Add("@bprc", SqlDbType.VarChar, 50).Value = bprc;
cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
DataList1.EditItemIndex = -1;
listbind();
}

Below is canceling event, fire when user click on cancel button with command name cancel

protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)
{
DataList1.EditItemIndex = -1;//Call Item Template
listbind();
}

no fear choudhary is here, Tuesday, July 13, 2010