Ado+ Manager Features

In the past, data access is 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 the DataSet between and data objects like
relational databases or XML documents. There are three main levels to the managed provider:

Connections, Commands and Parameters are responsible for communication between and data datasets.
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 the Connection and Command objects. 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 is 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=;d atabase=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 the opening a connection to a data source look at 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 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 does 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" and put the "data into a" useable object like
The DataReader. For a complete discussion of the DataReader objects, check out my I-article about
Data access with the Ado+ DataReader object.

Using Stored Procedures

Ok, so how about something a little. 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 have back a list of authors. Let ' s assume we want to information about a specific
Author, we need to do a couple of things. We need to write a simple stored procedure that takes one
parameter, an author ID. Next We need to specify we are using a stored procedure and add parameters to the
Parameters collection before executing the command. The steps for both providers are as follows:

Create a parameter, specifying the parameter name (as IT appears in the STORED PROCEDURE), the data type
And the size of the parameter.
Give the parameter a value
ADD the new parameter to the command objects Parameters collection
Execute the command as before.

Sql

SQLCommand cmd = new SQLCommand ("Spgetauthorbyid", con);
Cmd.commandtype = CommandType.StoredProcedure;

SqlParameter prmid = new SqlParameter ("@AuthID",
sqldatatype.varchar,11);

Prmid.value = "111-11-1111"
Cmd. SELECTCOMMAND.PARAMETERS.ADD (Prmid);
SqlDataReader Dr;
Cmd. Execute (out DR);


Csharpindex.com/colorcode

Ado

Adocommand cmd = new Adocommand ("Spgetauthorbyid", con);
Cmd.commandtype = CommandType.StoredProcedure;

Adoparameter prmid = new Adoparameter ("Authid",
Adodatatype.varchar, 11);

Prmid.value = "111-11-1111";

Cmd. SELECTCOMMAND.PARAMETERS.ADD (Prmid);

Adodatareader Dr;
Cmd. Execute (out DR);


Csharpindex.com/colorcode

What ' s left?

So what are left? Plenty. Each of the objects I have discussed is here could is elaborated on further. For the
Sake of brevity, I ' ve tried to stick with what I would be would be is a fairly typical use of the managed
Providers. I ' ve shown how to make a connection to a data source, use command objects and specify a
Parameter. In my next article, I'll be discussing data binding and I'll also include a downloadable
Working example!