Microsoft KB Archive/313480

= INFO: Roadmap for .NET Data Providers =

Article ID: 313480

Article Last Modified on 5/13/2007

-

APPLIES TO


 * Microsoft ADO.NET 1.1
 * Microsoft ADO.NET 1.0

-



This article was previously published under Q313480



CONTENTS
 SUMMARY  Overview and Architecture QuickStart Samples, Walkthroughs, and Microsoft Knowledge Base Articles  Connections Commands and Parameters Transactions DataReader</li> Error Handling</li> Database-Specific</li> Achieve .NET Data Provider Independence</li> Write a .NET Data Provider</li></ul>

Troubleshooting</li></ul> </li></ul>

<div class="summary_section">

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:

313590 INFO: Roadmap for ADO.NET

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

313483 INFO: Roadmap for ADO.NET DataAdapter Objects

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:

http://msdn.microsoft.com/dataaccess

MSDN Articles

ADO.NET Primer

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/Dnsql2k/html/sql_adonetprimer.asp

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 &quot;How To&quot; 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 Articles

Using .NET Data Providers to Access Data

http://msdn2.microsoft.com/en-us/library/s7ee2dwt(vs.71).aspx

QuickStart 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:

C:\Program Files\Microsoft Visual Studio .NET\FrameworkSDK\Samples\QuickStart\...

If you install the QuickStart sample files as part of the .NET development platform, the sample files are located in the following folder:

C:\Program Files\FrameworkSDK\Samples\QuickStart\...

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 Articles

310985 HOW TO: Use the ODBC .NET Managed Provider in Visual Basic .NET and Connection Strings

310988 HOW TO: Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings

Click here to view a list of How To articles about .NET data providers

back 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:

OLE DB Interfaces Used by the OLE DB .NET Data Provider

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconoledbinterfacessupportedbyoledbnetdataprovider.asp

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:

SqlConnection.ConnectionString Property

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlConnectionClassConnectionTimeoutTopic.asp

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 Documentation

Connecting to a Data Source Using ADO.NET

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetconnections.asp

Connecting to SQL Server Using ADO.NET

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectingtosqlserverusingadonet.asp

Connection Pooling for the SQL Server .NET Data Provider

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforsqlservernetdataprovider.asp

Connecting to an OLE DB Data Source Using ADO.NET

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectingtooledbdatasourceusingadonet.asp

Connection Pooling for the OLE DB .NET Data Provider

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconconnectionpoolingforoledbnetdataprovider.asp

Working with Connection Events

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconworkingwithconnectionevents.asp

QuickStart Samples

Open and Close a SqlConnection (...\howto\samples\adoplus\adooverview1)

http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/adoplusoverview.aspx

Connection Pooling Using SqlConnection (...\howto\samples\adoplus\connectionpooling)

http://samples.gotdotnet.com/QuickStart/howto/default.aspx?url=/quickstart/howto/doc/adoplus/connectionpooling.aspx

Microsoft Knowledge Base Articles

309485 HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual Basic .NET

310083 HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual C# .NET

308075 HOW TO: Use Data Link Files with the OleDbConnection Object in Visual Basic .NET

back 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 Documentation

Executing a Command

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconadonetcommands.asp

Using Stored Procedures with a Command

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

Obtaining a Single Value from a Databasehttp://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingsinglevaluefromdatabase.asp

Obtaining BLOB Values from a Database

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingblobvaluesfromdatabase.asp

Performing Catalog Operations

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconperformingcatalogoperations.asp

Modifying Data in a Database

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconmodifyingdataindatabase.asp

Obtaining Data as XML from SQL Server

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconobtainingdataasxmlfromsqlserver.asp

QuickStart Samples

Execute a Command (...\howto\samples\adoplus\adooverview2)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/executecommand.aspx

Get Out Parameters from a Stored Procedure (...\howto\samples\adoplus\outparamswithacommand)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/outparams.aspx

Generate XML with data from SQL Server (...\howto\samples\adoplus\xmlfromsqlsrv)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/xmlfromsqlsrv.aspx

Walkthroughs

In the help index, type Walkthroughs, data, and then select the following topic:

Walkthrough: Updating Data Using a Database Update Query in Web Forms

Microsoft Knowledge Base Articles

301075 HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual Basic .NET

306636 HOW TO: Connect to a Database and Run a Command by Using ADO .NET and Visual C# .NET

308049 HOW TO: Call Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET

310070 HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET

310071 HOW TO: Call a Parameterized Stored Procedure by Using ADO .NET and Visual C++ .NET

309486 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual Basic .NET

310130 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET

310142 HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET

305079 HOW TO: Create a SQL Server Database Programmatically by Using ADO.NET and Visual Basic .NET

307283 HOW TO: Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET

307402 HOW TO: Create a SQL Server Database Programmatically by Using ADO .NET and Visual C++ .NET

Click here to view a list of How To articles about commands

back 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 Documentation

Performing Transactions

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconperformingtransactions.asp

IsolationLevel Enumeration

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataIsolationLevelClassTopic.asp

QuickStart Samples

Use Database Transactions (...\howto\samples\adoplus\dbtransactionswithacommand)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/databasetrans.aspx

Microsoft Knowledge Base Articles

Click here to view a list of How To articles about transactions

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 Documentation

Retrieving Data Using the DataReader

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetdatareader.asp

Using Stored Procedures with a Command

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconusingstoredprocedureswithcommand.asp

CommandBehavior Enumeration

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataCommandBehaviorClassTopic.asp

QuickStart Samples

These samples are linked to online versions where available.

Retrieve Data using OLE DB (...\howto\samples\adoplus\adodatareader)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/adodtreader.aspx

Retrieve Data from SQL Server (...\howto\samples\adoplus\sqldtreader)

http://samples.gotdotnet.com/quickstart/howto/doc/adoplus/sqldtreader.aspx

Using the SqlDataReader (...\howto\samples\adoplus\adooverview3)

http://samples.gotdotnet.com/quickstart/util/srcview.aspx?path=/quickstart/howto/samples/adoplus/adooverview3/adooverview3.src

Using the SqlDataReader (...\howto\samples\adoplus\employees)

Microsoft Knowledge Base Articles

308278 HOW TO: Retrieve and Display Records from an Access Database by Using ASP.NET, ADO.NET, and Visual Basic .NET

308100 HOW TO: Retrieve and Display Records from an Access Database by Using ASP.NET, ADO.NET, and Visual C# .NET

309490 HOW TO: Handle Multiple Results by Using the DataReader in Visual Basic .NET

311274 HOW TO: Handle Multiple Results by Using the DataReader in Visual C# .NET

308045 HOW TO: Use the ADO SHAPE Command with a DataReader in Visual Basic .NET

309130 HOW TO: Use the ADO SHAPE Command with a DataReader in Visual C# .NET

310108 HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual Basic .NET

310107 HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET

309683 HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C++ .NET

310348 HOW TO: Avoid the Boxing Penalty When You Use the DataReader in Visual Basic .NET

312855 HOW TO: Avoid the Boxing Penalty When You Use the DataReader in Visual C# .NET

Click here to view a list of How To articles about DataReader

back 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 Documentation

SqlException Class

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlExceptionClassTopic.asp

SqlError Class

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataSqlClientSqlErrorClassTopic.asp

OleDbException Class

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbExceptionClassTopic.asp

OleDbError Class

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOleDbOleDbErrorClassTopic.asp

QuickStart Samples

Error handling is illustrated as part of other QuickStart samples.

Microsoft Knowledge Base Articles

308043 HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual Basic .NET

308650 HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual C# .NET

308651 HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual C++ .NET

back 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 Articles

308071 HOW TO: Access an Oracle Database by Using the OleDbDataReader and Visual Basic .NET

308448 HOW TO: Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET

308073 HOW TO: Use a DataReader Against an Oracle Stored Procedure in Visual Basic .NET

309361 HOW TO: Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET

309362 HOW TO: Use DataReader Against an Oracle Stored Procedure in Visual C++ .NET

Click here to view a list of How To articles about Oracle

back 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 &quot;factory&quot; 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 Documentation

Writing Common Code for .NET Data Providers

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpcontheadonetcommonmodel.asp

Microsoft Knowledge Base Articles

308046 HOW TO: Use Base Classes to Reduce Code Forking with Managed Providers in Visual Basic .NET

313304 HOW TO: Use Base Classes to Reduce Code Forking by Using Visual C# .NET

back 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 Documentation

Implementing a .NET Data Provider

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingnetdataprovider.asp

Implementing a Connection

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingconnection.asp

Implementing a Command

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingcommand.asp

Implementing a DataReader

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingdatareader.asp

Implementing a DataAdapter

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingdataadapter.asp

Sample .NET Data Provider

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpguide/html/cpconimplementingdataadapter.asp

MSDN Articles

ADO.NET: Building a Custom Data Provider for Use with the .NET Data Access Framework (MSDN Magazine)

http://msdn.microsoft.com/msdnmag/issues/01/12/DataProv/DataProv.asp

back 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.

MSDN Newsgroups

http://msdn.microsoft.com/newsgroups/

Microsoft Knowledge Base

http://support.microsoft.com/search

back to the top

Additional query words: back-end database

Keywords: kbarttyperoadmap kbinfo kboracle kbsqlclient kbsystemdata KB313480

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.