Different Methods of Data Binding to a DataGridView

Introduction

These pieces of code show different ways of binding data to a DataGridView. It would serve as a tutorial for beginners in ADO.NET programming. The program is written using C#, in Visual Studio 2005.

In Visual Studio 2005 .NET, the general way of showing a table in an ASP page is to drag and drop the DataGridView control on the page and then configuring it. See picture below.

As we configure the data source, the appropriate code is added to the file of the ASP page, and is seen in the source file as shown below:

<asp:SqlDataSource ID="MydataSource" runat="server" 
      ConnectionString="<%$ ConnectionStrings:AdventureWorksConnectionString %>"
      SelectCommand="SELECT AddressID, AddressLine1, City, 
                     PostalCode FROM Person.Address WHERE 
                     (AddressID < 15)">
</asp:SqlDataSource>

<asp:GridView ID="GridView1" runat="server" 
     DataMember="DefaultView" AutoGenerateColumns="False" 
     DataKeyNames="AddressID" DataSourceID="MydataSource">
  <Columns>
    <asp:BoundField DataField="AddressID" HeaderText="AddressID" 
        InsertVisible="False" ReadOnly="True" 
        SortExpression="AddressID" />
    <asp:BoundField DataField="AddressLine1" 
        HeaderText="AddressLine1" SortExpression="AddressLine1" />
    <asp:BoundField DataField="City" 
        HeaderText="City" SortExpression="City" />
    <asp:BoundField DataField="PostalCode" 
        HeaderText="PostalCode" SortExpression="PostalCode" />
  </Columns>
</asp:GridView>

The data fields are the columns that are added to the control. The Select command is shown, and the connection string that is added to the Web.Config file is also seen in the source file.

When we run the page, we see the following:

Currently, the DataGridView is bound to the data-source, called, say 'MyDataSource'. (This source was generated when we dragged and dropped the grid-view on the page and configured it.)

Suppose we want to remove the data-binding from the DataGridView control; we may write the following piece of code, say on a button-click, where 'GridView1' is the DataGridView:

GridView1.DataSourceID = null;
GridView1.DataBind();

When the button is clicked, the grid is no more bound to the data source. Suppose, we want to re-bind the control; we just say:

GridView1.DataSource = MydataSource ;
GridView1.DataBind();

This would again bind the data to the source. This code may be executed on the click of a button. We have thus far seen how to connect to a data source that is configured using the wizard.

There will be situations where we deal with Object Oriented Programming domain, and in those situations, we do the following. We expose a method of the appropriate class to return a dataset or a datatable. We then use this dataset or data-table as the data source for the DataGridView. The technique generally used is to employ a SqlDataAdapter, to which we pass the "connection string" and the "Select" query. Once the dataset or datatable is available, we use it in our ASP code-behind as a data-source.

The code would be similar to::

public class CDatabase
{ 
    public string myconnectionString = 
       " Data Source=KRISHNA;Initial " + 
       "Catalog=AdventureWorks;Integrated Security=True";
    public string myselectstr = "SELECT AddressID, AddressLine1," + 
       " City, PostalCode FROM Person.Address" + 
       " WHERE (AddressID between 15 and 20)";
    public SqlDataAdapter mydataadapter;
    public DataTable mydatatable = new DataTable();

    public CDatabase()
    {
        InitializeAdapter();
    }
    public void InitializeAdapter()
    {
        mydataadapter = new SqlDataAdapter(myselectstr, 
                new SqlConnection(myconnectionString)); 
    }
    public DataTable ReturnDataSetForQuery()
    {
        FillDataTable(); 
        return mydatatable;
    }
    public void FillDataTable()
    {
        mydataadapter.Fill(mydatatable);
    }
}

Here, the method ReturnDataSetForQuery ( ) can be called to return the data table. The connection string may be read directly from the web.config file, and the Select statement may be placed as a Stored Procedure to improve the efficiency of the system.

There might be situations where we need to bind a DataGridView by populating an array. This may be used in situations where we get data from the user and we need to update it in the data-grid immediately. We do the following:

We create a class and we use parameters that match the data fields of the DataGridView. Thus, by means of 'get' and 'set' methods, we may set the parameters and then pass an array of items to the grid-view as the data-source.

public class Person
{
    private string _AddressID;
    private string _AddressLine1;
    private string _City;
    private string _PostalCode;
 
    public Person(string addrid, string addlin1,
                  string cit, string pscode)
    {
        _AddressID = addrid ;
        _AddressLine1 = addlin1 ;
        _City = cit;
        _PostalCode = pscode ;
    } 
 
    public string AddressID
    {
        get
        {
            return _AddressID;
        }
        
    }
     
    public string AddressLine1
    {
        get
        {
            return _AddressLine1;
        }
    }

    public string City
    {
        get
        {
            return _City;
        }
      
    }
 
    public string PostalCode
    {
        get
        {
            return _PostalCode;
        }
      
    }
}

We may create an array of objects of type of such a class discussed above by a code similar to:

Person[] persarray = new Person[4];
      
persarray[0] = new Person("100", "addressline100", "delhi", "110048");
persarray[1] = new Person("101", "addressline101", "bombay", "334243");
persarray[2] = new Person("102", "addressline102", "calcutta", "343234");
persarray[3] = new Person("103", "addressline103", "chennai", "638002");
 
GridView1.DataSource = null;
GridView1.DataSourceID = null;
 
GridView1.DataSource = persarray;
GridView1.DataBind();

Another way of binding data to a grid-view is to read an XML file that was generated by some other class or application and use it as a data-source. The following class has a function for writing into an XML file:

public class WriteXML :  CDatabase 
{
    public WriteXML()
    {
        InitializeAdapter();
    }
    public void WriteIntoXmlFile()
    {
        FillDataTable();
        mydatatable.TableName = "datatable";
        mydatatable.WriteXml("C:\\xmlfile");
    }
}

The XML table looks similar to:

<?xml version="1.0" standalone="yes"?>
<DocumentElement>
  <datatable>
    <AddressID>15</AddressID>
    <AddressLine1>4912 La Vuelta</AddressLine1>
    <City>Bothell</City>
    <PostalCode>98011</PostalCode>
  </datatable>
  <datatable>
    <AddressID>16</AddressID>
    <AddressLine1>40 Ellis St.</AddressLine1>
    <City>Bothell</City>
    <PostalCode>98011</PostalCode>
  </datatable>
  <datatable>
    <AddressID>17</AddressID>
    <AddressLine1>6696 Anchor Drive</AddressLine1>
    <City>Bothell</City>
    <PostalCode>98011</PostalCode>
  </datatable>

Similarly, we may read an XML file into a data-set and then use it as a data-source. This situation may occur when another application generates data, which we might want to read in as XML. For reading XML, we use the ReadXml function. The dataset that is returned then serves as a data-source.

public DataSet ReadXMLandReturnTable()
{
    DataSet mydatatable = new DataSet();
    mydatatable.ReadXml("C:\\xmlfile");
    return mydatatable;
}