Title: Execute ad hoc database commands in C#

Visual Studio provides lots of data-aware controls that can help you display and edit data, but it doesn't include much in the way of objects to manipulate a database's structure. It doesn't include objects or methods that let you easily execute ad hoc database commands such as CREATE TABLE, INSERT INTO, DROP TABLE, and so forth in C#. Fortunately you can do this fairly easily by executing SQL statements.

This example uses OLE DB to connect to an Access database. (Unfortunately this technique cannot create a database but you can use any old database and then modify it using this technique.)

When the program starts, it executes the following code.

// The connection object. private OleDbConnection Conn; // Prepare the connection to open later. private void Form1_Load(object sender, EventArgs e) { // Compose the database file name. // This assumes it's in the executable's directory. string file_name = Application.StartupPath + "\\Books.mdb"; // Connect. Conn = new OleDbConnection( "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + file_name + ";" + "Mode=Share Deny None"); // Select the first sample command. cboSamples.SelectedIndex = 0; }

This code declares and initializes an OldDbConnection object to manipulate the database. (It doesn't actually open the connection yet, though.)

Use the combo box to select a sample command or type one into the text box and then click Execute. The following code shows how the program executes the command in the text box.

// Execute the command. private void btnExecute_Click(object sender, EventArgs e) { OleDbCommand cmd = new OleDbCommand(); cmd.Connection = Conn; cmd.CommandText = txtCommand.Text; try { Conn.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Done"); } catch (Exception ex) { MessageBox.Show("Error executing command.\n" + ex.Message); } finally { Conn.Close(); } }

The code creates an OleDbCommand object. It sets its Connection property so it knows over which connection to execute, and sets its Command property to the SQL statement.

The program then opens the connection, executes the command, and closes the connection. That's all there is to it.

Note that in general you should not let users execute ad hoc commands because they could destroy data, damage the database structure, and generally wreak havoc. This example is really just to show you how you can let your program use command objects do perform these sorts of actions.

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