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"
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 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!
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”
