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

    Posted on April 10, 2012 by in ASP.NET, C#, Dotnet


    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:

    Using GridView in ASP.NET & C# —PART 1
    Using GridView in ASP.NET & C# —PART 2

    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.

    <add name="Sql" connectionString="Data Source={SERVER_NAME};
    Initial Catalog=TEST; User=testuser; Password=testuser;"
    providerName="System.Data.SqlClient"/>

    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.

    <asp:gridview id="gvProducts"
    PageSize="5" AllowPaging="True"
    autogeneratecolumns="False"
    BorderColor="White" BorderStyle="Ridge"
    CellSpacing="1" CellPadding="3" GridLines="None"
    BackColor="White" BorderWidth="2px"
    emptydatatext="No data available."
    runat="server" DataKeyNames="ProductID"
    OnPageIndexChanging="gvProducts_PageIndexChanging">
    protected void gvProducts_PageIndexChanging(object sender,
                                                GridViewPageEventArgs e)
    {
        gvProducts.PageIndex = e.NewPageIndex;
        BindData();
    }

    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.

    <asp:gridview id="gvProducts"
        PageSize="5" AllowPaging="True"
        autogeneratecolumns="False"
        BorderColor="White" BorderStyle="Ridge"
        CellSpacing="1" CellPadding="3" GridLines="None"
        BackColor="White" BorderWidth="2px"
        emptydatatext="No data available."
        runat="server" DataKeyNames="ProductID"
        OnPageIndexChanging="gvProducts_PageIndexChanging">
    <Columns>
        <asp:TemplateField>
            <ItemTemplate>
                <asp:CheckBox ID="cbSelProduct" runat="server"/>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:BoundField DataField="ProductID" HeaderText="Product ID">
            <HeaderStyle HorizontalAlign="Left"  Width="150px"/>
        </asp:BoundField>
        <asp:TemplateField headertext="Product Number">
            <HeaderStyle HorizontalAlign="Left" Width="200px" />
            <ItemStyle HorizontalAlign="Left" Width="200px"/>
            <ItemTemplate>
                <asp:Label ID="lblProductNumber"
                           text='<%#Eval("ProductNumber")%>' runat="server"/>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField headertext="Product Name">
            <HeaderStyle HorizontalAlign="Left" Width="200px" />
            <ItemStyle HorizontalAlign="Left" Width="200px"/>
            <ItemTemplate>
                <asp:Label ID="lblProductName" text='<%#Eval("ProductName")%>'
                                               runat="server"/>
            </ItemTemplate>
        </asp:TemplateField>
        <asp:TemplateField headertext="Price">
            <HeaderStyle HorizontalAlign="Right" Width="80" />
            <ItemStyle HorizontalAlign="Right" Width="80" />
             <ItemTemplate>
                <asp:Label ID="lblListPrice"
                      text='<%#Eval("ListPrice","{0:c}")%>' runat="server"/>
             </ItemTemplate>
        </asp:TemplateField>
    </Columns>
    <PagerTemplate>
        <table>
        <tr>
      <td><asp:LinkButton ID="FirstButton" CommandName="Page"
            CommandArgument="First" Text="First" RunAt="server"/></td>
      <td><asp:LinkButton ID="PrevButton"  CommandName="Page"
            CommandArgument="Prev"  Text="Previous"  RunAt="server"/></td>
      <td><asp:LinkButton ID="NextButton"  CommandName="Page"
            CommandArgument="Next"  Text="Next"  RunAt="server"/></td>
      <td><asp:LinkButton ID="LastButton"  CommandName="Page"
            CommandArgument="Last"  Text="Last" RunAt="server"/></td>
        </tr>
    </table>
    </PagerTemplate>
    </asp:gridview>
    <asp:Button ID="btnCopy" runat="server" Text="Copy" OnClick="Copy" />
    &nbsp;<asp:Button ID="btnCheckAll" runat="server" Text="Check All" OnClick="CheckAll" />
    &nbsp;<asp:Button ID="btnUnCheckAll" runat="server" Text="UnCheck All" OnClick="UnCheckAll" />
    <br />
    <i><asp:Label ID="lblMessage" runat="server"></asp:Label></i>

    Source Code:
    I have created a simple Product class to store attributes of all selected products on the grid.

    public class Product
    {
        public string Name;
        public string ProductNumber;
        public double ProductPrice;
    }

    We load the grid initially in the Page_Load event and will save the dataset into ViewState for future page refresh or post backs.

    protected void Page_Load(object sender, EventArgs e)
    {
        if(!Page.IsPostBack)
            BindData();
    }
    private void BindData()
    {
        //Bind the grid view
        gvProducts.DataSource = RetrieveProducts();
        gvProducts.DataBind();
    }
    private DataSet RetrieveProducts()
    {
        //if products is available in the viewstate, use that
        //instead of fetching again from the database
        if (ViewState["Products"] != null)
            return (DataSet)ViewState["Products"];
        //fetch the connection string from web.config
        string connString =
            ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
        //SQL statement to fetch entries from products
        string sql = @"Select P.ProductID, P.Name as ProductName,
                P.ProductNumber, ListPrice from tblProduct P";
        DataSet dsProducts = new DataSet();
        //Open SQL Connection
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                //Fill the result set
                adapter.Fill(dsProducts);
            }
        }
        ViewState["Products"] = dsProducts;
        return dsProducts;
    }

    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.

    protected void Copy(object sender, EventArgs e)
    {
        Hashtable productList = new Hashtable();
        Product product;
        foreach (GridViewRow row in gvProducts.Rows)
        {
            // Get the checkbox control that contain the user selection.
            CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
            if (cbSelProducts != null && cbSelProducts.Checked)
            {
                product = new Product();
                Label lblProductName = (Label)row.FindControl("lblProductName");
                Label lblProductNumber =
                                     (Label)row.FindControl("lblProductNumber");
                Label lblListPrice = (Label)row.FindControl("lblListPrice");
                product.Name = lblProductName.Text;
                product.ProductNumber = lblProductNumber.Text;
                product.ProductPrice = double.Parse(lblListPrice.Text,
                                                        NumberStyles.Currency);
                /// Get the product id of the selected product
              productList.Add(gvProducts.DataKeys[row.RowIndex].Value.ToString(),
                                product);
            }
        }
        if (productList.Count > 0)
            CopyProducts(productList);
        BindData();
    }
    
    private void CopyProducts(Hashtable htProducts)
    {
        //fetch the connection string from web.config
        string connString =
            ConfigurationManager.ConnectionStrings["Sql"].ConnectionString;
    
        //SQL statement to insert into products
        string sql = @"Insert into tblProduct_Copy
                        values('{0}','{1}',{2})";
        string formattedSQL;
    
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
    
            //Initialize command object
            using (SqlCommand cmd = new SqlCommand(sql, conn))
            {
                foreach (Product product in htProducts.Values)
                {
                    formattedSQL = string.Format(sql, product.Name,
                                   product.ProductNumber,product.ProductPrice);
                    cmd.CommandText = formattedSQL;
                    cmd.ExecuteNonQuery();
                }
            }
        }
        lblMessage.Text = "Following products were copied";
        foreach (string key in htProducts.Keys)
        {
            lblMessage.Text += String.Format("<br/> ProductID: {0}; ProductName:
                             {1}", key, ((Product)htProducts[key]).Name);
        }
        BindData();
    }



    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.

    protected void CheckAll(object sender, EventArgs e)
    {
        foreach (GridViewRow row in gvProducts.Rows)
        {
            // Get the checkbox control that contain the user selection.
            CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
            if (cbSelProducts != null)
                cbSelProducts.Checked = true;
        }
    }
    protected void UnCheckAll(object sender, EventArgs e)
    {
        foreach (GridViewRow row in gvProducts.Rows)
        {
            // Get the checkbox control that contain the user selection.
            CheckBox cbSelProducts = (CheckBox)row.FindControl("cbSelProduct");
            if (cbSelProducts != null)
                cbSelProducts.Checked = 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.

    CREATE TABLE [dbo].[tblProduct](
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](255) NOT NULL,
    	[ProductNumber] [varchar](255) NOT NULL,
    	[ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_ProductID] PRIMARY KEY CLUSTERED 
    (
    	[ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    CREATE TABLE [dbo].[tblProduct_COPY](
    	[ProductID] [int] IDENTITY(1,1) NOT NULL,
    	[Name] [varchar](255) NOT NULL,
    	[ProductNumber] [varchar](255) NOT NULL,
    	[ListPrice] [numeric](9, 2) NOT NULL,
     CONSTRAINT [PK_tblProduct_COPY_ProductID] PRIMARY KEY CLUSTERED 
    (
    	[ProductID] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, 
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    GO
    
    DECLARE @COUNTER INT, @productname VARCHAR(255), 
    		@productnumber VARCHAR(255), @listprice DECIMAL
    SET @COUNTER = 0
    SET @productname = 'product name '
    SET @productnumber = 'product number '
    SET @listprice = 500
    WHILE(@COUNTER <= 20)
    BEGIN
    	SET @listprice= @listprice + 100
    	INSERT INTO [dbo].[tblProduct]
         VALUES
               (@productname + convert(VARCHAR, @COUNTER)
               ,@productnumber + convert(VARCHAR, @COUNTER)
               ,@listprice)
    	SET @COUNTER = @COUNTER + 1           
    END
    Be Sociable, Share!
      Post Tagged with , ,

    Written by

    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.

    View all articles by

    Email : [email protected]

    Leave a Reply