Data Access

Data Access

In recent years, Microsoft has promoted an alphabet soup of database access technologies. First was ODBC. Then came DAO, RDO, ADO, and OLE DB, to name just a few. The .NET Framework has its own database API called ADO.NET. The bad news is that despite its name, ADO.NET has little in common with ADO. The good news is that learning the basics of ADO.NET requires all of about 15 minutes.

The classes that make up ADO.NET are found in the System.Data namespace and its descendants. Some ADO.NET classes, such as DataSet, are generic and work with virtually any kind of database. Others, such as DataReader, come in two distinctly different flavors: one for Microsoft SQL Server databases (SqlDataReader) and one for all others (OleDbDataReader). Sql classes belong to the System.Data.SqlClient namespace. They use a managed provider (that is, a database access layer that consists solely of managed code) that s optimized to work with Microsoft SQL Server databases. Significantly, Sql classes work only with SQL Server. OleDb classes, on the other hand, can be used with any database for which an OLE DB provider that is compatible with the .NET Framework is available. They tend to be somewhat slower than Sql classes because they re not optimized for any particular database and because they rely on a combination of managed and unmanaged code, but they re also more generic, enabling you to switch databases without having to rewrite your application. OleDb classes are defined in the System.Data.OleDb namespace.

ADO.NET is covered in detail in Chapter 12. The next several sections of this chapter offer an introductory look at ADO.NET, which will help you understand some of the data-aware sample programs presented in intervening chapters. For readers accustomed to working with traditional database APIs, the sections that follow also provide an educational first look at data access in the era of .NET.

DataReaders

One of the most common tasks that data-aware applications are asked to perform involves executing a query and outputting the results. For managed applications, the DataReader class is the perfect tool for the job. DataReader objects expose the results of database queries as fast, forward-only, read-only streams of data. DataReaders come in two flavors: SqlDataReader for SQL Server databases and OleDbDataReader for other types of databases. The following example uses SqlDataReader to query the Pubs database that comes with Microsoft SQL Server for all the records in the Titles table. It then writes the Title field of each record to a console window:

SqlConnection connection = new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs"); connection.Open ();

SqlCommand command = new SqlCommand ("select * from titles", connection); SqlDataReader reader = command.ExecuteReader ();

while (reader.Read ()) Console.WriteLine (reader.GetString (1));

connection.Close ();

The SqlConnection object represents the database connection. Open opens a connection, and Close closes it. SqlCommand encapsulates the query used to extract records from the database. Calling ExecuteReader on the SqlCommand object executes the command and returns a SqlDataReader object. Reading the records returned by the query is as simple as calling SqlDataReader.Read repeatedly until it returns null.

I purposely didn t include exception handling code in this sample to keep the code as simple and uncluttered as possible. In the real world, you ll want to use try/catch/finally to recover gracefully from errors and to ensure that the database connection is closed even in the face of inopportune exceptions:

SqlConnection connection = new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs"); try { connection.Open ();

 SqlCommand command = new SqlCommand ("select * from titles", connection); SqlDataReader reader = command.ExecuteReader ();

 while (reader.Read ()) Console.WriteLine (reader.GetString (1)); } catch (SqlException e) { Console.WriteLine (e.Message); } finally { connection.Close (); }

Tailoring this code to work with databases other than Microsoft SQL Server is a simple matter of changing the Sql classes to OleDb classes and modifying the connection string accordingly.

Inserts, Updates, and Deletes

A Command object s ExecuteReader method executes a query and returns a DataReader encapsulating the results. The complementary ExecuteNonQuery method performs inserts, updates, and deletes. The following code uses a SQL INSERT command to add a record to SQL Server s Pubs database:

SqlConnection connection = new SqlConnection ("server=localhost;uid=sa;pwd=;database=pubs"); try { connection.Open (); string sqlcmd =  "insert into titles (title_id, title, type, pub_id, " +  "price, advance, royalty, ytd_sales, notes, pubdate) " +  "values ('BU1001', 'Programming Microsoft.NET', " +  "'Business', '1389', NULL, NULL, NULL, NULL, " +  "'Learn to program Microsoft.NET', 'Jan 01 2002')"; SqlCommand command = new SqlCommand (sqlcmd, connection); command.ExecuteNonQuery (); } catch (SqlException e) { Console.WriteLine (e.Message); } finally { connection.Close (); }

To update or delete a record (or set of records), you simply replace the INSERT command with an UPDATE or DELETE command. Of course, there are other ways to add, modify, and remove records. The full range of options is discussed in Chapter 12.

DataSets and DataAdapters

DataSet, which belongs to the System.Data namespace, is the centerpiece of ADO.NET. A DataSet is an in-memory database capable of holding multiple tables and even of modeling constraints and relationships. Used in combination with SqlDataAdapter and OleDbDataAdapter, DataSet can handle virtually all the needs of modern-day data access applications and is frequently used in lieu of DataReader to facilitate random read/write access to back-end databases.

The following code fragment uses SqlDataAdapter and DataSet to query a database and display the results. It s functionally equivalent to the SqlData Reader example presented earlier:

SqlDataAdapter adapter = new SqlDataAdapter (  "select * from titles",  "server=localhost;uid=sa;pwd=;database=pubs" );

DataSet ds = new DataSet (); adapter.Fill (ds);

foreach (DataRow row in ds.Tables[0].Rows) Console.WriteLine (row[1]);

SqlDataAdapter serves as a liaison between DataSet objects and physical data sources. In this example it executes a query, but it s capable of performing inserts, updates, and deletes, too. For details, see you guessed it Chapter 12.