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
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
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
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
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 "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 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
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
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
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
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
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
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
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 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
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
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
Additional query words: back-end database
Keywords: kbarttyperoadmap kbinfo kboracle kbsqlclient kbsystemdata KB313480
