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:
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:
313483Overview and Architecture
INFO: Roadmap for ADO.NET DataAdapter Objects
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
, and Parameter
objects, as well as the new DataReader
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
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
Always call the Close
or the Dispose
method to explicitly close or dispose of Connection
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
, you occupy an expensive resource until the next garbage collection.
However, do not call Close
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
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).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:
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:
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
HOW TO: Use the ODBC .NET Managed Provider in Visual Basic .NET and Connection Strings
HOW TO: Use the ODBC .NET Managed Provider in Visual C# .NET and Connection Strings
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
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 DocumentationMicrosoft Knowledge Base Articles
HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual Basic .NET
HOW TO: Build a Connection String Programmatically in ADO.NET by Using Visual C# .NET
308075Commands and Parameters
HOW TO: Use Data Link Files with the OleDbConnection Object in Visual Basic .NET
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
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 DocumentationWalkthroughs
In the help index, type Walkthroughs, data
, and then select the following topic:
Walkthrough: Updating Data Using a Database Update Query in Web FormsMicrosoft Knowledge Base Articles
HOW TO: Connect to a Database and Run a Command by Using ADO.NET and Visual Basic .NET
HOW TO: Connect to a Database and Run a Command by Using ADO .NET and Visual C# .NET
HOW TO: Call Parameterized Stored Procedure by Using ADO.NET and Visual Basic .NET
HOW TO: Call a Parameterized Stored Procedure by Using ADO.NET and Visual C# .NET
HOW TO: Call a Parameterized Stored Procedure by Using ADO .NET and Visual C++ .NET
HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual Basic .NET
HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET
HOW TO: Execute SQL Parameterized Stored Procedures by Using the ODBC .NET Provider and Visual C# .NET
HOW TO: Create a SQL Server Database Programmatically by Using ADO.NET and Visual Basic .NET
HOW TO: Create a SQL Server Database Programmatically by Using ADO.NET and Visual C# .NET
HOW TO: Create a SQL Server Database Programmatically by Using ADO .NET and Visual C++ .NET
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 DocumentationMicrosoft Knowledge Base ArticlesDataReader
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
You cannot access the underlying Command
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
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 DocumentationMicrosoft Knowledge Base Articles
HOW TO: Retrieve and Display Records from an Access Database by Using ASP.NET, ADO.NET, and Visual Basic .NET
HOW TO: Retrieve and Display Records from an Access Database by Using ASP.NET, ADO.NET, and Visual C# .NET
HOW TO: Handle Multiple Results by Using the DataReader in Visual Basic .NET
HOW TO: Handle Multiple Results by Using the DataReader in Visual C# .NET
HOW TO: Use the ADO SHAPE Command with a DataReader in Visual Basic .NET
HOW TO: Use the ADO SHAPE Command with a DataReader in Visual C# .NET
HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual Basic .NET
HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C# .NET
HOW TO: Retrieve Column Schema by Using the DataReader GetSchemaTable Method and Visual C++ .NET
HOW TO: Avoid the Boxing Penalty When You Use the DataReader in Visual Basic .NET
HOW TO: Avoid the Boxing Penalty When You Use the DataReader in Visual C# .NET
Errors that the data provider raises are specialized exceptions:
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
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 Articles
HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual Basic .NET
HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual C# .NET
HOW TO: Obtain Underlying Provider Errors by Using ADO.NET in Visual C++ .NET
Some databases require special techniques to perform certain operations. This section lists some of the more common scenarios.Microsoft Knowledge Base Articles
HOW TO: Access an Oracle Database by Using the OleDbDataReader and Visual Basic .NET
HOW TO: Access an Oracle Database by Using the OLE DB .NET Data Provider and Visual C# .NET
HOW TO: Use a DataReader Against an Oracle Stored Procedure in Visual Basic .NET
HOW TO: Use a DataReader Against an Oracle Stored Procedure in Visual C# .NET
309362Achieve .NET Data Provider Independence
HOW TO: Use DataReader Against an Oracle Stored Procedure in Visual C++ .NET
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 Articles
HOW TO: Use Base Classes to Reduce Code Forking with Managed Providers in Visual Basic .NET
313304Write a .NET Data Provider
HOW TO: Use Base Classes to Reduce Code Forking by Using Visual C# .NET
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 ArticlesTroubleshooting
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.