In the past, data access was done using a two-tiered, connected model. With the increased development of
multi-tiered applications, the need for a disconnected model has arisen. The ADO+ managed providers give
us this model.

Managed providers are responsible for creating connections between DataSet objects and data sources like
relational databases or XML documents. There are three main levels to the managed provider implementation:

Connections, Commands and Parameters are responsible for communication between DataSets and data sources.
The DataSetCommand actually retrieves the data and provides column and table mappings.
The DataReader provides high-speed, forward only access to data. Under the covers, the DataStream object
provides the direct connection to the data source.
Lower level objects connect to the specific data sources and provide the system specific commands.

At the center of the ADO+ model are the Connection, Command and DataSet objects. In this article I'm going
to focus on the Connection and Command objects. You can read more about the DataSet in my previous
article "ADO+ DataSets, Recordsets on Steroids?"

Two Ways to Connect

Why two managed providers? Microsoft has given us one provider for connecting directly to a SQL Server
database and one for accessing data via an OLE DB layer. The two Connection objects with which to connect
to data stores are: The SQLConnection for connecting to Microsoft SQL Server and the ADOConnection for
connecting via an OLE DB provider. The SQL managed provider can be used if you include the System.Data.SQL
namespace. To use the ADO managed provider, include the System.Data.ADO namespace. A connection can be
established the following two ways (in C#):

SQL

String sConnectionString = "server=localhost;uid=sa;pwd=;database=pubs";
SQLConnection con = new SQLConnection(sConnectionString);
con.Open();


csharpindex.com/colorCode

ADO

String sConnectionString = "Provider= SQLOLEDB.1;
Data Source=localhost;
uid=sa; pwd=; Initial Catalog=pubs";

ADOConnection con = new ADOConnection(sConnectionString);
con.Open();


csharpindex.com/colorCode

These two methods of opening a connection to a data source look remarkably similar, but let's take a
closer look. The connection string for the ADO managed provider should look very familiar to anyone who
has used ADO (it's identical). The SQLConnection supports a multitude of connection string keywords, but
the most common ones are server, uid, pwd and database. The first and last are obvious. The keywords uid
and pwd are just shortened versions of the database user id and password.

Execute A Statement

In order to get data from our data source, we need to execute commands against that data source. The
easiest way to do this is through either the ADO or SQL Command objects. Like this:

SQL

SQLCommand cmd = new SQLCommand(("SELECT * FROM Authors", con);
SQLDataReader dr = new SQLDataReader();
cmd.Execute(out dr);


csharpindex.com/colorCode

ADO

ADOCommand cmd = new ADOCommand("SELECT * FROM Authors", con);
ADODataReader dr = new ADODataReader();
cmd.Execute(out dr);


csharpindex.com/colorCode

In order to get to the data, we need to execute the command and put the data into a useable object like
the DataReader. For a more complete discussion of the DataReader objects, check out my first article about
data access with the ADO+ DataReader object.

Using Stored Procedures

Ok, so how about something a little more real world. Most of us use stored procedures to access data from
a database. Additionally, most of the time we need to pass parameters to these stored procedures. In the
example above, we get back a list of authors. Let's assume we want to see information about