Wednesday, June 13, 2012

Use a SQLDataReader more efficiently. Using List(Of <t>)

Hack the Gibson

The SQLDataReader -
The SqlDataReader is a forward only looking representation of a result set on a SQL Server.  The reason it is referred to as a representation is that it is a stream of (on demand) rows from a result set defined by a query.  What the heck does that mean?
Well, if you run a query on a SQL Server, and populate a SqlDataReader.. You are not really populating the SqlDataReader.  What essentially happens is that you open a connection to the server using a SqlCommand.  The SqlCommand is then dedicated to the SqlDataReader until the SqlDataReader is destroyed (closed).  When you close the SqlDataReader, the connection through the SqlCommand is automatically closed.  To further complicate this, as you read through the SqlDataReader, the data pointer moves to the next row in the stream.  This means when you first invoke the SqlDataReader, it’s current position is –1.  As you read through, it advances one row in the stream until it reaches EOS (End of Stream), at which point it automatically destroys the reader (however, it is good programming practices to close the reader). 
This means that the SqlDataReader never knows how many rows (records) the stream contains.  The dedicated connection, thus stream, exists throughout the lifecycle of the SqlDataReader.  This can be very resource intensive depending on your result set size, and how you process the stream.
How can we make this more efficient?  I’m glad you asked.  The idea behind this is we want to be able to effect our result set, get a count, and discard the reader (thus the connection) as quickly as possible.  How do we accomplish this?  Well we’re going to create an object, populate its Public Properties with the values in the reader and then we can massage the data on our own time and leisure.
Why not use a DataSet or DataTable?  This would be fine if we are dealing with an enormous amount of data, like over 10,000 records.  This is what the DataSet dreams are made of.  However, for smaller result sets, lets do something a little more practical and less resource intensive.

First we need to create a table in a database (In the context of this article, the database will be assumed to be named Intranet, however, what you name your database is up to you).
image

As you can see here, we are building a table that will store hyperlinks.  The table contains four columns, thus our object will contain four parameters.  However, when you are dealing with any type of data in a .Net application, you always start at the database and end at the UI.  So lets create our SP that will fetch the data -
image

This will fetch our records.  Remember that I did not include the USE TSQL Command due to our agreed upon understanding that this article uses the database Intranet.
So now we get into the ADO.Net portion of the article.  Of course these changes take place in VS2010.  This being said, let’s build the DAL (Data Access Layer).  Add a class and call it Links -
We’ll need to import some references -
image

Now we need to build a method to fetch the data from SQL and then convert it into our list -
image

As you can see here, we are creating a SQLConnection using the WebConfigurationManager to fetch our ConnectionString from the web.config.  We are then issuing a SQLCommand to run a SP and then opening the defined connection.  We invoke the ExecuteReader command and “populate” our SqlDataReader.  Now we need to build our convertReader method -
image

You might notice my blue lines of runtime death in the screenshot above.  This is because we have not created our polymorph New() method yet. When we create it, the lines will disappear.
So here we are creating a generic list of type LinkDetail.  We then cycle through the reader and return our new created list menu of type LinkDetail.  In the getMenu method, you will notice we then populate a local variable for the list, then close the connection and return the list.
So now we need to create our LinkDetails object in our BLL (Business Logic Layer).
We will not need to import any references, so our starting point is the properties of the object.  So add a class to your BLL and call it LinkDetails and add the following properties -
image

Now we need our polymorphed New methods for Constructors -
image

Now we just need to add a method of getting that list into a string of hyperlinks so that we can populate our Literal control on our form.
image

Here we are making a reference to the DAL using the variable aMenu.  Then we are building our list and populating it into menu.  We define a StringBuilder and set a flag.  The flag is because I wanted the pipe character ( | ) to be a seperator in my links.  This way it will skip the first pipe and then add it after the first iteration thus we have -
Link 1 | Link 2
and not
| Link 1 | Link 2
We are using a For Each loop to cycle through the entire list and building our StringBuilder.  Finally, we are returning a string representation based on the StringBuilder.ToString() that is a HTML representation of our links.
Now we need to invoke our getAdminMenu method from the code behind of the page, so in our default | Page_Load event we need to add the following code -
image

Which makes a reference variable to the LinkDetails and then invokes our getAdminMenu method.  From there we just need to add the litMenu Literal to the HTML of the page where we want the menu to appear -
image
Thus, we have connected to a SQL Database, retrieved values into a SQLDataReader, reader through the reader and discarded it (closing the connection as quickly as possible).  Then we used the created list and created hyperlinks which we cycled through and built a string which we populated a Literal on the UI. 
As a final note, notice that in the convertReader method, I am casting the Link_CD as an integer.  We can massage and manipulate the data at any point throughout the process.

Of course, if you were wanting to Bind the list to an object, you could use the ObjectDataSource and then Bind it to a control.  This is beyond the context of this article.

Happy .Netting
Hack the Gibson

1 comment:

  1. Hello, the whole thing is going fine here and of course every one is sharing data, that’s in fact fine, keep up writing. I favorite your blog post so I can visit again in the future, Thanks.

    5-Star Ranking by Google. Optimized Websites and Medical Marketing for Doctors. Dental, Medical, Optometry, Chiropractic and other specialties.

    ReplyDelete