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 a specific
author, we need to do a couple of things. First 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 is left? Plenty. Each of the objects I have discussed here could be elaborated on further. For the
sake of brevity, I've tried to stick with what I think will be 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 simple
parameter. In my next article, I will be discussing data binding and I will also include a downloadable
working example!
|
