SQL in a Nutshell, 2nd Edition

Executing an ADO.NET Statement

The following code fragment provides the syntax for executing a SQL INSERT statement using ADO.NET:

{Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );
statement.CommandText = "INSERT INTO authors(au_id, au_lname, " +
                        "                    au_fname, contract) " +
                        "VALUES ('xyz', 'Brown', 'Emmit', 1)";
int rowsInserted = statement.ExecuteNonQuery( );
statement.Close( );

To execute a statement using ADO.NET, take the following steps:

1. Create an ADO.NET Command object. SqlCommand is used for access to Microsoft SQL Server, OdbcCommand is used for ODBC data sources, and OleDbCommand for OLE-DB data sources.

   {Odbc|OleDb|Sql}Command statement = connection.CreateCommand( );

2. After creating the appropriate Command object, a SQL statement needs to be associated with the Command before it can be executed.

   statement.CommandText = "INSERT INTO authors(au_id, au_lname, " +
                           "                    au_fname, contract) " +
                           "VALUES ('xyz', 'Brown', 'Emmit', 1)";

3. After assignment of the SQL statement, the Command object is ready for execution.

   int rowsInserted = statement.ExecuteNonQuery( );

   There are three methods that can be used to execute ADO.NET Command objects:

   1. ExecuteReader : Executes SQL statements that return rows, such as SELECT statements. The return value is an ADO.NET DataReader object.

   2. ExecuteNonQuery : Executes SQL statements that do not typically return result sets, such as INSERT, DELETE, or UPDATE statements. The return value is an integer value equal to the number of rows affected by the ...