DML and DDL operation in DataList
In this article I am going to explain about insert,delete, update and select operation in DataList control.
DataList
The DataList control has been around since the beginning of ASP.NET. It is part of a series of controls that enable you to display your data using template. Templates enable you to create more sophisticated layouts for your data and perform functions that controls such as the GridView server cannot. DataList basically used three types of templates and these templates also contains sub templates, which is given below:
In this article I am going to explain about insert,delete, update and select operation in DataList control.
DataList
The DataList control has been around since the beginning of ASP.NET. It is part of a series of controls that enable you to display your data using template. Templates enable you to create more sophisticated layouts for your data and perform functions that controls such as the GridView server cannot. DataList basically used three types of templates and these templates also contains sub templates, which is given below:
1.
Item
Templates
It have contains following templates:
It have contains following templates:
·
ItemTemplate
The core template that is used to define a row or layout for each item
The core template that is used to define a row or layout for each item
in the display.
·
AlternatingItemTemplate
Works in conjunction with the ItemTemplate to provide a layout for
all the odd rows within the layout. This is commonly used if you want
to have a grid or layout where each row is distinguished in some way
(such as having a different background color).
Works in conjunction with the ItemTemplate to provide a layout for
all the odd rows within the layout. This is commonly used if you want
to have a grid or layout where each row is distinguished in some way
(such as having a different background color).
·
SelectedItemTemplate
It allows for a row or item to be defined on how it looks and behaves
when selected.
It allows for a row or item to be defined on how it looks and behaves
when selected.
·
EditItemTemplate
It allows for a row or item to be defined on how it looks and behaves when editing.
It allows for a row or item to be defined on how it looks and behaves when editing.
2.
Header
and Footer Templates
It have contains following templates:
It have contains following templates:
·
HeaderTemplate
It allows the first item in the template to be defined. If this is not
defined, then no header will be used.
It allows the first item in the template to be defined. If this is not
defined, then no header will be used.
·
FooterTemplete
It allows the last item in the template to be defined. If this is not
defined, then no footer will be used.
It allows the last item in the template to be defined. If this is not
defined, then no footer will be used.
3.
Separator
Template
It does not contains any sun templates:
The layout of any separator that is used between the items in
the display.
It does not contains any sun templates:
The layout of any separator that is used between the items in
the display.
In this example I have only used header and itemTemplate.
There are following steps, which are used in this example of DataList:
There are following steps, which are used in this example of DataList:
Step [1] It is design image table which have stored the
employee details:
Step [2] Source coding of DataList control
|
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default"
%>
<!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></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:DataList ID="DataList1" runat="server"
onitemcommand="DataList1_ItemCommand" EnableViewState="true">
<HeaderTemplate>
<table>
<tr>
<td> <asp:TextBox ID="t1" runat="server"
Text="EmpId"
BackColor="Aqua"
ForeColor="Red"></asp:TextBox></td>
<td> <asp:TextBox ID="t2" runat="server"
Text="EmpNmae"
BackColor="Aqua"
ForeColor="Red"></asp:TextBox></td>
<td> <asp:TextBox ID="t3" runat="server"
Text="EmpSalary"
BackColor="Aqua"
ForeColor="Red"></asp:TextBox></td>
</tr>
</table>
</HeaderTemplate>
<ItemTemplate>
<table>
<tr>
<td> <asp:TextBox ID="t4" runat="server" BackColor="Azure" ForeColor="Blue" ReadOnly="true" Text=<%#Eval("id")%>/></asp:TextBox></td>
<td> <asp:TextBox ID="t5" runat="server" BackColor="Azure" ForeColor="Blue" ReadOnly="true" Text=<%#Eval("name")%>/></asp:TextBox></td>
<td><asp:TextBox ID="t6" runat="server"
BackColor="Azure"
ForeColor="Blue"
ReadOnly="true"
Text=<%#Eval("salary")%>/></asp:TextBox></td
</tr>
<td>
<asp:Button ID="b1" runat="server" BackColor="Yellow"
Width="55"
Text="Edit"
CommandName="edit"/>
</td>
<td>
<asp:Button ID="b2" runat="server" BackColor="Yellow"
Width="55"
Text="Select"
CommandName="sel"/>
</td>
<td>
<asp:Button ID="b3" runat="server" BackColor="Yellow"
Width="55"
Text="Delete"
CommandName="del"/>
</td>
<td>
<asp:Button ID="b4" runat="server" BackColor="Yellow"
Width="55"
Text="New"
CommandName="new"/>
</td>
</table>
</ItemTemplate>
</asp:DataList>
</div>
</form>
</body>
</html>
|
Steps [3] Default.aspx page 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
{
SqlDataAdapter
_da;
DataSet
_ds;
SqlConnection
_con;
SqlCommand
_cmd;
protected
void Page_Load(object
sender, EventArgs e)
{
if
(!IsPostBack)
{
GetData();
}
}
public void GetData()
{
_da = new
SqlDataAdapter("select
* from emp", "Data
Source=.;Initial Catalog=employees;User ID=sa;pwd=*********;");
_ds = new
DataSet();
_da.Fill(_ds);
DataList1.DataSource = _ds.Tables[0];
DataList1.DataBind();
}
public void save(string s)
{
_cmd = new
SqlCommand(s);
_con = new
SqlConnection("Data
Source=.;Initial Catalog=employees;User ID=sa;pwd=sharad;");
_con.Open();
_cmd.Connection = _con;
_cmd.ExecuteNonQuery();
_con.Close();
}
protected
void DataList1_ItemCommand(object source, DataListCommandEventArgs
e)
{
int
i= e.Item.ItemIndex;
if
(e.CommandName == "edit")
{
Button
b = (Button)DataList1.Items[i].FindControl("b1");
Button
bb = (Button)DataList1.Items[i].FindControl("b2");
TextBox
t = (TextBox)DataList1.Items[i].FindControl("t4");
TextBox
tt = (TextBox)DataList1.Items[i].FindControl("t5");
TextBox
ttt = (TextBox)DataList1.Items[i].FindControl("t6");
t.ReadOnly = false;
tt.ReadOnly = false;
ttt.ReadOnly = false;
b.Text = "Cancel";
bb.Text = "Update";
b.CommandName="can";
bb.CommandName = "up";
}
else if (e.CommandName == "can")
{
GetData();
}
else if (e.CommandName == "up")
{
TextBox
t = (TextBox)DataList1.Items[i].FindControl("t4");
TextBox
tt = (TextBox)DataList1.Items[i].FindControl("t5");
TextBox
ttt = (TextBox)DataList1.Items[i].FindControl("t6");
string
s ="update emp set id = '" +
t.Text + "' , name= '" + tt.Text
+ "' , salary ='" + ttt.Text + "' where id= '" + t.Text + "'";
save(s);
GetData();
}
else if (e.CommandName == "del")
{
TextBox
t = (TextBox)DataList1.Items[i].FindControl("t4");
string
s = "delete from emp where id='"
+ t.Text + "'";
save(s);
GetData();
}
else if (e.CommandName == "new")
{
Button
bins = (Button)DataList1.Items[i].FindControl("b4");
TextBox
t = (TextBox)DataList1.Items[i].FindControl("t4");
TextBox
tt = (TextBox)DataList1.Items[i].FindControl("t5");
TextBox
ttt = (TextBox)DataList1.Items[i].FindControl("t6");
t.ReadOnly = false;
tt.ReadOnly = false;
ttt.ReadOnly = false;
t.Text = "";
tt.Text = "";
ttt.Text = "";
bins.Text = "Insert";
bins.CommandName = "ins";
}
else if (e.CommandName == "ins")
{
TextBox
t = (TextBox)DataList1.Items[i].FindControl("t4");
TextBox
tt = (TextBox)DataList1.Items[i].FindControl("t5");
TextBox
ttt = (TextBox)DataList1.Items[i].FindControl("t6");
string
s = "insert into emp values('" +
t.Text + "','" + tt.Text + "','" + ttt.Text + "' )";
save(s);
GetData();
}
}
}
|
Steps [4] Datalist Select operation Image
Steps [5] Datalist Edit operation Image
Steps [6] Datalist Update operation Image
Steps [7] Datalist Insert operation Image
Steps [8] Datalist Insert operation Image
