Using Connection and Command Objects

Regardless of whether a DataReader or DataSet is used to access data, an ADO.NET client application relies on a connection to the DBMS. The application can explicitly open a connection by calling the Open method of a Connection object. To open a connection using the .NET Framework data provider for SQL Server, for example, the client invokes this method on an instance of the SqlConnection class. To open a connection to some other data source, the client invokes a similar method on the Connection class provided by whatever .NET Framework data provider it's using. With the .NET Framework data provider for OLE DB, for example, the client calls Open on an instance of the OleDbConnection class. Whatever Connection class is used, the client must first set the class's ConnectionString property, indicating which database it's interested in and other information.

Once a connection exists, a client can issue queries and other commands on it using a Command object. An application can create a Command object by invoking a Connection object's CreateCommand method. Once again, different data providers use different Command object classes. The .NET Framework data provider for SQL Server uses SqlCommand, for example, while the .NET Framework data provider for OLE DB uses OleDbCommand. Whatever Command class is used, all of them allow specifying a SQL query by setting the object's CommandText property.

A client relies on Command objects to issue queries and perform other DBMS operations

Once a Command object exists, a client can choose one of several methods this object provides to execute the command it contains. Those methods are as follows:

• ExecuteReader: returns a DataReader that can be used to read the results of the query. A DataReader can access the result of a SQL query one row at a time.

• ExecuteScalar: returns a single value, such as the result from a SQL SUM function. If the result of the query contains more than one value, this method will return the value in the first column of the first roweverything else will be ignored. The value it returns is of the type System.Object, which means that it can contain a result of any type.

• ExecuteNonQuery: returns no data, but instead sends back the number of rows affected by the query. This method is used with commands that don't return results, such as SQL UPDATEs, INSERTs, and DELETEs.

A Command object provides several options for executing the operation it contains

The SqlCommand class provided by the .NET Framework data provider for SQL Server also has another choice: ExecuteXmlReader. This method returns an XmlReader object that can be used to access XML-formatted data returned by SQL Server. And regardless of which data provider a developer chooses, Command objects can also be used to execute stored procedures and can have parameters whose values are set before the command is executed.

When finished, an ADO.NET client must invoke either the Close or the Dispose method on an in-use Connection object. You can't just forget about the object and rely on garbage collection to shut down the open connectionit won't work. Each connection must be explicitly closed.

Connections must be explicitly closed

Connection objects are also used to start a transaction in the DBMS. If a client is using the .NET Framework data provider for SQL Server, for instance, calling the BeginTransaction method on a SqlConnection object instructs SQL Server to start a new transaction. This method allows the client to specify the transaction's isolation level, offering the usual choices: Serializable, RepeatableRead, ReadCommitted, and a few more. Interestingly, however, although the Connection object is used to start a transaction, it isn't used to end one. Instead, a call to SqlConnection. BeginTransaction returns an instance of the SqlTransaction class, and a client ends the transaction by calling a method in this object rather than in SqlConnection.To commit the work done in the transaction, the client calls SqlTransaction.Commit, while calling SqlTransaction.Rollback instructs the DBMS to abort the transaction, rolling back all changes made to data since the call to SqlConnection.BeginTransaction. The details of how this is done vary slightly across different data providers, but the basics are the same in every case.

Clients can start and end DBMS transactions