Background
When we want to create database applications, we must interact repeatedly with a database. At the code level, for each interaction with the database, we must create a connection, define the command that will be executed and finally get any results. Quite often the database access code is troublesome and it's not optimized for high performance database applications. For best results in terms of creating database applications in less time and with higher performance, the best practice is to separate the Data Access Layer from our main application code. Database Helper serves exactly that purpose and helps produce database applications easier and faster.
About Database Helper
Database Helper is an open source application that helps programming and interacting with a database. It has three useful tools which will be described in the following sections
- Generate Stored Procedures Code in Database Level
- Generate .NET (VB and C#) Single Class and Collection Class for each table in database (BLL)
- Generate .NET (VB and C#) Code for Stored Procedures and some other useful methods (DAL)
Generate Stored Procedures Code in Database
This tool, creates 5 stored procedures for each table including SelectAll, SelectRow, Insert, Update, Delete. I want to thank Mr. Keramati for writing this tool and giving me the source code to merge with my other tools. His original version generated 5 procedures for a selected table, but I customized it to generate the 5 stored procedures for all of tables in database. Mr. Keramati has also published his source code on CodePlex seperately.
The output of this tool is a folder named Sql containing a single file with .sql extension with all of the stored procedures generated for all tables in database.
Generate .NET Classes (Business Logic Layer)
This tool, creates two classes for each table. The first class helps you write your code more strongly-typed, while the second class is a Collection class (for first class) inherited from the CollectionBase class to hold data rows in memory like DataTable. This permits working with many rows in a strongly-typed manner and you can even bind a DataGridView.DataSource to this object. The classes may be generated in either C# or VB.NET language in a namespace that has same name as the database.
The output of this tool is a folder named BLL that contains a .NET source file including all the classes that were generated from tables in the database.
Generate .NET Code for Stored Procedures (Data Access Layer)
This tool extracts all stored procedures from the database and creates a method for each one including method parameters, if any. In other words, it will create the Data Access Layer for you.
All generated methods return a DataTable object, if DataTable.Rows > 0, otherwise, the returned DataTable object does not have any valid rows.
It also generates the following additional methods for easier interaction with the database.
- First overload of the constructor that takes the full connection string as a parameter. You can access the connection string via the
ConnectionString property. - Second overload of the constructor that takes two parameters,
ServerName and DataBaseName, and creates a Trusted Connection. You can access the connection string via the ConnectionString property. - Third overload of the constructor that takes
ServerName,DatabaseName, UserName and Password as parameters and creates an Untrusted Connection. You can access the connection string via the ConnectionString property. GetData(): This method takes a SqlCommand as a parameter and returns the query result as DataTable object. Note: you don't need to pass a connection to the constructor of SqlCommand object. It will be initialized inside the GetData() method. ExecNonQuery(): This method executes a non-query SQL command and like GetData(), takes a SqlCommand as a parameter. It then return an int as result.
The output of this tool is a .NET source file named DAL, containing a class named DAL in the Database namespace.
How Database Helper Works
Database Helper is very easy to use following these steps:
- Enter the Data Source in the corresponding ComboBox.
- Click the
Connect button to load all databases on your server (this takes a few minutes). After loading databases, each database node has two sub nodes: Stored Procedures and Tables. - Select which database that you want to generate code for (or each sub node of specified detabase).
- In the output section, select an option. If you want to generate T-SQL code for stored procedures at the database level, then select
Generate Stored Procedures radio button. If you want to generate .NET code for existing stored procedures and create the data access layer, then select Generate Code for Stored Procedures (DAL) radio button and select a language for code generation. - If you want to generate .NET class code for the tables, select
Generate Tables Classes (BLL) check box (optional) and select a language for code generation. - Select output path for result.
- Click the
Generate Code button. Database Helper will generate the output results in specified directory. If your output is .NET code, then you must add it to your project along with a using directive.
How to Work with the Sample Project
Download the sample file attached to this article and open zip file. If you run TestDb_Script.sql in your Sql Server Management Studio (SSMS), it will create a sample database named TestDB that contains a table named Student and some records to work with.
Next, run DbHelper from the debug folder, initialize the connection options (DataSource, User, Password, etc.) and then click Connect. All of the databases on the current DataSource will be loaded. Select the TestDb database that we created earlier.
Because this database does not have any stored procedures, in the output, select Generate Stored Procedures, select the directory path (language is not important in this mode), then click Generate Code. Open the file that is created (for example, Your Path\Sql\TestDB_Sps.Sql) with SSMS and run it in your TestDb database.
Then, run DbHelper again (or click Connect again if you didn't close it) and again select the TestDb database.
In the output, select Generate Code for Stored Procedures (DAL), and check Generate Tables Classes (BLL). Select a language and path, then click Generate Code. A class sourceFile named DAL.cs or DAL.vb will be created which includes the code for the stored procedures and Data Access Layer along with a folder named BLL which contains TestDb.cs or TestDb.vb that includes all the classes code for the tables. You must add these files and a using directive to your solution.
New Functionality in Database Helper v 2.0.0 (Plugins)
This version of Database Helper has a small SDK to extend its funtionality. PlugIns are *.dll files and exist in the PlugIns folder, for example PlugIns\SamplePlugInforDbHelper.dll. When the program is started, it reads the available plugins in the PlugIns folder and adds them to the PlugIns menu automatically. When the user selects a PlugIn from the PlugIns menu, the plugin executes.
To create a new plugin for Database Helper, follow these steps:
- Create a new
Class Library project. - Add reference to DatabaseHelperSdk.dll.
- Create a new class that implements the
IPlugIn interface (this interface is in the DatabaseHelperSdk namespace). - Implement the three methods defined in the
IPlugIn interface):
- InitializeComponent: This method is the main method that initializes the plugIn and adds it to the
PlugIns menu. - Author: This method contains information about the plugin's author.
- Description: This method contains a description of the plugin.
- Create a
Click event for your plugin menu in the InitializeComponent method and implement its event handler method to execute the plugin code when selected. - Compile your project and save the created assembly (with .dll extension) in the Database Helper\PlugIns folder. Now when Database Helper starts, you should see your plugin in the
PlugIns menu.
For more information to know how to create plugins, please download the SamplePlugin project from the URL at the top of this article.
History
Version 1.0.0
Database Helper was written in C# 2.0 and the source code is available from the URL above. To generate .NET code, I used the System.CodeDom and System.CodeDom.Compiler namespaces. For loading database information, I used the SQLDMO.dll library. This library is quite good, but takes a long time to load the database info.
Version 2.0.0
In Version 2.0.0 I used Smo (Sql Server Management Objects) instead of Dmo (Distributed Management Objects) to work with Sql Server (2005) easier and use full multi-threading (thanks to Anonymous Delegates). The database info loading algorithm was optimized, which made loading the database information about 10 times faster.
The new release of Database Helper Version 2.0 has some important changes as follow:
- Use of
SMO (Sql Server Management Objects) instead of DMO (Distributed Management Objects) to load database information. - Changed the algorithm for loading database information to full multi-threading, improving the speed about 10 times faster.
- Changed
Insert stored procedure to use all types, including characters, integers, and unique identifiers. In version 1.0.0, it only with integers. - Changed
Insert method in .NET code to return the inserted record using the out keyword. - Added plugin support and created SDK for it to facilitate third party development. The sample plugin application can be downloaded from the URL at the top of this article)
Finally
Thank you for reading my article, I hope enjoy it. If you encounter any problems for any problems or have any questions please email me at: sst.server@gmail.com.
Regards,
Hamed Vaziri.
