18.3 Methods Reference

IDbTransaction = Connection.BeginTransaction();
IDbTransaction = Connection.BeginTransaction(IsolationLevel iso);
IDbTransaction = Connection.BeginTransaction(String transactionName);
IDbTransaction = Connection.BeginTransaction(IsolationLevel iso, 
 String transactionName);

Returns a strongly typed provider-specific IDbTransaction object that governs a client-initiated transaction (such as SqlTransaction or OleDbTransaction). You enlist operations within the scope of this transaction by setting the Command.Transaction property with the IDbTransaction object. Finally, you must use one of the methods of the IDbTransaction object to either commit or roll back the complete transaction.

Parameters

IsolationLevel iso

Sets the isolation level for the transaction. The higher the isolation level, the less the chance of data errors or discrepancies such as phantom reads, and the greater the negative effect on user concurrency. See Table 16-3 for valid IsolationLevel values.

String transactionName

Assigns a name to the transaction. This is useful if you are creating nested transactions or save points and want to selectively roll back a nested transaction. See Chapter 31 for more information about the methods of the Transaction class.

Example

Here is an example that starts a transaction, enlists two commands, and commits the entire transaction:

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

string SQL1 = "INSERT INTO Categories (CategoryName, Description) " +
              "VALUES ('Beverages', 'Soft drinks')";
string SQL2 = "UPDATE Categories SET Description ='Coffee and tea' " +
              "WHERE CategoryName='Beverages'";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmdA = new SqlCommand(SQL1, con);
SqlCommand cmdB = new SqlCommand(SQL2, con);

int rowsAffected;

SqlTransaction tran = null;
try
{
    con.Open();

    // Start the transaction.
    tran = con.BeginTransaction();

    // Enlist the commands.
    cmdA.Transaction = tran;
    cmdB.Transaction = tran;

    // Execute the commands.
    rowsAffected = cmdA.ExecuteNonQuery();
    rowsAffected += cmdB.ExecuteNonQuery();

    // Commit the transaction.
    tran.Commit();
}
catch
{
    tran.Rollback();
}
finally
{
    con.Close();
}

Notes

A client-initiated transaction requires a round trip over the network to inform the data source to start, commit, or rollback the transaction. This introduces some latency and means that a client-initiated transaction can never perform quite as well as a stored procedure transaction. When using the BeginTransaction( ) method, follow the same guidelines you would when coding a transaction in SQL, and try to enclose the least amount of critical commands into a short, well-encapsulated transaction.

If possible, consider replacing client-initiated transactions with stored procedure transactions. Client-initiated transactions are subject to coding errors that can inadvertently leave the transaction running longer than intended, which has a negative effect on user concurrency.

Connection.ChangeDatabase(String databaseName);

Changes the current database used by the connection. This database is then used for all subsequent commands. This method corresponds to SQL Server's USE command. In order to invoke this method, the connection must be open.

Parameter

String databaseName

The name of the new database to attach to (e.g., "Northwind"). A provider-specific exception such as SqlException is thrown if this database doesn't exist in the data source.

Example

The following code opens a connection and executes two commands. The first command uses the Northwind database; the second uses the pubs database.

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";

string SQL1 = "UPDATE Categories SET Description='Coffee and tea' " +
              "WHERE CategoryName='Beverages'";
string SQL2 = "UPDATE Titles SET Title='The Busy Executive' " +
              "WHERE Title_Id='BU1032'";

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmdA = new SqlCommand(SQL1, con);
SqlCommand cmdB = new SqlCommand(SQL2, con);

int rowsAffected;

try
{
    con.Open();

    // Execute the first command using the Northwind database.
    rowsAffected = cmdA.ExecuteNonQuery();

    // Execute the second command using the pubs database.
    con.ChangeDatabase("pubs");
    rowsAffected += cmdB.ExecuteNonQuery();
}
finally
{
    con.Close();
}

Note

You can set the initial database for a connection using the Initial Catalog parameter in the connection string. Oracle databases don't support this setting or the ChangeDatabase( ) method.

Connection.Close();

Closes the connection. This method also rolls back any pending transactions, if necessary, and ends by releasing the connection to the connection pool (assuming connection pooling is enabled). No exception is thrown if the connection is already closed.

Note

The Close( ) method is preferred over the Dispose( ) method because the Dispose( ) method destroys the connection and doesn't return it to the connection pool. To ensure that connections are closed properly, even in the case of an unhandled error, you should close them in the finally block of an exception handler.

IDbCommand cmd = Connection.CreateCommand();

Returns a strongly typed provider-specific IDbCommand that can execute a SQL statement. This method is primarily useful when writing generic database access code that can work with more than one provider. By generating an IDbCommand object with CreateCommand( ), you don't need to create a provider-specific Command object instance.

Example

The following code uses the CreateCommand( ) method and is completely provider-agnostic (aside from the first two lines, which create the provider-specific Connection object):

string connectionString = "Data Source=localhost;" +
  "Initial Catalog=Northwind;Integrated Security=SSPI";
IDbConnection con = new SqlConnection(connectionString);

IDbCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT * FROM Categories";

try
{
    con.Open();
    IDataReader r = cmd.ExecuteReader();

    // (Read the results here.)

    r.Close();
}
finally
{
    con.Close();
}

EnlistDistributedTransaction(ITransaction transaction);

If auto-enlistment is disabled, this method enlists the Connection in the specified active distributed transaction. If the transaction is committed or rolled back, all modifications made to the data source using the Connection are also committed or rolled back.

Parameters

transaction

The existing transaction in which to enlist.

Example

The following example shows how to enlist a Connection in a distributed transaction and how to vote to commit or abort the transaction based on the success or failure of a Command executed against the data source:

// create the connection with auto-enlistment disabled
SqlConnection conn = new SqlConnection(
    "Data Source=localhost;Integrated Security=SSPI;" +
    "Initial Catalog=Northwind;Enlist=false;");

SqlCommand cmd = new SqlCommand();

//... define the command to update the data source

conn.Open();

// get the current COM+ DTC transaction,
// and enlist the connection if the transaction exists
ITransaction tran = (ITransaction)ContextUtil.Transaction;
if(tran != null)
    conn.EnlistDistributedTransaction(tran);

try
{
    // execute the command against the data source
    cmd.ExecuteNonQuery();
    // vote to commit after successful command
    ContextUtil.SetComplete();
}
catch (SqlException ex)
{
    // vote to roll back if an error occurs
    ContextUtil.SetAbort();
}
finally
{
    conn.Close();
}

Notes

Auto-enlistment is disabled for the Connection using the connection string parameter Enlist=false for a SqlConnection or using the connection string parameter OLE DB Services=-7 for an OleDbConnection.

The Connection must be open prior to calling EnlistDistributedTransaction(). An exception is raised if the Connection has already started a transaction with BeginTransaction(). If, however, a local transaction at the data source exists, it's rolled back without notification, and the Connection is enlisted in the distributed transaction.

DataTable = Connection.GetOleDbSchemaTable(Guid schema,    
    Object restrictions);

This method allows you to retrieve a schema table that includes information about the structure of your database. This can include information such as column data types, constraint, table, and view names, database owners, and so on. This method is provided only by the OLE DB provider, but SQL Server provides similar functionality through its information views, which can be queried directly (see Chapter 5 for more information and a specific example).

This method returns the requested schema information in a DataTable object.

Parameters

Guid schema

This is a globally unique identifier (GUID) that indicates the schema rowset to return. Each type of schema table is uniquely identified accordingly to OLE DB conventions. You can either create a Guid structure manually with the required value, or you can retrieve a GUID from one of the public fields of the OleDbSchemaGuid class. For example, the field OleDbSchemaGuid.Tables returns the Guid required to retrieve the list of tables and views that are accessible to the current user.

Object restrictions

An array that contains restriction values. These values are objects usually containing strings that filter the results based on any one of the returned columns. They are applied in the order that the columns are returned. Thus, the first restriction value applies to the first returned column, the second restriction value applies to the second column, and so on.

Example

The following example retrieves and displays a list of tables defined in the current database using the GetOleDbSchemaTable( ) method:

string connectionString = "Data Source=localhost;" +
  "Provider=SQLOLEDB;Initial Catalog=Northwind;" +
  "Integrated Security=SSPI";

OleDbConnection con = new OleDbConnection(connectionString);
DataTable schema;

try
{
    con.Open();
    schema = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
             new object[] {null, null, null, "TABLE"});
}
finally
{
    con.Close();
}

// Display the schema table.
foreach (DataRow row in schema.Rows)
{
    Console.WriteLine(row["TABLE_TYPE"] + ": " +
                      row["TABLE_NAME"]);
}

Notes

For more information on schema rowsets, you can refer to Appendix B of the OLE DB Programmer's Reference (see it online on the MSDN at http://msdn.microsoft.com/library/en-us/oledb/htm/oledb_providers_overview.asp). You can also refer to the fields of the OleDbSchemaGuid class in the MSDN class library reference. Each field has a list of the corresponding restriction columns.

Know that your OLE DB provider won't necessarily support all OLE DB schema rowset values. Consult your provider-specific documentation for more information.

Connection.Open();

Opens a connection using the settings that are specified in the ConnectionString property. If you use connection pooling, this method retrieves the first available connection from the pool (or creates a new connection if none is available and the maximum pool size has not been reached).

Example

When opening a connection, you should always use exception handling to ensure that the connection is closed properly, even if an error occurs. This pattern is shown here:

try
{
    con.Open();

    // (Executed commands here)
}
finally
{
    con.Close();
}

Connection.ReleaseObjectPool();

Releases the resources required for the connection pool. You can call this method if you know that no connections will be reused within the amount of time OLE DB normally keeps the connections alive. However, these resources are freed only if every connection is closed and has timed out of the pool.

Note

Using this method can compromise the effectiveness of connection pooling and so is rarely used. The resources required for the connection pool are fairly minimal, and it's rare for an application to be able to predict that connections won't be used for a certain interval of time.