Search
Close this search box.

GridView Insert, Edit, Update and Delete – The Ado.Net way

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 to this example.

Basically, this demo describes the basic way 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, this time 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 paramaters basically comes 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.

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 View2.       Right Click on the GridView and Select  –> Show Smart Tag –> Add New Columns3.       On the List Select CommandField4.       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 Designer2.       Click on the GridView3.       Navigate to the GridView Property Pane and then SWITCH to Event Properties4.       From there you would be able to find the list of events including those three  events mentioned above5.       Double Click on that to generate the Event handler for you6.       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!

Note: If you wan’t to display a confirmation when deleting a row in GridView then you can refer to my next example about “Display Confirmation Message on GridView Deleting

Print | posted on Sunday, February 22, 2009 1:34 AM |

This article is part of the GWB Archives. Original Author: Vinz’ Blog

Related Posts