Export (0) Print
Expand All
Creating Audit Tables, Invoking COM Objects, and More
Exception-handling Techniques
Exploring SQL Server Triggers: Part 2
Deliver User-Friendly Reports from Your Application with SQL Server Reporting Services
SQL Server: Display Your Data Your Way with Custom Renderers for Reporting Services
Updating Data in Linked Servers, Information Schema Views, and More
XML Features in SQL Server 2000
Expand Minimize

ADO.NET Primer

SQL Server 2000
 

Eric Schmidt
Microsoft Corporation

March 2002

Applies to:
    Microsoft® ADO.NET
    Microsoft SQL Server™ 2000

Summary: Explains the benefits of using ADO.NET with SQL Server 2000. (20 printed pages)

Contents

Introduction
Brief Background
Architecture
Namespace Review
Resources

Introduction

The purpose of this paper is to provide a concentrated, yet pragmatic, overview of ADO.NET by highlighting the performance and usability benefits of using ADO.NET with Microsoft® SQL Server™ 2000. The intended audience is architects and developers who are familiar with ADO and are interested in learning about data access in the Microsoft .NET Framework.

Brief Background

Almost every software application is driven by data access and data management-based code. This is most prevalent in business process-focused applications in which data-rich objects are the core of applications. An integral part of these data driven applications are the APIs used for data access and data manipulation. These APIs are what ultimately drive the features produced by the developer, the maintainability of the application, and the extensibility of the application.

The evolution of data access API has been a painfully iterative process focusing predominantly on how to deal with relational data in a more flexible manner. We have seen the rise and fall of ODBC, Microsoft® Jet, Data Access Objects (DAO) and Remote Data Objects (RDO), in addition to many non-Microsoft-based APIs. These APIs did not bridge the gap between object-based and semi-structured (XML) data programming needs, on the one hand, and the rigid world of normalized relational data, on the other. Combine this problem with the task of dealing with heterogeneous data stores, non-relational data like XML and applications operating across multiple languages and you have a tremendous opportunity for complete re-architecture.

Embracing similar challenges in the data access space, Microsoft has spent a great deal of time and effort redesigning the basic APIs that are used to build applications. The main deliverable from this process is the Microsoft .NET Framework. One of the primary design goals for the .NET Framework was to provide developers a more simplified and extensible development platform for distributed application development. In addition, the .NET Framework should be able to be used to build any type of application from the simplest console application to a completely distributed XML Web service driven application.

What is ADO.NET? From an architect's perspective ADO.NET represents the abstract design concepts used to build the data access classes within the .NET Framework. These classes will be reviewed later, but it is first important to understand why these classes were created and how they evolved from current data access APIs. There were several main design goals driving ADO.NET:

  • Explicit and factored object model. ADO.NET is designed to be a simple to use object model in which the developer has complete control over how to control data source connectivity, command execution, and data manipulation.
  • Disconnected data cache model. N-tier programming and XML Web service architecture require that applications can participate in a disconnected, loosely coupled manner. ADO.NET provides a comprehensive caching data model for marshalling data between applications or services and then to optimistically update the original data sources or source.
  • XML support. XML is the key to building interoperable applications and more robust data processing models. XML support has been built directly into the .NET Framework. ADO.NET leverages this implementation by providing a seamless interaction with XML in a relational manner or in a native XML manner.
  • Leverage existing ADO knowledge. Although the ADO.NET object model is different from the existing ADO model, the basic constructs are the same. The ADO.NET object model consists of a provider, connection, and command objects. Thus current ADO developers should be able to efficiently migrate to ADO.NET.

From a developer's perspective ADO.NET represents the concrete implementation of classes inside the .NET Framework used for data access. The following section reviews the overall physical architecture and hierarchy of the classes.

Architecture

The ADO.NET architecture can be divided into two logical pieces: command execution and caching. Command execution requires features like connectivity, execution, and reading of results. These features are enabled with .NET data providers. Caching of results is handled by the DataSet.

The provider enables connectivity and command execution to underlying data sources. Note that these data sources do not have to be relational databases. Once a command has been executed the results can be read using a DataReader. A DataReader provides efficient forward-only stream level access to the results. In addition, results can be used to render a DataSet a DataAdapter. This is typically called "filling the DataSet."

The DataSet object represents a disconnected cache of data. This cache is made up of DataTables and DataRelations that represent the results of the command. The DataSet tracks changes to the underlying data in the cache. Changes can be submitted back to the original data source by using the DataAdapter and applicable Insert, Update, or Delete commands. The DataSet also provides a direct XML view of the underlying data in the cache. This XML can be manipulated with XML standards like XPath and XSLT.

Aa902662.sql_adonetprimer_01(en-us,SQL.80).gif

Figure 1

In addition to being filled through a .NET data provider, the DataSet can also be filled with raw XML data simply by loading it from a file or XML Document. Further details about the XML integration within ADO.NET appear later in this document.

The .NET provider objects are provider-specific. The above objects (Connection, Command, DataAdapter, DataReader) are abstract representations that each provider developer could implement. Thus there will be separate Connection, Command, DataReader, and DataAdapter implementations for each data source. This is much different from the existing ADO model in which OLE DB providers integrate into the common ADO connection, Command, and Recordset objects. This approach was taken in order to let the .NET data provider implementer take full advantage of the underlying features of the data source. This removes several layers of abstraction inherent in the OLE DB provider model, and so improves performance and simplifies the object model.

Namespace Review

This section reviews the primary namespaces in the .NET Framework that are associated with data access and data manipulation. These namespaces contain both abstract and concrete classes. The majority of ADO.NET is physically housed within the "System.Data" assembly. Keep in mind that a namespace can be partitioned across assemblies; however, they are normally contained within one assembly.

System.Data

The "System.Data" namespace contains the basic classes and interfaces that make up ADO.NET. The primary class within "System.Data" is the DataSet. The DataSet represents an in-memory cache of data. Note, this data can come from various sources, including relational data stores and XML files.

Note   Current ADO users sometimes initially view the DataSet as the ADO Recordset object. As you will see, the DataSet is much more powerful and flexible than the ADO Recordset.

The DataSet contains DataTables or a DataTable. A DataTable contains DataRows and DataColumns.

Example 1

The following example shows how to load XML into the DataSet. This example lets the DataSet infer the structure of the XML. Examples in the following sections show how to use XML schema to predefine the DataTable and DataRelation structure using schema before loading the XML.

private static void LoadDataSetWithXML()
{
try
    {
string xml="<customername='BillGates'>
<order><ItemProductId='100'/></order></customer>";
      StringReader reader =  new StringReader(xml);

      DataSet ds = new DataSet("XMLData");
      ds.ReadXml(reader);

      StringWriter writer = new StringWriter();
      ds.WriteXml(writer);

      Console.WriteLine(writer.ToString());
   }
   …
}

Example 2

The following example demonstrates how to enumerate over all of table contained within the DataTables collection of the DataSet. The sample prints out the inferred table names from the XML loaded in Example 1.

private void PrintTablesRowsColumns(DataSet myDataSet)
{
   // For each table in the DataSet, print the name of the table
   foreach(DataTable thisTable in myDataSet.Tables)
   {
       Console.WriteLine("Table: " + thisTable.TableName);
   }
}

"System.Data" also contains additional classes for defining relationships and constraints. These topics are beyond the scope of this document; for more information see the .NET Framework SDK class library reference section.

Finally, "System.Data" contains a series of interfaces that builders of .NET data providers would use to implement their own provider. The primary interfaces for implementing a provider are IDbConnection, IDbCommand, IDataReader, and IDbDataAdapter. Building custom .NET data providers is beyond the scope of this paper; the .NET Framework SDK includes a complete section on building custom providers. However, the IDataReader interface requires a bit more focus because it provides functionality not previously offered by ADO.

The IDataReader interface is implemented by .NET data providers to provide forward-only stream access to results returned from commands executed on a relational data source. Unlike the DataSet (which is an in-memory cache), implementations of IDataReader do not cache data, thus providing a more efficient environment for scanning and reading results. Correctly built IDataReader implementations, like the SqlDataReader, will provide you with the most efficient access to command results. This is a tremendous performance enhancement for developers needing fast firehouse access to their data. See the Performance section for more details.

Example 3

This sample shows how to use the SqlDataReader built for SQL Server, which implements IDataReader. The SqlDataReader is a class within the "System.Data.SqlClient" namespace (this is covered later in this document). Remember that each .NET data provider will have its own IDataReader implementation; therefore the SQL Server-specific classes are housed within the "System.Data.SqlClient" namespace.

This code is simple, but it is important to note that you should be explicit with your implementation. Make sure you specify the CommandType. In addition, you will need to understand the types of data that you will be pulling from the reader. For example, when calling the Read method the reader advances to the next logical row in the result. This logical row is physically accessed with Get methods on ordinal positions (or by column name) within the current row buffer. The developer is responsible for understanding the types and positioning of the results. The sample below requires a connection to SQL Server which will be described later in the document.

private static void UseSqlDataReader(SqlConnection con)
{
try
   {
      SqlCommand command = new SqlCommand();
      command.CommandType = System.Data.CommandType.Text;
      command.CommandText = "SELECT EmployeeID, LastName, FirstName,
        BirthDate FROM Employees";
      command.Connection = con;
      SqlDataReader reader = command.ExecuteReader();

      while(reader.Read())
      {
         Console.WriteLine("Record:");
         Console.WriteLine(reader.GetSqlInt32(0));
         Console.WriteLine(reader.GetSqlString(1));
         Console.WriteLine(reader.GetSqlString(2));
            Console.WriteLine(reader.GetSqlDateTime(3));
         Console.WriteLine();
      }
   }
}

System.Data.Common

The "System.Data.Common" namespace contains a set of abstract classes that are commonly used to build .NET data providers. Since these are classes, abstract users cannot directly consume them. You must either inherit from these classes, in the case of building your own .NET data provider, or use classes that aggregate them like the SQL Server .NET Data Provider or the OLE DB .NET Data Provider.

For more information on building .NET data providers, see Implementing a .NET Data Provider.

System.Data.OleDb (.NET OLEDB Data Provider)

The "System.Data.OleDb" namespace contains all of the classes associated with the OLE DB .NET Data Provider. These classes are used to connect and interact with existing OLE DB provider-enabled sources. These underlying OLE DB providers must be OLE DB 2.0 compliant or greater; however, none of the OLE DB 2.5 interfaces for Web publishing or OLAP interfaces are supported by the OLE DB .NET Data Provider.

"System.Data.OleDb" was built and tested against the following Microsoft OLE DB providers:

  • SQLOLEDB—OLE DB Provider for SQL Server
  • MSDAORA—OLE DB Provider for Oracle
  • Microsoft.Jet.OLEDB.4.0—OLE DB Provider for Jet

The OLE DB .NET Data Provider does not provide access to ODBC sources with the MSDASQL OLE DB Provider for ODBC. If you are using ODBC as your means of connectivity, you will need to use the ODBC .NET Data Provider. It is highly recommended that you use a native .NET data provider or compliant OLE DB provider when possible.

The commonly-used classes in "System.Data.OleDb" are as follows:

  • OleDbConnection—used for connecting to OLEDB enables data stores
  • OleDbCommand—used for executing commands against data sources
  • OleDbDataAdapter—used for filling a dataset with the results of an executed command and updating the data source
  • OleDbDataReader—provides forward-only record level access

Example 4

The following example shows how to connect to SQL Server using the OLE DB Provider for SQL Server (SQLOLEDB):

private static OleDbConnection BuildOleDbConnection()
{
OleDbConnection connection = new OleDbConnection();
// Connecting to SQL Server via OLEDB
   connection.ConnectionString = "Integrated Security = SSPI;
  Provider=SQLOLEDB;
Data Source = localhost; Database = Northwind";
   connection.Open();
   return (connection);
}
Note   You should use a native .NET data provider when possible to interact with your data source. Using the OLEDB .NET Data Provider will incur a performance penalty due to the usage of the underlying OLE DB service layer. If you are connecting to an instance of SQL Server, use "System.Data.SqlClient" classes instead.

System.Data.SqlClient (.NET SQL Server Data Provider)

The "System.Data.SqlClient" namespace contains all of the classes for the SQL Server .NET Data Provider. This provider should be used to access SQL Server 2000 and SQL Server 7.0 databases from the .NET Framework. This provider was specifically designed to work in an optimized and efficient manner with SQL Server.

The commonly used classes in "System.Data.SqlClient" are as follows:

  • SqlConnection—used for connecting to data sources
  • SqlCommand—used for executing commands against data sources
  • SqlDataAdapter—used for filling a dataset with the results of an executed command and updating the data source
  • SqlDataReader—provides forward-only record level access

Example 5

The following demonstrates how to connect to SQL Server with the SqlConnection class.

private static SqlConnection BuildConnection()
{
SqlConnection connection = new SqlConnection();
   connection.ConnectionString = "Integrated Security = true;
Data Source = localhost; Initial Catalog = Northwind;";
connection.Open();
   return (connection);
}

This connection can then be used to by a SqlCommand to execute commands. You cannot execute commands or queries directly from a SqlConnection, unlike ADO. You will find that the entire ADO.NET class model is entirely factored; this means that each class performs an atomic function. Another example of this is how transactions are managed. The connection class provides a BeginTransaction method. This method returns a SqlTransaction object. Further transaction management is done with the SqlTransaction class.

Example 6

The following example shows how to create a SqlCommand instance to execute a stored procedure. This example requires the connection from Example 5.

private static SqlCommand BuildSqlCommand(SqlConnection connection)
{
SqlCommand command = new SqlCommand();
   command.CommandType = System.Data.CommandType.StoredProcedure;
   command.CommandText = "CustOrdersDetail";
   command.Connection = connection;
   command.Parameters.Add("@OrderID", SqlDbType.Int);
   command.Parameters[0].Value = 10248;

   return (command);
}

Example 6 builds a SqlCommand for the CustOrdersDetail stored procedure in the SQL Server 2000 Northwind database. The SqlCommand class is very simple to use. Again, you must be explicit in how you build your command. Typically you will need to set the CommandType and CommandText properties. Next, by using the command's parameters collection, you can create the necessary input or output parameters. For performance purposes it is best to be as explicit as possible and use the SqlDbType enumerators to specify the data type of the parameter.

Example 7

The following example demonstrates using SqlDataReader to read the results of an executed SqlCommand. This example requires the command from Example 6.

private static void ExecuteReaderSqlCommand(SqlCommand command)
{
SqlDataReader reader = 
  command.ExecuteReader(CommandBehavior.CloseConnection);

   int recordCount = 0;
   while(reader.Read())
   {
Console.WriteLine("Record#: " + ++recordCount);
      Console.WriteLine(reader.GetSqlString(0));
      Console.WriteLine(reader.GetSqlMoney(4));
   }
   reader.Close();
}

Executing SqlCommand couldn't be easier. The reader will automatically close the underlying connection when the reader is closed. This is a nice shortcut so you don't forget to explicitly close connections. Using the Read() method to navigate through the results in a row-by-row manner, you can access the column data by order or by column name. Once you know the column that you want to read you can use the SQL Server-specific typed getters or the .NET Framework-typed getters. For performance reasons, it is best to use the SQL Server getters when possible. Finally, SqlDataReader does not have the concept of record count thus you can implement this feature where you see applicable.

Example 8

The following example demonstrates building a DataSet from a SqlDataReader. This sample requires the command created in Example 6.

public static DataSet BuildDataSetFromSqlCommand(SqlCommand command)
{
   DataSet dsOrders = new DataSet();

   SqlDataAdapter adapter = new SqlDataAdapter();
   adapter.SelectCommand = command;
   adapter.Fill(dsOrders,"OrderDetails");

   return (dsOrders);
}

In Example 8 the underlying results of the SqlCommand are read into the DataSet. The SqlDataAdapter acts a bridge between the SqlCommand and the DataSet. The SqlDataAdapter can also be used to execute insert, update and delete commands on changes made to the DataSet data cache. Here the SelectCommand property is set to the stored procedure command of the previous example that selects order details. Calling the adapters Fill() method, executes the underlying command and reads the results into the DataSet. The DataSet contents are then read with the previous DataTable enumerator example.

System.Data.SqlTypes

SQL Server and the .NET Framework are based on different typing systems. For example, the decimal type (System.Decimal) in the .NET Framework has a maximum scale of 28, whereas SQL Server supports a decimal with a maximum scale of 38. If you are dealing with very precise financial or scientific data, data might be lost. Given these type differences, the "System.Data.SqlTypes" namespaces contain structures and classes for native SQL Server type usage in order to maintain data fidelity during read and write operations. In addition, these SqlTypes can be used to increase performance by avoiding conversion of types between the .NET Common Type System. Finally, all SqlTypes are implemented from the INullable interface. This provides the ability to have the SqlType contain a null value representing the absence of data rather than a null object reference.

In order to benefit from using the SqlTypes all possible members or fields that map to return data need to be of a SqlType. Example 9 provides an illustration.

Example 9

The following sample shows how to use the SqlDataReader's "SqlDataType" enabled getters. The sample is based on a test table called tblSqlTypeDemo that has three columns.

CREATE TABLE [dbo].[tblSqlTypeDemo]
(
   [Id] [int] NOT NULL ,
   [Price] [money] NULL ,
   [CostRatio] [decimal](29, 29) NOT NULL
) ON [PRIMARY]

The reader maps the underlying data to local variables based on SqlTypes. Additional comments have been added to show some different behavior when .NET common types are used.

private static void UseSqlTypes(SqlConnection connection)
{
SqlCommand command = new SqlCommand();
   command.CommandText = "Select Id, Price, CostRatio From 
     tblSqlTypeDemo";
   command.Connection = connection;
   command.CommandType = System.Data.CommandType.Text;

   SqlDataReader reader = 
     command.ExecuteReader(CommandBehavior.CloseConnection);

   while(reader.Read())
   {
      // This won't compile if id is declared as type int
      // Int32 id = reader.GetInt32(0) would require type conversion
      SqlInt32 id = reader.GetSqlInt32(0);

      SqlMoney price = reader.GetSqlMoney(1);
      // Although legal, this will throw a runtime error
      // because price as type decimal does not support
      // nullablity. However, the underlying database
      // type does allow and contains null values.
      /*
      decimal price = reader.GetDecimal(1);
      */

      // This would fail if costRatio was typed as
      // decimal because GetSqlDecimal returns a decimal
      // of scale 29
      SqlDecimal costRatio = reader.GetSqlDecimal(2);

      // Although legal, this will throw a runtime error
      // because the underlying value has a scale greater
      // than 28 and is using the .NET CTS-based getter.
      /*
      SqlDecimal costRatio = reader.GetDecimal(2);
      */

      Console.WriteLine("Successfully read with SqlTypes...");
   }
   reader.Close();
}

For most scenarios SqlDataReader will perform implicit conversions for you if you provide the appropriate cast. For example, the following code is valid, but you would have null checking and possible type conversion exception handling. In addition, this is less efficient because the buffer has to be read and then copied to a variable of the type integer.

int id = (int)reader.GetSqlInt32(0);

System.Xml.XmlDataDocument

XML support is built into ADO.NET at a fundamental level. More important, the XML classes in the .NET Framework and ADO.NET are part of the same architecture. This provides developers with a unified programming environment for XML and traditional data access programming.

The System.Xml.XmlDataDocument class is the bridge between structured XML data and relational data stored in the DataSet. The XmlDataDocument inherits from the XmlDocument; therefore it implements all of the standard based navigation and editing capabilities of the XML Document Object Model (DOM). For more information, see Document Object Model (DOM) at the W3C Architecture domain (http://www.w3.org/DOM/). Also, the XmlDataDocument can be queried with XPath and transformed with XSLT. Finally, the XmlDataDocument provides real-time synchronization of changes made to data in the source DataSet and visa versa. This includes keeping track of before and after state during edits.

Before looking at how the XmlDataDocument works, it is prudent to review XML Schema (XSD) technology. For more information, see XML Schema at the W3C Architecture domain (http://www.w3.org/XML/Schema). XSD provides facilities for describing the structure and constraining the contents (including data-typing) of XML documents. XSD is one of the most important XML technologies because without it XML documents would be meaningless and ambiguous. XSD technology is used throughout the .NET Framework for validation and various mapping services. There will be more discussion about XML schema later in this paper.

As stated previously, XmlDataDocument is based on the DOM, which is an in-memory tree structure representation of XML data. The XML in the XmlDataDocument is actually contained within the DataSet it is associated with. This connection enables real-time editing of data between the DataSet or the XmlDataDocument. Changes are simultaneously updated in both views.

Click here for larger image

Figure 2 (click picture to see larger image )

To effectively navigate and query the XmlDataDocument or DataSet it is important to use XML Schema to define the structure and hierarchy of the XML data. The DataSet has a ReadXMLSchema method that will read an XML schema or XDR-based schema into the DataSet. The DataSet uses schema information to build the appropriate DataTable and relationship mapping to cache future incoming data. It's important to note that the data can be XML or relational.

Consider the following XSD-based schema. The complexType bookType is hierarchical in nature: the element contains attributes and child elements. Typically in a relational environment this would be flattened into one table or row. The DataSet has a built-in set of rules that determine how to appropriately map common hierarchies found in schema and XML document instances.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
   <xsd:element name="bookstore" type="bookstoreType" />
   <xsd:complexType name="bookstoreType">
      <xsd:sequence maxOccurs="unbounded">
         <xsd:element name="book" type="bookType" />
      </xsd:sequence>
   </xsd:complexType>
   <xsd:complexType name="bookType">
      <xsd:sequence>
         <xsd:element name="title" type="xsd:string" />
         <xsd:element name="author" type="authorName" />
         <xsd:element name="price" type="xsd:decimal" />
      </xsd:sequence>
      <xsd:attribute name="genre" type="xsd:string" />
   </xsd:complexType>
   <xsd:complexType name="authorName">
      <xsd:sequence>
         <xsd:element name="first-name" type="xsd:string" />
         <xsd:element name="last-name" type="xsd:string" />
      </xsd:sequence>
   </xsd:complexType>
</xsd:schema>

Example 10

The following example demonstrates using the DataSet to read schema. Using the ReadXmlSchema() method you can load either XSD or XDR based schema. ReadXmlSchema is overloaded to enable reading from a file, stream, or XmlDocument.

private static DataSet ReadXMLSchema()
{
string path = System.AppDomain.CurrentDomain.BaseDirectory;

   DataSet ds = new DataSet();

   ds.ReadXmlSchema(path + @"..\..\BookStore.xsd");

   return (ds);
}

Aa902662.sql_adonetprimer_03(en-us,SQL.80).gif

Figure 3

Now that the DataSet has a defined table and relation structure, hierarchical XML data can be loaded into the DataSet. The data will be mapped to the appropriate tables.

Example 11

The following example demonstrates loading XML into the DataSet based on a defined schema. This sample uses the previously schema loaded DataSet and reads some hierarchical XML data into the existing DataTables.

private static DataSet LoadXmlWithSchema(DataSet ds)
{
string path = System.AppDomain.CurrentDomain.BaseDirectory;

   ds.ReadXml(path + @"..\..\BookStore.xml");

   return (ds);
}

When this code is executed you will see that the data is now appropriately mapped to the underlying tables. Notice that the author table has no rows; however, the table does exist because it was part of the original schema.

Aa902662.sql_adonetprimer_04(en-us,SQL.80).gif

Figure 4

As you can see, reading and writing XML data is easy with the DataSet. The XML in these scenarios was pre-shaped: the XML data was already in the correct schema format. However, there are times when you need to query a database and render the results in a predefined XML format, such as an XML-based purchase order. In this case you need the database to be able to natively render and map relational results into XML before the data is consumed, say, by the DataSet or XmlReader. This type of XML shaping work can be done by an additional set of classes called SqlXml. These classes are part of the "Microsoft.Data.SqlXml" namespace and ship with SQLXML 3.0. The features, previously available in ADO, can now be used within a managed ADO.NET environment. The core-mapping feature of SqlXml relies on annotated XML schemas. For example, the schema below is annotated with several attributes that provide SQL Server with relational mapping information about the structure of the XML. The element Emp comes from the Employees table and will contain two elements FName and LName. Emp also contains an attribute EmployeeId.

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
            xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
  <xsd:element name="Emp" sql:relation="Employees" >
   <xsd:complexType>
     <xsd:sequence>
        <xsd:element name="FName"
                     sql:field="FirstName"
                     type="xsd:string" />
        <xsd:element name="LName"
                     sql:field="LastName"
                     type="xsd:string" />
     </xsd:sequence>
     <xsd:attribute name="EmployeeID" type="xsd:integer" />
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

Given this annotated schema, you can use the SqlXml managed classes to generate hierarchical XML based on the mappings above.

Example 12

{
   ...
      DataRow row;
      SqlXmlAdapter ad;
      //need a memory stream to hold diff gram temporarily
      MemoryStream ms = new MemoryStream();
      SqlXmlCommand cmd = new SqlXmlCommand(NorthwindConnString);
      cmd.RootTag = "ROOT";
      cmd.CommandText = "Emp";
      cmd.CommandType = SqlXmlCommandType.XPath;
      cmd.SchemaPath = "MySchema.xml";
      //load data set
      DataSet ds = new DataSet();
      ad = new SqlXmlAdapter(cmd);
      ad.Fill(ds);
      row = ds.Tables["Emp"].Rows[0];
      row["FName"] = "Susan";
      ad.Update(ds);
      return 0;
   }

Example 12 executes an XPath query of Emp against the annotated schema and then loads the XML results into the DataSet using the SqlXmlAdapter. This is just a simple preview of the true power behind SqlXml mapping features. For a comprehensive review of SQLXML, see the paper SQLXML Managed Classes.

Performance

For the majority of data access operations, ADO.NET performance is significantly improved over ADO and OLE DB. These performance improvements can be attributed to the following factors:

  • ADO.NET and its classes are built upon and managed by the Common Language Runtime (CLR). Therefore, ADO.NET indirectly benefits from improvements in memory management, type conversion, object pooling, and various other low level performance enhancements.
  • A new provider model enables explicit, stream-based access to data sources. For example, the SQL Server .NET Data Provider was built to deal with SQL Server data types in a native manner.
  • A simplified and disconnected object model. By removing cursor support and minimizing the object model result sets can be processed in a more efficient manner with fewer resources.

Even with these statements, measuring performance can be complicated due to a number of extenuating factors. For the release of ADO.NET several common benchmarks were measured. The following performance benchmarks are provided as a frame of reference (measuring Request Per Second (RPS) on Dell PII Xeon 450 4 Proc with 1 GB RAM on an isolated network). Higher RPS is better.

  • Scenario 1: Comparing the SqlDataReader and the OLE DB Rowset. This reads a 4 column by 23 row resultset and walks each row. This is an improvement because there is no rowset construction and SqlTypes are used for reading.
  • Scenario 2: Comparing the SqlDataReader and the OLE DB Rowset. Reads an out parameter from the command. Similar to Scenario 1, except that less data is being materialized.
  • Scenario 3: Filling the ADO.NET DataSet with the SqlDataReader compared to filling ADO Recordset with the SQL Server OLE DB Provider. This is a very common scenario. The main improvement here is that the DataSet is a much more efficient structure than the ADO disconnected recordset.
    ScenarioADO.NETADO/OLE DBImprovement
    14544289257%
    27937414991%
    32434790308%

Again, these are simple scenarios. The point here is that the new object model and typing system in ADO.NET and the CLR provide a more efficient environment for stream management and data caching. For best performance use a native .NET data provider (SQL Server or applicable data source), and avoid using the .NET Framework OLE DB Data Provider or .NET Framework ODBC Data Provider unless you need specific functionality. ADO.NET simplifies the areas that could cause performance problems. Above all, you need to focus on what type of data is in a result set, how to read the data in its native form, and deciding whether to cache the data in the dataset. Finally, make sure you have a good performance specification before you start writing your application: outline your performance requirements based on your needs.

Best Practices and FAQ

  1. Does ADO.NET provide connection pooling?

    Yes, in fact connection pooling is turned on by default. The time to acquire a connection from the pool has been greatly improved compared to existing connection pooling in ADO. Connections are identified by a hash of the connection string. Therefore, the connection string must match exactly, including case sensitivity. Pooling is controlled though the connection string, not the registry. For example, the SQL Server .NET Data Provider allows you to control min and max pool size and connection lifetime with the connection string. For more information, see the .NET Framework SDK.

  2. What happened to cursors?

    In ADO it is possible, within a common recordset object, to request multiple and differing cursor types (dynamic, keyset, static, and forward-only) with different properties that define how the cursor behaves, for example whether the cursor is updateable or is read-only, or whether it is implemented on the client or on the server. In ADO.NET, however, different classes are exposed that give you greater control over each type of interaction. The DataReader provides an extremely fast, forward-only, read-only cursor on the server side that enables you to retrieve a stream of results from a database. The DataSet provides a completely disconnected "client" cursor, through which you can scroll and update, that is equivalent to the static cursor in ADO. These objects, along with the DataAdapter that enables you to move data between the DataSet and a database, provide you with optimal access methods for the most common types of data interactions. If you need to implement server-side cursors, use stored procedures to declare and open cursors and then interact with the cursor using the DataReader. As a last resort you could use ADO through .NET COM interoperability. This is not a recommended practice. As with all COM interoperable implementations you will experience a performance penalty. What databases are supported by ADO.NET?

    ADO.NET supports native connectivity to SQL Server with the "SqlClient" namespace. In addition ADO.NET supports connectivity to OLE DB sources with classes in the "OleDb" namespace. For example you could use the SQL Server OLE DB Provider in the OleDbConnection class, but it is recommended to use the SqlConnection class instead for better performance. ADO.NET also supports ODBC sources and natively supports Oracle.

  3. How can I convert an ADO recordset to an ADO.NET DataSet?

    COM components that return or consume ADO objects are available in the .NET Framework using COM interop services. Additionally, the .OLE DB .NET Data Provider includes overloads to the OleDbDataAdapter.Fill method which take as input an ADO Recordset or Record object returned by existing COM components, and populate a DataSet with the data contained in the ADO object. Updates to the data in the DataSet can be propagated back to the data source using a DataAdapter. You can also use an Extensible Stylesheet Language Transformation (XSLT transformation) to transform between the XML format of the ADO Recordset and the XML format of the ADO.NET DataSet.

  4. How do I build my own .NET data provider?

    ADO.NET also provides you with a minimal set of interfaces to enable you to implement your own .NET data provider. The simple form of a .NET data provider will only support the DataSet, through the IDataAdapter interface, and possibly will provide additional support for parameterized queries by implementing a version of the IDataParameter interface. Using this kind of .NET data provider, you will be able to load a DataSet with data, modify the contents of the DataSet, and save the changes back to the original data source. For more information, see the .NET Framework home page.

  5. Tip: Always close implementations of IDataReader when you are finished reading. This will help release the underlying connection back to the connection pool. Most IDataReader.ExecuteReader() implementations have loaded over constructors with a CommandBehavior parameter. Use the CommandBehavior.CloseConnection enum to close the connection implicitly when the reader is closed. This is convenient when the reader is being marshaled to other consumers that may not close the connection explicitly.
  6. Tip: Always close implementations of IDbReader when you are finished with an operation. This will explicitly release the connection back to the connection pool. Otherwise the connection will be returned to the pool when it is garbage collected by the Common Language Runtime.
  7. Tip: Use SqlTypes when interacting with SQL Server data to ensure data fidelity, nullability and to minimize type conversion.
  8. Tip: The SqlCommand.ExecuteScalar() method does not improve performance over ExecuteReader. It is simply a programming shortcut when you only want the first row from a resultset.
  9. Tip: Use the SqlCommand.Cancel() method to cancel long running or rogue queries. Create another thread and call the Cancel method on the running command. This will cancel the locally running command and notify the server to kill the command process, thus freeing up resources.

Resources

.NET Framework SDK

Microsoft Knowledge Base Articles

White Papers & Articles

Recommended Reading

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft