Since this is all about using Stored Procedures to conduct actions on SQL tables, I will walk you through each step of using a DataList control to insert, select, edit, cancel, update, and remove data.
First Step: SQL Database
To add a data value to an EmployeeData table, first build a table in a database store in the manner shown below.
Table: EmployeeData
Create Table EmployeeData (
EmpID int identity (1, 1) Primary Key,
EmpName varchar(30),
Contact nchar(15),
EmailId nvarchar(50)
)
Stored Procedure:
Also create an insert procedure, a select procedure, an update procedure and a delete procedure as in the following.
Select procedure for DataList control.
create procedure sp_FillData
As
Begin
set nocount on;
select EmpID, EmpName, Contact, EmailID from EmployeeData
End
Insert procedure.
create procedure sp_InsertEmployeeData @EmpName varchar(30),
@Contact nchar(15),
@EmailId nvarchar(50) As Begin
set
nocount on;
Insert into dbo.EmployeeData (EmpName, Contact, EmailId)
values
(@EmpName, @Contact, @EmailId) End
Delete procedure.
Create procedure sp_DeleteEmployeeData
@EmpID int
As
Begin
set nocount on;
Delete from EmployeeData where EmpID=@EmpID
End
Select procedure.
create procedure sp_SelectEmployeeData
@EmpID int
As
Begin
set nocount on;
select EmpID, EmpName, Contact, EmailID from EmployeeData where EmpID=@EmpID
End
Update procedure.
Create procedure sp_UpdateEmployeeData @EmpID int,
@EmpName varchar(30),
@Contact nchar(15),
@EmailId nvarchar(50) As Begin
set
nocount off;
UPDATE
Employeedata
SET
EmpName = @EmpName,
Contact = @Contact,
EmailId = @EmailId
WHERE
EmpID = @EmpID End
Step 2: Maintain database connection
Provide the database connection string inside the project's Web.Config as in the following:
<connectionStrings>
<add name="connstring" connectionString="Data Source=RAKESH-PC;Initial Catalog=SqlServerTech;User ID=sa;Password=password" providerName="System.Data.SqlClient"/>
</connectionStrings>
Step 3: UI design
In Visual Studio create a UI design using the following procedure:
Go to Solution Explorer.
Right-click on the project and click Add tab.
Click Add New Item as in the following:
Figure 1: Add web form
Now I will write the design code inside DataList.aspx. In this page add some TextBox controls, Button controls and a DataList Control.
Also add two button controls and TextBox controls inside the DataList data row update and delete with DataList event Item commands. The following is the UI design code.
<%@ Page Title="" Language="C#" MasterPageFile="~/Master/Master.master" AutoEventWireup="true" CodeFile="DataList.aspx.cs" Inherits="UI_DataList" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" Runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">
<div>
<fieldset style="width: 269px" ><legend><b>DataList Example</b></legend>
<div style="width: 250px; background-color: #99FF66;">
<asp:Table runat="server">
<asp:TableRow>
<asp:TableCell>Name</asp:TableCell><asp:TableCell><asp:TextBox ID="txtName" runat="server"></asp:TextBox ></asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Contact</asp:TableCell><asp:TableCell><asp:TextBox ID="txtContact" runat="server"></asp:TextBox></asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell>Email id</asp:TableCell><asp:TableCell><asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></asp:TableCell>
</asp:TableRow>
<asp:TableRow>
<asp:TableCell></asp:TableCell><asp:TableCell><asp:Button ID="btnSave" Text="Add Record" runat="server" OnClick="btnSave_Click" /></asp:TableCell>
</asp:TableRow>
</asp:Table>
</div>
</fieldset>
<br />
<fieldset style="width: 535px"><legend><b>Employee Information</b></legend>
<div style="background-color: #66FF66">
<asp:DataList ID="DataListEmp" runat="server"
DataKeyField="EmpID"
OnDeleteCommand="DataListEmp_DeleteCommand"
OnEditCommand="DataListEmp_EditCommand"
OnUpdateCommand="DataListEmp_UpdateCommand"
OnCancelCommand="DataListEmp_CancelCommand" Width="527px" >
<HeaderTemplate>
<table><tr style="background-color: #800000; color: #FFFFFF">
<th>Name</th><th>Contact</th><th>Email ID</th><th>Action</th></tr>
</HeaderTemplate>
<ItemTemplate>
<tr >
<td><%# DataBinder.Eval(Container.DataItem, "EmpName")%></td>
<td><%# DataBinder.Eval(Container.DataItem,"Contact")%></td>
<td><%# DataBinder.Eval(Container.DataItem, "EmailId")%></td>
<td><asp:Button ID="imgbtnedit" runat="server" Text="Edit" ToolTip="Edit" CommandName="Edit"/></td>
<td><asp:Button ID="btndelete" runat="server" Text="Delete" CommandName="Delete" ToolTip="Delete"/></td>
</tr>
</ItemTemplate>
<EditItemTemplate>
<tr>
<td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtName" runat="server" Text='<%# Eval("EmpName") %>'></asp:TextBox></td>
<td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtContact" runat="server" Text='<%# Eval("Contact") %>'></asp:TextBox></td>
<td><asp:TextBox BackColor="Yellow" Font-Bold="true" ID="txtEmail" runat="server" Text='<%# Eval("EmailId") %>'></asp:TextBox></td>
<td><asp:Button ID="btnupdate" runat="server" ToolTip="Update" Text="Update" CommandName="Update" /></td>
<td><asp:Button ID="btncancel" runat="server" ToolTip="Cancel" Text="Cancel" CommandName="Cancel" /></td>
</tr>
</EditItemTemplate>
</asp:DataList>
</div>
</fieldset>
</div>
</asp:Content>
Step 4: UI code
Here is all about an operation on a DataList row records.
In this first write code for the TextBox value insertion into the SQL table by the preceding Insert Procedure, then write code for the inserted record display in the DalaList control. Write the code for two buttons, one is the delete and the second is an update button code.
Now create an update command, an edit command, a cancel command, an Item command according to the following Figure 3 and also maintain a datakeyfield in the property data section as in Figure 2.
Figure 2: Add Data Key Field
Figure 3: Add Action Command
Code
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
public partial class UI_DataList : System.Web.UI.Page
{
string connection = ConfigurationManager.ConnectionStrings["connstring"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
GetEmpDataList();
}
}
protected void btnSave_Click(object sender, EventArgs e)
{
using (SqlConnection con = new SqlConnection(connection))
{
using (SqlCommand cmd = new SqlCommand("sp_InsertEmployeeData", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Clear();
Response.Write("<script type=\"text/javascript\">alert('Record Inserted Successfully');</script>");
GetEmpDataList();
}
}
}
void Clear()
{
txtName.Text = String.Empty;
txtContact.Text = String.Empty;
txtEmail.Text = String.Empty;
}
private void GetEmpDataList()
{
using (SqlConnection con = new SqlConnection(connection))
{
SqlDataAdapter sd = new SqlDataAdapter("sp_FillData", con);
sd.SelectCommand.CommandType = CommandType.StoredProcedure;
DataTable dt = new DataTable();
sd.Fill(dt);
if (dt.Rows.Count > 0)
{
DataListEmp.DataSource = dt;
DataListEmp.DataBind();
}
}
}
protected void DataListEmp_DeleteCommand(object source, DataListCommandEventArgs e)
{
int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());
using (SqlConnection con = new SqlConnection(connection))
{
using (SqlCommand cmd = new SqlCommand("sp_DeleteEmployeeData", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpID",EmpID);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Response.Write("<script type=\"text/javascript\">alert('Record Deleted Successfully');</script>");
GetEmpDataList();
}
}
}
protected void DataListEmp_EditCommand(object source, DataListCommandEventArgs e)
{
DataListEmp.EditItemIndex = e.Item.ItemIndex;
GetEmpDataList();
}
protected void DataListEmp_CancelCommand(object source, DataListCommandEventArgs e)
{
DataListEmp.EditItemIndex = -1;
GetEmpDataList();
}
protected void DataListEmp_UpdateCommand(object source, DataListCommandEventArgs e)
{
int EmpID = Convert.ToInt32(DataListEmp.DataKeys[e.Item.ItemIndex].ToString());
TextBox txtName = (TextBox)e.Item.FindControl("txtName");
TextBox txtContact = (TextBox)e.Item.FindControl("txtContact");
TextBox txtEmail = (TextBox)e.Item.FindControl("txtEmail");
using (SqlConnection con = new SqlConnection(connection))
{
using (SqlCommand cmd = new SqlCommand("sp_UpdateEmployeeData", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EmpID", EmpID);
cmd.Parameters.AddWithValue("@EmpName", SqlDbType.VarChar).Value = txtName.Text.Trim();
cmd.Parameters.AddWithValue("@Contact", SqlDbType.NChar).Value = txtContact.Text.Trim();
cmd.Parameters.AddWithValue("@EmailId", SqlDbType.NVarChar).Value = txtEmail.Text.Trim();
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Clear();
Response.Write("<script type=\"text/javascript\">alert('Record Update Successfully');</script>");
DataListEmp.EditItemIndex = -1;
GetEmpDataList();
}
}
}
}
Step 5: Browser Side
Now run your new page in the browser. I hope you understood how to work with the DataList control Action Command Event and SQL Procedures.
Have a nice day.
HostForLIFEASP.NET SQL Server 2022 Hosting
