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>
<add key="connect" value="Initial Catalog=Data; Data Source=DHARMENDRA\SQLSERVER2005; uid=sa; pwd=wintellect"/>
</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">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>DataList</title>
</head>
<body>
<form id="form1" runat="server">
<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">
<HeaderTemplate>
<table width="100%">
<tr align="left">
<th width="10%">
StId
</th>
<th width="10%">
Name
</th>
<th width="10%">
ClassName
</th>
<th width="10%">
RollNo
</th>
<th width="10%">
EmailId
</th>
<th width="10%">
TotalRecord
</th>
<th>
</th>
</tr>
</HeaderTemplate>
<ItemTemplate>
<tr align="left">
<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>
<EditItemTemplate>
<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>
<FooterTemplate>
</table>
</FooterTemplate>
<FooterStyle BackColor="Tan" />
<SelectedItemStyle BackColor="DarkSlateBlue" ForeColor="GhostWhite" /> <AlternatingItemStyle BackColor="PaleGoldenrod" />
<HeaderStyle BackColor="Tan" Font-Bold="True" />
</asp:DataList>
<table width="100%">
<tr>
<td height="10px">
</td>
</tr>
<tr id="PagingRow" runat="server">
<td colspan="3" style="background-color: #3D3D3D; padding-left: 15px;">
<table width="99%" class="pagingBackground" cellpadding="2" cellspacing="2">
<tr>
<td width="20%">
</td>
<td align="center" width="50">
</td>
<td align="center">
</td>
<td align="center" width="20%">
</td>
</tr>
<tr>
<td width="20%" align="left">
<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 width="50" align="center">
</td>
<td align="center" id="tdPageNumbers" runat="server">
<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 width="20%" align="right">
</td>
</tr>
<tr>
<td width="20%">
</td>
<td align="center" width="50">
</td>
<td align="center">
</td>
<td align="center" width="20%">
</td>
</tr>
</table>
</td>
</tr>
</table>
<br />
<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.
