Export (0) Print
Expand All

Executing Updates or Database Commands using a Data Command

Visual Studio .NET 2003

Some types of data commands that you can execute against a database do not return any values except a value indicating the success of the command. These types of commands include:

  • Database definition (DDL) commands that you use to create and manage database structures such as tables and stored procedures.
  • Update commands (Update, Insert, and Delete statements).

You must use database commands to execute DDL operations; there is no other way to perform these tasks in ADO.NET.

If you are using a dataset, you do not need to use a separate data command to execute database updates. Instead, you use the data adapter to update the database. For more information, see Dataset Updates in Visual Studio .NET. However, if you are not using a dataset, you can send update commands directly to the database. For more information on the relative advantages of datasets versus data commands, see Recommendations for Data Access Strategies.

With both types of commands, the command returns an integer indicating the success of the operation. The return value differs according to whether you are updating records or issuing a DDL command:

  • If you are creating or modifying database structures, the return value is -1 if the operation was successful.
  • If you are updating records, the return value indicates the number of records affected by the operation.

In either case, if the operation fails, the command returns zero.

To execute a command to update a database

  1. Add a data command to your form or component and configure it with the SQL statement or stored procedure to execute. For more information, see Adding Data Commands to a Form or Component.
  2. If the command takes parameters, configure them. For more information, see Configuring Parameters for Data Adapters.
  3. If there are parameters, add code to set their values. For more information, see Setting and Getting Data Command Parameters.
  4. Add code to open the connection associated with the data command.
  5. Call the command's ExecuteNonQuery Method, setting the result to an integer.
    Note   Although the method returns only a single integer value, a stored procedure you call might return values using the command's Parameters collection. For more information about working with parameters, see Setting and Getting Data Command Parameters.
  6. Close the connection.

The following example shows how to create a new table in a database. The example assumes that you have already added a data command and a connection to the form or component and configured the command to use that connection. The code sets the CommandText Property to a SQL Server Create Table statement that creates a two-column table of lookup codes. The method returns -1 if the table is created successfully.

' Visual Basic
Dim newtablecmd As String
Dim cmdresults As Integer
newtablecmd = "CREATE TABLE LookupCodes (code_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, code_desc  varchar(50) NOT NULL)"

' The following two property settings can also be set 
' in the Properties window, but are shown here for completeness.
OleDbCommand1.CommandType = CommandType.Text
OleDbCommand1.CommandText = newtablecmd

' The connection must be open before you can execute a command.
OleDbConnection1.Open()
cmdresults = OleDbCommand1.ExecuteNonQuery()
OleDbConnection1.Close()
MessageBox.Show("After creating the table, results = " & cmdresults.ToString)

// C#
string newtablecmd;
int cmdresults;
newtablecmd = "CREATE TABLE LookupCodes (code_id  smallint IDENTITY(1,1) PRIMARY KEY CLUSTERED, code_desc  varchar(50) NOT NULL)";

// The following two property settings can also be done 
// in the Properties window, but are shown here for completeness.
OleDbCommand1.CommandType = CommandType.Text;
OleDbCommand1.CommandText = newtablecmd;

// The connection must be open before you can execute a command.
OleDbConnection1.Open();
cmdresults = OleDbCommand1.ExecuteNonQuery();
OleDbConnection1.Close();
MessageBox.Show("After creating the table, results = " + cmdresults.ToString());

The following example shows how to use a data command to update a database by inserting a new record into the Authors table of the SQL Server Pubs database. In this instance, the command calls a stored procedure named "NewAuthor" that is assumed to contain an Insert Into statement with nine values for the new author record. The data command OleDbCommand2 has already been configured to have a Parameters collection with nine parameters in it, one each for the parameters being passed to the stored procedure. The code sets the parameters values based on text boxes in a form, opens the connection, calls the ExecuteNonQuery method, and then closes the connection.

' Visual Basic
Dim cmdresults As Integer
' The following two property settings can also be done 
' in the Properties window, but are shown here for completeness.
OleDbcommand2.CommandText = "NewAuthor"
OleDbCommand2.CommandType = CommandType.StoredProcedure

' Set parameter values. In this case, all parameter values
' are strings.
OleDbCommand2.Parameters("au_id").Value = TextBox1.Text
OleDbCommand2.Parameters("au_lname").Value = TextBox2.Text
OleDbCommand2.Parameters("au_fname").Value = TextBox3.Text
OleDbCommand2.Parameters("phone").Value = TextBox4.Text
OleDbCommand2.Parameters("address").Value = TextBox5.Text
OleDbCommand2.Parameters("city").Value = TextBox6.Text
OleDbCommand2.Parameters("st").Value = TextBox7.Text
OleDbCommand2.Parameters("zip").Value = TextBox8.Text
OleDbCommand2.Parameters("contract").Value = CheckBox1.Checked

OleDbConnection2.Open()
Try
   cmdresults = OleDbcommand2.ExecuteNonQuery()
Catch ex as Exception
   MessageBox.Show("Failed to execute command")
End Try
OleDbConnection2.Close()
MessageBox.Show("Number of records inserted = " & cmdresults.ToString)

// C#
int cmdresults = 0;
// The following two property settings can also be done 
// in the Properties window, but are shown here for completeness.
OleDbCommand2.CommandText = "NewAuthor";
OleDbCommand2.CommandType = CommandType.StoredProcedure;

// Set parameter values. In this case, all parameter values
// are strings.
OleDbCommand2.Parameters["au_id"].Value = TextBox1.Text;
OleDbCommand2.Parameters["au_lname"].Value = TextBox2.Text;
OleDbCommand2.Parameters["au_fname"].Value = TextBox3.Text;
OleDbCommand2.Parameters["phone"].Value = TextBox4.Text;
OleDbCommand2.Parameters["address"].Value = TextBox5.Text;
OleDbCommand2.Parameters["city"].Value = TextBox6.Text;
OleDbCommand2.Parameters["st"].Value = TextBox7.Text;
OleDbCommand2.Parameters["zip"].Value = TextBox8.Text;
OleDbCommand2.Parameters["contract"].Value = CheckBox1.Checked;

OleDbConnection2.Open();
try
{
   cmdresults = OleDbCommand2.ExecuteNonQuery();
}
catch (Exception ex)
{
   MessageBox.Show("Failed to execute command");
}
OleDbConnection2.Close();
MessageBox.Show("Number of records inserted = " + cmdresults.ToString());

See Also

Introduction to DataCommand Objects in Visual Studio | Working with Data Commands: High-Level Process | Setting and Getting Data Command Parameters | Executing a Data Command that Returns a Result Set | Executing a Data Command that Returns a Single Value

Show:
© 2014 Microsoft