Let's see with an example:
We are creating a table
In the aspx file put the following code
In code behind add the following code
We are creating a table
select * from [EmployeeTest]
We are Using 2 Procedures
Create proc A_GetEmployeeGetAll
As
select * from EmployeeTest
GO
Create proc A_GetEmployeeDetailsByID
@EmpID varchar(200)
As
select * from EmployeeTest where EmpID like @EmpID
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="SqlCommandBuilder.aspx.cs" Inherits="ADONET.SqlCommandBuilder" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title></title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td>EmpID :
</td>
<td>
<asp:TextBox ID="txtEmpID" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Name :
</td>
<td>
<asp:TextBox ID="txtName" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td>Designation :
</td>
<td>
<asp:TextBox ID="txtDesignation" runat="server"></asp:TextBox>
</td>
</tr>
<tr>
<td colspan="2"></td>
</tr>
<tr>
<td>
<asp:Button ID="btn_GetEmployee" runat="server" OnClick="GetEmployee_Onclick" Text="Get Employee" />
</td>
<td>
<asp:Button ID="btn_UpdateEmployee" runat="server" OnClick="UpdateEmployee_Onclick" Text="Update Employee" />
</td>
</tr>
<tr>
<td>
<asp:Button ID="btn_AddEmployee" runat="server" OnClick="AddEmployee_Onclick" Text="Add Employee"/>
</td>
<td>
<asp:Button ID="btn_DeleteEmployee" runat="server" OnClick="DeleteEmployee_Onclick" Text="Delete Employee" />
</td>
</tr>
<tr>
<td colspan="2">
<asp:Label ID="lblMessage" runat="server" Visible="false" ForeColor="Green"></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.Common;
namespace ADONET
{
public partial class SqlCommandBuilder : System.Web.UI.Page
{
public SqlDataAdapter DataAdapter { get; set; }
protected void Page_Load(object sender, EventArgs e)
{
}
protected void GetEmployee_Onclick(object sender, EventArgs e)
{
if (!(string.IsNullOrEmpty(txtEmpID.Text)))
{
int EmpID = Convert.ToInt32(txtEmpID.Text.Trim());
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeDetailsByID", sqlcon);
sqlcmd.CommandType =
System.Data.CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@EmpID", EmpID);
SqlDataAdapter adapter
= new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds);
DataTable dt = ds.Tables[0];
if (dt.Rows.Count > 0)
{
txtName.Text =
dt.Rows[0]["EmpName"].ToString();
txtDesignation.Text =
dt.Rows[0]["EmpDesignation"].ToString();
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "No data present with the given Employee ID";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Please fill Employee ID";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
protected void UpdateEmployee_Onclick(object sender, EventArgs e)
{
int UpdatedRows = 0;
if (!(string.IsNullOrEmpty(txtEmpID.Text)))
{
int EmpID = Convert.ToInt32(txtEmpID.Text.Trim());
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeDetailsByID", sqlcon);
sqlcmd.CommandType =
System.Data.CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@EmpID", EmpID);
SqlDataAdapter adapter
= new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "EmployeeTest");
if (ds.Tables[0].Rows.Count
> 0)
{
ds.Tables[0].Rows[0]["EmpName"] =
txtName.Text;
ds.Tables[0].Rows[0]["EmpDesignation"]
= txtDesignation.Text;
string strUpdate = "Update EmployeeTest set
EmpName=@EmpName,EmpDesignation=@EmpDesignation where EmpID=@EmpID";
SqlCommand UpdateCommand = new SqlCommand(strUpdate, sqlcon);
UpdateCommand.Parameters.Add("@EmpName", SqlDbType.NVarChar, 200, "EmpName");
UpdateCommand.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar, 200, "EmpDesignation");
UpdateCommand.Parameters.Add("@EmpID", SqlDbType.Int, 4, "EmpID");
adapter.UpdateCommand =
UpdateCommand;
UpdatedRows =
adapter.Update(ds, "EmployeeTest");
}
if (UpdatedRows > 0)
{
lblMessage.Visible = true;
lblMessage.Text = "Updation Successfull";
lblMessage.ForeColor =
System.Drawing.Color.Green;
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Updation Failed";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Please fill Employee ID";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
protected void AddEmployee_Onclick(object sender, EventArgs e)
{
int AddRows = 0;
if (!(string.IsNullOrEmpty(txtEmpID.Text)))
{
int EmpID = Convert.ToInt32(txtEmpID.Text.Trim());
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeDetailsByID", sqlcon);
sqlcmd.CommandType =
System.Data.CommandType.StoredProcedure;
sqlcmd.Parameters.AddWithValue("@EmpID", EmpID);
SqlDataAdapter adapter
= new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "EmployeeTest");
if (ds.Tables[0].Rows.Count
> 0)
{
DataRow dr =
ds.Tables[0].NewRow();
dr["EmpName"] =
txtName.Text;
dr["EmpDesignation"]
= txtDesignation.Text;
ds.Tables[0].Rows.InsertAt(dr, 1);
string strAdd = "insert into EmployeeTest(EmpName,EmpDesignation)
values(@EmpName,@EmpDesignation)";
SqlCommand AddCommand = new SqlCommand(strAdd, sqlcon);
AddCommand.Parameters.Add("@EmpName", SqlDbType.NVarChar, 200, "EmpName");
AddCommand.Parameters.Add("@EmpDesignation", SqlDbType.NVarChar, 200, "EmpDesignation");
adapter.InsertCommand =
AddCommand;
AddRows =
adapter.Update(ds, "EmployeeTest");
}
if (AddRows > 0)
{
lblMessage.Visible = true;
lblMessage.Text = "Row Successfully Added";
lblMessage.ForeColor =
System.Drawing.Color.Green;
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Insertion Failed";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Please fill Employee ID";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
protected void DeleteEmployee_Onclick(object sender, EventArgs e)
{
int DeletedRows = 0;
if (!(string.IsNullOrEmpty(txtEmpID.Text)))
{
int EmpID = Convert.ToInt32(txtEmpID.Text.Trim());
String CS = ConfigurationManager.ConnectionStrings["EmplyoeeDB"].ConnectionString;
using (SqlConnection sqlcon = new SqlConnection(CS))
{
SqlCommand sqlcmd = new SqlCommand("A_GetEmployeeGetAll", sqlcon);
sqlcmd.CommandType =
System.Data.CommandType.StoredProcedure;
SqlDataAdapter adapter
= new SqlDataAdapter(sqlcmd);
DataSet ds = new DataSet();
adapter.Fill(ds, "EmployeeTest");
if (ds.Tables[0].Rows.Count
> 0)
{
ds.Tables[0].PrimaryKey
= new DataColumn[] {
ds.Tables[0].Columns["EmpID"] };
DataRow dr =
ds.Tables[0].Rows.Find(EmpID);
dr.Delete();
string strDelete = "delete from EmployeeTest where EmpID=@EmpID";
SqlCommand DeleteCommand = new SqlCommand(strDelete, sqlcon);
DeleteCommand.Parameters.Add("@EmpID", SqlDbType.Int, 4, "EmpID");
adapter.DeleteCommand =
DeleteCommand;
DeletedRows =
adapter.Update(ds, "EmployeeTest");
}
if (DeletedRows > 0)
{
lblMessage.Visible = true;
lblMessage.Text = "Row Successfully Deleted";
lblMessage.ForeColor =
System.Drawing.Color.Green;
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Deletion Failed";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
}
else
{
lblMessage.Visible = true;
lblMessage.Text = "Please fill Employee ID";
lblMessage.ForeColor =
System.Drawing.Color.Red;
}
}
}
}
Lets Execute our application we will get the following output
Enter the EmployeeID and Click on GetEmployee
Posts
0 comments:
Post a Comment