Copy GridView rows to a DataTable in Asp.Net C# and Vb.Net

A DataTable in Asp.Net represents a temporary data storage facility, which stores data in a tabular format (columns and rows). Temporary, since its scope is limited to the .Net application that creates it. It provides various methods to copy data from various data sources and later provide the data to other data consuming objects such as a GridView or an HTML5 DataList.

A popular method for binding a DataTable to a data source is using the SqlDataAdapter. However, in this article I’ll show you how you can fill data to a DataTable with data extracted from GridView rows.

Along with data transferring, I’ll show you how do you use the DataTable Select method to filter the data before sharing it with other objects.

You first need to add a GridView control to your web page and populate the GridView with some data. The SqlDataSource control is ideal for populating data to a GridView.

The Markup

<div>
    <asp:GridView ID="grdBooks" 
        AutoGenerateColumns="False" 
        DataKeyNames="BookID" DataSourceID="SqlDataSource1" 
        runat="server" 
        CssClass="Grid">

        <Columns>
            <asp:BoundField DataField="BookID" HeaderText="BookID" InsertVisible="False" 
                ReadOnly="True" SortExpression="BookID" />
            <asp:BoundField DataField="BookName" HeaderText="BookName" 
                SortExpression="BookName" />
            <asp:BoundField DataField="Category" HeaderText="Category" 
                SortExpression="Category" />
            <asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />
        </Columns>
    </asp:GridView>

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
        ConnectionString="<%$ ConnectionStrings:DNA_CLASSIFIEDConnectionString %>" 
                    
        SelectCommand="SELECT [BookID], [BookName], [Category], [Price] FROM [Books]">
    </asp:SqlDataSource>

    <div id="list" runat="server"></div>
</div>

In the markup section I have added the GridView and populate it with data extracted from a database table using SqlDataSource. In addition, I have added a <div> element (id='list') at the end. I’ll use this element to display the data in the DataTable.

Code Behind (C#)

using System;

public partial class SiteMaster : System.Web.UI.MasterPage
{
    System.Data.DataTable mytable = new System.Data.DataTable();
    System.Data.DataRow dr = null;

    protected void Page_Load(object sender, EventArgs e)
    {
        create_DataTable();
    }
        
    private void create_DataTable()
    {
        int iRowCnt = 0;
        
        // CREATE A DATATABLE AND ADD COLUMNS TO IT.
        mytable.Columns.Add(new System.Data.DataColumn("Book ID", System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Name of the Book", 
            System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Category", System.Type.GetType("System.String")));
        mytable.Columns.Add(new System.Data.DataColumn("Price ($)", 
            System.Type.GetType("System.Decimal")));

        foreach (System.Web.UI.WebControls.GridViewRow row in grdBooks.Rows)
        {
            dr = mytable.NewRow();
            dr[0] = grdBooks.Rows[iRowCnt].Cells[0].Text;
            dr[1] = grdBooks.Rows[iRowCnt].Cells[1].Text;
            dr[2] = grdBooks.Rows[iRowCnt].Cells[2].Text;
            dr[3] = grdBooks.Rows[iRowCnt].Cells[3].Text;

            mytable.Rows.Add(dr);

            iRowCnt += 1;
        }
        DataTable_Output();     // SHOW THE OUTPUT.
    }

    private void DataTable_Output()
    {
        for (int i = 0; i <= mytable.Rows.Count - 1; i++)
        {
            // SHOW DATATABLE OUTPUT IN A DIV ELEMENT.
            list.InnerHtml = list.InnerHtml + "<br />" +
                mytable.Rows[i][0] + ' ' +
                mytable.Rows[i][1] + ' ' +
                mytable.Rows[i][2] + ' ' +
                mytable.Rows[i][3];
        }
    }
}

Vb.Net

Option Explicit On

Partial Class Site    Inherits System.Web.UI.MasterPage

    Dim mytable As New Data.DataTable()
    Dim dr As Data.DataRow

    Protected Sub Page_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        create_DataTable()
    End Sub

    Private Sub create_DataTable()
        Dim iRowCnt As Integer = 0

        ' CREATE A DATATABLE AND ADD COLUMNS TO IT.
        mytable.Columns.Add(New Data.DataColumn("Book ID", System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Name of the Book", 
            System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Category", System.Type.GetType("System.String")))
        mytable.Columns.Add(New Data.DataColumn("Price ($)", System.Type.GetType("System.Decimal")))

        ' POPULATE DATATABLE USING GRIDVIEW ROW VALUES.
        For Each GridViewRow In grdBooks.Rows

            dr = mytable.NewRow
            dr(0) = grdBooks.Rows(iRowCnt).Cells(0).Text
            dr(1) = grdBooks.Rows(iRowCnt).Cells(1).Text
            dr(2) = grdBooks.Rows(iRowCnt).Cells(2).Text
            dr(3) = grdBooks.Rows(iRowCnt).Cells(3).Text

            mytable.Rows.Add(dr)

            iRowCnt += 1
        Next

        ' SHOW THE OUTPUT.
        DataTable_Output()
    End Sub

    Private Sub DataTable_Output()

        For i As Integer = 0 To mytable.Rows.Count - 1

            ' SHOW DATATABLE OUTPUT IN A DIV ELEMENT.
            list.InnerHtml = list.InnerHtml & "<br />" & _
                mytable.Rows(i)(0) & " " & _
                mytable.Rows(i)(1) & " " & _
                mytable.Rows(i)(2) & " " & _
                mytable.Rows(i)(3)
        Next

    End Sub
End Class

Filter DataTable data using Select Method

You can use the DataTable Select method to filter data extracted from the GridView control (or any other object). Filtering data would allow you to display or use the data according to your requirement.
I am sure you might have used queries to fetch data from a database table using SQL “select” query. The Asp.Net DataTable is like a small table, which resembles a database table like structure. The Select method takes a parameter in the form of a string and it resembles the SQL select query.

Syntax

table.Select (filterExpression as String)

I am extending the above example and change the code inside DataTable_OutPut() procedure.

private void DataTable_Output()
// FILTER DATA WHERE PRICE IS LESS THAN OR EQUAL TO 100.

{
    System.Data.DataRow[] filter = mytable.Select("[Price ($)] <= 100");

    list.InnerHtml = "Found " + filter.Length + " record(s) <br />";

    for (int i = 0; i <= filter.Length - 1; i++)
    {
        list.InnerHtml = list.InnerHtml + "<br />" + 
            filter[i][0] + " " + 
            filter[i][1] + " " + 
            filter[i][2] + " " + 
            filter[i][3];
    }

}

Vb.Net

Private Sub DataTable_Output()

    ' FILTER DATA WHERE PRICE IS LESS THAN OR EQUAL TO 100.
    Dim filter() As Data.DataRow = mytable.Select("[Price ($)] <= 100")

    list.InnerHtml = "Found " & filter.Length & " record(s) <br />"

    For i As Integer = 0 To filter.Length - 1
        list.InnerHtml = list.InnerHtml & "<br />" & _
            filter(i)(0) & " " & _
            filter(i)(1) & " " & _
            filter(i)(2) & " " & _
            filter(i)(3)
    Next

End Sub

Conclusion

I am sure this article would help you understand how you can bind an Asp.Net DataTable with a GridView data source and share the data with other objects and elements. Filtering the data before populating the DataTable using the Select method is an added advantage. This way you can control the data you would possibly share on your web application. You can filter data using a range, and even set conditions using the AND keyword.

← PreviousNext →