Introduction

Database Helper Class Library is a set of class library that helps to make operation of accessing database easier. By using the class library, life will be easier as you can almost select data, insert data, update data, or delete data from database even without writing a single line of SQL statement. As a database application developer, there are 2 tedious tasks, which are the main source of headaches. One is the business logic and another one is the effort of repeating the same code snippets while accessing database to perform SQL statements. The database helper class library will solve the second headache and make developers have more time to focus on business logic.

This class library is not the object mapping stuff that generates various classes for corresponding tables nor stuff that generate data access layer classes automatically. It is a set of classes that can be set to perform database operation on a table very easily. It is very suitable to be used in small and medium scale database application development.

Background

With 4 years of experience as a database application developer, I found that the repetitious block of codes when executing every SQL statement is very time consuming. As a result, I tried to minimize this tedious process and started surfing the net. I found many answers, some of them are:

Object Mapping - Tools that will generate classes for every corresponding table. I think this is suitable for large scale project only.

Data Access Layer - Tools that will generate data access layer automatically. Again, I think it is useful in large scale projects only. Example is LLBLGen.

Data Component - Component that tightly integrates with frond end component and can be used to update a table easily. Example is TTable component in Delphi.

Based on the answers I got, I think there is a need to create a class library that is similar to data component as mentioned above but will not integrate tightly with interface. Based on Data Access Application Block as released by Microsoft Patterns and Practices (I don't want to use data access component in Enterprise Library as it is too heavy weight), LLBLGen version 1 (free for version 1) and some of my brain juice, I had churned out this Database Helper Class Library which has a feature of TTable component and other more useful features. This class library is very useful for backend processing and front end table maintenance task in small and medium scale project.

Using the Code

For Database Helper Class Library, the most useful classes are SqlTableHelper, SqlHelper, SqlDataSetHelper and SqlConnectionProvider. SqlTableHelper is a class to help to perform database operation on a table without writing a single line of SQL statement. SqlHelper is to help to execute other customized SQL statements easily. SqlDataSetHelper is a class related to database relationship and makes retrieving related data row easier. At last, SqlConnectionProvider is a class that provides database connection and can span among 1 or more SqlTableHelpers and SqlHelpers. This make control of database transaction among several tables easier.

Important Things to Note

For SqlTableHelper instance, Compile method must be called at least 1 time before any database operation can be performed. If FieldToSelect and/or FieldToUpdate property value is reset, Compile method must be recalled so that new fields to select and/or update can work.

SqlHelper Samples

SqlHelper is a class that is used to perform customized SQL statements, e.g. stored procedure that cannot be done through </code />SqlTableHelper </code />class.

SqlDataSetHelper Samples

You can use SqlDataSetHelper.FillParents method or SqlDataSetHelper.FillChilds method which is static to retrieve all related tables' rows once all the parent or child relationships are defined. Example is as follows:

Points of Interest

When I developed this class library, I learnt that object mapping and auto-generated data access layer method are useful only for large scale projects where there are many programmers for the project. For small and medium scale projects where there are 1 or 2 developers normally, why not we consider every table as an object. With the concept - a table is an object; I developed the SqlTableHelper class.

Comments and Discussions

Please, explain why you feel the DAAB is too heavy and what makes this is library different, or better, then the plethora of other Data Access Librarys found in existing articles and numerous samples from other sources.

I am not saying DAAB - data access application block version 2 (verson which is stand alone and not integrate in Enterprise Library) is too heavy. Actually, my library is built based on some classes from DAAB.

I said that DAAB version in Enterprise Library is too heavy as it needs other application blocks to work such as Configuration Application Block.

Most Data Access Library found in existing articles is a tool to generate data access layer dynamically. But, my solution is not generate any codes, it just need to instance a class to perform all database operation on a table. It is very suitable for small and medium project where there are 1 or 2 programmer only. Just imagine, if only 1 programmer works on a project, do you want to use a tool to generate data access layer and then work on the data access layer. It is tedious. So, my solution make a programmer perform database operation on a table without generating any codes. It save your time and effort.

Unless I'm missing something, DAAB is part of the Enterprise Library and fully integrated with it. Where is the stand alone version? Do you have a link?

Working in teams of all sizes, from one to 50+ developers, I can say that I would rather use the EntLib. Your code may be fine for your very limited experience and for dabbeling with the family business but not something I would use in a mission critical enterprise application.

Before Microsoft developed Enterprise Library, there are stand alone version for application blocks in Enterprise Library. For Data Access Application Block, Microsoft had restructured it in Enterprise Library totally which use factory concept and can access MS SQL server or Oracle database easily. The previous stand alone version 2 was released in 2004 and can access MS SQL server only.

The Data Access Application Block version 2 link is http://www.microsoft.com/downloads/details.aspx?FamilyID=f63d1f0a-9877-4a7b-88ec-0426b48df275&DisplayLang=en

I do not agree with you that it should not be used in a mission critical enterprise application. If the misson critical enterprise application is small and not performance-driven, why you should use a heavy weight enterprise library. The exception is if you want to integrate it with other part of system which also use enterprise library to make consistency.

As my previous reply, the library is very suitable for small and medium project which is developed by 1 or 2 programmer only and normally used in retail shop. Some of its example is POS, Inventory Control Sytem, Accounting System, or any customized system as requested by customer.

A newer version of this application block has been released as a part of the patterns & practices Enterprise Library. The new version includes updated functionality based on customer feedback, as well as improved extensibility, ease of use, and consistency and integration with other blocks in Enterprise Library. The application block you have requested is still available for download but is now considered as an archive release.

The above is from the link you provided. DAAB 2.0 is outdated and has been replaced.

If you actually had some experience on applications beyond the family business/hobbyist level you would understand the model behind the EntLib and recognize it's benefits; yes it has some limitations, but your's far exceeds those. Again, your library, although an interesting side project, is not worth using or replacing other, more robust libraries. Do some reseach and education before your reply or submit another article.

Note that I had 4 years of experience working as analyst programmer in IT and not my family business. I had worked on POS, Procurement, and many customized business projects. While I develop my library, I was working as analyst programmer and not helping my family business.

I know the benifits of EntLib. I also believe that "The best method is the most suitable method for a situation". So, does the newer Windows Presentation Foundation in .Net 3.0 will replace WinForm? Sometimes, an older version is better than newer one.

Take a consideration, if your boss told you to develop a customized project and the project is small and you are the only programmer to work on it, do you will still use EntLib to develop it. Please justify it as it need more time and more effort.

Furthermore, my library is not intended to compete with EntLib-likes framework which is very suitable to be used in big-scale project nor it is to replace any other frameworks. I just suggest an option and thinks it is very suitable to used in small and medium project.

With the EntLib I can use configuration files to control a great deal of the application. Your library doesn't allow for this. If the database changed from SQL Server to Oracle, or another provider, or visa versa, how would an applicaiton using your libary deal with this? How does your library address different data providers. You are limited to SQL Server. In a good many organizations there are both SQL Serve and Oracle databases that must be accessed, even within the same application.

I don't have the time, nor desire, to point out all of the flaws in your code. In short your library is written for your needs, for a very small area, not for use in other projects by other developers. It is not scalable nor easily configurable to meet the needs of a real application.

For EntLib, all application blocks are integrated. This is the benifits and also the drawback of EntLib. I know that you can use configuration application blocks to control your application. But, what happen if I am not prefer using configuration file but to control my application by other way, e.g put all settings in database.

My point at exception handling is to catch all unexpected exception (may be out of memory) besides sql exception. Note that in code, I save the Sql Error no. if the exception is SQL exception. Then, I rethrow the exception.

If you want to log exception, you can free to use other exception handling library or your own ones. My point at here, you can integrate vary applicaton blocks easily to achieve your goal. My library responsibility at here is to act as helper class to database access operation. It even can work side by side with other DAAB. If you look into my code in details, you will note that I also make use of Microsoft Exception Handling Application Block (earlier version but stand alone) to create my custom exception. To logging, you can easily use this application block to log exception.

There are always flaws in a library. Codes will be better only after you test it and debug it. I truly welcome any good feedback or suggestion on my library.

Do you know sometimes a hobbyist can do even better that a professionist?

To logging, you can easily use this application block to log exception.

By adding it to your library and recompiling the code base. Not very configuable and maintainable.

falconsoon wrote:

Do you know sometimes a hobbyist can do even better that a professionist?

Perhaps, but not you.

You have failed several times to address the original statement: Please, explain why you feel the DAAB is too heavy and what makes this is library different, or better, then the plethora of other Data Access Librarys found in existing articles and numerous samples from other sources.

If code like this, you need to re-throw every exception in every catch block. If you differentiate hobbyist and professionist by this snippets of code only, I wonder your profession.

Mark Nischalke wrote:By adding it to your library and recompiling the code base. Not very configuable and maintainable.

Please point out why it is not very configurable and maintainable. I know that many programmer like to use framework without knowing how the framework works. This cause problem when they meet something that cannot be done using the framework. My point here is to make all individual framework stand alone, e.g. Exception Handling Block, DAAB, and Configuration Block, then you can easily integrate them to achieve maximum flexibility.

Mark Nischalke wrote:Perhaps, but not you.

When I worked full time as analyst programmer for 4 years, I met many programmers who code bugging system. So, I am sure I am better than someone even I am hobbyist by now.

Mark Nischalke wrote:You have failed several times to address the original statement: Please, explain why you feel the DAAB is too heavy and what makes this is library different, or better, then the plethora of other Data Access Librarys found in existing articles and numerous samples from other sources.

Unless, I make a mistake to say that DAAB in EntLib is heavy. My real meaning is the whole EntLib is heavy weight. If you want to use DAAB only, you need to add the whole EntLib to your project and configure the DAAB using Configuration Application Block.

My library can let user perform database operation on a table without writing a single line of SQL using SqlTableHelper class. Please read through my article carefully. My library also make connection and database transaction very easy to control while maintain its flexibility. For example:

There are plethora of other Data Access Library, some even don't have difference but only the implementaion method is different. As a result, it is just a whether favour meet your taste question. If my library don't meet your taste, don't use it and don't say it is useless.

If code like this, you need to re-throw every exception in every catch block. If you differentiate hobbyist and professionist by this snippets of code only, I wonder your profession.

If you do some reading and research on Exception Handling this is the way it is done. Perhaps not every exception need be rethown to the user of the library. Perhaps an internal exception may be handled. Perhaps different exceptions should be handled and logged in a different manner. Your method does not allow for this.

falconsoon wrote:

If you want to use DAAB only, you need to add the whole EntLib to your project

Your ignorance shows again. You do not need to add the entire Enterprise Library to use just the data access block.

falconsoon wrote:

Please point out why it is not very configurable and maintainable.

Please stop, it hurts when I laugh so much.

I grow weary of this. Again you have failed to answer and justify your library. You have only reiterated your ignorance of professional development. Save yourself further embassment and don't reply. If you want further tutoring contact me offline and we can arrange a fee. Afterall, I'm a professional, not a hobbyist, my time is valuable.

Mark Nischalke wrote:If you do some reading and research on Exception Handling this is the way it is done. Perhaps not every exception need be rethown to the user of the library. Perhaps an internal exception may be handled. Perhaps different exceptions should be handled and logged in a different manner. Your method does not allow for this.

My point here is to let all exceptions handled by users. You show yours ignorance here as you only look at things from 1 point only. What's a shame! ;P

Mark Nischalke wrote:Your ignorance shows again. You do not need to add the entire Enterprise Library to use just the data access block.

But, you sill need to add Configuration Application Block and Common Block to use data access block. This leave no choice for user to choose their favoured configuration method.

Mark Nischalke wrote:Please stop, it hurts when I laugh so much.

You point out nothing. What's a ignorant!

Mark Nischalke wrote:I grow weary of this. Again you have failed to answer and justify your library. You have only reiterated your ignorance of professional development. Save yourself further embassment and don't reply. If you want further tutoring contact me offline and we can arrange a fee. Afterall, I'm a professional, not a hobbyist, my time is valuable.

I had justify my library in previous reply. You ignored the reply and I accepted it. I am a professional too before become hobyyist. More important, I don't need professional whose only look things at 1 point like you to lead me. I have many time. So, do reply in future to show your ignorance.

An EXCELLENT example of the difference between criticism and being an @ssh0le. With criticism, there's at least a *chance* the one being criticized will respond helpfully. Not so with being an @ssh0le.