Working with Microsoft's Data Access Application Blocks

Using the Microsoft's Data Access Application Blocks (DAAB) is easy. Just follow the 5 steps below.

STEP 1: Create SQL Server Database object using a Connection String

SqlDatabase sqlDatabase 
     = new SqlDatabase(myConnectionString);

STEP 2: Create SqlCommand Object for Stored Procedures

SqlCommand sqlCommand 
  = sqlDatabase.GetStoredProcCommand("GetCustomerById") 
    as SqlCommand;

If you need to set a custom timeout value, set it here

sqlCommand.CommandTimeout = 200;

STEP 3: Pass IN, OUT, RETURN_VALUE Parameters

Use AddInParameter, AddOutParameter methods to pass parameters.

sqlDatabase.AddInParameter
    (sqlCommand, "@CustomerID", SqlDbType.Int32, 589);
sqlDatabase.AddOutParameter
    (sqlCommand, "@FirstName", SqlDbType.VarChar, 20); 

AddInParameter does not take a size parameter. It assigns a size=0 to all DBTypes. To specify a size, use the lengthier AddParameter method.
You use AddParamter for getting back the Return Value also.

sqlDatabase.AddParameter
(   dbCommand, 
    "@RETURN_VALUE", 
    DbType.Int32, 
    ParameterDirection.ReturnValue, 
    null, 
    DataRowVersion.Default, 
    null
); 

You can also pass the parameters by using the GetStoredProcCommand method. This methods takes an object[] array with the values of the parameters. Use null for OUT parameters.

object[] parameters = new object[]{"Joe", "589", null};
SqlCommand sqlCommand =
sqlDatabase.GetStoredProcCommand("GetCustomerById", parameters) as SqlCommand;

The SqlParameter[] will not work since it no longer accepts IDbDataParameter type. It just accepts an object[] array with values. The param object[] array uses Parameter Discovery to discover number of parameters and their types. It does not get the names of the parameters. So the parameter values must be same in number and in the proper order as coded in the stored procedure.

Parameter Discovery work by calling the DeriveParameter method of the SqlCommand object. Parameter discovery takes a little performance hit. It has to make an extra call to the stored procedure to get its signature. But the call is made only the first time. After that, the DAAB caches these parameters in a HashTable to be used for next time. Discovery automatically adds the return parameter as well. The return parameter name from discovery is always @RETURN_VALUE.

STEP 4: Make the call to the database

sqlDatabase.ExecuteNonQuery(sqlCommand);
sqlDatabase.ExecuteScalar(sqlCommand);

STEP 5: Close the Connection

It is good practice to keep connections open only as long as they are needed and to close them as soon as practical.

• ExecuteNonQuery, ExecuteScalar, ExecuteDataSet
  
  By design, these Database class methods handle the opening and closing of connections to the database on each call. Therefore, the application code does not need to include code for managing connections. (By default, and for performance reasons, ADO.NET returns connections to the connection pool without closing them. Therefore, you do not need to cache your Database objects.)
  
  
• ExecuteReader
  ExecuteReader method is designed to read specific portions of the data as needed, which requires an open connection. If the DAAB methods close the connection before returning the DataReader, the DataReader becomes useless to the client code. In this situation, it is considered a best practice for the application to ensure that the DataReader is closed in a timely fashion.
  
  Use the using statement to dispose off the DataReader object, which causes the connection to close.

  using (IDataReader dataReader = sqlDatabase.ExecuteReader(sqlCommand))
  {
      // Process results
  } 

  
  
• ExecuteXmlReader
  ExecuteXmlReader is similar to ExecuteReader. It also requires an open connection. But disposing or clossing the XmlReader object will not close the DB connection. You will have to explicitly close it.

  finally {
    // Close the Reader.
    if (myXmlReader != null)
    {
        myXmlReader.Close();
    }
  
    // Explicitly close the connection. The connection is not closed
    // when the XmlReader is closed.
    if (myCommand.Connection != null)
    {
        myCommand.Connection.Close();
    }
  }