Direct Access to Data

< BACK  NEXT >

[oR]

Regardless of what approach 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 to SQL Server, for example, the client invokes this method on an instance of the SqlConnection class. To open a connection to some other database using the OLE DB .NET data provider, the client invokes the Open method on an instance of the OleDbConnection class. In either case, the client must first set the class's ConnectionString property, indicating which database it's interested in and other information.

A client uses a Connection object's Open method to open a connection to a DBMS

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 SQL provider uses SqlCommand, while the OLE DB provider uses OleDbCommand. Whichever is used, both allow specifying a SQL query for a Command object by setting the object's CommandText property.

A client relies on Command objects to issues queries and 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 row everything 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 also has another choice, one not provided by OleDbCommand: ExecuteXmlReader. This method returns an XmlReader object that can be used to access XML-formatted data returned by SQL Server.^[1] Command objects can also be used to execute stored procedures and can have parameters whose values can be set before the command is executed.

^[1] This option works with SQL Server 2000, which supports the FOR XML clause in a SQL statement, but it doesn't work with earlier versions.

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 connection it 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. Calling the BeginTransaction method on a SqlConnection object instructs the DBMS 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 is not 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.

Clients can start and end DBMS transactions

Direct access to data relies on DataReader objects. DataReaders are fast, and they don't use much memory, because only one row of data at a time is made accessible (although more may be cached). An application using a DataReader can read a query's results only one row at a time, and can move forward only through those results no random access is allowed. This is very simple, but it's the right solution for a significant set of applications. Like everything else, data access should be as simple as possible. There's enough inherent complexity in software development without unnecessarily adding more.

A DataReader object provides fast access to data

To read a row, the application calls a DataReader's Read method, which makes the next row from the result of the executed query accessible. (This method returns FALSE when there are no more rows to be read from the result.) Once this has been done, the contents of that row can be accessed in various ways. If you know the types of the columns in the result (which is the usual case since you probably wrote the query), the values from the current row can be read by calling the appropriately typed Get methods (officially called typed accessor methods) provided by the DataReader. For example, if a SQL query asks for a list of all employee names and ages, each row in that query's result will contain a string and an integer. To read these, a client application could use the DataReader's GetString and GetInt32 methods, respectively. The SqlDataReader and OleDbDataReader classes provide many more Get methods, each capable of reading a particular type of data in a row. It's also possible to access each column of the current row by the column name or position, but using the Get methods is more efficient.

A client reads data from a DataReader one row at a time

Here's a C# class that illustrates opening a connection, creating a command, and reading the results using a DataReader:

 using System.Data.SqlClient; class DataReaderExample {     public static void Main()     {         SqlConnection Cn = new SqlConnection(             "Data Source=localhost;" +             "Integrated Security=SSPI;" +             "Initial Catalog=example");         SqlCommand Cmd = Cn.CreateCommand();         Cmd.CommandText =             "SELECT Name, Age FROM Employees";         Cn.Open();         SqlDataReader Rdr = Cmd.ExecuteReader();         while (Rdr.Read())         {             System.Console.WriteLine(                 "Name: {0} , Age: {1} ",                 Rdr.GetString(0),                 Rdr.GetInt32(1));         }         Rdr.Close();         Cn.Close();     } } 

This example uses the SQL .NET data provider, so it begins with the appropriate using statement for this set of classes. Following this is a single class, DataReaderExample, containing the single method Main. This method begins by creating a new Connection object, passing in a very simple connection string. (The connection string format used by the OLE DB .NET data provider is essentially the same as that used by ADO, but the format used by the SQL .NET data provider is just a little different.) The example then creates a Command object and sets its CommandText property to contain a simple SQL query. Next, the Connection object's Open method is used to open a connection to the database, the command is executed by a call to ExecuteReader, and a DataReader object is returned. The result of the query is read using a simple while loop. Each iteration reads an employee name and age from the current row in the result using the appropriate Get method. When there are no more results, first the DataReader and then the Connection object are closed. And although it's not shown in this simple example, using a try/catch block to handle any exceptions that occur is a common thing to do.

Accessing relational data in a DBMS using a DataReader object is simple and fast. For applications that need nothing more than sequential access to data or straightforward updates, this approach is perfect. Not all applications can get by with this simple mechanism, however. For those that need more, ADO.NET provides the much more flexible (and much more complicated) alternative of a DataSet, described next.

DataReaders are useful, but they're not always the best approach

< BACK  NEXT >