The IDbConnection interface is the ADO.NET equivalent of the JDBC Connection interface. An explicit instance of the JDBC Connection interface is obtained via the getConnection factory method of the java.sql.DriverManager or javax.sql.DataSource interface. IDbConnection instances are created using constructors; the programmer must explicitly instantiate the appropriate implementation for the underlying data source.

The IDbConnection implementation classes accept a connection string as a constructor argument containing connection configuration parameters in the form of name/value pairs separated by semicolons, similar in nature to a JDBC URL. The connection string can be passed as an argument to the IDbConnection implementation class constructor or set via the ConnectionString property after construction. Invalid or missing values in the connection string cause a System.ArgumentException to be thrown.

Once the IDbConnection has been created, it’s prepared for use by calling the Open method. This method should be called only once for each connection; repeated calls to the Open method cause a System.InvalidOperationException to be thrown. The state of a Connection can be determined using the IDbConnection.State property. State returns one of the following values from the System.Data.ConnectionState enumeration: Broken, Open, or Closed.

The DBDemo.Connect method creates and opens an IDbConnection to a Microsoft SQL Server with the name contained in the sqlServerName field.

private void Connect() {
    // Create a connection to the specified SQL Server using
    // a database named MyDatabase and integrated Windows security
    string conStr = "Data Source=" + sqlServerName +
        " ; Database=MyDataBase;" +
        " Integrated Security=SSPI";
    sqlCon = new SqlConnection(conStr);
    // Open the SQL Server Connection
    sqlCon.Open();
}

Table 16-3 contains a summary of the commonly used parameters for the SqlConnection class.

If implemented by the data source vendor, JDBC Connection instances obtained via the DataSource.getConnection method will be pooled, meaning that a small number of connections will be shared to service requests from a larger number of clients.

All connections obtained through the .NET SQL and OLE Data Provider implementations are pooled by default. The native OLE DB implementation provides connection pooling for the OLE Data Provider, and the SQL Provider implements pooling directly in the .NET classes; consult the .NET documentation for details of the pooling strategy.

Table 16-4 summarizes the connection string parameters that can be used to specify the default connection pooling behavior of the SqlConnection class.

Connections should always be closed using the IDbConnection.Close method or the Dispose method inherited from the IDisposable interface. If connection pooling is disabled, the underlying connection to the server will be closed; otherwise, the connection is maintained and the IDbConnection instance will be returned to the connection pool for future use. The DBDemo.Disconnect method demonstrates the use of IDbConnection.Close to close a database connection.

private void Disconnect() {
    // Close the SQL Server connection
    sqlCon.Close();
}

The IDbConnection.ChangeDatabase method can be used to change the current database without the need to close and reopen a connection.

In both JDBC and ADO.NET, connection objects act as factories for creating command objects. The JDBC Connection interface defines methods for creating instances of the Statement, PreparedStatement, and CallableStatement interfaces: the createStatement, prepareStatement, and prepareCall methods, respectively. The ADO.NET equivalent is the IDbConnection.CreateCommand method; CreateCommand takes no arguments and returns an IDbCommand instance. Use of the CreateCommand method can be seen at the start of the InsertPeople, DeletePeople, and SetAge methods of DBDemo.

Both the SQL and OLE Data Provider implementations of IDbCommand can be instantiated using constructors; however, use of the CreateCommand factory method ensures that the correct implementation of IDbCommand is always instantiated, provides default configuration for IDbCommand, and improves code portability.

Once IDbCommand is instantiated, it must be configured using the properties detailed in Table 16-6. If IDbCommand is created via the IDbConnection.CreateCommand method, the Connection property will be set to the correct value for the current IDbConnection. The key properties for any IDbCommand are CommandText and CommandType; the text contained in CommandText must be compatible with the CommandType value.

private void DeletePeople() {
    // Create the SQL command to delete all records from
    // the "people" table
    IDbCommand sqlCmd = sqlCon.CreateCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "DELETE FROM people";

    // Execute the DELETE command
    sqlCmd.ExecuteNonQuery();
}

IDbCommand sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "UPDATE people SET age = @age" +
    " WHERE name = @name";

IDbCommand sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandType = CommandType.Text;
sqlCmd.CommandText = "UPDATE people SET age = ?" +
    " WHERE name = ?";

// Create a command to call the Stored Procedure SetAge
IDbCommand sqlCmd = sqlCon.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "SetAge";

Parameters for use with stored procedures or for substitution into text commands are represented by IDbDataParameter instances. An IDbDataParameter object must be instantiated and configured to satisfy each parameter specified in an IDbCommand. The IDbCommand.Parameters property contains an IDataParameterCollection in which the parameter objects are placed before the command is executed. IDataParameterCollection inherits from IList, ICollection, and IEnumerable and thus provides flexibility for adding, retrieving, and manipulating the contained IDbDataParameter instances.

If the IDbCommand uses positional parameters (as with the OLE Data Provider implementation), the IDbDataParameter instances must be placed in the collection in the same order they occur in the statement. Named parameters can be added in any order and will be mapped correctly during command execution. If a stored procedure provides a return value, the parameter to receive the return value must always be the first IDbDataParameter in the collection.

Table 16-7 summarizes the members of IDbDataParameter.

The DBDemo.SetAge method demonstrates the creation and configuration of an UPDATE command that uses parameters to change the age of a specified person:

public void SetAge(string p_name, int p_age) {
    // Create a Text command to perform an UPDATE
    // the age of a specified person
    IDbCommand sqlCmd = sqlCon.CreateCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "UPDATE people SET age = @age" +
        " WHERE name = @name";

    // Instantiate and add parameters, order is irrelevant
    IDbDataParameter nameParam = sqlCmd.CreateParameter();
    nameParam.ParameterName = "@name";
    nameParam.DbType = DbType.String;
    nameParam.Value = p_name;
    sqlCmd.Parameters.Add(nameParam);

    IDbDataParameter ageParam = sqlCmd.CreateParameter();
    ageParam.ParameterName = "@age";
    ageParam.DbType = DbType.Int32;
    ageParam.Value = p_age;
    sqlCmd.Parameters.Add(ageParam);

    // Execute the command
    sqlCmd.ExecuteNonQuery();
}

We can replace this DBDemo.SetAge implementation with a version that calls a stored procedure to update the person’s age. First create a stored procedure in MyDatabase named SetAge, which can be done with the SQL Server Query Analyzer, using the following CREATE PROCEDURE script.

CREATE PROCEDURE SetAge
    (@name varchar(20), @age int, @sex varchar(10) OUTPUT)
AS
    DECLARE @oldAge int
    SELECT @oldAge = age, @sex = sex
        FROM people WHERE name = @name
    UPDATE people SET age = @age
        WHERE name = @name
    RETURN (@oldAge)

Note that the stored procedure returns the person’s original age as its return value, so the IDbDataParameter instance to receive this value must be added to the IDbCommand.Parameters property first. With these changes in mind, here’s the revised DBDemo.SetAge method:

public void SetAge(string p_name, int p_age) {

    // Create a command to call the Stored Procedure SetAge
    IDbCommand sqlCmd = sqlCon.CreateCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "SetAge";

    // Instantiate and add parameters, return value must be first
    IDbDataParameter returnParam = sqlCmd.CreateParameter();
    returnParam.ParameterName = "@oldAge";
    returnParam.DbType = DbType.Int32;
    returnParam.Direction = ParameterDirection.ReturnValue;
    sqlCmd.Parameters.Add(returnParam);
    IDbDataParameter nameParam = sqlCmd.CreateParameter();
    nameParam.ParameterName = "@name";
    nameParam.DbType = DbType.String;
    nameParam.Direction = ParameterDirection.Input;
    nameParam.Value = p_name;
    sqlCmd.Parameters.Add(nameParam);

    IDbDataParameter ageParam = sqlCmd.CreateParameter();
    ageParam.ParameterName = "@age";
    ageParam.DbType = DbType.Int32;
    ageParam.Direction = ParameterDirection.Input;
    ageParam.Value = p_age;
    sqlCmd.Parameters.Add(ageParam);

    IDbDataParameter sexParam = sqlCmd.CreateParameter();
    sexParam.ParameterName = "@sex";
    sexParam.DbType = DbType.String;
    sexParam.Direction = ParameterDirection.Output;
    sexParam.Size = 10;
    sqlCmd.Parameters.Add(sexParam);

    // Execute the stored procedure
    sqlCmd.ExecuteNonQuery();

    // Display the return information
    Console.WriteLine("{0} was {1} years old and is a {2};" +
        " {0} is now {3} years old.",
        ((IDbDataParameter)sqlCmd.Parameters["@name"]).Value,
        ((IDbDataParameter)sqlCmd.Parameters["@oldAge"]).Value,
        ((IDbDataParameter)sqlCmd.Parameters["@sex"]).Value,
        ((IDbDataParameter)sqlCmd.Parameters["@age"]).Value);
}

Running DBDemo with the new SetAge method generates the following output:

Fred was 27 years old and is a male; Fred is now 28 years old.
Bob is 32 years old; Bob is male.
Fred is 28 years old; Fred is male.
Betty is 43 years old; Betty is female.

Notice the first line, which is generated by the new DBDemo.SetAge method. It contains Fred’s original age obtained from the stored procedure return value, as well as his sex, which was specified as an OUTPUT parameter.

The process of creating, adding, and accessing parameters is a little longwinded when working with the generic interfaces. Both the SQL and OLE Data Provider implementations provide typed convenience methods that simplify parameter management at the cost of code portability. To demonstrate this, we can cast sqlCon to an instance of SqlConnection (because DBDemo is built using the SQL Server Data Provider) and replace the SetAge method with the following implementation:

public void SetAge(string p_name, int p_age) {

    // Cast sqlCon to a SqlConnection
    SqlConnection newCon = (SqlConnection)sqlCon;

    // Create a command to call the Stored Procedure SetAge
    SqlCommand sqlCmd = newCon.CreateCommand();
    sqlCmd.CommandType = CommandType.StoredProcedure;
    sqlCmd.CommandText = "SetAge";

    // Instantiate and add parameters, return value must be first
    sqlCmd.Parameters.Add("@oldAge", SqlDbType.Int).Direction =
        ParameterDirection.ReturnValue;
    sqlCmd.Parameters.Add("@name", SqlDbType.VarChar).Value = p_name;

    sqlCmd.Parameters.Add("@age", SqlDbType.Int).Value = p_age;
    sqlCmd.Parameters.Add("@sex", SqlDbType.VarChar, 10).Direction =
        ParameterDirection.Output;

    // Execute the stored procedure
    sqlCmd.ExecuteNonQuery();

    // Display the return information
    Console.WriteLine("{0} was {1} years old and is a {2};" +
        "{0} is now {3} years old.",
        sqlCmd.Parameters["@name"].Value,
        sqlCmd.Parameters["@oldAge"].Value,
        sqlCmd.Parameters["@sex"].Value,
        sqlCmd.Parameters["@age"].Value);
}

Once the properties and parameters of the IDbCommand are configured, the command can be executed; this has been demonstrated many times in the preceding examples. As with JDBC, the method used to execute a command depends on the contents of the command and the type of result expected or desired. Table 16-8 summarizes the methods available for executing a command.

private void ListPeople() {
    // Create and configure the SQL command
    IDbCommand sqlCmd = sqlCon.CreateCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "SELECT name, sex, age FROM people";

    // Execute the SQL command and create the IDataReader
    IDataReader sqlRdr = sqlCmd.ExecuteReader();

    // Loop through the results and display each record
    while (sqlRdr.Read()) {
        Console.WriteLine("{0} is {1} years old; {0} is {2}.",
            sqlRdr.GetString(0),       // Typed get
            sqlRdr["age"],             // Named indexer
            sqlRdr[1]);                // Integer indexer
    }
}

Bob is 32 years old; Bob is male;
Fred is 28 years old; Fred is male;
Betty is 43 years old; Betty is female;