Copy rows from ASP.NET GridView Control to a SQL Server table

In this article, I am going to demonstrate how to add checkbox columns to the grid view and copy selected (checked) items to a new table in the database. I am not going to cover any basics in this article. If you are new to using grid view or if you would like know more about grid view, I would recommend you to reach following tutorials:

Overview:
I am going to create a simple Product table with some records. When page is initially loaded, we retrieve products from tblProduct table and bind them to the grid. We display some product attributes such as Product ID, Product Number, Product Name, and List Price. User can select one or more products and delete them from the database. We also give them an option of CheckALL & UnCheckAll features to help him in check/uncheck all items in the grid. I am going to enable built-in GridView paging, but not going to explain about it. If you are not aware of this gridview functionality, you could refer to Default Paging in ASP.NET GridView Control

Database Connection
Added following entry under connectionStrings element in web.config.

Page Design
I have created a new ASP.NET web page (CopyProductsView.aspx). Page is enabled by setting AllowPaging flag to true and setting the page size. When user clicks one of the pager buttons is clicked, PageIndexChanging event is raised. We have to implement this event handler. Since it occurs before the GridView control handles the paging operation, we need to set the new page index to the grid and rebind the data as it is a postback.

We render ProductID Product Number, Product Name & Price using ItemTemplate in TemplateField. A checkbox control is added to the grid rendered in a TemplateField as shown below. User would select this checkbox and hits “Copy” button to copy product(s). In order to support the three actions (Copy, CheckAll, UnCheckAll) mentioned in the overview, added three link buttons to the page. Added a message label to notify user about the details of products he has copied. Since we set the AllowPaging property of the GridView control to true, the GridView control automatically adds user interface (UI) controls for paging. We can customizethe UI for paging by adding a PagerTemplate template. If you combine everything together, you get your final design.

If you run the application now, you would see the following page listing the first page of products.

Select the second & third product and click on “Copy” button. In the Copy event handler routine, we iterate through all the rows and will check if the checkbox is checked. If that row is selected, we add it to the list of products that need to be copied over. Once we run through all the records, we insert them one-by-one into tblProduct_Copy table. We make a list of inserted products information and will display it to the user.

If you want to select all products at once, click on “CheckALL”. If you want to unselect all, click on “UnCheck All”. We iterate through all the grid rows and will set the checked property of the checkbox to true or false.

SQL Statements:
I have created a tblProduct table and loaded it with some test data. I have also created destination table tblProduct_Copy with schema identical to tblProduct. If you would like to use the same table, you could use below scripts.

Software architect with over 10 years of proven experience in designing & developing n-tier and web based software applications, for Finance, Telecommunication, Manufacturing, Internet and other Commercial industries. He believes that success depends on one's ability to integrate multiple technologies to solve a simple as well as complicated problem.