INFO: Roadmap for .NET Data Providers (313480)

SUMMARY

This article provides a roadmap to learn and master ADO.NET data providers. Roadmap articles provide links to useful information, including online documentation, Microsoft Knowledge Base articles, and Microsoft white papers, to help you learn about a Microsoft product or technology.

For additional information about ADO.NET technology roadmap articles, click the article number below to view the article in the Microsoft Knowledge Base: For additional information about ADO.NET DataAdapter objects, click the article number below to view the article in the Microsoft Knowledge Base: back to the top

Overview and Architecture

Microsoft .NET data providers are a set of classes that provide access to a database or data source. The Microsoft .NET Framework includes two data providers:

• OLE DB .NET data provider (OleDb), which accesses databases through Microsoft OLE DB
• SQL Server .NET data provider (SqlClient), which provides direct, high performance access to Microsoft SQL Server version 7.0 and later

Other data providers will be available on the Web and from third-party vendors. In addition, you can use the available Software Development Kit (SDK) to write your own data provider.

Each data provider includes Connection, Command, and Parameter objects, as well as the new DataReader and Transaction objects. This article provides references to help you use the .NET data provider objects in your application.

For an overview of the .NET data providers, refer to the following topics in the Microsoft Visual Studio .NET Online Help documentation:

• Visual Studio .NET
  • .NET Framework
    • Programming the .NET Framework
      • Accessing Data with ADO.NET
        • Overview of ADO.NET
          • ADO.NET Architecture
          • .NET Data Providers
            • OLE DB Interfaces Used by the OLE DB .NET Data Provider
          • Writing Common Code for .NET Data Providers

Visual Studio .NET and the .NET development platform include the SqlClient and OleDb .NET data providers. You can download the ODBC .NET data provider from the following Microsoft Web site: MSDN Articles

Programming Notes

Always call the Close or the Dispose method to explicitly close or dispose of Connection and DataReader objects in your application code. Do not let the object fall out of scope or set it to Nothing (in Visual Basic) or null (in Visual C# and Visual C++). If you do not call Close or Dispose, you occupy an expensive resource until the next garbage collection.

However, do not call Close or Dispose on a Connection object, a DataReader object, or any other managed object in the Finalize method of your class. The finalizer of an object is called during garbage collection. When you call Close or Dispose on a managed object in the Finalize method of your class, problems can occur if the class that you dispose of is not thread safe and if your application is running on a multiprocessor computer.

In your finalizer, you should only release unmanaged resources that your class owns directly. If you do not own any unmanaged resources, do not include a Finalize method in your class definition. This is especially important if a Windows service, a Web service, or other ASP.NET application uses your class. These types of applications do not have a user interface on the server computer. Any assertion or other error that is raised can cause the server process to stop responding (hang).

back to the top

QuickStart Samples, Walkthroughs, and Microsoft Knowledge Base Articles

QuickStart sample files provide code samples for your reference. Walkthroughs provide mini-tutorials that walk you through typical application development scenarios. Microsoft Knowledge Base "How To" articles provide step-by-step instructions about how to accomplish specific tasks.

The Visual Studio .NET Online Help topics, QuickStart sample files, walkthroughs, and Microsoft Knowledge Base articles in the sections to follow describe how to use .NET data providers.

MSDN ArticlesQuickStart Samples

QuickStart sample files are installed on your computer in one of two locations. If you install the QuickStart sample files as part of Visual Studio .NET, the sample files are located in the following folder: If you install the QuickStart sample files as part of the .NET development platform, the sample files are located in the following folder: Walkthroughs

In Visual Studio .NET, on the Help menu, click Index. In the Look For text box, type Walkthroughs, data. The Index Results pane displays a list of data access walkthroughs.

Microsoft Knowledge Base Articlesback to the top

Connections

In general, valid connection strings for the OleDbConnection and the OdbcConnection objects are the same as the connection strings for Microsoft ActiveX Data Objects (ADO) OLE DB and ODBC respectively. The main difference is that you cannot use the OleDbConnection object to connect to ODBC drivers; you must use the ODBC .NET data provider instead. In addition, the OleDb .NET data provider requires that the OLE DB Provider support certain interfaces. For a list of these interfaces, refer to the following topic in the Visual Studio .NET Online Help documentation or MSDN: The connection strings for the SqlClient .NET data provider can use elements from either ODBC or OLE DB connection strings with one major exception: the SqlClient .NET data provider cannot use data source names (DSNs), file DSNs, or user-defined type (UDT) files. In addition, the SqlClient .NET data provider cannot specify Driver or Provider elements.

For more information about the ConnectionString elements that are available with the SqlConnection object, refer to the following topic in the Visual Studio .NET Online Help documentation or MSDN: Unlike earlier versions of ADO, Connection objects in ADO.NET do not allow you to run commands. You must use the Command object instead. In addition, how you control transactions in ADO.NET is different than it is in earlier versions of ADO.

If you add a SqlCommand or a OleDbCommand component to your application through the toolbox (on the Database tab), you can edit the properties at design time through the Properties window.

Visual Studio .NET Online Help DocumentationQuickStart SamplesMicrosoft Knowledge Base Articlesback to the top

Commands and Parameters

You use commands to run statements on the server. The Execute method comes in several varieties:

• ExecuteNonQuery. No output is expected.
• ExecuteScalar. Only a scalar result is returned.
• ExecuteReader. Returns a stream of records.
• ExecuteXml of SqlCommand. Returns a System.Xml.XmlReader object from a SQL Server FOR XML query.

To control the execution of the command, either encode data in the SQL statement, or include the data through Parameter objects. The preferred method is to use parameters because you do not have to escape delimiters and other special characters that may result from invalid SQL script. In addition, the SqlClient .NET data provider binds parameters by name. Therefore, you can reuse a parameter in the SQL script, and you do not have to send the data multiple times.

You can also read data back from output parameters. However, if the Command returns a DataReader, you must call the Close or the Dispose method on the DataReader before you can access the output parameter values. Because the server sends the values of output parameters at the end of the data stream, you must clean up or otherwise process the intervening data.

If you add a SqlCommand or OleDbCommand component to your application from the toolbox (on the DataBase tab), you can edit its properties at design time through the Properties window and create the command through a graphical designer.

Visual Studio .NET Online Help DocumentationQuickStart SamplesWalkthroughs

In the help index, type Walkthroughs, data, and then select the following topic: Microsoft Knowledge Base Articlesback to the top

Transactions

In Data Access Objects (DAO), Remote Data Objects (RDO), or ActiveX Data Objects (ADO), you use methods of the Database or the Connection object to control the transaction state. In the .NET data providers, you use a Transaction object to control the transaction state.

To create the Transaction object, use the BeginTransaction method of the Connection object. To commit or roll back the transaction, you can use methods on the Transaction object. Because some OLE DB Providers, such as Microsoft Jet, support nested transactions, the OleDbTransaction object includes a Begin method that returns another Transaction object that has more local scope.

When you create a Transaction object, you can specify a transaction isolation level. If you use a transaction isolation level other than the default, your code may read uncommitted data unexpectedly. In addition, your code may generate excessive locking on the server.

You must assign the Transaction object to the Transaction property of a Command in order for that Command to participate in the transaction. If the database does not support multiple transactions on the same connection, you receive an exception during a transaction when you try to run a command that does not participate in the transaction.

You can also run SQL statements to control transactions on the server. However, do not mix SQL statements with the Transaction object. You may encounter unexpected behavior.

Visual Studio .NET Online Help DocumentationQuickStart SamplesMicrosoft Knowledge Base Articles

back to the top

DataReader

You can use the DataReader object to read records from the database server. Use the Command.ExecuteReader method to create the DataReader object. The DataReader can handle both multiple resultsets and hierarchical or chaptered resultsets. The DataReader uses a server-side, forward-only/read-only cursor. Normally, the DataReader buffers the entire record. However, you can specify flags in the Command.ExecuteReader method so that the DataReader buffers only the current field and makes other changes for additional performance.

Only one DataReader can be open at a time on any given Connection. Unlike ADO, ADO.NET does not open an additional Connection when it runs against a blocked Connection. You receive an exception instead.

You can bind Web Form controls to the DataReader because the Web page is generated sequentially. However, you cannot bind Windows Form controls to the DataReader because Windows Form controls require a scrollable cursor and bind to a DataSet instead.

You cannot access the underlying Command or Connection object from the DataReader. Therefore, if you pass the DataReader to a component that is not trusted, the component cannot run malicious commands against the server. If you want the DataReader to automatically close the Connection when the component closes it, set the CloseConnection flag in the Command.ExecuteReader method.

You cannot pass the DataReader out of the current AppDomain. If you want to pass data to another process, use a DataSet. Alternately, pass out the connection string and the command text, and then allow the other application to create the DataReader locally.

Visual Studio .NET Online Help DocumentationQuickStart Samples

These samples are linked to online versions where available. Microsoft Knowledge Base Articlesback to the top

Error Handling

Errors that the data provider raises are specialized exceptions:

• SqlException
• OleDbException
• OdbcException

You can use the properties of these classes to obtain extended error information. Because the SqlClient .NET data provider is associated with SQL Server, it can provide more server-specific information in the SqlException class than the OleDbException and the OdbcException classes provide. Third-party .NET data providers have their own custom exception classes.

Low severity server errors or warnings are returned as messages. To process these low severity server errors, add an event handler for the Connection.InfoMessage event. Other types of exceptions, such as InvalidArgumentException, are raised directly from the .NET data provider classes.

To determine the properties that each exception exposes, query for the exception class in Visual Studio .NET Online Help. You can also query for specific methods to learn which exceptions a method raises under what circumstances.

To ensure that you do not leak expensive system resources, close any open Connection or DataReader objects in the Finally clause of the exception handler. These system resources are cleaned up during the next garbage collection or when the application shuts down. However, you may exhaust the resource pool before that time.

Visual Studio .NET Online Help DocumentationQuickStart Samples

Error handling is illustrated as part of other QuickStart samples.

Microsoft Knowledge Base Articlesback to the top

Database-Specific

Some databases require special techniques to perform certain operations. This section lists some of the more common scenarios.

Microsoft Knowledge Base Articlesback to the top

Achieve .NET Data Provider Independence

DAO and Jet minimize differences between different database systems. As a result, you can port an application from one database to another with very few changes. RDO and ADO remove the Jet intermediary for better performance and to expose more server-specific functionality. However, this makes it more difficult to port an application between databases.

ADO.NET improves performance more, though ADO.NET includes separate classes for each .NET data provider. However, you can use standard interfaces and isolate initialization code into "factory" functions to minimize the amount of code that you must change. In addition, the DataSet object provides a central object for data binding and remoting that is provider-independent.

Visual Studio .NET Online Help DocumentationMicrosoft Knowledge Base Articlesback to the top

Write a .NET Data Provider

If you write a .NET data provider, you can access data for which there is no OLE DB Provider or ODBC Driver. You can also write a data provider that is optimized for a particular database.

Because the data provider manipulates the DataSet (unlike the ADO Recordset, which manipulates the OLE DB Provider), there are no conformance requirements. You can write a function to read data and to add the data to a DataSet. Alternately, you can write a more complete data provider with Connection objects and so on.

Visual Studio .NET Online Help DocumentationMSDN Articlesback to the top

Troubleshooting

If you encounter problems and need answers to your questions, consult the MSDN newsgroups. The MSDN newsgroups are the best place to obtain answers to your questions. In the MSDN newsgroups, you can share your experiences with your peers or search the Microsoft Knowledge Base for articles about specific issues. back to the top