Title: Let the user pick database search criteria in C#

The top part of this program holds three columns of controls. The left column contains combo boxes holding the names of the fields in a database table. The middle column holds operators such as =, <, and >=. The right column holds text boxes. The user can use these controls to determine how the program queries the database. For example, if the user selects the Title field from the first combo box, the >= operator from the second, and enters R in the first text box, the program searches for records where the Title field has value >= R.

The following code executes when the form loads. The Form1_Load event handler calls the PrepareForm method, which gets the form ready for work.

// The connection object. private OleDbConnection Conn; // The table's column names. private List<string> ColumnNames = new List<string>(); private string TableName = ""; // The query controls. private ComboBox[] CboField, CboOperator; private TextBox[] TxtValue; private List<type> DataTypes = new List<type>(); // Make a list of the table's fields. private void Form1_Load(object sender, EventArgs e) { // Compose the database file name. // This assumes it's in the executable's directory. string db_name = Application.StartupPath + "\\Books.accdb"; // Prepare the form for use. PrepareForm(db_name, "BookInfo"); } // Make a list of the table's field names and // prepare the first ComboBox. private void PrepareForm(string db_name, string table_name) { TableName = table_name; // Make the connection object. Conn = new OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + db_name + ";" + "Mode=Share Deny None"); // Get the fields in the BookInfo table. // Make a command object to represent the command. OleDbCommand cmd = new OleDbCommand(); cmd.Connection = Conn; cmd.CommandText = "SELECT TOP 1 * FROM " + table_name; // Open the connection and execute the command. try { // Open the connection. Conn.Open(); // Execute the query. The reader gives access to results. OleDbDataReader reader = cmd.ExecuteReader(); // Get field information. DataTable schema = reader.GetSchemaTable(); foreach (DataRow schema_row in schema.Rows) { ColumnNames.Add(schema_row.Field<string>("ColumnName")); DataTypes.Add(schema_row.Field<type>("DataType")); // Console.WriteLine( // schema_row.Field<type>("DataType").ToString()); } // Initialize the field name ComboBoxes. CboField = new ComboBox[] { cboField0, cboField1, cboField2, cboField3 }; CboOperator = new ComboBox[] { cboOperator0, cboOperator1, cboOperator2, cboOperator3 }; TxtValue = new TextBox[] { txtValue0, txtValue1, txtValue2, txtValue3 }; for (int i = 0; i < CboField.Length; i++) { CboField[i].Items.Add(""); // Allow blank. foreach (string field_name in ColumnNames) { CboField[i].Items.Add(field_name); } CboField[i].SelectedIndex = 0; // Select blank. CboOperator[i].SelectedIndex = 0; // Select blank. } } catch (Exception ex) { MessageBox.Show("Error reading " + table_name + " column names.\n" + ex.Message); } finally { // Close the connection whether we succeed or fail. Conn.Close(); } }

The PrepareForm method creates a database connection object. It then selects a record from the indicate database table so it can get information about that table. It saves the table's column names in the ColumnNames list and it saves the column data types in the DataTypes list. It then uses the ColumnNames list to initialize the left column of combo boxes that display the column names.

When the user makes selections and clicks Query, the following code executes.

// Build and execute the appropriate query. private void btnQuery_Click(object sender, EventArgs e) { string where_clause = ""; for (int i = 0; i < CboField.Length; i++) { // See if the field and operator are non-blank. if ((CboField[i].SelectedIndex <= 0) || (CboOperator[i].SelectedIndex <= 0)) { // Don't use this row. Clear it to prevent confusion. CboField[i].SelectedIndex = 0; CboOperator[i].SelectedIndex = 0; TxtValue[i].Clear(); } else { // See what delimiter we need for this type of field. string delimiter = ""; string value = TxtValue[i].Text; int column_num = CboField[i].SelectedIndex - 1; if (DataTypes[column_num] == typeof(System.String)) { delimiter = "'"; value = value.Replace("'", "''"); } else if (DataTypes[column_num] == typeof(System.DateTime)) { // Use # for Access, ' for SQL Server. delimiter = "#"; } // Add the constraint to the WHERE clause. where_clause += " AND " + CboField[i].SelectedItem.ToString() + " " + CboOperator[i].SelectedItem.ToString() + " " + delimiter + value + delimiter; } // if field and operator are selected. } // for (int i = 0; i < CboField.Length; i++) // If where_clause is non-blank, remove the initial " AND ". if (where_clause.Length > 0) where_clause = where_clause.Substring(5); // Compose the query. string query = "SELECT * FROM " + TableName; if (where_clause.Length > 0) query += " WHERE " + where_clause; // Console.WriteLine("Query: " + query); // Create a DataAdapter to load the data. OleDbDataAdapter data_adapter = new OleDbDataAdapter(query, Conn); // Create a DataTable. DataTable data_table = new DataTable(); try { data_adapter.Fill(data_table); } catch (Exception ex) { MessageBox.Show("Error executing query " + query + "\n" + ex.Message); } // Bind the DataGridView to the DataTable. dgvBookInfo.DataSource = data_table; }

This code loops through the combo boxes. If the user has selected a field name and a corresponding operator, the code adds a condition to the WHERE clause it is building. If the user leaves a field name or operator blank, the program blanks the other corresponding controls so it doesn't look like they may be contributing to the final WHERE clause.

When it builds each piece of the WHERE clause, the code uses delimiters for string and date values. It uses a single quote ' for strings, and it uses a # for dates. (Access databases require # delimiters for dates. SQL Server databases require ' delimiters for dates.)

After is has built the WHERE clause, the program composes a final query and executes it. It uses a data adapter to load the results into a DataTable and sets the form's DataGridView control's DataSource property equal to the DataTable so the user can see the result.

This is a simple example that lets you quickly build a query form for a single table, but it doesn't handle multiple linked tables.

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