Multiple Class Data Access
Well, I've created my first helper method. Christian includes it in his class, appending it to the end of his Data Access class. However, he's also configuring his SqlCommand object for use with the other methods in the class. I'd like this to be a little more generic, and then specify the implementation in a second class; then the work I'm doing now can be extended to other classes as I go. Before we go anywhere though, testing testing, testing. OK! Success! Let's check things out...
// A base helper function to be used to attach any specific connection to any specific SqlCommand
// This is the real method, but it's sequestered for testing
// public static SqlCommand createSqlCommand(string connectionNameInWebDotConfig)
public static string createSqlCommand(string connectionNameInWebDotConfig)
{
.
.
.
}
First, I have a comment; the method name should clearly identify it, but that's no reason not to make add a little explanation, this will appear directly above the method when testing is complete. However, this method, createSqlCommand(), won't work for testing; because it returns an object of SqlCommand type, public static SqlCommand createSqlCommand(...). Therefore, I've created a temporary method declaration which returns a string I can pass to the page, to make sure everything is OK. This is a public function that can be accessed by other classes, and a static function so that you don't have to create an instance of it. It's going to be a helper function, so there shouldn't be any instances of it lying around. The actual command will be added later either from a Stored Procedure or from text, so for right now it only needs one parameter, the name of the connection which is stored in the Web.config file. The abstraction we're creating for use by other functions is an empty command object for a particular database, all they have to do is provide the name of a connection string; once that's done, you only need to add SQL and send.
public static string createSqlCommand(string connectionNameInWebDotConfig)
{
// Take the name of a connection in the Web.config (which is passed in as a parameter) and retrieve the connection string
string connectionString = WebConfigurationManager.ConnectionStrings[connectionNameInWebDotConfig].ConnectionString;
// Use the connection string to prepare a connection to the database
SqlConnection connectionToTheDatabase = new SqlConnection(connectionString);
// Create the SqlCommand to contain the commands for the database and attach the connection to it.
SqlCommand commandForTheDatabase = connectionToTheDatabase.CreateCommand();
// return commandForTheDatabase;
// Testing
string testVariable = commandForTheDatabase.ToString();
return testVariable;
}
Next, I've created the following code inside the createSqlCommand() method; this creates the connection and declares that it's to be used for the set of SQL commands that we'll eventually put into it. I've defined a variable named connectionString that keeps the actual connection string; using the WebConfigurationManager I use the parameter passed in the createSqlCommand call, connectionNameInWebDotConfig to grab the actual connection string from the web.config. With the connection string, we create a connection to the database, connectionToTheDatabase. Then we create the SqlCommand object and define the connection string which it's supposed to use. Although it's commented out for testing, the final step is to return the command object. Note that all of this is done based on a connection name from the web.config which is passed as the original parameter. This is what makes this a helper method for data access. This method doesn't actually do anything, there's no database connection and no command actually being created. Another class is necessary to assign an actual connection name which then creates an instance of a SqlCommand which can be used to connect to a database. This is also the reason that this is a static method. It makes access to this method easier, and we never want to create an instance of a helper anyway - ie we'd never want to hit a database with an empty command object. Some more complete object with SQL and a place to store data is the type of thing we want to create an instance of, and this is simply an assistant method for that operation.
Finally, since this is part of testing, I create a string variable that I can return to make sure everything is OK so far. I take the command object which I eventually want to return as the method output and temporarily output it as a string. Now I just need to call it.
<span class="testOutput"><%= BetaTestingDatabase04BaseDataAccess.createSqlCommand("SqlTestingDb") %></span>
Here's the way I'm calling this from the page. It's the same style of code render block that I use for the link factory. This is a quick and easy way to make sure that the method can be accessed from the page, and that it does what I need it to do. And here's the output. System.Data.SqlClient.SqlCommand Because this simple text appears, we know that by passing a working connection name to the helper function, it's able to generate the SqlCommand object we want to output. To be sure, this only tells us that it exists, but we'll test additional functionality as it becomes testable. I've even wrapped a span with a new class around this to highlight the text output.
Now to be sure I'm cheating. As we continue with testing I keep revising and revising these methods until I get what I want. While I'm going to preserve the code for this webpage so that it will continue working, I'm not going to preserve each individual test as you see above, the only record will be here. The alternative is to have a mess of classes like BetaTestingDatabase04BaseDataAccessTest1, BetaTestingDatabase04BaseDataAccessTest2, BetaTestingDatabase04BaseDataAccessTest3, etc. and that seems like a mess to me right now. I can always change my mind and do future projects that way. So the text you see here on the page is a record of this phase of development. We're only going to wind up with one BetaTestingDatabase04BaseDataAccess class.