Inserting Multiple Rows Using GridView
Introduction
ASP.NET GridView control is one of the most popular control when it comes to
displaying and editing tabular data. However, when it comes to inserting data
the GridView has very little to offer. Using a technique as illustrated in my
article titled
Inserting a New Row in GridView you can insert a single record using
EmptyDataTemplate of the GridView. However, what if you want to insert multiple
rows using GridView? In real world cases developers often require such a
mechanism. Though there is no out of the box answer to this problem this article
is going to demonstrate a possible solution. Read on...
Example Scenario
Have a look at the following figure that shows part of a web form.

The web form consists of a GridView control and two buttons. The interesting
part is that the GridView is showing five rows that are empty. The user can
enter data in those five rows and then click on "Save All" button to save the
data in the database. Clicking on the "CLear Grid" will clear the entered data.
GridView and data source
By design the GridView control is a data bound control. That means it always
requires some data source to bind with. It cannot be used in unbound fashion. In
order to display empty rows in the GridView you need to have a data source that
contains empty items. Remember that there is a difference between "empty data
source" and "data source with empty rows". Once you have such a data source you
can bind it with grid so as to render its rows. Accepting data from the user is
just a matter of creating template columns.
Example
To illustrate how all this works create a new web site using Visual Studio.
Add a new class to the web site named Customers. The following code shows the
Custoemrs class:
public class Customer
{
private string strCustomerID;
private string strCompanyName;
private string strContactName;
private string strCountry;
public string CustomerID
{
get
{
return strCustomerID;
}
set
{
strCustomerID = value;
}
}
public string CompanyName
{
get
{
return strCompanyName;
}
set
{
strCompanyName = value;
}
}
public string ContactName
{
get
{
return strContactName;
}
set
{
strContactName = value;
}
}
public string Country
{
get
{
return strCountry;
}
set
{
strCountry = value;
}
}
}
The Customer class consists of four private variables and four public
properties viz. CustomerID, CompanyName, ContactName and Country - that
encapsulate them. We use a generic List of Customer objects to bind with the
GridView. We opted for generic collection over DataSet or DataTable so as to
make our application lightweight.
Now drag and drop a GridView and two buttons as shown in the above figure.
Add four TemplateField columns to the GridView and set their HeaderText property
to Customer ID, Company Name, Contact Name and Country respectively. Design all
the four template columns to have one textbox in the ItemTemplate. (see below).

Open the data bindings editor for the textboxs and bind them with CustomerID,
CompanyName, ContactName and Country columns respectively.

Remember that we are binding Text property of the textboxes with the
properties of the Customer class.
Go in the code behind of the web form and create a private method called
BindGrid().
private void BindGrid()
{
List<Customer> items = new List<Customer>(5);
for (int i = 0; i < 5; i++)
{
Customer c = new Customer();
items.Add(c);
}
GridView1.DataSource = items;
GridView1.DataBind();
}
The BindGrid() method creates a generic List of customers with capacity of
five elements. It then runs a for loop to create five objects of Customer class.
The List is then bound with the GridView. Since we just want to insert new
records we need not set any properties of the Customer class. Had it been an
update operation you would have set the properties of Customer objects to the
appropriate values from the database.
The BindGrid() method is called from two places - Page_Load event handler and
Click event handler of "Clear Grid" button.
protected void Page_Load(object sender,
EventArgs e)
{
if(!IsPostBack)
{
BindGrid();
}
}
protected void Button2_Click(object sender,
EventArgs e)
{
BindGrid();
}
The main job of inserting the entered data goes inside the Click event of
"Save All" button. We also need few of helper methods namely BeginAdd(),
AddCustomer() and CompleteAdd().
The BeginAdd() method looks as shown below:
SqlConnection cnn = new SqlConnection
("data source=.;initial catalog=northwind;
user id=sa;password=sa");
SqlCommand cmd = new SqlCommand();
private void BeginAdd()
{
cnn.Open();
SqlTransaction tran= cnn.BeginTransaction();
cmd.Connection = cnn;
cmd.Transaction = tran;
cmd.CommandText = "insert into customers
(customerid,companyname,contactname,country)
values(@custid,@company,@contact,@country)";
SqlParameter p1 = new SqlParameter
("@custid",SqlDbType.VarChar);
SqlParameter p2 = new SqlParameter
("@company", SqlDbType.VarChar);
SqlParameter p3 = new SqlParameter
("@contact", SqlDbType.VarChar);
SqlParameter p4 = new SqlParameter
("@country", SqlDbType.VarChar);
cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);
cmd.Parameters.Add(p3);
cmd.Parameters.Add(p4);
}
The code declares a connection and command at the class level. The BeginAdd()
method establishes a connection with the Northwind database and initiates a
transaction. In our example we insert all the records as a single transaction.
You may omit the transactional operation if you don't need it. The code then
configures Connection, Transaction and CommandText properties of the command
object. The CommandText property specifies an INSERT statement that inserts a
record to Customers table of the Northwind database. The code then adds four
parameters to the command object. The BeginAdd() method needs to be called
before actual INSERT operation begins.
The actual INSERT operation is carried out by another method called
AddCustomer().
private void AddCustomer(string custid, string company,
string contact, string country)
{
try
{
cmd.Parameters[0].Value = custid;
cmd.Parameters[1].Value = company;
cmd.Parameters[2].Value = contact;
cmd.Parameters[3].Value = country;
cmd.ExecuteNonQuery();
}
catch
{
cmd.Transaction.Rollback();
}
}
The AddCustomer() method accepts four parameters representing customer ID,
company name, contact name and country. It then sets the respective parameter
values of SqlCommand object we configured in BeginAdd() method earlier. Then
ExecuteNonQuery() method of the command object is called. The catch block traps
any exceptions occured during the INSERT operation and calls Rollback() method
of the transaction object. The AddCustomer() method is called multiple times
depending on the rows entered by the user.
The CompleteAdd() method commits the transaction.
private void CompleteAdd()
{
try
{
cmd.Transaction.Commit();
Label1.Text = "Customers added successfully!";
}
catch(Exception ex)
{
Label1.Text = "Error completing the operation!";
}
finally
{
cnn.Close();
}
}
The ComplateAdd() method calls Commit() method of transaction object and
displays a success or failure message. Finally, it closes the connection that
was opened in BeginAdd() method.
The BeginAdd(), AddCustomer() and CompleteAdd() methods are used in the click
event handler of "Save All" button.
protected void Button1_Click(object sender,
EventArgs e)
{
BeginAdd();
foreach (GridViewRow row in GridView1.Rows)
{
if (row.RowType == DataControlRowType.DataRow)
{
string custid = ((TextBox)row.FindControl
("TextBox1")).Text;
string company = ((TextBox)row.FindControl
("TextBox2")).Text;
string contact = ((TextBox)row.FindControl
("TextBox3")).Text;
string country = ((TextBox)row.FindControl
("TextBox4")).Text;
if (custid != "")
{
AddCustomer(custid, company, contact, country);
}
}
}
CompleteAdd();
}
The click event handler of the button calls the BeginAdd() method. It then
starts iterating through the rows of the GridView. With each iteration it
extracts the values from the four textboxes using FindControl() method. We
assume that if CustomerID is entered then that record is to be added to the
database. You can add extra validations here. The AddCustomer() method is then
called by passing newly entered customer ID, company name, contact name and
country. Finally, ComplateAdd() method is called to complete the insert
operation.
That's it! Our GridView is now ready to accept multiple empty rows and insert
them as a single batch in the database.