Database Helper

Introduction There are many "database helper" components around, each offering some unique features of its own. In fact Microsoft's own MS- Data Access Application Block (MSDAAB) is one of the popular data access component. This article reviews Database Helper Ver. 2.0 and discusses pros and cons of the component. What is Database Helper? Database Helper is a .NET 1.1 component that encapsulates common database tasks such as executing queries and stored procedures. This is a free component that you can use in your applications. I myself is using MS-DAAB in many of my applications. It does serves its purpose to a large extent. When I saw this component's announcement on ASP.NET forums, at first glance I though - "Oh! Yet another clone of MSDAAB". But when I glanced through the feature list and downloaded the component, I found that it does included features that I always wanted in my applications. I am going to discuss these features in the later parts. Using Database Helper Using Database Helper in your applications is pretty simple. It consists of 4 classes:    

SqlHelper OleDbHelper OracleHelper OdbcHelper

Each of the above class has following methods:    

ExecuteNonQuery ExecuteScalar ExecuteReader ExecuteDataSet

Each of the above methods come in two flavors:  

One that accepts just an SQL query i.e. no parameters The other accepts an SQL query and an array of parameters

http://www.binaryintellect.com/displayproduct.aspx?productid=1 has many code

samples that show many of the above methods in action. In order to use the component just follow these steps: 1. 2. 3. 4.

Download the component and unzip it in a folder Give a reference to this component in VS.NET. Create instance of required class in your code using appropriate constructor Call various methods of the class

Comparing MS-DAAB and Database Helper Before we compare MS-DAAB with this component let me tell you about some of the requirement that I often face in my applications:

1. Support for multiple databases. Many times we use databases other that SQL 2.

3.

4. 5.

Server (MS-Access/Oracle). So, we need a component that caters this requirement. Working with multiple databases in a single application. Connection string changes at run time based on some requirement. This is more often happens when database is MS-Access because many people divide their entire data in multiple Access databases. Pass huge number of parameter to queries or stored procedures. Many of my queries and stored procedures require huge list of parameters (many times 30+). So, whatever component I use should have easy mechanism to pass such huge list. Built-in transaction support. This means that I should not write any code at ADO.NET level to initiate the transactions. The component should take care of that. In some applications our business layer is hosted in COM+. We also like to host our data access layer in COM+. Though I do not use COM+ too often, some applications do call for this.

Now, let us see how MS-DAAB and Database Helper can handle these requirements. Note that this comparison is purely based on my personal experience of developing .NET application. Though I anticipate that many of you might be having similar requirements, I do not claim that Database Helper is the best choice in each and every possible situation. 1. MS-DAAB support only SQL server. So, I can not use it for all of my application. Since it comes with source code, I can change it for other providers but that calls for another round of testing from my side. Database Helper on the other hand provides classes for all 4 data providers i.e. SqlClient, OleDb, OracleClient and Odbc. 2. MS-DAAB allow you to use multiple databases in a single application but the way it supports it is bit rigid. Consider following methods from MS-DAAB: ExecuteNonQuery(ByVal connectionString As String, ....... Now, certainly I can pass different connection strings in this method. But that means if I am using this method 100 times, I need to pass it so many times. Wouldn't it be nice if I can pass it in the constructor of the component. That is what Database Helper allows. Note that MS-DAAB has all the methods as "static" (or shared in VB.NET). It does not make use of instance constructors. In addition Database Helper also allows to configure connection string via config files (web.config or app.config). 3. MS-DAAB uses ParamArray mechanism to pass query or stored procedure parameters. That means you can call a stored procedure like this: ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24))

This works fine if my no. of parameters are small (say 5-6). But think if you have 30-40 parameters. Your call will look very huge. If you decide to declare separate parameter variables then you need to think 30-40 variable names :-). Database Helper accepts array of parameters instead of individual parameters. So, you create an array of parameter objects and pass it to various methods. This makes it very simple to pass huge list of parameters. 4. Let us see, how MS-DAAB supports transactions. ExecuteNonQuery(ByVal transaction As SqlTransaction,..... In this case you need to create an instance of Transaction object in "your" code and then pass it to various methods. This means you should take care of opening a connection and initiating a transaction. This is not what I certainly want. If my component is encapsulating much of the code for nontransactional methods why not for transactional ones. This is what Database Helper does. Initiating a transaction and ending it requires just two calls: db.BeginTransaction() ' more code here db.CommitTransaction() 5. Some of my applications require my components to be hosted in COM+. In order to do that your .NET components should inherit from ServicedComponent class. Currently neither MS-DAAB nor Database Helper provides significant support in this regards. However, I hope that support will be added in future releases. In addition to these features Database Helper has support for built-in error handling. Consider how you typically write your data access code. 'MS-DAAB version Try SqlHelper.ExecuteNonQuery(....) Catch exp As Exception Label1.Text=exp.Message End Try With Database Helper this code becomes: db.HandleErrors= True db.ExecuteNonQuery(....) Label1.Text=db.LastError Note that Database Helper also allows you to Try...Catch. MS-DAAB has advantage over Database Helper in that it allows many overloads of every methods (around 9). This provides flexibility to the developer. However, I myself find using only few overloads again and again. Also, when I write business logic components, I strictly avoid creating or passing ADO.NET objects from the external world to the data access layer. So, I don't need many of the overloads provided by MS-DAAB.

Another feature of Database Helper that I liked is that nowhere you need to specify command type (i.e. Text or Stored Procedure). Internally the component decides whether it is an SQL query or not. This has a disadvantage, however, that you can pass only SELECT, INSERT, UPDATE and DELETE queries and not any other DDL or TSQL/PL-SQL statements. If you pass then it's CommandType is treated as stored procedure! I am already using MS-DAAB. Do I really need to switch? If MS-DAAB is satisfying all of your requirements then there is no point in switching to any other component. However, if your requirements are similar to mine then I strongly suggest you try this component. As a standardization you may also think of adopting it for all the future projects. In addition BinaryIntellect has also released another cool and free tool called "Stored Procedure Wrapper Generator" that generates VB.NET wrapper code for your SQL Server stored procedures. This tool emits code that makes use of Database Helper component.

I don't think there's a need for explaining what RSS is, or what RSS does. Most of the time, the headlines of an RSS feed are retrieved from a database, such as SQL server. An example of this is Feedpedia.com which creates feeds containing the latest headlines retrieved from the website's database. One of these feeds is located at http://www.feedpedia.com/RSS/TodaysHeadlines.aspx?Group=13.

In this tutorial we're going to create a similar RSS feed, using ASP.NET 2.0.

In the ASP.NET 2.0 project, create a new file which will be the RSS feed. We used "TodaysHeadlines.aspx". Now open the markup of the file and replace everything inside it with the following two lines:

Don't forget to change the CodeFile and Inherits attributes of the Page tag in case you gave your RSS file a different name than TodaysHeadlines.aspx. If you're wondering what the second page does: it tells the ASP.NET server to keep the page in cache for 120 seconds (2 minutes) so that the SQL server doesn't have to be accessed every time someone opens your RSS feed. You can change the duration depending on how often your content updates.

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="TodaysHeadlines.aspx.cs" Inherits="TodaysHeadlines" %> <%@ OutputCache Duration="120" %>

Now since we are already discussing the OutputCache, I want to bring your attention towards something. If you're going to create a dynamic RSS feed file to which you're going to pass a parameter which changes the content of the feed (such as the one on Feedpedia: http://www.feedpedia.com/RSS/TodaysHeadlines.aspx?Group=13), the cache you will create you some problems: when you open the RSS feed with a different attribute (in Feedpedia's case, a different Group ID), the content will not change, but the cached one will be shown. In that case you will need to tell the cache that the content varies by that parameter, and it should build a different cache based on the parameter passed. Here's how you can do that:

<%@ OutputCache Duration="120" VaryByParam="Group" %>

Now if you didn't understand any of my mumbling above, don't worry, as long as you don't plan to pass parameters to the RSS feed, everything will work fine for you without the VaryByParam attribute.

Believe it or not, we're done with the markup, so switch to code view. Add the following using statements at the top:

using System.Data.SqlClient; using System.Text; using System.Xml;

Now what's left to do for your RSS feed to work, is add this code in the Page_Load event:

// Clear any previous output from the buffer Response.Clear(); Response.ContentType = "text/xml"; XmlTextWriter xtwFeed = new XmlTextWriter(Response.OutputStream, Encoding.UTF8); xtwFeed.WriteStartDocument(); // The mandatory rss tag xtwFeed.WriteStartElement("rss"); xtwFeed.WriteAttributeString("version", "2.0"); // The channel tag contains RSS feed details xtwFeed.WriteStartElement("channel"); xtwFeed.WriteElementString("title", "Feedpedia Today's World News"); xtwFeed.WriteElementString("link", "http://www.feedpedia.com"); xtwFeed.WriteElementString("description", "The latest news and journals from all over the world."); xtwFeed.WriteElementString("copyright", "Copyright 2005 - 2006 Feedpedia.com. All rights reserved."); // Objects needed for connecting to the SQL database SqlConnection SqlCon; SqlCommand SqlCom; SqlDataReader SqlDR; // Edit to match your connection string SqlCon = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ToString());

// Edit to match your stored procedure or SQL command SqlCom = new SqlCommand("GetTodaysHeadlines", SqlCon); SqlCom.CommandType = CommandType.StoredProcedure; if (SqlCon.State == ConnectionState.Closed) { SqlCon.Open(); } SqlDR = SqlCom.ExecuteReader(); // Loop through the content of the database and add them to the RSS feed while (SqlDR.Read()) { xtwFeed.WriteStartElement("item"); xtwFeed.WriteElementString("title", SqlDR["Title"].ToString()); xtwFeed.WriteElementString("description", SqlDR["Description"].ToString()); xtwFeed.WriteElementString("link", "http://www.feedpedia.com/View.aspx?View=" + SqlDR["ID"]); xtwFeed.WriteElementString("pubDate", SqlDR["Date"].ToString()); xtwFeed.WriteEndElement(); } SqlDR.Close(); SqlCon.Close();

// Close all tags xtwFeed.WriteEndElement(); xtwFeed.WriteEndElement(); xtwFeed.WriteEndDocument();

xtwFeed.Flush(); xtwFeed.Close(); Response.End();

Of course, you'll need to do the necessary changes so that the code matches your SQL database table. If you have any questions or doubts about the code, don't hesitate to place a comment for this tutorial. I will answer ASAP.

If you did everything right, your RSS feed will be valid and should have a structure similar to the one below:

There are several tags which you can add to the RSS feed, such as the author of the headline, the category or an unique ID. You can find more information about this at the RSS 2.0 specification page.