Retrieving Data from the Database
A quick note, and this came a surprise to me. I reset the method declaration for the command loaded with SQL after I was finished testing; it now passes the SqlCommand itself and not a string, however, when I accidentally compiled everything to see if the link back to page 4 worked, it didn't through an error. System.Data.SqlClient.SqlCommand It processes just fine, if I only want to confirm the existence of the object I don't even need to convert it to a string first. Interesting.
All right, enough prep work. We're building a DAL, a Data Access Layer; let's freakin' access some data!
using System;
using System.Data;
using System.Data.SqlClient;
using System.Web;
using System.Web.Configuration;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
/// <summary>
/// Obtain the information necessary to create a data packet with everything necessary to construct the link (including absolute URL) for our link factory
/// </summary>
public static class BetaTestingDatabase05PagePaths
{
// Can we generalize this into a helper function so that I can call this helper function and feed it some info?? It seems like I ought to be able to do that. I think this is what Christian Darie and Marco Bellinaso are doing when they're calling custom ExecuteNonQuery methods and stuff. Not sure if I completely understand what they're doing, but surely I can improve this a bit.
// This method queries the Testing Database and stores the results to a data table
// public static DataTable retrieveAllPathInformationFromTheTestingDatabase()
public static string retrieveAllPathInformationFromTheTestingDatabase()
{
.
.
.
}
}
First are all the default using statements (with a couple extra) and the summary for the class. Then I declare this as a class that's public (it can be called and run from anywhere) and static (you don't create instances of it to act on, you just call it). I've also inserted some programming notes to myself for production. Then I declare my first method which retrieves all the path information from the testing database. Remember this method benefits from the work we did on the prior page. All it's going to do is receive a fully SQL-loaded, connection-loaded command object and hit the database with it. I'm still working out the actual implementation but for now I just want to output some data to the page and make sure everything is working.
public static string retrieveAllPathInformationFromTheTestingDatabase()
{
// Get the command for querying the database
SqlCommand commandLoadedWithSqlForTheTestingDatabase = BetaTestingDatabase05PagePathsDataAccess.createACommandLoadedWithSqlForTheTestingDatabase();
// Create a data table to fill with the results from the database
DataTable dataContainerForAllPathInformation = new DataTable();
.
.
.
}
First, I create a SqlCommand, commandLoadedWithSqlForTheTestingDatabase, which calls the static method from the last page. The command object has the connection information for the database and the set of SQL commands that tell the database what we want it to do. In this case, the database is getting information for us and that information will vanish into the aether unless our program has a place to put it. Enter the DataTable. That's all the necessary setup, we can now hit the database.
// Get the database info and store it in the data table
try
{
commandLoadedWithSqlForTheTestingDatabase.Connection.Open();
dataContainerForAllPathInformation.Load(commandLoadedWithSqlForTheTestingDatabase.ExecuteReader());
}
finally
{
commandLoadedWithSqlForTheTestingDatabase.Connection.Close();
}
// Testing
string testVariable = dataContainerForAllPathInformation.Rows.Count.ToString();
return testVariable;
Now we're starting to do things that are a little more dangerous, so I'm using try-catch-finally blocks. While the method is fairly simple, but dangerous doesn't mean complicated, it means doing something that could crash the program. We're now calling an external resource that may not be dependable for us. So all of the potentially dangerous items are wrapped up in the try block. The first thing we do is use our command, commandLoadedWithSqlForTheTestingDatabase, to open a connection to the database. Then I call the DataTable's Load() method to access database information. We specify the command object we're using (since it has the commands we want to send to the database), and call the command's ExecuteReader() method which finally, truly, actually, goes and gets our data... and for an encore, it sticks it in the DataTable so we don't lose it.
If anything bad happens during the try block, it prevents the program from dying horribly. Probably. ;) Just kidding. The catch block then handles the exception. Since there's not anything terribly useful we can do here to handle a database exception, I'm borrowing a common technique, not handling the exception at all to improve performance. It's not uncommon for database errors to be pretty long in coming, so we don't really want to make someone wait even longer while we try to handle an exception. But we do need to tidy up the connection if it still exists, so we place that in a finally block. Whatever else happens, the finally block runs so we can close the connection.
Now we should have a DataTable filled with ooey gooey data-y goodness. When I tried to pass it back the same way I passed the SqlCommand at the top of the page, I got an empty string. So besides making me a little nervous, I changed the method to return a string and altered the testVariable to return the number of rows in the DataTable. 3 Ahhhh... so far so good, we have three rows in our DataTable. Not surprisingly, here's the code in the webpage that pulls the information in.
<span class="testOutput">
<%= BetaTestingDatabase05PagePaths.retrieveAllPathInformationFromTheTestingDatabase() %>
</span>
So now for the first time, I can say that we really have a data access layer. It's for testing, so it still needs some extension to plug into the n-tier architecture. However, the next thing I would like to do is see inside the DataTable, and really verify that it's pulling the data we want. Based on what I've learned so far, the quickest way to test this will require taking a side trip into the business logic of the application, so let's pause the DAL for now.