Data Access With Ado.net
With the advent of .net Microsoft has rolled up most of its data access into the Ado.net base classes. These classes can be used to work with a multitude of data sources but this section will start to take a look at how we can work with SQL Server 2000.
The first thing we should do when working with Ado.net is reference the appropriate namespaces as follows:
using System.Data;
using System.Data.SqlClient;
There are other namespaces too such as System.Data.Odbc and System.Data.OleDb but Microsoft has provided us with the SqlClient Classes which have been specifically optimised for use with SQL Server so it makes sense to use these in this case.
With Ado.net as with ADO there are many ways of doing the same thing so I'll just look at some typical examples here. First we are going to need a connection object which we get as follows:
Here the connection string is hard coded but this is of course bad practice. In reality this would typically be held in some globally accessible place such as a config file. So in an asp.net application for example we might get:
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["SQLDBConnectionString"]);
Next we need a command object. We instaniate this and set some properties as follows:
The command class has various methods, one of which is ExecuteReader. This returns a SqlDataReader object which provides a means of reading a forward only stream of rows from the database. We can call this method and create an instance of the SqlDataReader all in one as follows:
SqlDataReader myReader = myCommand.ExecuteReader();
We can then do something with the data in the SqlDataReader and then tidy things up. Below is complete code for this in the form of a little console app.
Did you notice the use of a using statement above. In .net there are many classes which have a method called Dispose. What this method typically does is clean up any resources used by the class such as database connections, open files etc. So when ever you use a class which does have a Dispose method thne you should always call that method when you are finished with it. Rather than going myConnection.Dispose() we are able to use the using statement which is a shorthand for doing this. Whenever we declare a variable inside a using statement follwed ny { and } then when that object goes out of scope - ie when we get to the closing curly bracket then the Dispose method will automatically be called on that object. This whole subject can get quite involved and it is well worth checking out the following article for a more in depth look at this:
Dispose and IDisposable in .net classes
We also need to look at how to work with ado.net and stored procedures. This needs to include passing input parameters, getting output parameters and getting records back.
The following code example shows how to use a stored procedure to achieve the same result as the previous example which used a SQL text string.
As you can see the only differences are on the following 2 lines:
myCommand.CommandText = "sp_SelectPeople";
myCommand.CommandType = CommandType.StoredProcedure
Ok so that was easy enough. How about if we now need to pass an input parameter into the stored procedure. We will pass in a Firstname and the stored procedure will return records matching that Firstname:
To achieve this then the following 3 lines of code were added:
myCommand.Parameters["@Firstname"].Value = "Dave";
myCommand.Parameters["@Firstname"].Direction = ParameterDirection.Input;
We can therefore see that the SqlCommand object has a Parameters collection which has an Add method for adding parameters. This add method has various overloads so have a look and see what works for you. I like the above method but thats just my choice.
After weve added a parameter we can refer to it by name and set its properties as we do on lines 2 and 3 above. That's all there is to it, easy really!
The stored procedure for the above example looks like this:
CREATE PROCEDURE sp_SelectPeople
@Firstname VarChar(50)
As
Select * From tbl_People Where Firstname = @Firstname
Ok so now lets look at another variation on this theme. We will pass in a Firstname and return an output parameter containing the corresponding Surname. We will not return a recordset.
First lets modfiy the stored procedure which needs to look like this.
CREATE PROCEDURE sp_SelectPeople
@Firstname VarChar(50),
@Surname VarChar(50) Output
As
Select @Surname = Surname From tbl_People Where Firstname = @Firstname
Of course this is flawed as we might have more than one person with the same Firstname but we won't worry about that - we are just concerned with how to use output parameters. So lets look at the code below:
Ok so we have added an extra parameter which we have specified as being an Output parameter via the SqlDbType enumerator. Of course we don't set a value for this as it's an output parameter.
We have also changed the method which we use to execute the query - we now use ExecuteNonQuery which runs the stored procedure but returns no records - it does of course populate the output parameter though which we can get at with: