Title: ad hoc SQL commands

This example shows how you can execute ad hoc SQL commands such as CREATE TABLE and INSERT INTO in an Access 2007 database. ADO.NET itself doesn't provide much in the way of objects to manipulate a database's structure. Fortunately you can work around 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, the following code executes to prepare the connection object.

// The connection object. private OleDbConnection Conn; // Prepare the database connection. 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.accdb"; // 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; }

Note that this code prepares the connection but doesn't actually open it.

Also note that the code uses the database provider Microsoft.ACE.OLEDB.12.0. This works with Access 2007 databases (with a .accdb extension).

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

// Execute the command. 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 = txtCommand.Text; // Open the connection and execute the command. try { Conn.Open(); cmd.ExecuteNonQuery(); MessageBox.Show("Done"); } catch (Exception ex) { MessageBox.Show("Error executing command.\n" + ex.Message); } finally { // Close the connection whether we succeed or fail. Conn.Close(); } }

This code creates an OleDbCommand object to represent the command. It sets its Connection property so it knows which connection to use, and sets its Command property so it knows what to do.

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 code use command objects to execute code composed by your program.

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