Click here to Skip to main content
Click here to Skip to main content

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

, 26 May 2006
Rate this:
Please Sign up or sign in to vote.
How to populate DataGridView, GridView with SQL statement in C#
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->

Introduction

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

 

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.

 

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

 

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

 

protected void Page_Load(object sender, EventArgs e)

{

 

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

SqlConnection sqlConnection = new SqlConnection(strSQLconnection);

SqlCommand sqlCommand = new SqlCommand("select * from table1", sqlConnection);

sqlConnection.Open();

 

SqlDataReader reader = sqlCommand.ExecuteReader();

       

GridView1.DataSource = reader;

GridView1.DataBind();

}

 

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.

 

 

II. Populate DataGridView control with SQL statement for Window form based application

 

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.

 

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

 

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:

 

private void Form1_Load(object sender, EventArgs e)

{

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

string strSQL = “select * from table1”;

 

SqlDataAdapter dataAdapter = new SqlDataAdapter(strSQL, strCon);

SqlCommandBuilder commandBuilder = new SqlCommandBuilder(dataAdapter);

 

// 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;

 

// 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;

}

 

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

 

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

 

Enjoy C# and happy coding!

- Hongwei Li

 

 

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

About the Author

hong_wei_li@yahoo.com
Web Developer
United States United States
.Focus on database (SQL Server 2005/2000/7/6.5 and Oracle 10g) development with C#,
 
ASP.NET, ASP, Java, PHP.
.Like to work with MS Server 2005 SSIS and report service
.Like full cycle software design, development and deployment
.Microsoft Certified SQL Server Developer and DBA.

Comments and Discussions

 
QuestionMessage Automatically Removed PinmemberMember 102322447-Oct-13 22:25 
Questionthumbs up!! PinmemberMember 1005266429-Aug-13 9:01 
GeneralMy vote of 5 Pinmembercpt_Steel20-May-12 2:09 
QuestionChild list for field " "cannot be created. PinmemberTooze15-May-12 7:45 
GeneralMy vote of 5 Pinmemberhoraceleung3-May-12 5:42 
GeneralMy vote of 4 PinmemberItz.Irshad28-Dec-11 3:41 
GeneralMy vote of 5 Pinmemberkeshavkundal19-Nov-10 23:02 
GeneralMy vote of 4 PinmemberMember 263644025-Oct-10 1:38 
GeneralMy vote of 2 PinmemberKSagar20-Aug-10 2:04 
GeneralMy vote of 1 Pinmemberblackr2d5-Jul-10 23:05 
GeneralMy vote of 1 Pinmemberadolfhardik11-Jun-10 0:40 
Generalmy vote of 2 PinmemberMaximus Byamukama18-Sep-09 20:56 
GeneralMy vote of 1 Pinmemberdemigod231-May-09 3:59 
GeneralRe: My vote of 1 Pinmembersupermankelly6-Aug-09 2:56 
GeneralMy vote of 1 PinmemberUtku KAYA18-Jan-09 23:20 
GeneralMy vote of 2 PinmemberDonsw26-Nov-08 16:40 
GeneralRe: My vote of 2 Pinmembersupermankelly6-Aug-09 2:54 
GeneralRegurgitated Crap Pinmemberpithhelmet4-Sep-08 4:44 
GeneralRe: Regurgitated Crap Pinmembersupermankelly6-Aug-09 2:53 
Generaltry to solve PinmemberNazneen.Insignia28-Aug-08 20:38 
Questionnice copy paste? PinmemberLaoujin23-Apr-08 22:35 
GeneralFilling DataGridView using Stored Procedures PinmemberbuyValu13-Feb-08 8:34 
GeneralRe: Filling DataGridView using Stored Procedures PinmemberGlen Austin31-Mar-09 12:20 
GeneralThanks! Exactly what I was looking for. Pinmembertmpuserz27-Nov-07 10:16 
GeneralQuestion. PinmemberGil.Y10-Jun-07 14:28 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140721.1 | Last Updated 26 May 2006
Article Copyright 2006 by hong_wei_li@yahoo.com
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid