<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

ADO.NET: Execute a Command

Commands are issued against databases to take actions against data stores. For example, you could execute a command that inserts or deletes data. For more information on moving data into and out of databases, see "Update a Database from a DataSet". Commands include any command that can be issued against a database, and in the case of the OleDbCommand, can be data store specific. For example, you could issue a stored procedure call for a command, or perhaps a command to "set quoted_identifier on". Whatever the command may be, the OleDbCommand or SqlCommand can be used to get the command to your back-end data store.

With ADO classic, you could issue commands through the Command object, Connection object, or Recordset object. In ADO.NET, only the Command object executes commands.

To issue a command against a database, the Command object must have two basic things: a Connection and CommandText, both of which can be set in the constructor. To execute the command, the Connection has to be open and not in fetching state:

String InsertCmdString; InsertCmdString = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')"; SqlCommand mySqlCommand = new SqlCommand(InsertCmdString, myConnection); Dim InsertCmdString As String InsertCmdString = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')" Dim mySqlCommand As SqlCommand = New SqlCommand(InsertCmdString, myConnection) SqlCommand * myCommand; myCommand->CommandText = "Insert into Region (RegionID, RegionDescription) VALUES (100, 'Description')";

This topic covers executing non-result generating commands. To execute the command that does not return results, call the ExecuteNonQuery method.

mySqlCommand.ExecuteNonQuery(); mySqlCommand.ExecuteNonQuery() mySqlCommand->ExecuteNonQuery();

The OleDbCommand and SqlCommand objects have parameter collections that behave like the parameter collections from ADO classic. You can pass your parameters inline:

mySqlCommand.CommandText = "myStoredProc 'CustId'"; mySqlCommand.CommandText = "myStoredProc 'CustId'" mySqlCommand->CommandText = "myStoredProc 'CustId'";

Or by using the Parameters collection:

workParam = mySqlCommand.Parameters.Add("@CustomerID", SQLDataType.NChar, 5); workParam.Value = "NewID"; workParam = mySqlCommand.Parameters.Add("@CustomerID", SQLDataType.NChar, 5) workParam.Value = "NewID" workParam = mySqlCommand->Parameters->Add("@CustomerID", SQLDataType.NChar, 5); workParam.Value = "NewID";

For more information about using parameters, see Getting Out Parameters from a Stored Procedure.

The following sample shows how to execute an insert command against a SQL database using the SqlCommand.