Performing Insert ,Update,Delete and Load operations onto the database using SQL authentication.
This article explains step by step process how to perform Insert, Update, Delete and Load operations onto the database.
Step-1
Create Database and Tables:
1. Open SQL Server Management studio and login either using Windows authentication or SQL Server authentication.
2. Create a new database by right clicking on the database in Object Explorer and give your Database name.
3. Create a table “Employee” with fields “EmployeeNo(int), EmployeeName(varchar(max)),Designation(varchar(max)) and EmployeeSalary(int).
4. Open“Employee” table in the database and add sample data as follows.
Step-2
Create Website:
5. Open Microsoft Visual Studio go to File menu >> click on New Website it opens a window then choose “ASP.NET Website” template from the list of available templates and choose language as C# and location as File System.
6. Go to web.config file and add connection string as follows:
|
<connectionStrings >
<add name ="ConnectionString" connectionString ="Server=CHINNU-PC;Database=employeedatabase;User ID=sa;Password=sql2005"/>
</connectionStrings>
|
Following are the parameters used in connection string.
|
Name |
Connection string name |
|
Server |
Database Server name |
|
Database |
Database name |
|
User ID |
User id |
|
Password |
Password |
To connect to the SqlServer using windows authentication as follows
|
<connectionStrings >
<add name ="ConnectionString" connectionString ="Server=CHINNU-PC;Database=employeedatabase/>
</connectionStrings> |
Add the following code to add GridView in default.aspx page.
|
<asp:GridView ID=”gvEmployees" runat="server" "></asp:GridView>
|
Step-3
Fetching the data from the database
Create a Stored Procedure for selecting the data from a table in database as below.
|
CREATE PROCEDURE SelectEmpDetails
AS
BEGIN
SET NOCOUNT ON
SELECT EmployeeNo, EmployeeName, Designation, EmployeeSalary FROM Employee
END
GO |
Call the Stored Procedure from C# and bind output to GridView
|
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Collections ;
using System.Configuration;
protected void load(object sender, EventArgs e)
{ string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
SqlCommand comm = new SqlCommand();
comm.CommandText = "SelectEmpDetails";
comm.CommandType = CommandType.StoredProcedure;
comm.Connection = con;
try
{
con.Open();
SqlDataAdapter sqladap = new SqlDataAdapter(comm);
DataSet ds = new DataSet();
sqladap.Fill(ds);
GridView1.DataSource = ds;
GridView1.DataBind();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
comm.Dispose();
con.Dispose();
}
}
|
1. In the above code a variable connstring is taken and assigned with the connection string from web.config file.
2. Create the object for SqlConnection class by passing the connection string to the constructor.
3. Create the object for SqlCommand class .
4. Set the StoredProcedure name(“SelectEmpDetails”) to the CommandText property of command object.
5. Set CommandType as StoredProcedure to the CommandType property(Enum type) of command object.
6. Create the object for SqlDataAdapter class by passing command object to the constructor.
7. Create the object for DataSet class.
8. Load data into DataSet by calling Fill method of SqlDataAdapter object.
9. Set DataSet object to the DataSource property of GridView.
10. DataBind method will bind the data to the table in GridView.
Here we are selecting the data from the database and displaying in a GridView as follows:
Insert the data into Database
Create Store Procedure to insert the data into the database
|
CREATE PROCEDURE InsertEmplDetails |
The above command will create the stored procedure.
Add the parameters for the stored procedure here
|
@EmployeeNo int,
@EmployeeName varchar(50),
@Designation varchar(50),
@EmployeeSalary int |
AS
BEGIN
SET NOCOUNT ON
|
INSERT INTO Employee
(EmployeeNo,
EmployeeName,
Designation,
EmployeeSalary)
VALUES(
@EmployeeNo,
@EmployeeName,
@Designation,
@EmployeeSalary)
END
GO
|
The following view is to enter the values in textboxes to insert into the database.
Following is the code snippet for executing insert stored procedure by passing user entered values as input parameters.
|
protected void Insert(object sender, EventArgs e)
{
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
SqlCommand comm = new SqlCommand();
comm.CommandText = "InsertEmplDetails";
comm.CommandType = CommandType.StoredProcedure;
comm.Connection = con;
comm.Parameters.AddWithValue("@EmployeeNo", TxtEmployeeNo.Text);
comm.Parameters.AddWithValue("@EmployeeName", TxtEmployeeName.Text);
comm.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
comm.Parameters.AddWithValue("@EmployeeSalary", TxtEmployeeSalary.Text);
try
{
con.Open();
comm.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
comm.Dispose();
con.Dispose();
}
GridView1.DataBind();
}
|
1. In the above code a variable connstring is taken and assigned with the connection string from web.config file.
2. Create the object for SqlConnection class by passing the connection string to the constructor.
3. Create the object for SqlCommand class .
4. Set the StoredProcedure name(SelectEmpDetails) to the CommandText property of command object.
5. Set CommandType as StoredProcedure to the CommandType property(Enum type) of command object.
6. Add the parameters to add the textbox values to the command object .
using try,catch ,finally blocks here inorder to handle errors in a block of code.In this try block, connection is opened to connect to sqlserver ,executes the query and in finally block closes the connection ,dispose the command and dispose the connection. DataBind method will bind the data to the table in GridView.
Create stored procedure to Update the data into the database.
|
CREATE PROCEDURE UpdateEmpDetails |
-- Add the parameters for the stored procedure here
|
@EmployeeNo int,
@EmployeeName varchar(50),
@Designation varchar(50),
@EmployeeSalary int
|
AS
BEGIN
SET NOCOUNT ON
|
UPDATE Employee
SET EmployeeName=@EmployeeName,
Designation=@Designation,
EmployeeSalary=@EmployeeSalary
WHERE EmployeeNo=@EmployeeNo
END
GO
|
Update the information by using Employee number
|
protected void Update(object sender, EventArgs e)
{
try
{
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
SqlCommand comm = new SqlCommand();
comm.CommandText = "UpdateEmpDetails";
comm.CommandType = CommandType.StoredProcedure;
comm.Connection = con;
comm.Parameters.AddWithValue("@EmployeeNo", TxtEmployeeNo.Text);
comm.Parameters.AddWithValue("@EmployeeName", TxtEmployeeName.Text);
comm.Parameters.AddWithValue("@Designation", TxtDesignation.Text);
comm.Parameters.AddWithValue("@EmployeeSalary", TxtEmployeeSalary.Text);
try
{
con.Open();
comm.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
comm.Dispose();
con.Dispose();
}
//GridView will bind the data from the table in the database.
GridView1.DataBind();
}
|
1. In the above code a variable connstring is taken and assigned with the connection string from web.config file.
2. Create the object for SqlConnection class by passing the connection string to the constructor.
3. Create the object for SqlCommand class .
4. Set the StoredProcedure name(SelectEmpDetails) to the CommandText property of command object.
5. Set CommandType as StoredProcedure to the CommandType property(Enum type) of command object.
6. Add the parameters to add the textbox values to the command object .
using try,catch ,finally blocks here inorder to handle errors in a block of code.In this try block, connection is opened to connect to sqlserver ,executes the query and the finally block closes the connection ,dispose the command and dispose the connection. DataBind method will bind the data to the table in GridView.
Delete the data from database
create storedprocedure to Delete the data in the database
|
CREATE PROCEDURE DeleteEmpDetails
|
-- Add the parameters for the stored procedure here
AS
BEGIN
SET NOCOUNT ON;
|
DELETE FROM Employee WHERE EmployeeNo=@EmployeeNo
END
GO
|
The following view is to delete the employee record by passing employee number.
|
protected void Delete(object sender, EventArgs e)
{
string constring = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
SqlConnection con = new SqlConnection(constring);
SqlCommand comm = new SqlCommand();
comm.CommandText = "DeleteEmpDetails";
comm.CommandType = CommandType.StoredProcedure;
comm.Connection = con;
comm.Parameters.AddWithValue("@EmployeeNo", TxtEmployeeNo.Text );
try
{
con.Open();
comm.ExecuteNonQuery();
}
catch (Exception)
{
throw;
}
finally
{
con.Close();
comm.Dispose();
con.Dispose();
}
GridView1.DataBind();
}
|
1. In the above code a variable connstring is taken and assigned with the connection string from web.config file.
2. Create the object for SqlConnection class by passing the connection string to the constructor.
3. Create the object for SqlCommand class .
4. Set the StoredProcedure name(SelectEmpDetails) to the CommandText property of command object.
5. Set CommandType as StoredProcedure to the CommandType property(Enum type) of command object.
6. Add the parameters to add the textbox values to the command object .
using try,catch ,finally blocks here inorder to handle errors in a block of code.In this try block, connection is opened to connect to sqlserver ,executes the query and the finally block closes the connection ,dispose the command and dispose the connection. DataBind method will bind the data to the table in GridView.
Conclusion
By using this simple code we can easily perform operations such as Insert,Update,Delete,Load onto the Database.