[Beginning Database Design Solutions, Second Edition]

Title: Execute ad hoc SQL queries in C#

This example shows how you can execute an ad hoc SQL query at run time. Normally a program uses Recordsets, DataGrids, LINQ to SQL objects, and other database objects and controls to display and manipulate database records. Those techniques tend to favor design-time creation of queries, but sometimes it's useful to be able to execute ad hoc queries on the fly while the program runs.

Note: You should generally not allow users to type in queries such as the ones demonstrated by this program because that would allow users to read parts of the database that they should not necessarily be able to see. For example, it might let a sales clerk see customer billing data that should be private. This example is mostly to show you how to execute ad hoc queries in your code and loop through the results by using a data reader object.

I've also found it very useful to allow the system's "super users" (supervisors and system managers) to be able to run ad hoc queries. You can do some parsing on the command to prevent them from trying to execute commands such as DROP TABLE that would trash the database. (For example, you might only allow then to execute SELECT statements.)

Anyway, the following code shows how the example program executes the query entered in the txtQuery text box and how it displays the result. It also uses a few other useful techniques such as figuring out the names of the columns returned and sizing the DataGridView control's columns to fit the data.

// Execute the query. private void btnExecute_Click(object sender, EventArgs e) { // Make a command object to represent the command. OleDbCommand cmd = new OleDbCommand(); cmd.Connection = Conn; cmd.CommandText = txtQuery.Text; // Open the connection and execute the command. try { // Open the connection. Conn.Open(); // Execute the query. // The reader gives access to the results. OleDbDataReader reader = cmd.ExecuteReader(); // Prepare the DataGridView. dgvResults.Columns.Clear(); dgvResults.Rows.Clear(); if (reader.HasRows) { // Get field information. DataTable schema = reader.GetSchemaTable(); int field_num = 0; foreach (DataRow schema_row in schema.Rows) { // Create the column. int col_num = dgvResults.Columns.Add( "col" + field_num.ToString(), schema_row.Field<string>("ColumnName")); field_num++; // Make the column size to fit its data. dgvResults.Columns[col_num].AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells; } // Make room to hold a row's values. object[] values = new object[reader.FieldCount]; // Loop while the reader has unread data. while (reader.Read()) { // Add this row to the DataGridView. reader.GetValues(values); dgvResults.Rows.Add(values); } } } catch (Exception ex) { MessageBox.Show("Error executing command.\n" + ex.Message); } finally { // Close the connection whether we succeed or fail. Conn.Close(); } }

The code starts by creating an OleDbCommand object to represent the query. It sets the object's Connection property to the database connection object and sets its command text.

Next the code opens the database connection and calls the command's ExecuteReader method to execute the query and obtain a command reader object to iterate through the result rows.

Now the program does some work to prepare its DataGridView control. It first clears the control's previous rows and columns. Then if the data reader contains any returned rows, the code gets a schema table representing the reader's results. This table contains information about the columns returned by the reader such as each column's name and data type. The code uses that information to add new columns to the DataGridView. It also sets each column's AutoSizeMode property so the column resizes to fit its data.

Next the code makes an array of objects big enough to hold the values for one returned row. It then loops through the data reader's results. For each row, it calls the reader's GetValues method to copy the row's values into the array, and then calls the DataGridView control's Rows.Add method to add the array of values to the grid.

Alternatively you could use the data reader's methods to examine the individual fields in each row, but this example just copies them all en masse into the DataGridView.

Download the example to experiment with it and to see additional details.