Introduction to new features of ADO.NET 2.0
Intended Readers: Persons having basic knowledge of ADO.NET 1.x and database concepts.
Level: Beginners
Introduction
.NET framework 2.0 extends almost every aspect of the .NET technology this also includes ADO.NET which contains dramatic enhancements and supports of new features; this document is all about exploring these features along with the appropriate code examples and detail descriptions. Some of the enhancements in ADO.NET 2.0 are
Base Class Provider Model
In the previous versions of ADO.NET all providers implement the common set of interfaces such as IDbConnection for connection classes, IDbCommand for command classes, and every provider need to implement both the common as well as vendor specific functionalities. ADO.NET 2.0 now provides common base classes which contain the common functionalities of data access, and every .NET data provider is responsible for providing its own specific implementation.
Now abstract DbConnection class replaces IDbConnection interface, DbCommand class replaces IDbCommand interface etc.
Abstract Factory Provider Model
ADO.NET 2.0 supports provider independent data access through factory classes which implements the classic abstract factory pattern. Every .NET data provider now includes a factory class for creation of connection, commands etc.
In ADO.NET 2.0 every data provider need to be installed in Machine.config and it includes DbProviderFactory class for enumerating the installed providers and creating the instance of a particular provider factory. Following code enumerates the .net data provider installed in machine.config.
static void EnumerateFactories()
{
DataTable factories = DbProviderFactories.GetFactoryClasses();
// display the columns in factories table
foreach (DataColumn col in factories.Columns)
{
Console.Write(col.ColumnName + "\t");
}
foreach (DataRow row in factories.Rows)
{
for (int a = 0; a < factories.Columns.Count; a++)
{
Console.Write(row[a] + "\t");
}
Console.WriteLine();
}
}
The Following example demonstrates the provider implement data access and not a single line of code contain provider specific information.
static void ProviderIndependent()
{
DbProviderFactory dbFactory;
dbFactory = DbProviderFactories.GetFactory("System.Data.SqlClient");
using (DbConnection conn = dbFactory.CreateConnection())
{
conn.ConnectionString = "<>";
conn.Open();
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select * from Students";
DbDataReader reader = cmd.ExecuteReader();
// .. manipulate reader
}
}
Batch Updates
In the previous versions of ADO.NET, data adapters update method round trips to the data source for every modified row in the dataset. When number of modified row increases the update process becomes slow and inefficient. Batch update makes this process efficient by making a batch of modified rows and hits database once per batch. DataAdapters.UpdateBatchSize property represents the size of batch in rows.
Multiple Active Resultsets
In ADO.NET 1.x we cannot open more than one active datareaders on the same connection, but ADO.NET 2.0 relaxes this restriction and now we can have more than one active datareaders at the same time but from the different command objects. This feature is only supported in SqlClient data provider as demonstrate in the following code
static void MultipleResultset()
{
SqlConnection conn = new SqlConnection("<>");
conn.Open();
SqlCommand cmd1 = new SqlCommand("Select * from Students order by SID", conn);
SqlDataReader reader1 = cmd1.ExecuteReader();
SqlCommand cmd2 = new SqlCommand("Select * from Students order by Name", conn);
SqlDataReader reader2 = cmd2.ExecuteReader();
while (reader1.Read() && reader2.Read())
{
Console.WriteLine("Reader 1 = " + reader1.GetValue(0).ToString() + reader1.GetValue(1).ToString());
Console.WriteLine("Reader 2 = " + reader2.GetValue(0).ToString() + reader2.GetValue(1).ToString());
}
reader1.Close();
reader2.Close();
conn.Close();
}
Bulk Copy
Bulk copy is also a new feature in the SqlClient, which copies the bulk of information to the database efficiently. SqlBulkCopy is a new class in SqlClient which copies bulk of record from datareader, datatable, array, collection into the database. Following code example demonstrates this
static void BulkCopyDemo()
{
DataSet ds = new DataSet();
DataTable table = new DataTable("table");
table.Columns.Add("SID", typeof(string));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("CGPA", typeof(float));
table.Columns.Add("Class", typeof(string));
for (int a = 1; a < 100; a++)
{
DataRow row = table.NewRow();
row["SID"] = a;
row["Name"] = "Nasir " + a;
row["CGPA"] = 3.0;
row["Class"] = "MS " + a;
table.Rows.Add(row);
}
ds.Tables.Add(table);
SqlBulkCopy bc = new SqlBulkCopy("<>");
bc.DestinationTableName = "Students";
bc.WriteToServer(ds.Tables[0]);
}
Asynchronous Commands
.NET framework supports asynchronous method invocations through BeginXXX and EndXXX methods, in ADO.NET 2.0 this asynchronous paradigm is adopted in the database operations, ExecuteNonQuery, ExecuteReader, ExecuteXmlReader comes with their BeginXXX and EndXXX counterparts for asynchronous operations. Asynchronous commands only supported in SqlClient data provider and async=true must be provided in the connection string of SqlConnection. I.e.
static SqlConnection conn;
static SqlCommand cmd;
static void AsyncDemo()
{
conn = new SqlConnection("<>;async=true");
conn.Open();
cmd = new SqlCommand("Select * from Students", conn);
cmd.BeginExecuteReader(new AsyncCallback(MyCallBack), null);
// Do Some other work
Console.WriteLine("Im Waiting for Enter Key");
Console.ReadLine();
}
static void MyCallBack(IAsyncResult ar)
{
if (ar.IsCompleted)
{
SqlDataReader reader = cmd.EndExecuteReader(ar);
while (reader.Read())
{
Console.WriteLine(reader.GetValue(0).ToString() + reader.GetValue(1).ToString());
}
reader.Close();
conn.Close();
}
}
Provider Statistics
ADO.NET 2.0 supports provider statistics on the connection, these statistics are useful to do the real time monitoring of database application. By default statistics monitoring is disabled and we change this through SqlConnection.StatisticsEnabled property. Following code demonstrate the connection statistics.
static void TryStatistics()
{
SqlConnection conn = new SqlConnection("<>");
conn.Open();
conn.StatisticsEnabled = true;
SqlCommand cmd = new SqlCommand("Select * from Students", conn);
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Console.WriteLine(reader.GetValue(0).ToString() + reader.GetValue(1).ToString());
}
Hashtable stats = (Hashtable)conn.RetrieveStatistics();
IDictionaryEnumerator e = stats.GetEnumerator();
while (e.MoveNext())
{
Console.WriteLine("{0} : {1}", e.Key, e.Value);
}
conn.Close();
}
SqlDependency and SqlNotificationRequest
Probably the greatest feature of all, the database notifications which provides event based notification depending upon the change in the database, this functionality is specific to the SQL Server 2005 and obviously SqlClient.
static void DependencyDemo()
{
SqlConnection conn = new SqlConnection("<>");
conn.Open();
SqlCommand cmd1 = new SqlCommand("Select * from Students", conn);
SqlDependency dep = new SqlDependency(cmd1);
dep.OnChange += new OnChangeEventHandler(dep_OnChange);
cmd1.ExecuteReader();
// Wait for some notification, its time change the database
Console.ReadLine();
conn.Close();
}
static void dep_OnChange(object sender, SqlNotificationEventArgs e)
{
Console.WriteLine("Notification received" + e.Info);
}
System.Transactions
.NET framework 2.0 introduces a new specialized namespace for transactions called System.Transactions, which provides distributed transactions support and transactions consist of multiple databases. ADO.NET 2.0 also supports promotable transactions which are capable of spanning into multiple databases.
Miscellaneous Features
- SqlClient in ADO.NET 2.0 don’t use the MDAC components compared to ADO.NET 1.x.
- Connection Pooling enhancements, and support of explicit clearance of pool is supported.
- Connection String Builder (DbConnectionStringBuilder), this class is useful for building the connection string and provides the individual access to the different properties of connection string.
- Load and Save methods of DataTable class.
- DataSet.Remoting format which determines the serialize format used in remoting and webservices.
Conclusion
ADO.NET 2.0 contains major enhancements and new features as described through out the document. Some of the enhancements are specific to SqlClient and SQL Server 2005 and other are general through out the ADO.NET. This is not the comprehensive list of features and enhancements there are still more to explore.
Your comments and feedbacks are always welcome