Populating a DataGridView From a Database Query

Over the years I have made sporadic attempts the wrap my head around programming using the .Net class library.  I have used Delphi for over 10 years so maybe I am biased, but it seems as though many of the things that can be done with a couple of mouse clicks in Delphi require a lot more work to accomplish using .Net.  Populating a grid with data from an SQL query is a case in point.  In Delphi, you drop query, data source and grid components on your form, link the components together, set the query’s SQL property to return the data you want and the grid gets populated with the query’s data as if by magic. If only it were that easy using Visual Studio.

In the VS designer you still have the query, data source (data set in .Net parlance) and grid components but you now have to deal with a data adapter as well.  Further, once you have the components on the form, linked them together and set the query’s CommandText property nothing happens.  Rather than retrieving the table and column definitions from the query, VS forces you to manually add a table to the data set component and manually add each of the data columns to that table.  Then you have to manually add each of the columns to the grid.  Once you do this you will see the columns appear in the grid component in the designer.  But run the program and you still won’t get any data in your grid.  That is because you need to add a line of code into your program somewhere to load the query’s data into the data set’s table.  This is done using the data adapter’s Fill method.  Suppose your data set is named DS1 and your data adapter is named DA1.  Somewhere in your code you need to put the following statement:

DA1.Fill(DS1.Tables(0))

Step By Step

I am using Firebird, so the component names given here reflect the naming used by the Firebird ADO.Net components.  If you use SQL Server or some other database, substitute the components appropriate to your environment.

  1. Add an FbCommand component to your form.  Set the CommandText property to the appropriate SELECT statement.
  2. Add an FbDataAdapter component to your form.  Set the SelectCommand to point to your FbCommand component.
  3. Add a DataSet component to your form.
  4. Add a table to the DataSet using the designer provided for the Tables property.  The table name is not important.
  5. Add the columns that you want to be visible in the grid to the table using the designer provided for the Columns property.  The value given for the ColumnName property must match the name of one of the columns returned by your SELECT statement.  The Caption property seems to be ignored.
  6. Add a DataGridView component to your form.  Set the DataSource property to point to your DataSet.  Set the DataMember property to the table that you added to your DataSet.
  7. I can’t figure out the reason, but some of the columns belonging to the DataSet will appear in the grid automatically and some won’t.  To be certain you must use the designer provided for the DataGridView Columns property and add any missing columns to the grid.  This is also where you set the Caption, or column header, that will be used for each column.
  8. Add a line of code to your program to fill the DataSet’s table from the DataAdapter.  Like this, DataAdapter.Fill(DataSet.Tables(0))

With a little luck you should now have a program that will display the results of your query in a grid.

3 Responses to “Populating a DataGridView From a Database Query”

  1. JaneRadriges Says:

    The best information i have found exactly here. Keep going Thank you

  2. KonstantinMiller Says:

    How soon will you update your blog? I’m interested in reading some more information on this issue.

  3. admin Says:

    I update the blog as I learn something I didn’t know before. There is no fixed schedule or even a consistent set of topics. When I get a chance to delve more deeply into .Net there will undoubtedly be more opportunities to learn something new and hence more blogging.

Leave a Reply