ADO.NET

Dynamic SQL

When issuing dynamic SQL statements against a database, you routinely will follow this process:

Create a Connection
Create a Command using the Connection
Create a DataAdapter using the Command
Fill a DataSet using the DataAdapter

The following code snippet shows the programmatic equivalent of the steps aforementioned:

string ConnStr = "server=localhost;uid=sa;pwd=;database=pubs";
string CmdStr = "select * from authors order by au_lname";
DataSet ds = new DataSet();

// Establish Connection
SqlConnection myConnection;
myConnection = new SqlConnection(ConnStr);

// Create Command
SqlCommand objCmd = new SqlCommand(CmdStr, myConnection);

// Create Adapter
SqlDataAdapter myAdapter = new SqlDataAdapter(objCmd);

//Fill Dataset
myAdapter.Fill(ds, "Authors");

When running Insert, Update, or Delete statements, you can call a series of Execute methods on the Command object. Here are your choices for executing your command:

Method Description

ExecuteReader Executes commands that return rows, such as SELECT statements.

ExecuteNonQuery Executes non-SELECT SQL commands such as INSERT, UPDATE, and DELETE statements. This method returns the number of rows affected by the SQL statement.

ExecuteScalar Retrieves a single value (for example, an aggregate value) from a database.

ExecuteXmlReader Returns an XmlReader. Used for SELECT queries using the FOR XML extension.

Transactions

When making multiple updates to a database, you may only want the database updated if all updates are successful, or the changes rolled back in the event an error occurs anywhere within the update process. For this, you need transactions, and in the case of ADO.NET, the SqlTransaction and OleDbTransaction objects.

To create a transaction object, such as a SqlTransaction, you need to call the BeginTransaction method on a SqlConnection or OleDbConnection object. This method has four overloads:

//Begins a database transaction.
public SqlTransaction BeginTransaction();

//Begins a database transaction with the specified isolation level.
public SqlTransaction BeginTransaction(IsolationLevel);

//Begins a database transaction with the specified transaction name.
public SqlTransaction BeginTransaction(string);

//Begins a database transaction with the specified isolation level and transaction name.
public SqlTransaction BeginTransaction(IsolationLevel, string);

Here are the most common IsolationLevel enumeration values, from the .NET Framework SDK Documentation:

Enumeration Value Description

ReadCommitted Shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in non-repeatable reads or phantom data.

ReadUncommitted A dirty read is possible, meaning that no shared locks are issued and no exclusive locks are honored.

RepeatableRead Locks are placed on all data that is used in a query, preventing other users from updating the data. Prevents non-repeatable reads but phantom rows are still possible.

Serializable A range lock is palced on the DataSet, preventing other users from updating or inserting rows into the dataset until the transaction is complete.

Stored Procedures

Calling Stored Procedures involves establishing a connection, creating a SqlCommand, setting parameters, and executing the command. The same SqlCommand object used for dynamic SQL statements is also used for invoking Stored Procedures, by setting the CommandType property to CommandType.StoredProcedure. The SqlCommand class has a Parameters collection, and by adding Parameter objects to this collection, you can take care of Stored Procedure input, output, and return parameters.

The direction of the parameter, input, output, or return is indicated by the Parameter class' Direction property. The default direction is ParameterDirection.Input. Other values in the enumeration are Output and ReturnValue.

The following sample shows how to add each of these parameter types to a SqlCommand, and then execute the stored procedure. Notice how the varying ways we can create and add Parameters:

//Create Connection
SqlConnection nwindConn = new SqlConnection("Data Source=localhost;User ID=sa;Password=;Initial Catalog=northwind");
nwindConn.Open();

//Create Command w/Parameters
SqlCommand sc = new SqlCommand("InsertCategory", nwindConn);
sc.CommandType = CommandType.StoredProcedure;

//Param: Return Value
SqlParameter myParm = sc.Parameters.Add("@RowCount", SqlDbType.Int);
myParm.Direction = ParameterDirection.ReturnValue;

//Param: Input
sc.Parameters.Add("@CategoryName", SqlDbType.NChar, 15);
sc.Parameters["@CategoryName"].Value = "New Category Name";

//Param: Output
SqlParameter myOutputParam = new SqlParameter("@NewCategoryID", SqlDbType.Int);
myOutputParam.Direction = ParameterDirection.Output;
sc.Parameters.Add(myOutputParam);

//Execute Stored Procedure
sc.ExecuteNonQuery();

//Get Return and Output Values
Int32 rowCount = (Int32)sc.Parameters["@RowCount"].Value;
Int32 newID = (Int32)sc.Parameters["@NewCategoryID"].Value;

nwindConn.Close();