Add, Delete and Update command in DataList

Sapna Malik
15y
90k
0
3

Article

In this article, I will show you how to use an ASP.NET 2.0 DataList control to Add, Select, update, and delete data in a SQL database.

We will use SQL Client data provider to provide database connectivity.

Before you can use any classes related to SQL Client data adapter, we need to import the SqlClient namespace in your application by using the following using statement.

using System.Data.SqlClient;

Next, we need to define the database connection string.

The below is my connection string which is stored in web.config file. You can change this connection string according to your SQL server database setting. I am storing my database file in App_Data folder. If you want use my database file then attach that file.

</appSettings>

This is aspx code of DataList.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="DataList.aspx.cs" Inherits="sapnamalik_List" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<title>DataList</title>

</head>

<body>

<div>

<asp:DataList ID="DataList1" runat="server" DataKeyField="StId" Width="100%" BackColor="LightGoldenrodYellow" BorderColor="Tan" BorderWidth="1px" CellPadding="2" ForeColor="Black" OnCancelCommand="DataList1_CancelCommand"

OnDeleteCommand="DataList1_DeleteCommand" OnEditCommand="DataList1_EditCommand"

OnUpdateCommand="DataList1_UpdateCommand">

StId

</th>

Name

</th>

ClassName

</th>

RollNo

</th>

EmailId

</th>

TotalRecord

</th>

<th>

 </th>

</tr>

</HeaderTemplate>

<td>

<%# DataBinder.Eval(Container.DataItem, "StId") %>

</td>

<td>

<%# DataBinder.Eval(Container.DataItem, "Name") %>

</td>

<td>

<%# DataBinder.Eval(Container.DataItem, "ClassName")

</td>

<td>

<%# DataBinder.Eval(Container.DataItem, "RollNo") %>

</td>

<td>

<%# DataBinder.Eval(Container.DataItem, "EmailId") %>

</td>

<td>

<%# DataBinder.Eval(Container.DataItem, "TotalRecord") %>

</td>

<td>

<asp:LinkButton ID="lnkEdit" runat="server" CommandName="edit"> Edit </asp:LinkButton>

<asp:LinkButton ID="lnkDelete" runat="server" CommandName="delete"> Delete </asp:LinkButton>

</td>

</tr>

</ItemTemplate>

<tr>

<td>

<asp:TextBox ID="txtStId" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "StId") %>'>

</asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "Name") %>'>

</asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtClassName" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "ClassName") %>'>

</asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtRollNo" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "RollNo") %>'>

</asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtEmailId" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "EmailId") %>'>

</asp:TextBox>

</td>

<td>

<asp:TextBox ID="txtTotalRecord" runat="server" Text='<%# DataBinder.Eval(Container.DataItem, "TotalRecord") %>'>

</asp:TextBox>

</td>

<td>

<asp:LinkButton ID="lnkUpdate" runat="server" CommandName="update">Update </asp:LinkButton>

<asp:LinkButton ID="lnkCancel" runat="server" CommandName="cancel">Cancel </asp:LinkButton>

</td>

</tr>

</EditItemTemplate>

</table>

</FooterTemplate>

</asp:DataList>

<tr>

</td>

</tr>

<tr>

</td>

</td>

</td>

</td>

</tr>

<tr>

<asp:Label ID="LabelPageFirstRecord" runat="server"></asp:Label>

<asp:Label ID="label2" runat="server" Text="-"></asp:Label>

<asp:Label ID="LabelPageLastRecord" runat="server"></asp:Label>

<asp:Label ID="label1" runat="server" Text="of"></asp:Label>

<asp:Label ID="LabelTotalRecords" runat="server"></asp:Label>

</td>

</td>

<asp:LinkButton ID="LinkButtonFirst" runat="server" OnClick="LinkButtonFirst_Click"CssClass="PagerLinkStyle">

<img border=0 src='<%=ResolveUrl("../Images/Paging/First.gif")%>' onmouseover="this.src='<%=ResolveUrl("../Images/Paging/FirstHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("../Images/Paging/First.gif")%>';"/>

</asp:LinkButton>

<asp:LinkButton ID="LinkButtonPrevious" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButtonPrevious_Click">

<img border=0 src='<%=ResolveUrl("../Images/Paging/Previous.gif")%>' onmouseover="this.src='<%=ResolveUrl("../Images/Paging/PreviousHover.gif")%>';"onmouseout="this.src='<%=ResolveUrl("../Images/Paging/Previous.gif")%>';"/>

</asp:LinkButton>

<asp:LinkButton ID="LinkButton1" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButton1_Click">1</asp:LinkButton>

<asp:LinkButton ID="LinkButton2" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButton1_Click">2</asp:LinkButton>

<asp:LinkButton ID="LinkButton3" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButton1_Click">3</asp:LinkButton>

<asp:LinkButton ID="LinkButton4" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButton1_Click">4</asp:LinkButton>

<asp:LinkButton ID="LinkButton5" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButton1_Click">5</asp:LinkButton>

<asp:LinkButton ID="LinkButtonNext" runat="server" CssClass="PagerLinkStyle" OnClick="LinkButtonNext_Click">

<img border="0" src='<%=ResolveUrl("../Images/Paging/Next.gif")%>' onmouseover="this.src='<%=ResolveUrl("../Images/Paging/NextHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("../Images/Paging/Next.gif")%>';"/>

</asp:LinkButton>

<asp:LinkButton ID="LinkButtonLast" runat="server" OnClick="LinkButtonLast_Click" CssClass="PagerLinkStyle">

<img border="0" src='<%=ResolveUrl("../Images/Paging/Last.gif")%>' onmouseover="this.src='<%=ResolveUrl("../Images/Paging/LastHover.gif")%>';" onmouseout="this.src='<%=ResolveUrl("../Images/Paging/Last.gif")%>';"/>

</asp:LinkButton>

</td>

</td>

</tr>

<tr>

</td>

</td>

</td>

</td>

</tr>

</table>

</td>

</tr>

</table>

<br />

<table>

<tr>

<td>

<asp:Label ID="lblEmpId" runat="server" Text="StId"></asp:Label>

<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblname" runat="server" Text="Name"></asp:Label>

<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblClassname" runat="server" Text="ClassName"></asp:Label>

<asp:TextBox ID="TextBox3" runat="server"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblRollNo" runat="server" Text="RollNo"></asp:Label>

<asp:TextBox ID="TextBox4" runat="server"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblEmailId" runat="server" Text="EmailId"></asp:Label>

<asp:TextBox ID="TextBox5" runat="server"></asp:TextBox>

</td>

<td>

<asp:Label ID="lblTotalRecord" runat="server" Text="TotalRecord"></asp:Label>

<asp:TextBox ID="TextBox6" runat="server"></asp:TextBox>

</td>

</tr>

</table>

<asp:Button ID="btnsubmit" runat="server" Text="Submit" OnClick="btnsubmit_Click" />

<asp:Button ID="btnReset" runat="server" Text="Reset" OnClick="btnReset_Click" />

<asp:Button ID="btnDelete" runat="server" Text="Delete" />

</div>

</form>

</body>

</html>

This is .cs code of DataList.

using System;

using System.Data;

using System.Configuration;

using System.Collections;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class sapnamalik_List : System.Web.UI.Page

{

SqlDataAdapter da;

SqlConnection con;

SqlCommand cmd = new SqlCommand();

int PageSize = 3;

BlogsHelper blogsHelper = new BlogsHelper();

DataSet dataSet = new DataSet();

DataSet ds = new DataSet();

protected void Page_Load(object sender, EventArgs e)

{

if (!Page.IsPostBack)

{

GetLatestBlogs();

}

public void GetLatestBlogs()

{

BlogsHelper blogsHelper = new BlogsHelper();

DataTable dataTable = new DataTable();

dataTable = blogsHelper.GetLatestBlogs().Tables[0];

if (!object.Equals(dataTable, null))

{

if (dataTable.Rows.Count > 0)

{

if (PagingRow.Visible == true)

{

// Setting viewstate for TotalRecords

LabelTotalRecords.Text = dataTable.Rows.Count.ToString();

// Setting viewstate for TotalPages

if (int.Parse(LabelTotalRecords.Text) % PageSize > 0)

// If page size is not exactly divisible by total records

ViewState["TotalPages"] = int.Parse(LabelTotalRecords.Text) / PageSize;

else

// If page is exactly divisible by total records

ViewState["TotalPages"] = int.Parse(LabelTotalRecords.Text) / PageSize - 1;

ViewState["dataTable"] = dataTable;

if (!object.Equals(dataTable, null))

{

BindList(ManagePaging(dataTable));

}

else

BindList(dataTable);

}

else

PagingRow.Visible = false;

}

else

PagingRow.Visible = false;

}

protected void btnsubmit_Click(object sender, EventArgs e)

{

SqlConnection con;

con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);

con.Open();

SqlCommand cmd;

cmd = new SqlCommand("Insert into StudentRecord (Name,ClassName,RollNo,EmailId,TotalRecord) values('" + TextBox2.Text + "','" + TextBox3.Text + "','" + TextBox4.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "')", con);

cmd.ExecuteNonQuery();

con.Close();

}

protected void btnReset_Click(object sender, EventArgs e)

{

TextBox1.Text = "";

TextBox2.Text = "";

TextBox3.Text = "";

TextBox4.Text = "";

TextBox5.Text = "";

TextBox6.Text = "";

}

protected void LinkButtonFirst_Click(object sender, EventArgs e)

{

CurrentPage = 0;

ReloadControl();

}

protected void LinkButtonPrevious_Click(object sender, EventArgs e)

{

CurrentPage -= 1;

ReloadControl();

}

protected void LinkButtonNext_Click(object sender, EventArgs e)

{

CurrentPage += 1;

ReloadControl();

}

protected void LinkButtonLast_Click(object sender, EventArgs e)

{

CurrentPage = int.Parse(ViewState["TotalPages"].ToString());

ReloadControl();

}

protected void LinkButton1_Click(object sender, EventArgs e)

{

LinkButton lnkbtn = (LinkButton)sender;

CurrentPage = (int.Parse(lnkbtn.Text) - 1);

ReloadControl();

}

private void BindList(DataTable dataTable)

{

string photo = dataTable.Rows[0]["Photo"].ToString();

DataList1.DataSource = dataTable;

DataList1.DataBind();

}

private void BindList(PagedDataSource pageDataSource)

{

DataList1.DataSource = pageDataSource;

DataList1.DataBind();

ShowPagingLinks();

}

private void ReloadControl()

{

if (!object.Equals(ViewState["dataTable"], null))

BindList(ManagePaging((DataTable)ViewState["dataTable"]));

}

private void ShowPagingLinks()

{

if (CurrentPage == int.Parse(ViewState["TotalPages"].ToString()))

{

LinkButtonNext.Enabled = false;

LinkButtonLast.Enabled = false;

}

else

{

LinkButtonNext.Enabled = true;

LinkButtonLast.Enabled = true;

}

if (CurrentPage == 0)

{

LinkButtonPrevious.Enabled = false;

LinkButtonFirst.Enabled = false;

}

else

{

LinkButtonPrevious.Enabled = true;

LinkButtonFirst.Enabled = true;

}

private void ShowTotalNumberOfRecords()

{

int i, j;

if (CurrentPage == 0)

i = 1;

else

i = (CurrentPage * PageSize);

LabelPageFirstRecord.Text = i.ToString();

if (CurrentPage == int.Parse(ViewState["TotalPages"].ToString()))

LabelPageLastRecord.Text = LabelTotalRecords.Text;

else

{

j = ((CurrentPage + 1) * PageSize);

LabelPageLastRecord.Text = j.ToString();

}

private void ShowPageNumbers()

{

int startPagenumber, endPageNumber;

if (CurrentPage < 3)

{

startPagenumber = 1;

endPageNumber = 5;

}

else

if (CurrentPage > (int.Parse(ViewState["TotalPages"].ToString()) - 2))

{

startPagenumber = int.Parse(ViewState["TotalPages"].ToString()) - 3;

endPageNumber = int.Parse(ViewState["TotalPages"].ToString()) + 1;

if (startPagenumber == 0)

{

startPagenumber = 1;

endPageNumber += 1;

}

else

{

startPagenumber = CurrentPage - 1;

endPageNumber = CurrentPage + 3;

}

int linkButtonNumber = 1;

LinkButton lnkbtn;

for (int k = startPagenumber; k <= endPageNumber; k++)

{

lnkbtn = (LinkButton)(tdPageNumbers.FindControl("LinkButton" + linkButtonNumber.ToString()));

lnkbtn.Text = k.ToString();

linkButtonNumber++;

}

for (int idLoop = 1; idLoop <= 5; idLoop++)

{

lnkbtn = (LinkButton)(tdPageNumbers.FindControl("LinkButton" + idLoop.ToString()));

if (int.Parse(lnkbtn.Text) == (CurrentPage + 1))

{

lnkbtn.Enabled = false;

lnkbtn.CssClass = "PagerLinkSelected";

}

else
if (int.Parse(lnkbtn.Text) > (int.Parse(ViewState["TotalPages"].ToString()) + 1))

{

lnkbtn.Visible = false;

}

else

{

lnkbtn.Enabled = true;

lnkbtn.CssClass = "PagerLinkStyle";

lnkbtn.BackColor = System.Drawing.Color.Empty;

}

private PagedDataSource ManagePaging(DataTable dTable)

{

PagedDataSource pageDataSource = new PagedDataSource();

pageDataSource.DataSource = dTable.DefaultView;

pageDataSource.AllowPaging = true;

pageDataSource.PageSize = PageSize;

pageDataSource.CurrentPageIndex = CurrentPage;

ShowTotalNumberOfRecords();

ShowPageNumbers();

return pageDataSource;

}

public int CurrentPage

{

get

{

// look for current page in ViewState

object current = this.ViewState["CurrentPage"];

if (current == null)

return 0; // default page index of 0

else

return (int)current;

}

set

{

this.ViewState["CurrentPage"] = value;

}

protected void DataList1_DeleteCommand(object source, DataListCommandEventArgs e)

{

con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);

cmd.Connection = con;

int StId = (int)DataList1.DataKeys[(int)e.Item.ItemIndex];

cmd.CommandText = "Delete from StudentRecord where StId=" + StId;

cmd.Connection.Open();

cmd.ExecuteNonQuery();

cmd.Connection.Close();

DataList1.EditItemIndex = -1;

GetLatestBlogs();

}

protected void DataList1_CancelCommand(object source, DataListCommandEventArgs e)

{

DataList1.EditItemIndex = -1;

GetLatestBlogs();

}

protected void DataList1_EditCommand(object source, DataListCommandEventArgs e)

{

DataList1.EditItemIndex = e.Item.ItemIndex;

GetLatestBlogs();

}

protected void DataList1_UpdateCommand(object source, DataListCommandEventArgs e)

{

// Get the DataKey value associated with current Item Index.

int StId = (int)DataList1.DataKeys[(int)e.Item.ItemIndex];

// Get updated value entered by user in textbox control for

// Name field.

TextBox txtName;

txtName = (TextBox)e.Item.FindControl("txtName");

TextBox txtClassName;

txtClassName = (TextBox)e.Item.FindControl("txtClassName");

TextBox txtRollNo;

txtRollNo = (TextBox)e.Item.FindControl("txtRollNo");

TextBox txtEmailId;

txtEmailId = (TextBox)e.Item.FindControl("txtEmailId");

TextBox txtTotalRecord;

txtTotalRecord = (TextBox)e.Item.FindControl("txtTotalRecord");

// string variable to store the connection string

// retrieved from the connectionStrings section of web.config

con = new SqlConnection(ConfigurationManager.AppSettings["connect"]);

// sql command object initialized with update command text

cmd.Parameters.Add("@Name", SqlDbType.VarChar).Value = txtName.Text;

cmd.Parameters.Add("@ClassName", SqlDbType.VarChar).Value = txtClassName.Text;

cmd.Parameters.Add("@RollNo", SqlDbType.VarChar).Value = txtRollNo.Text;

cmd.Parameters.Add("@EmailId", SqlDbType.VarChar).Value = txtEmailId.Text;

cmd.Parameters.Add("@TotalRecord", SqlDbType.VarChar).Value = txtTotalRecord.Text;

cmd.Parameters.Add("@StId", SqlDbType.Int).Value = StId;

cmd.CommandText = "Update StudentRecord set Name=@Name,ClassName=@ClassName,RollNo=@RollNo,EmailId=@EmailId,TotalRecord=@TotalRecord where StId=" + StId;

cmd.Connection = con;

cmd.Connection.Open();

cmd.ExecuteNonQuery();

// reset the DataList mode back to its initial state

DataList1.EditItemIndex = -1;

GetLatestBlogs();

}

Output of DataList.

Up Next

Ebook Download

View all

Coding Principles

Read by 0 people

Download Now!

Learn

View all