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.