Database Connectivity with ADO.NET

Database connectivity has become a routine component of today's distributed application development. A key requirement set by the diverse architectures in today's enterprise and the number of competing database products available is for an application design to be relatively independent from the underlying database, at least from a connectivity perspective.

ADO (ActiveX Database Objects) has come a long way in fulfilling this objective of providing a seamless object-oriented database-independent mechanism of interacting with relational databases. Also, developers have specifically benefited from the key productivity improvements provided by the ADO rich programming model, which is well used by object-oriented application developers.

The database connectivity class System.Data and its associated namespace, popularly known as ADO.NET, builds on the runaway success of ADO and improves on some of the key points that have not been dealt with completely in ADO. The ADO.NET communication model with the underlying data providers (also known as database drivers) is based on the industry standard, portable, platform-independent, text-based XML format. This improves interoperability within the various providers and .NET applications and services. Also, ADO.NET introduces an enhanced DataSet-based disconnected database interaction model. Essentially, DataSet is a high- powered , turbo-charged, disconnected ADO recordset.

DATASET: AN IN-MEMORY CACHE

ADO DataSets provide an in-memory cache of the underlying database tables and their related relationships. This disconnected database connectivity model coupled with connection pooling (which is by default used in ADO.NET connections) makes the development of performance-centric, scalable applications that can support a large number of users much easier, as applications no longer have to connect to databases for every piece of data.

Managed Providers

Currently, a large number of managed providers are available for different databases. Although OLE-DB Managed Provider provides access to a whole variety of databases using existing OLE-DB providers, for best performance and interoperability, the trend is to use managed providers provided by either Microsoft or the vendors. For instance, currently Microsoft provides managed providers for both SQL Server and Oracle. (Oracle was introduced in the 1.1 Framework.) Managed providers are also available for IBM DB2 (http://www-3.ibm.com/software/data/news/dotnet.html) and MySQL PostgreSql (http:// sourceforge .net/projects/mysqlnet/). Additionally, managed providers are available from third-party vendors such as DataDirect Technologies, which provides a product called Connect for .NET that includes managed providers for Oracle, DB2, SQL Server, and Sybase (http://www. datadirect .com/products/dotnet/dotnetindex.asp).

Using a DataReader

The irst step in connecting with a database is to create a Connection object. As shown in Listing 4.28, you create a SqlConnection object that provides connectivity with a SQL Server database. In this example, you are connecting with the sample Northwind demo database that is available with SQL Server. Next, you create a Command object with your SQL query. To associate the two, you assign the Connection property of the Command object to the previously created Connection object. Then you open the connection and get a reader that represents a fast, noncached, forward-only reader to read relational data.

Listing 4.28 Using a SQL DataReader

 using System; using System.Data; using System.Data.SqlClient; public class ExecSql {    public static void Main()    {       String connectionString = "Initial Catalog=Northwind;"         +Data Source=localhost;Integrated Security=SSPI";       SqlConnection con = new SqlConnection(connectionString);       String query = "select * from Orders";       SqlCommand cmd = new SqlCommand(query);       cmd.Connection = con;       con.Open();       SqlDataReader reader = cmd.ExecuteReader();       while (reader.Read())       {          Console.WriteLine("Order #{0} from Customer ({1})",            reader["OrderId"],reader["CustomerId"]);       }       con.Close();    } } 

Running the preceding application should yield the following output:

 Order #10248 from Customer (VINET) Order #10249 from Customer (TOMSP) Order #10250 from Customer (HANAR) ... 

Reading Data Using DataSets

Listing 4.29 is similar to the previous one but uses the ADO.NET DataSet instead. Similar to the first example, you create a Connection object. However, this time instead of creating a Command object, you create a DataAdapter constructed with the query string and the Connection object. Next, you create a blank DataSet object and then use the Fill method on the DataAdapter to fill the People table in the in-memory DataSet with the rows returned from the database. To illustrate that ADO.NET indeed uses an underlying XML representation, you can output the data contained in the DataSet as XML. Note: Even though in the simple example you have used only one table, DataSet does support the use of multiple tables that have relationships between them. For instance, you can easily construct a DataSet that contains both order master and order detail data with the master-detail relationship intact.

Listing 4.29 Using DataSets

 using System; using System.Data; using System.Data.SqlClient; public class ExecSql {    public static void Main()    {       String connectionString = "Initial Catalog=Book;"          +"Data Source=localhost;Integrated Security=SSPI";       SqlConnection con = new SqlConnection(connectionString);       String query = "select * from People";       SqlDataAdapter sqlAdapter = new SqlDataAdapter(query,con);       DataSet ds = new DataSet();       sqlAdapter.Fill(ds,"People");       ds.WriteXml(Console.Out,XmlWriteMode.WriteSchema);       con.Close();    } } 

Running the preceding application should yield the following output:

 <NewDataSet>   <xs:schema id="NewDataSet" xmlns=""    xmlns:xs="http://www.w3.org/2001/XMLSchema"    xmlns:msdata="urn:schemas-microsoft-com:xml-msdata">     <xs:element name="NewDataSet" msdata:IsDataSet="true">       <xs:complexType>         <xs:choice maxOccurs="unbounded">           <xs:element name="People">             <xs:complexType>               <xs:sequence>                 <xs:element name="Id" type="xs:int" minOccurs="0" />                 <xs:element name="Name" type="xs:string" minOccurs="0" />                 <xs:element name="Email" type="xs:string" minOccurs="0" />                 <xs:element name="Tel" type="xs:string" minOccurs="0" />               </xs:sequence>             </xs:complexType>           </xs:element>         </xs:choice>       </xs:complexType>     </xs:element>   </xs:schema>   <People>     <Id>1</Id>     <Name>Hitesh Seth</Name>     <Email>[e-mail]</Email>     <Tel>[phone]</Tel>   </People> </NewDataSet> 

Inserting Data

Next, the fun starts. DataSet isn't just for reading data. It is for updating, inserting, and deleting data as well. To insert a new row, create a New DataRow, assign the values of the DataRow for the various columns , and invoke the Update method on the DataAdapter to sync with the underlying back-end database, as shown in Listing 4.30. This application also illustrates the notion of XML-based Diff Grams, which contain an XML representation of the changes that have occurred to the data since the last update.

Listing 4.30 Inserting Data Using ADO.NET

 using System; using System.Data; using System.Data.SqlClient; public class Insert {    public static void Main()    {      try {       String connectionString = "Initial Catalog=Book;"         +"Data Source=localhost;Integrated Security=SSPI";       SqlConnection con = new SqlConnection(connectionString);       String query = "select * from People";       SqlDataAdapter sqlAdapter = new SqlDataAdapter(query,con);       DataSet ds = new DataSet();       SqlCommandBuilder builder = new SqlCommandBuilder(sqlAdapter);       sqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;       sqlAdapter.Fill(ds,"People");       DataRow row = ds.Tables["People"].NewRow();       row["Name"] = "Hitesh Seth";       row["Email"] = "[e-mail]";       row["Tel"] = "[phone]";       ds.Tables["People"].Rows.Add(row);       DataSet changes = ds.GetChanges();       changes.WriteXml(Console.Out,XmlWriteMode.DiffGram);       sqlAdapter.Update(ds,"People");       } catch (Exception ex) {       Console.WriteLine(ex.Message);       }    } } 

Running the preceding application should yield the following output:

 <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   xmlns:dir="urn:schemas-microsoft-com:xml-diffgram-v1">   <NewDataSet>     <People diffgr:id="People1" msdata:rowOrder="0"             diffgr:hasChanges="insert>       <Id>0</Id>       <Name>Hitesh Seth</Name>       <Email>[e-mail]</Email>       <Tel>[phone]</Tel>     </People>   </NewDataSet> </diffgr:diffgram> 

Updating Existing Data

To update existing data (shown in Listing 4.31), you need to first get a reference to a DataRow object. You can find a DataRow either through the table's primary key (which has been previously set in the database DDL schema) or by specifically assigning specific column(s) as the primary key and searching on top of that. In the example that follows , you find an existing person in the People table by the person's ID and then by updating the email address. The mechanism for deleting data is similar.

Listing 4.31 Updating Existing Data

 using System; using System.Data; using System.Data.SqlClient; public class Update {    public static void Main()    {      try {       String connectionString = "Initial Catalog=Book;"            +"Data Source=localhost;Integrated Security=SSPI";       SqlConnection con = new SqlConnection(connectionString);       String query = "select * from People";       SqlDataAdapter sqlAdapter = new SqlDataAdapter(query,con);       DataSet ds = new DataSet();       SqlCommandBuilder builder = new SqlCommandBuilder(sqlAdapter);       sqlAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey;       sqlAdapter.Fill(ds,"People");       DataRow row = ds.Tables["People"].Rows.Find(1);       row["Email"] = "[e-mail]";       DataSet changes = ds.GetChanges();       changes.WriteXml(Console.Out,XmlWriteMode.DiffGram);       sqlAdapter.Update(ds,"People");       } catch (Exception ex) {       Console.WriteLine(ex.Message);       }    } } 

Running the preceding application should yield the following output:

 <diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"   xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">   <NewDataSet>    <People diffgr:id="People1" msdata:rowOrder="0"            diffgr:hasChanges="modified">       <Id>1</Id>       <Name>Hitesh Seth</Name>       <Email>[e-mail]</Email>       <Tel>[phone]</Tel>     </People>   </NewDataSet>   <diffgr:before>     <People diffgr:id="People1" msdata:rowOrder="0">       <Id>1</Id>       <Name>Hitesh Seth</Name>       <Email>[e-mail]</Email>       <Tel>[phone]</Tel>     </People>   </diffgr:before> </diffgr:diffgram> 

Executing Stored Procedures

A command design pattern used by application developers especially for complex database updates is to utilize native database stored procedures. As shown in Listing 4.32, the Command object supports invoking stored procedures by passing the name of the stored procedure instead of the SQL query in its constructor. Then you need to set up the various input/output parameters of the stored procedure and assign the respective values. When you are done with the initialization, to execute the stored procedure you execute the command object.

Listing 4.32 Executing Stored Procedures

 using System; using System.Data; using System.Data.SqlClient; public class ExecSP {    public static void Main()    {      try {       String connectionString = "Initial Catalog=Book;"              +"Data Source=localhost;Integrated Security=SSPI";       SqlConnection con = new SqlConnection(connectionString);       SqlCommand cmd = new SqlCommand("CreatePerson",con);       cmd.CommandType = CommandType.StoredProcedure;       con.Open();       SqlParameter param = null;       param = cmd.Parameters.Add("@Name",SqlDbType.NVarChar,255);       cmd.Parameters["@Name"].Value = "John Doe";       param = cmd.Parameters.Add("@Email",SqlDbType.NVarChar,255);       cmd.Parameters["@Email"].Value = "[e-mail]";       param = cmd.Parameters.Add("@Tel",SqlDbType.NVarChar,25);       cmd.Parameters["@Tel"].Value = "[phone]";       param = cmd.Parameters.Add("@Id",SqlDbType.Int,4);       param.Direction = ParameterDirection.Output;       cmd.ExecuteNonQuery();       Console.WriteLine("New Person Created with ID:{0}",cmd.Parameters["@Id"].Value);       con.Close();       } catch (Exception ex) {       Console.WriteLine(ex.Message);       }    } } 

Running the preceding application should yield the following (or similar) output:

 New Person Created with ID:2