|
|
|
| Title | Execute ad hoc database commands such as CREATE TABLE, INSERT INTO, DROP TABLE, and so forth in Visual Basic .NET |
|---|
| Description | This example shows how to execute ad hoc database commands such as CREATE TABLE, INSERT INTO, DROP TABLE, and so forth in Visual Basic .NET. |
|---|
| Keywords | database, ADO.NET, OleDb, OLE DB, CREATE TABLE, INSERT INTO, DROP TABLE, execute database commands, connection, command, Visual Basic, VB.NET |
|---|
| Categories | Database |
|---|
|
|
|
ADO.NET doesn't provide much in the way of objects to manipulate a database's structure. Fortunately this is fairly easy 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 prorgam starts, the following code executes to prepare the connection object. It doesn't actually open the connection yet, though.
|
|
' The connection object.
Private conn As OleDbConnection
' Prepare the database connection.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
' Compose the database file name.
' This assumes it's in the executable's directory.
Dim file_name As String = Application.StartupPath + _
"\\Books.mdb"
' Connect.
conn = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" + file_name + ";" & _
"Mode=Share Deny None")
' Select the first sample command.
cboSamples.SelectedIndex = 0
End Sub
|
|
|
Use the combo box to select a sample command or type one into the textbox and then click Execute. The following code shows how the program executes the command in the textbox.
|
|
' Execute the command.
Private Sub btnExecute_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnExecute.Click
Dim cmd As New OleDbCommand()
cmd.Connection = conn
cmd.CommandText = txtCommand.Text
Try
conn.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Done")
Catch ex As Exception
MessageBox.Show("Error executing command.\n" + _
ex.Message)
Finally
conn.Close()
End Try
End Sub
|
|
|
The code creates an OleDbCommand object. It sets its Connection property so it knows over which connection to execute, 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 program use command objects.
|
| |
|
| |
|
|
