How to Populate a DataGridView Control using OleDbDataReader

• Download source - 28.68 KB

Introduction

Many developers know that in order to populate a DataGridView control, all you have to do is set the DataSource property of the control to a DataTable object. This can be achieved in a number of ways with very little code and effort. The code in listing 1.1 below demonstrates how to populate a DataGridView control using a Microsoft Access database.

Listing 1.1

string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
string strSql = "SELECT * FROM tbl_employees";
OleDbConnection con = new OleDbConnection(strProvider);
OleDbCommand cmd = new OleDbCommand(strSql, con);
con.Open();
cmd.CommandType = CommandType.Text;
OleDbDataAdapter da = new OleDbDataAdapter(cmd);
DataTable employees = new DataTable();
da.Fill(employees);
dataGridView1.DataSource = employees;

The above code creates a connection to an Access database and fills a DataTable using a OleDbDataAdapter object with records. It then populates a DataGridView control with the records by setting the DataSource property of the control to the DataTable object.

But what if you need to use the OleDbDataReader to read the records from a table? The problem is that you cannot use the OleDbDataReader object as the DataSource for the DataGridView control. This means you will need to bind the OleDbDataReader object in code yourself. Lucky for us the OleDbDataReader class provides the methods necessary to do the binding ourselves through code.

The Code

The first thing we need to do is set up the DataGridView control with headers. These headers will be the column names from the OleDbDataReader object. But before we can get the column names, we need to know how many columns there are in total. This is easily achieved with the code in listing 1.2.

Listing 1.2

string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
string strSql = "SELECT * FROM tbl_employees";
OleDbConnection con = new OleDbConnection(strProvider);
OleDbCommand cmd = new OleDbCommand(strSql, con);
con.Open();
cmd.CommandType = CommandType.Text;
OleDbDataReader dr = cmd.ExecuteReader();
int columnCount = dr.FieldCount;

So now that we know how many columns there are in total, we can use a simple loop to loop through each column and get the name of the column. The OleDbDataReader class has a GetName() method. This method takes one argument, which is the index of a column. In the loop, we get the column name and add it to the DataGridView control using the DataGridViews' Columns.Add() method. The code in listing 1.3 below shows how to add the headers to the DataGridView control.

Listing 1.3

for (int i = 0; i < columnCount; i++)
{
dgv.Columns.Add(dr.GetName(i).ToString(), dr.GetName(i).ToString());
}

Now we have the headers set up for the DataGridView control. All that remains is to add the records using another loop. However this part is not as straightforward as you may think.

The reason is, when you get data from a column using the OleDbDataReader object, you need to specify the data type. There is a method for each data type. For example, if you have a column which is of Integer data type, then you need to return the column data as an Integer, likewise if you have a column data type of Text, then you must return the column data as a String.

What this means is, when we loop through the OleDbDataReader object to get each row data, we need to check the column data type and return the data using the correct method.

But before we move on to reading each row, we need to first declare a String array, this array will hold the column data for a row, it will then be used to add the row to the DataGridView control.

Because we know at this point how many columns there are, we can easily set the size of the String array.

string[] rowData = new string[columnCount];

After declaring the String array, we need to loop through the records in the OleDbDataReader object. We do this by using a While loop and the Read() method of the OleDbDataReader object. The Read() method returns a Boolean indicating if there are more records left in the OleDbDataReader object. When we read each row from the OleDbDataReader object, we need to loop through each column to get the data and store it into the String array.

When we loop through each column, we first check the data type of the column so that we can return the column data using the correct method. For example, if the first column in the table is of type Integer, then we need to use the GetInt32() method of the OleDbDataReader object to get the column data as an Integer.

To get the column data type, we use the GetFieldType() method of the OleDbDataReader object. This method takes an integer as its argument. The integer is the column index. We use an 'if statement' to check what the data type of the column field is and use the appropriate method to get the column data. All data types get converted into a String to populate the DataGridView control.

Listing 1.4 below shows the complete code. Alternatively you can download the project source files.

Listing 1.4

string strProvider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Employees.mdb";
            string strSql = "SELECT * FROM tbl_employees";
            OleDbConnection con = new OleDbConnection(strProvider);
            OleDbCommand cmd = new OleDbCommand(strSql, con);
            con.Open();
            cmd.CommandType = CommandType.Text;
            OleDbDataReader dr = cmd.ExecuteReader();

            int columnCount = dr.FieldCount;

            for (int i = 0; i < columnCount; i++)
            {
                dgv.Columns.Add(dr.GetName(i).ToString(), dr.GetName(i).ToString());
            }

            string[] rowData = new string[columnCount];
            while (dr.Read())
            {
                for (int k = 0; k < columnCount; k++)
                {
                    if (dr.GetFieldType(k).ToString() =="System.Int32")
                    {
                        rowData[k] = dr.GetInt32(k).ToString();
                    }

                    if (dr.GetFieldType(k).ToString() == "System.String")
                    {
                        rowData[k] = dr.GetString(k);
                    }
                }

                dgv.Rows.Add(rowData);
            }