How to populate DataGridView, GridView with SQL statement in C#

How to populate DataGridView, GridView with SQL statement in C#<o:p>

<o:p> 

When we do the development, we always want to make the code simple and error free if possible. In C#, GridView for web based application and DataGridView for windows form based application are different in using and behavior. It looks like Microsoft has two different teams to develop GridView and DataGridView separately.  This is why I wrote this article to share the coding for each control. Here I am using MS Visual Studio 2005.<o:p>

<o:p> 

I. Populate GridView control for web based application with SQL statement

<o:p> 

Let us put GridView1 control on the web form from Toolbox. The coding is straight forward and is like the following:<o:p>

<o:p> 

protected void Page_Load(object sender, EventArgs e)<o:p>

{<o:p>

<o:p> 

string strSQLconnection = "Data Source=dbServer;Initial Catalog=testDB;Integrated Security=True";<o:p>

SqlConnection sqlConnection = new SqlConnection(strSQLconnection);<o:p>

SqlCommand sqlCommand = new SqlCommand("select * from table1", sqlConnection);<o:p>

sqlConnection.Open();<o:p>

<o:p> 

SqlDataReader reader = sqlCommand.ExecuteReader();<o:p>

        <o:p>

GridView1.DataSource = reader;<o:p>

GridView1.DataBind();

}

<o:p> 

You run the code and you can see the result. But when you see the data binding for DataGridView in the following section, it is quite different.

<o:p> 

<o:p> 

II. Populate DataGridView control with SQL statement for Window form based application<o:p>

<o:p> 

When I used the DataGridView control in C# in MS Visual Studio 2005, I found DataGridView control is not friendly to use. Windows form-based DataGridView control is different from web based GridView control. DataGridView doesn’t have DataBind() method.  It took me a few days to figure out.

The logic is like this

1. Create data set from SQL statement or stored procedure
2. Create a table to hold this data set
3. Create a BindingSource and bind this table with this BindingSource
4. Then bind this BindingSource with GridView control.

<o:p> 

This looks trivial. But I found it is very efficient and error free.

<o:p> 

Let us put DataGridView control and BindingSource control on the windows form from Toolbox. Let us name DataGridView control as dbGridView, BindingSource control as dbBindSource. Let us apply the following code:

<o:p> 

private void Form1_Load(object sender, EventArgs e)

{

string strCon = "Data Source=dbServer;Initial Catalog=testDB;Integrated Security=True";

string strSQL = “select * from table1”;

<o:p> 

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strCon);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

<o:p> 

// Populate a new data table and bind it to the BindingSource.

DataTable table = new DataTable();

table.Locale = System.Globalization.CultureInfo.InvariantCulture;

dataAdapter.Fill(table);

dbBindSource.DataSource = table;

<o:p> 

// Resize the DataGridView columns to fit the newly loaded content.

dbGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);

// you can make it grid readonly.

dbGridView.ReadOnly = true; 

// finally bind the data to the grid

dbGridView.DataSource = dbBindSource;

}

<o:p> 

Now we compile it and run it. You can see the data in the grid.

<o:p> 

If you have any comments or questions, you can reach me at hong_wei_li@yahoo.com

<o:p> 

Enjoy C# and happy coding!

- Hongwei Li

<o:p>