6. Custom Data Access Layer

Main Points

• Project Planning: Returning a Single Row Laying out our plan for customizing the high level data access and business logic.
• Customizing the Data Container Tailoring the output of the DAL into a data container we can pass up to the business logic.
• Passing SQL Parameters Inserting Query parameters and passing the variables from the point of insertion on the webpage to the underlying SQL.

We've completed building a basic data access layer, but it's done technically not productively; it's a DAL just to get a DAL up and running. Now it's time to modify it to run the link factory, so we're going to need to customize it some more. Currently we're pulling all the fields we want to locate, but rather than pulling the entire table, we want to locate one specific row for the page that we're linking to. Next, we have to be able to pass that page from the Link Factory to the DAL so that the database knows which row to look for. Finally, when we retrieve that data, we need to create and populate that data into properties that we can call from the Link Factory.

Project Planning: Returning a Single Row

OK, we have three goals so the $64,000 question is... which one first. We need to pull one record, and we need to tell the database which specific record we want. Those two goals then are entangled. The third goal is to take the data and get it into the link factory. The third goal should be relatively simple, and we're not having any trouble testing without it. From a risk management standpoint that's clearly a clean up item that can be worked on later.

Let's take a look at the intertwined problems then. I believe that getting the proper page name into the data query will just be a matter of passing the page as a parameter of a DAL method, and using it to populate a query parameter in the SQL statement. While I haven't created the parameter yet, I have good examples and have worked with databases before. The workload is moderate, but the risk is low. The most potentially difficult portion of the problem then, is most likely getting a single row from the database. From a risk management standpoint then, we should attack 1. returning a single row, 2. passing the query parameter, and 3. passing the data up to the link factory.

From a testing perspective, phase 1 is easy to test. The ObjectDataSource and GridView technique we used on the last page can display output for us to examine. I will need to hard code a SQL WHERE statement to pull a particular row, since I can't yet pass the query parameter. This is easy and let's me attack the hard problem first. Passing the query parameter will be easy to check by performing the same tests, but dropping the hard coded SQL. For phase three, all the input will be handled by the live code and the output tests will simply be to produce the link to spec. And if for any reason I need to tackle phase three before phase two, the hard coded SQL will take care of my input. That even gives me a little flexibility if things need to change. Nevertheless, testing seems feasible in the order dictated by risk management. So let's see if we can pull just one record. Got it!

As on the other pages, I'm cheating so that you can see the testing results; although the testing statements are cleaned up as I go, I'm hard coding the XHTML output of the tests into the page so that you can follow along. Here's our single row from the hard coded query. I used the same ObjectDataSource and GridView with the same settings except I'm referencing the 06 version of the class, TypeName="BetaTestingDatabase06PagePaths". This may not be the most elegant test, we're returning an entire DataTable, but it confirms that the SQL works. The XHTML output is virtually identical to the ObjectDataSource output from the Database05 page, so I'm not posting it here. Here, however, is the live SQL.

I simply added a WHERE clause to the SQL code. Now I'm calling a specific page, but when generalized, this will be the key part of my solution for using a dynamic query parameter. In his E-Commerce case study, Christian Darie grabs the info by accessing the various fields of a DataRow, so I'm going to attempt to transform my DataTable into a DataRow the same way. Here, Christian transforms the table into a row with this trinary operator.

Christian's running a safety check to make sure that the results of the search is not an empty set. If the row count is greater than 0, the expression is true and the first item is returned, the first row in the table (the one with index [0]), otherwise the row count is 0 and the second value is returned, null.

Now, I'd also like to address the issue of multiple pages being returned. Unfortunately, examples of live error handling code are too weak to do what I need. I would like to throw a custom exception to warn people that there are two results, so the problem can be fixed. I've simply made some notes and commented them out. As I find more pieces, I can hopefully assemble robust error handling. Ideally, I want to protect against bad data in the database from all directions, validating data being sent to the database from the application, validating data at the database to prevent it from getting in, and handling issues with data coming out of the database. Right now I only have to worry about the output and I've gone as far as I can on that.

Anyway, after my detour into (and frustration with) error handling, I completed testing for the DataRow object returned from the DataTable. Everything looks like it's working OK, though some of the data was not what I expected.

The inline expression returned System.Data.DataRow which is what I figured, however, the ObjectDataSource returned this. Obviously, the GridView isn't behave normally when passed a DataRow object; it wants a DataTable.

Here's the XHTML output, aside from the contents of the table it's pretty similar to what we saw before.

This isn't the contents or the field names I expected, but clearly a GridView is set up to handle DataTables and DataSets, not individual DataRows. Nevertheless, it seems clear that the DataRow is being passed back to the page by the method. However, I would still like to see inside that DataRow to make sure. Therefore, I'm going to switch priorities. My most urgent priority is to get the data out of the DataRow and confirm that testing is really complete. I can deal with the dynamic query parameter after that.

Customizing the Data Container

OK, I was fighting with properties last night and it was a nightmare; I was googling articles pouring over my books, and every technique I found backfired horribly in some way. Finally, after six hours, I figured out that a temp file which the .NET compiler needs was corrupted and had been throwing at least some of those errors. I swore vociferously for about 5 minutes, and went to bed. This morning, everything seems to be working fine. I'm now realizing how grateful I am that I didn't tear apart my existing code. Another case where careful and methodical work has paid massive dividends.

First, I started with the link factory which we haven't touched in awhile. Previously, the strings were just hard coded while I worked out all the rest of the functionality for the link factory. Now they're drawing the data from a new class, my container class which will pull in the DataRow.

I have the link factory that builds links, and I have the DAL that connects to the database. I didn't want the information to live in either one. There are a lot of other things I want to do with this page data like create a site index, do breadcrumbs etc. so it made sense to me to create a data packet class. With a little more experience in design patterns under my belt, this is a data adapter. [chroniclemaster1, 2009/11/19] This forms the bottom level of my business logic; it connects down to the DAL to retrieve the data and then exposes that data as a logical object for the business layer to interact with. So in the link factory, I now create an instance of the data packet class. Then I call the goldenKeywords property of the instance to pass it the necessary data from the database. I've modified all the other elements similarly, so that the hard coded values are gone and the link factory does it's processing with data from the database, now I just need to get it the data from the DataRow.

Yesssssssssssss!!! It Works!

OK, here is where I started, and I learned a lot about what you can and can't do with the syntax, so I'll walk through it with you. I had three sections; one for the golden keywords, one for the website path, and one for the folder path. Each section is identical so let's follow testing for golden keywords. This is the basic format for creating a property. You find this all over the internet with statements like "you can customize this", but there are no examples. Naturally, it's unusable because it has no values, so the application collapses because the link factory doesn't operate on null values. Then I modified the get method to return a fixed value return "lots of golden keywords"; and everything worked fine. I've since changed this back to return accessorForGoldenKeywords;, and haven't touched the get and set accessors since, so they look just like they do above.

Next, I modified the private variable declaration to initialize hard coded values private string accessorForGoldenKeywords = "lots of golden keywords"; This worked too. Now I was pretty confident I could start performing operations on it, so next I needed to bring in the DataRow to do operations on. This turned out to be a huge issue. If I simply declared a new variable and didn't initialize it, private DataRow pagePathsDataPacket;, it was fine. However I needed to initialize the empty DataRow; that's why I worked to output the DataRow in the first place. Everything I tried in the variable declarations blew up in my face.

Normally, if I can't pull something off, I try to break it down. But this was something I anticipated having to hit the forums for. If you've never sought advice on forums, you tend to get assistance with "tasks", so if you ask for help with a test, you get a solution for a test and then typically get stuck again trying to extend that to the production code. Therefore, I thought about how I really wanted to do it. I was thinking that if this was going to take major research, bugging people on forums, etc. I wanted help to complete the project, not just complete one step. When the data packet is created, I need the DataRow created and initialized. That means it needed to go into a constructor; so I created a constructor and started playing around with that. After trying a couple different approaches, I found one that works. Here are the lines of code that create and initialize the the DataRow.

In the private variables, I don't instantiate the DataRow. I simply declare it. Then in the constructor I was able to call the method of my static class which returns the data from the database as a DataRow. So it's inside the constructor that was able to initialize the DataRow. Hey, if that's what it likes, it works for me.

Now I need to be able to access the information inside the DataRow. I had a line from Christian Darie's E-Commerce case study that populates items from a DataRow, so following in those footsteps, I removed the initialized value from goldenKeywords to throw a null exception. Then I could start trying to fix it using data from the DataRow. Here's what I was able to create...

The DataRow code hasn't changed at all, however, you can now see the initialized private variable for the golden keywords is commented out. Just as I declare the DataRow in the class and initialize it's value in the constructor, I do the same thing for accessorForGoldenKeywords. To accessorForGoldenKeywords, I assign the appropriate value from the DataRow; note that I need the ToString() method to validate the proper type. pagePathsDataPacket["GoldenKeywords"].ToString(); This grabs the GoldenKeywords field from the DataRow and assigns it as a string to my accessor variable. When I process this, it not only compiles, it returns this link. This is the successful test link to the volunteer page for article writers. As a more thorough test, I went back to the SQL and hard coded a new page. This is a test link to the C# link factory where this project all started. This is starting to look really good.

Passing SQL Parameters

The only thing we have left is to remove the hard coded string in the SQL statement. This involves passing the name of the page we want to link to down through each class, until we can put it in a SQL WHERE clause. This also means generalizing the hard coded page to a safe SQL query parameter.

So the first step in our work is to incorporate a single string parameter for the page name into a lot of methods that don't currently take one. This always makes me a little nervous because one typo in the chain... ouch, and that's all it takes. So let's start with parametrizing the SQL. That's the hardest part anyway. OK, we can output the revised code and everything compiles. I am a little surprised that I'm passing in the page name and the @PageName parameter appears rather than the string I'm feeding in. But if this is as close as I get, I'll be pretty happy. Everything complies and seems to be OK.

SELECT PagePaths.GoldenKeywords, WebsitePaths.WebsitePathName, FolderPaths.FolderPathName, PagePaths.PageName FROM PagePaths INNER JOIN WebsitePaths ON PagePaths.WebsitePathID = WebsitePaths.WebsitePathID INNER JOIN FolderPaths ON PagePaths.FolderPathID = FolderPaths.FolderPathID WHERE PagePaths.PageName = @PageName

My previous trick of returning a string variable temporarily has bombed out, because the application no longer compiles. I have functions above it that depend on receiving a SqlCommand object instead of a string, so changing the type blows everything up. I got around this relatively easily by creating a customizable test method. First I create an new method declaration that will pass in the single parameter I have, then I call the method I want to test and fill it with the parameter. Finally, I convert the CommandText to a string and you see the output. Since I can't see the parameter, I changed the test variable to return the parameters as a string, string testVariable = testCommand.Parameters.ToString();, but all I get is, System.Data.SqlClient.SqlParameterCollection. For one final hurrah, let me grab the count of the parameters in the collection. 1 :) All testing seems to indicate that things are where they're supposed to be. The only way to conduct further testing is to pass the parameter up and down the line. Success!!

Here's how we're modifying each class. Since we can now pass the page name as a parameter to the createACommandLoadedWithSqlForTheTestingDatabase() method, we change it to createACommandLoadedWithSqlForTheTestingDatabase(pageName). Now we can pass the page name into the command. However, this means I need to enter the parameter into this method, so retrieveAllPathInformationFromTheTestingDatabase() becomes retrieveAllPathInformationFromTheTestingDatabase(string pageName). We repeat this process for the data packet that calls this method and for the link factory.

Now all we need to do is test this one last time. Let's see if we can call all three pages stored in the database.

1. C# programming of the link factory
2. volunteer article writes
3. data access layer beginnings

And we're done! Everything is now customized beautifully and my link factory works using only these inline expressions to replace the opening <a> tag. Moreover, the tag includes not only the full absolute reference, but the SEO keywords that will help make the program successful. I'm still looking at how we're going to push this into production. I've made notes about some changes that may need to be made, a helper function or two that might be helpful, and some additional class files I may want to incorporate.

We also have to consider the database. Most of the stuff we've hard coded is done so you can see the test results for code which was then modified for other tests. However, this list of links should be run off the database as proof of completion. However, I can't use the MS SQL Server 2005 database, Testing.mdf, on the production server. So now what?

I could shove this into an Access database, and with proper caching I might even get away with it. However, there's also the more involved choice of taking advantage of the MySQL database on the account. However, this means both configuring the application and downloading the necessary DLLs to get .NET cooperating with MySQL and I would have to figure out how to install and configure everything on my dev box as well. Followed by learning to use MySQL admin to create backups of the databases and upload them to the production server and / or vice versa. That seems like a lot of database work to someone like me who has no interest in being a DBA. So some big choices need to be made as we push our link factory into production.

DBA or no DBA, I just spent the last week taking care of all the details. I now have a MySQL database up on my dev box and running live. With the help of my webhost, Earth Chronicle is also configured for MySQL on the production server. This includes not only the database, and the GUI tools (especially query browser and admin), but also getting the .NET connector classes installed. Then I could build a couple test pages that hit the MySQL database and display data. I've yet to fully vet the ability to backup and restore databases from one site to the other, but everything else is tested and working fine. So MySQL is our future for enterprise class database solutions.

The web.config files must each list the separate information for the connection strings to the local dev MySQL instance vs. the remote MySQL database we access on the production account. Other than that, all code works identically so far for both dev and production which is pretty awesome. If I can use database backups to copy information back and forth between development and production, I can store things like content to the database (a key step in going multi-lingual) and still copy the site from development up to production. If you'd like to check out the MySQL installation and development work, click here.

[chroniclemaster1, 2009/11/19]