Product Review : BinaryIntellect Database Helper 2.0

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 (MS-DAAB) 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. Download the component and unzip it in a folder
2. Give a reference to this component in VS.NET.
3. Create instance of required class in your code using appropriate constructor
4. 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 Server (MS-Access/Oracle). So, we need a component that caters this requirement.
2. 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.
3. 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.
4. 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.
5. 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 non-transactional 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 T-SQL/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.