Learn C# ASP.NET MVC WCF SQL Angular: c# datalist

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:

1. Item Templates

It have contains following templates:

· ItemTemplate

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).

· SelectedItemTemplate

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.

2. Header and Footer 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.

· FooterTemplete
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.

In this example I have only used header and itemTemplate.

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">

</head>

<body>

<div>

<asp:DataList ID="DataList1" runat="server"

onitemcommand="DataList1_ItemCommand" EnableViewState="true">

<table>

<tr>

</tr>

</table>

</HeaderTemplate>

<table>

<tr>

<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

Learn C# ASP.NET MVC WCF SQL Angular

Saturday, January 3, 2015

Insert Update Delete in DataList