ADO.NET Managed Providers: SQL Managed Provider and OleDB Managed Provider
This chapter is from the book
This chapter is from the book
This chapter is from the book
Introduction
In the last chapter, you began looking at how an ASP.NET data-driven solution works. You looked at the ADO.NET object model, and at how to build a DataSet dynamically. In this chapter, you'll dig into the two data access Managed Providers offered in ADO.NET: the SQL Managed Provider and the OleDB Managed Provider.
In this chapter, you'll learn the following:
How the .NET Managed Providers are a bridge from the application, such as an ASP.NET Web Form, to a data store, such as Microsoft SQL Server.
How to create Managed Connections to connect to a data store.
How to use Managed Commands to execute SQL statements on a database.
How to use DataAdapters to retrieve data and populate a DataSet.
How to create custom table and column mappings.
Managed Providers, as shown in Figure 3.1, are ADO.NET's bridge from an application, such as an ASP.NET Web Form to the data source. Data sources include Microsoft's SQL Server, Access, Oracle, or any other such data storage device.
){kind=link}
Figure 3.1 Managed Providers are the bridge from a data store to a .NET application
The Managed Providers have four core components:
Connection—The Connection represents a unique session to a data store. This may be manifested as a network connection in a client/server database application.
Command—The Command represents a SQL statement to be executed on a data store.
DataReader—The DataReader is a forward-only, read-only stream of data records from a data store to a client.
DataAdapter—The DataAdapter represents a set of Commands and a Connection which are used to retrieve data from a data store and fill a DataSet.
The Two Managed Providers
ADO.NET, the successor to Microsoft's highly successful ActiveX Data Objects (ADO), offers two Managed Providers. These providers are similar in their object model, but are chosen at design-time based on the data provider being used. The SQL Managed Provider offers a direct link into Microsoft's SQL Server database application (version 7.0 or higher), while the OleDb Managed Provider is used for all other data providers. Following is a brief description of each of the Managed Providers. Throughout this chapter we will show you how the Managed Providers work, and specify when a particular object, property, method or event is proprietary to only one of the Managed Providers.
OleDb Managed Provider
The OleDb Managed Provider uses native OLEDB and COM Interop to establish a connection to a data store and negotiate commands. The OleDb Managed Provider is the data access provider to use when you are working with data from any data source that is not Microsoft's SQL Server 7.0 or higher. To use the OleDb Managed Provider, you must import the System.Data.OleDb namespace.
SQL Managed Provider
The SQL Managed Provider is designed to work directly with Microsoft SQL Server 7.0 or greater. It connects and negotiates directly with SQL Server without using OLEDB. This provides a better performance model than the OleDb Managed Provider, but it's restricted to use with Microsoft SQL Server 7.0 or higher. To use the SQL Managed Provider, you must import the System.Data.SqlClient namespace.