 |
Development Lifecycle »
Code Generation »
General
License: The Code Project Open License (CPOL)
Database Helper v 2.0.0By hdv212An open source code generation utility with some useful features to generate procedures,class for tables and .net code for procedures automatically. |
C# (C# 1.0, C# 2.0, C# 3.0), VB (VB 8.0, VB 9.0)
|
||||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
When we want to create database applications, we must dealing with database repeatedly, then in our code level, for each interaction with database, we must create connection, then define command that will be execute in database and finally get results (if has result). However it's trouble to dealing with database level code in our application code and it's not best practice for high performance database applications. For best result and create database applications in less time and higher performance, this is best practice to split Data Access Layer from our main application code, for this work, Database Helper is one of the most applications that helps you to produce your database applications easier and rather.
Database Helper has been written by c# 2.0, and also Source code is available. For Generate .net code, I used System.CodeDom and System.CodeDom.Compiler namespaces. For loading database information, I used SQLDMO.dll library. This library is good for this purpose, but for the first time, it's take a long time to load database info.
In v 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 Deleates to prevent some trouble about to define a new delegate and write some methods), then optimize loading database info algorithm which improve loading database information about 10 time faster.
DataBase Helper is an open source application that helps you to better programming and interacting with database, it has three useful tools to :
1. Generate Stored Procedures Code in DataBase Level
2. Generate .net (Vb & C#) Single Class and Collection Class for each table in database (BLL)
3. Generate .net (Vb & C#) Code for Stored Procedures as methods and some other useful methods (DAL)
This tool, create 5 stored procedure for each table (include: SelectAll,SelectRow,Insert,Update,Delete), thanks to Mr.Keramati for write this tool and get me it's source code to merge with my other tools, but originally, it was generated 5 procedures for selected table, and I customized it to generate 5 stored procedures for all of tables in database, Mr.Keramati also publish his source code in CodePLex seperately.
The output of this tool is the folder (named Sql) that contained a single file with .sql extension that include all stored procedures generated for all tables in database.
This tool, create two classes for each table, first class is the single class that helps you write your codes more Strongly-Typed, the second class is Collection class (for first class) that inherited from CollectionBase class to hold number of it's single class as rows(like DataTable) in memory for working with many rows with Strongly-Typed manner, you even can bind your DataGridView.DataSource to this object. Classes are generates in either CSharp or Vb.Net language in namespace that has same name with database.
The output of this tool is a folder (named BLL) that contain a .net source file include all classes that generated from tables in Database namespace(the filename is equals to Database name).
This tool, extract all stored procedures from database and create a method for each one, parameters also defines in methods (if exists), in the other words, it will be create Data Access Layer for you.
All methods returns DataTable object, if it has results, then DataTable.Rows > 0, otherwise, the returned DataTable object has not any valid row (equals to 0).
It is also generate some useful methods for better work and interacting with database :
1. First overload of constructor : that get full connectionString as parameter, you can access to connectionString via ConnectionString property.
2. Second overload of constructor : that get two parameters, ServerName and DataBaseName, and then create Trusted Connection, you can access to connectionString via ConnectionString property.
3. Third overload of constructor that get ServerName,DatabaseName,UserName and Password and create UnTrusted Connection, you can access to connectionString via ConnectionString property.
4. GetData() : this method will be created for general purposes (that return query result) that take a SqlCommand as parameter and return result as DataTable object.
Note : you don't need to pass connection to contrsuctor of SqlCommand object, it's will be initialized inside GetData() method.
5. ExecNonQuery() : this method will be created for general purposes that execute in NonQuery mode and like GetData(), take a SqlCommand and execute it, then return int as result.
The output of this tool is .net source file named DAL, that has a class with the same name(DAL) in it's Database namespace.
It is very easy to use, just follow these steps :
1. Enter Data Source in the Specified ComboBox.
2. Click Connect button to loading all databases that exists in your server (this takes a few minutes). After loading databases, each database node has two sub nodes : Stored Procedures and Tables.
3. Select which database that you want to generate code for it (or each sub node of specified detabase)
4. In the output type section, select an option that you want to.
If you want to generate t-sql code for Stored Procedures in DataBase Level, then Select Generate Stored Procedures Radio Button.
If you want to generate .net code for existing stored procedures and create Data Access Layer, then select Generate Code for Stored Procedures (DAL) Radio Button, if so, select a language that you want to generate code with it.
5. If you want to generate .net class code for tables, select Generate Tables Classes (BLL) check Box (optional), if so, select a language that you want to generate code with it.
6. Select output path for result.
7. Click Generate Code button.
Db Helper, in attention to which output type you selected, generate output result in specified path. After that, if your output is .net code, then you must add it to your project and add using namespace in directive of your source code that you want to use it.
Download attachment file of this article and open zip file.
First, run TestDb_Script.sql in your Sql Server Management Studio (SSMS), it will be create a sample database named : TestDB that contains a table named : Student and some records to work with it.
Second, run DbHelper from debug folder, initialize connection options (DataSource,User,Pass, …) and then click Connect. It will be load all databases that exists in the current DataSource.
Then, select TestDb database that we created earlier.
Because our database has not any Stored Procedure, then in outpuType, select Generate Stored Procedures, select path (language is not important in this mode), then click Generate Code. Open file that has been created (like this : 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 select TestDb database.
In outputType, select Generate Code for Stored Procedures (DAL), and check Generate Tables Classes (BLL). select language and path, then click Generate Code. It will be create in your selected path,a class sourceFile named DAL.cs or DAL.vb which include all code of storedProcedures and Data Access Layer and a folder named BLL which contains TestDb.cs or TestDb.vb that include all the classes code for tables. You must add these files to your solution and add using namespace in your source code directive.
This version of dbHelper has a little Sdk to extend funtionality of it. plugIns is files that have *.dll (for this project) and exist in PlugIns folder (like PlugIns\SamplePlugInforDbHelper.dll),
program was started, it read the available plugins which existing in PlugIns folder and add to PlugIns menu in Database Helper automatically, when user click on each plugIn in PlugIns Menu, the specified
plugin execute it's duty.
for create a new plugin for Database Helper, you should implement this steps :
1. Create a new Class Library project.
2. Add reference to DatabaseHelperSdk.dll.
3. Create a new class that inherite from IPlugIn interface (this interface exist in DatabaseHelperSdk namespace).
4. Implement this three methods (which defined in IPlugIn interface) :
A. InitializeComponent : This method is main method that Initialize your plugIn and add it to
PlugIns menu.
B. Author : This method contains information about plugin author.
C. Description : This method contains information about plugin.
5. Create a Click event for your plugin menu in InitializeComponent method and implemet it's event handler method to execute it's duty.
6. Compile your project and place your created assembly (with .dll extension) in
Database Helper\PlugIns folder. Now when your Database Helper start, you should see your plugin in PlugIns menu.
For more information to know how to create plugin, please download SamplePlugin project that exist in top of this article.
The new realase of database helper (version 2.0) has some important changes as follow :
1. Use Smo (Sql Server Management Objects) instead of Dmo (Distributed Management Objects) to load database information.
2. change algorithm of loading database information (full multi threading) which imporove accessing to it about 10 time faster.
3. Changed insert stored procedure to return all type of inserted pk (including characters,integers and uniqueidentifier), in version 1.0.0, it only return pk which only being int.
4. Change insert method in .net code to return inserted pk using out keyword.
5. Add plugin support and create SDK for it to developing via Third parties easier (sample plugin application can be download in top of this article)
That's all, thank you for reading my article, I hope enjoy it. for any problem and question please email me to : sst.server@gmail.com.
Regards,
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 23 Aug 2008 Editor: Sean Ewington |
Copyright 2008 by hdv212 Everything else Copyright © CodeProject, 1999-2009 Web09 | Advertise on the Code Project |
