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();
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);
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!
