How to perform Insert,Update,Delete and Load operations onto the Database

Swethaperugu
Posted by in ASP.NET category on for Beginner level | Points: 150 | Views : 5864 red flag
Rating: 3 out of 5  
 1 vote(s)

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.

 

Update the data into the database

 

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

@EmployeeNo int

 

         

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.

 

Page copy protected against web site content infringement by Copyscape

About the Author

Swethaperugu
Full Name: swethaperugu perugu
Member Level: Starter
Member Status: Member
Member Since: 10/6/2010 4:35:56 AM
Country: India



Login to vote for this post.

Comments or Responses

Posted by: Samarmir on: 10/24/2010 | Points: 10
Thanks for this very nice article.
However I can't see where you mention the "Load operation". Or have I misunderstood something.

Sincerely

Mohammad
Posted by: Chvrsri on: 10/26/2010 | Points: 10
Hi Swetha,

Nice post ......!!!!
Posted by: S.mittall99 on: 1/31/2011 | Points: 25
hi,
this article is very good. but i found some problem when using. You have not mention how to bind delete, update & insert methods in the main program or how will they execute. In the coding you are using TXTEMPLOYEENO.TEXT etc. where are these text boxes. Plz. help.

Login to post response

Comment using Facebook(Author doesn't get notification)