This demo is a continuation of my previous example “Binding GridView with Data”. If you are not familiar of binding the GridView the ado.net way then I would suggest looking at my previous example first before you proceed reading  this example.

Basically, this demo describes the basic way on how to do INSERT, EDIT, UPDATE  and DELETE data in ASPNET GridView Control using the ADO.NET way. 

STEP 1: Creating a Database Table

In this demo, I presumed that you already have a basic background on how to create a simple database table. In this example, I used my own database called SampleDB which has Customers Table and basically contains the following field columns:

CustomerID – PK

CompanyName

ContactName

ContactTitle

Address

Country

 

STEP 2: Setting Up the Connection String

      <connectionStrings>

            <add name="DBConnection" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\SampleDB.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>

      </connectionStrings>

 

   

         

STEP 3: Setting up the GUI

Just for the simplicity of this demo, I set up the GUI like this:

   

   

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

    <title>GridView Data Manipulation</title>

</head>

<body>

    <form id="form1" runat="server">

    <div>

        <table cellpadding="0" cellspacing="0">

            <tr>

                <td style="width: 100px; height: 19px;">

                    Company ID</td>

                <td style="width: 100px; height: 19px;">

                    Company</td>

                <td style="width: 100px; height: 19px;">

                    Name</td>

                <td style="width: 100px; height: 19px;">

                    Title</td>

                <td style="width: 100px; height: 19px;">

                    Address</td>

                <td style="width: 100px; height: 19px;">

                    Country</td>

            </tr>

            <tr>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox1" runat="server"/></td>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox2" runat="server"/></td>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox3" runat="server"/></td>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox4" runat="server"/></td>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox5" runat="server"/></td>

                <td style="width: 100px">

                    <asp:TextBox ID="TextBox6" runat="server"/></td>

                <td style="width: 100px">

                    <asp:Button ID="Button1" runat="server" Text="Add New" OnClick="Button1_Click" /></td>

            </tr>

        </table>

       

        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="false" ShowFooter="true">

        <Columns>

            <asp:BoundField DataField="CustomerID" HeaderText="ID" ReadOnly="true"/>

            <asp:BoundField DataField="CompanyName" HeaderText="Company"/>

            <asp:BoundField DataField="ContactName" HeaderText="Name"/>

            <asp:BoundField DataField="ContactTitle" HeaderText="Title" />

            <asp:BoundField DataField="Address" HeaderText="Address"/>

            <asp:BoundField DataField="Country" HeaderText="Country"/>

        </Columns>

        </asp:GridView>

    </div>

    </form>

</body>

</html>

 

Note:  I have set the CustomerID field to ReadOnly so that the field cannot be edited.

STEP 4: Binding GridView with Data

I will not elaborate on this step because I already describe the details in my previous example about “Binding GridView with Data”. Here are the code blocks for binding the GridView.

public partial class _Default : System.Web.UI.Page

{

    protected void Page_Load(object sender, EventArgs e)

    {

        if (!IsPostBack)

        {

            BindGridView();

        }

    }

 

    private string GetConnectionString()

    {

        return System.Configuration.ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString;

    }

 

    #region Bind GridView

    private void BindGridView()

    {

        DataTable dt = new DataTable();

        SqlConnection connection = new SqlConnection(GetConnectionString());

        try

        {

            connection.Open();

            string sqlStatement = "SELECT Top(10)* FROM Customers";

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            SqlDataAdapter sqlDa = new SqlDataAdapter(cmd);

 

              sqlDa.Fill(dt);

              if (dt.Rows.Count > 0)

              {

                GridView1.DataSource = dt;

                GridView1.DataBind();

              }

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

                string msg = "Fetch Error:";

                msg += ex.Message;

                throw new Exception(msg);

        }

        finally

        {

            connection.Close();

        }

    }

    #endregion

}

 

Now, we already know how to bind our GridView with data from database. So let’s proceed on adding a new data in GridView.



STEP 5: Adding New Data in GridView

As you have noticed in STEP 2, we have added six TextBox and a Button in the web form in order for us to type the information there and Insert them to the database. Now let’s create a method for executing the Update or Insert.

Here are the code blocks for our Insert and Update method in the code behind:

#region Insert New or Update Record

    private void UpdateOrAddNewRecord(string ID, string Company, string Name, string Title, string Address, string Country, bool isUpdate)

    {

        SqlConnection connection = new SqlConnection(GetConnectionString());

        string sqlStatement = string.Empty;

 

        if (!isUpdate)

        {

            sqlStatement = "INSERT INTO Customers"+

"(CustomerID,CompanyName,ContactName,ContactTitle,Address,Country)" +

"VALUES (@CustomerID,@CompanyName,@ContactName,@ContactTitle,@Address,@Country)";

        }

        else

        {

            sqlStatement = "UPDATE Customers" +

                           "SET CompanyName = @CompanyName,

                           ContactName = @ContactName," +

                           "ContactTitle = @ContactTitle,Address = 

                           @Address,Country = @Country" +

                           "WHERE CustomerID = @CustomerID,";

        }

        try

        {

            connection.Open();

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            cmd.Parameters.AddWithValue("@CustomerID", ID);

            cmd.Parameters.AddWithValue("@CompanyName", Company);

            cmd.Parameters.AddWithValue("@ContactName", Name);

            cmd.Parameters.AddWithValue("@ContactTitle", Title);

            cmd.Parameters.AddWithValue("@Address", Address);

            cmd.Parameters.AddWithValue("@Country", Country);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Insert/Update Error:";

            msg += ex.Message;

            throw new Exception(msg);

 

        }

        finally

        {

            connection.Close();

        }

    }

    #endregion

 



The UpdateOrAddNewRecord is a method that takes seven parameters.  Six of those parameters basically come from the TextBox values that were entered in the page. The last parameter is a boolean value which tells the method whether to execute an Insert (false) or Update (true). Default is true.

Here’s the code block for calling the method UpdateOrAddNewRecord on Button_Click event and pass the corresponding parameters needed:



    protected void Button1_Click(object sender, EventArgs e)

    {

        UpdateOrAddNewRecord(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text, TextBox6.Text, false);

        //Re Bind GridView to reflect changes made

        BindGridView();

    }

 

As you can see from above, We have called the BindGridView() method again in order to reflect the changes made and display the new added data in the GridView. See output below with red mark.


Click to enlarge the image

ASP.Net Gridview Insert Edit Delete Update using ADO.Net



STEP 6: Edit and Update Records In GridView

One of the good things about GridView is that it provides a built-in CommandField Buttons which allows us to perform certain actions like editing, updating,deleting and selecting of GridView data.

To add those command fields mentioned in the GridView you can follow these few steps below:

1.       Switch to Design View

2.       Right Click on the GridView and Select  --> Show Smart Tag --> Add New Columns

3.       On the List Select CommandField

4.       Check Delete and Edit/Update options then OK

 

As you can see the Edit and Delete CommandField are automatically added in the last column of GridView.  Now we can start to write our codes for editing and updating the information in the GridView.

 

In-order to perform Edit and Update in GridView we need to use three events ( GridView_RowEditing, GridView_RowCancelingEdit , GridView_RowUpdating). For those who do not know on how to generate Events in GridView you can follow these steps below:

 

1.       Switch to Design View in Visual Studio Designer

2.       Click on the GridView

3.       Navigate to the GridView Property Pane and then SWITCH to Event Properties

4.       From there you would be able to find the list of events including those three  events mentioned above

5.       Double Click on that to generate the Event handler for you

6.       Then write the codes there

 

Here’s the code for each events:

   

        

 

 

protected void GridView1_RowEditing(object sender, GridViewEditEventArgs e)

{

        GridView1.EditIndex = e.NewEditIndex; // turn to edit mode

        BindGridView(); // Rebind GridView to show the data in edit mode

}

 

protected void GridView1_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)

{

        GridView1.EditIndex = -1; //swicth back to default mode

        BindGridView(); // Rebind GridView to show the data in default mode

}

 

protected void GridView1_RowUpdating(object sender, GridViewUpdateEventArgs e)

{

        //Accessing Edited values from the GridView

        string id = GridView1.Rows[e.RowIndex].Cells[0].Text; //ID

        string company = ((TextBox)GridView1.Rows[e.RowIndex].Cells[1].Controls[0]).Text; //Company

        string name = ((TextBox)GridView1.Rows[e.RowIndex].Cells[2].Controls[0]).Text; //Name

        string title = ((TextBox)GridView1.Rows[e.RowIndex].Cells[3].Controls[0]).Text; //Title

        string address = ((TextBox)GridView1.Rows[e.RowIndex].Cells[4].Controls[0]).Text; //Address

        string country = ((TextBox)GridView1.Rows[e.RowIndex].Cells[5].Controls[0]).Text; //Country

 

        UpdateOrAddNewRecord(id,company,name,title,address,country,true); // call update method

        GridView1.EditIndex = -1;

        BindGridView(); // Rebind GridView to reflect changes made

}

 



STEP 7: Perform Delete in GridView

 

Since we are using the Built-in Delete CommandField Button in GridView, we can use the GridView_RowDeleting event to delete specific row in GridView.

 

Here’s the code block for the Delete method:

   

          

 

#region Delete Record

    private void DeleteRecord(string ID)

    {

        SqlConnection connection = new SqlConnection(GetConnectionString());

        string sqlStatement = "DELETE FROM Customers WHERE CustomerID = @CustomerID";

        try

        {

            connection.Open();

            SqlCommand cmd = new SqlCommand(sqlStatement, connection);

            cmd.Parameters.AddWithValue("@CustomerID", ID);

            cmd.CommandType = CommandType.Text;

            cmd.ExecuteNonQuery();

        }

        catch (System.Data.SqlClient.SqlException ex)

        {

            string msg = "Deletion Error:";

            msg += ex.Message;

            throw new Exception(msg);

 

        }

        finally

        {

            connection.Close();

        }

    }

#endregion

 



Here’s the code block for calling the delete method at RowDeleting event

protected void GridView1_RowDeleting(object sender, GridViewDeleteEventArgs e)

{

   string id = GridView1.Rows[e.RowIndex].Cells[0].Text; get the id of the selected row

   DeleteRecord(id);//call delete method

   BindGridView();//rebind grid to reflect changes made

}

 



That’s it! Hope you will find this article useful!

Note: If you wan’t to display a confirmation when deleting a row in GridView then you can refer to my post here about “Display Confirmation Message on GridView Deleting” Download the source code using the link below.

 

GridView_-_Insert,_Edit,Update_and_Delete_the_ADO.NET_way_SRC.zip (159.04 kb)