Do Not Forsake Thy DataReader
I love the speed and efficiency of DataReaders (implemented as the provider-specific OleDbDataReader, SqlDataReader, etc.). Their primary function as a forward-only firehose cursor, though, causes its biggest problem. What if you need to use the data in it twice? For instance, suppose you are building an ASP.NET page that has a billing address and shipping address on it. Naturally, both of these page sections will have a DropDownList of states. It would be a waste to query the database twice to fill both DropDownLists. It would be equally wasteful to fill up a bloated DataSet or DataTable with such simplistic information.
There is a better solution, and it takes very little code to implement:
Dim dr as SqlDataReader
...code to populate dr...
.DataSource = dr
.DataTextField = "StateCode"
.DataValueField = "StateName"
.DataSource = ListBox1.Items
.DataTextField = "Text"
.DataValueField = "Value"
The key to understanding the code above is knowing a little bit about how the DataSource
property of ASP.NET Web controls works. The DataSource
property can accept any object that implements the IEnumerable interface. Most of the time that object is an ADO.NET object, but it doesn't have to be. In the case of the example above, the Items property of the DropDownList control implements the IEnumerable interface, so it is possible to bind the second DropDownList of states to the Items
property of the first one.
Another key point to note is that you are binding to a ListItemCollection object, which contains an internal collection of ListItem objects. Therefore, you can bind to any property implemented by the ListItem class. Obviously, the best approach here is to translate the Key and Value properties directly from the first DropDownList, but there is no rule that says that it has to be that way. In fact, if you leave off the data binding instructions, the Text property of each ListItem in the first DropDownList will be used for both the Key and Value properties of the second DropDownList. Keep in mind that the second ListBox has its own distinct set of ListItems, such that if you make additions, changes, or deletions to the items in ListBox1, they will have no affect on the items in ListBox2.
So, as you can see, you don't have to give up on using the optimized DataReader classes just because you need to use their data more than once, and you don't have to populate an intermediary collection object to hold the results. A little ingenuity provides an effective solution.
POST It Notes
Since .NET supports SOAP-based Web services out of the box so well, many people are lulled into thinking that this is the only way that they are used. Not true. There are a few toolkits out there to help you call .NET Web services from a non-.NET platform (such as classic ASP) using SOAP, but it's often far easier to just use HTTP-GET or HTTP-POST. For that, I use the XMLHTTPRequest object in the Microsoft XML class library.
I ran into an interesting exception the other day after I deployed a .NET Web service that had worked locally on my testing server. When I attempted to access it using the XMLHTTPRequest object from a VB6 application (yes, some of us still use it occasionally), I got an "invalid protocol" exception. I spent a long time debugging my Web service before I figured out that by default, ASP.NET v1.1 disables remote access to Web services via HTTP-GET and HTTP-POST.
Tucked away in your machine.config
file is the following section:
...other config info
<!-- <add name="HttpPost"/> -->
<!-- <add name="HttpGet"/> -->
...other config info...
Note how the HttpPost
tags are commented out. Also note how there is a tag named HttpPostLocalhost
that allows access to Web services via HTTP-POST when you are testing locally (which is why the problem didn't crop up until I deployed my Web service to my testing server).
Once I discovered this section of the machine.config
file, the problem was easy to solve. I simply uncommented the HttpPost
tag. What really threw me at first was that I had deployed this architecture successfully before, without having to do this step. Then, I remembered that my last solution used ASP.NET v1.0, and sure enough, remote access to Web services via HTTP-GET and HTTP-POST are allowed by default in v1.0. I'm sure that the change in defaults is a case of Microsoft becoming more diligent about security and leaving things turned off unless specifically turned on, but I really do wish that they had included a more intuitive error message that would have saved me a couple hours of frustration. (Are you sensing a pattern here?)