Click here to Skip to main content
Click here to Skip to main content

DBLayer Wizard V 1.0

, , 8 Nov 2005
Rate this:
Please Sign up or sign in to vote.
DBLayer Wizard is a code generator that generates Data Layer classes in C# using ADO.NET and SQL Server 2000.

Introduction

This is the first version of DBLayer Wizard. It's a tool to generate the Data Layer in N-tier architectures in CS file format. This helps developers to save their time from writing the same code every time they work in a new project. We tried to provide the best ways in coding and data access patterns to satisfy speed, ease of use, reliability, robustness, and portability.

As we stated above, our main goal for this tool was to save developer time in N-tier applications using .NET languages. We also tried very hard to cover many methods for data access; for that we studied many data layer frameworks, so we provide here many ways and options for database operations.

Features

  • Creates a class that is called DB which includes common data access functionality in any application like executing in-line SQL, executing a Stored Procedure and executing a transaction.
  • Creates a class for each table that performs the most common operations like select by primary key, select by foreign key, select all records, insert, update and delete.
  • Creates a class to execute Stored Procedures. For each Stored Procedure, gives the ability to return (DataSet, DataReader, or a return value).
  • Generated code is based on templates so developer can customize with save and open customized templates.
  • All generated CS files saved to folder that you choose.

Using the DataLayer wizard code generator

  1. Define SQL Server information (server name, authentication method, database name).
  2. Select tables for which you want to generate a .NET class for.
  3. Select Stored Procedures for which you want to generate methods for.
  4. Select the return data types for tables [DataSet or DataReader or both] and for Stored Procedures.
  5. Define the template which will be used to generate the .NET code.
  6. Define the folder which the code will be generated in.

Using the generated code from your application

Here we will see some examples of how to use the generated code. We are working on the NorthWind as the sample DB. This should act as a guide for the class developer.

  1. How to execute in-line SQL:
    //DBlayer is the namespace of generated code
    //all classes and method are static method 
    //Execute Common method form the base class 
    DataSet ds=DBLayer.DB.Execute("SELECT * FROM Customers");
    //Execute Scalar (Avg,Count,Sum,..)
    object result=DBLayer.DB.ExecuteScalar("SELECT COUNT(*) FROM Customers");
    //Execute Non Query like (Insert,Update,Delete)
    int AffectedRows=DBLayer.DB.ExecuteNonQuery(insert sql statment);

    As you see above, all methods are static and the base namespace is DBlayer.

  2. How to insert into a table:

    As you will see, in the insert operation, we have three different ways for inserting.

    //Insert
    bool result= DBLayer.DBTableShippers.Insert("New Tech","123456"); 
    //Insert DataRow
    DataRow row=DBLayer.DBTableShippers.GetSchema();
    row["CompanyName"]="New Tech";
    row["Phone"]="123456";
    bool result= DBLayer.DBTableShippers.Insert(row);
    //Insert DataTable
    DataTable table=DBLayer.DBTableShippers.GetSchemaTable();
    DataRow row1=table.NewRow();
    row1["CompanyName"]="New Tech";
    row1["Phone"]="123456";
    table.Rows.Add(row1);
    
    DataRow row2=table.NewRow();
    row2["CompanyName"]="New Tech";
    row2["Phone"]="123456";
    table.Rows.Add(row2);
    
    bool result= DBLayer.DBTableShippers.Insert(table);
  3. How to update data in a table:
    //Update
    bool result=DBLayer.DBTableShippers.Update(1,"Home","12365");
    //Update DataRow
    DataRow row=DBLayer.DBTableShippers.GetSchema();
    row["ShipperID"]=1;
    row["CompanyName"]="New Tech";
    row["Phone"]="123456";
    bool result=DBLayer.DBTableShippers.Update(row);
    
    //Update Data Table
    DataTable table=DBLayer.DBTableShippers.GetSchemaTable();
    DataRow row1=table.NewRow();
    row1["ShipperID"]=1;
    row1["CompanyName"]="New Tech";
    row1["Phone"]="123456";
    table.Rows.Add(row1);
    
    DataRow row2=table.NewRow();
    row2["ShipperID"]=2;
    row2["CompanyName"]="Ok";
    row2["Phone"]="123456";
    table.Rows.Add(row2);
    
    bool result=DBLayer.DBTableShippers.Update(table);
  4. How to delete data from a table:
    //Delete All the Rows
    int DeletedRows=DBLayer.DBTableOrders.DeleteAll();
    
    //Delete By PK
    bool result=DBLayer.DBTableOrders.DeleteByPK(int.Parse(txtPK.Text));
    
    //Delete By FK
    bool result=DBLayer.DBTableOrders.DeleteByFK(txtFK.Text);
  5. How to select from a table:
    //Select All Rows
    DataSet ds= DBLayer.DBTableOrders.SelectAll(); 
    //Select By Primary Key
    DataSet ds= DBLayer.DBTableOrders.SelectByPK(int.Parse(txtPKds.Text));
    //Select By Foreign Key
    DataSet ds= DBLayer.DBTableOrders.SelectByFK(txtFKds.Text);
  6. How to execute a transaction:
    //the method takes array of strings which 
    //is the SQL statements of the transaction
    bool result=DBLayer.DB.ExecuteTransaction(txtSQL.Lines);

UML class diagram for the data layer framework classes and methods

The common base class:

Table class:

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here

Share

About the Authors

Wael Amer
Web Developer
Austria Austria
Egyption ,B.Sc in computer science Department from faculty of computer science and information system Egypt-Helwan Univeristy ,
my master in progress in intelligent systems in Vienna Technology university ,
Microsoft Certified Professional science 2003 experience with
Turbo Pascal ,C++,VC++,C#,VB.NET,J# ,asp,ASP.Net,COM+ & XML , sql server 2000,action script 2

Hossam El-Deen
Web Developer
Egypt Egypt
I have B.Sc in Computer Science , Microsoft Certified Solution Developer and experienced with C/C++,MFC,C#,VB.NET,ASP.NET,XML,SQL Server 2000 & Crystal Reports.I like challenge and enjoy working with new technologies.

Comments and Discussions

 
GeneralMy vote of 5 Pinmembermeetarun00724-Apr-13 0:45 
Generalthanx for this good code Pinmembersewedy29-May-06 17:48 
GeneralStored Procedures Pinmemberhjabalin15-Nov-05 10:56 
QuestionCan't extract zip files !!! Pinmemberall4it5-Nov-05 3:32 
AnswerRe: Can't extract zip files !!! PinmemberWael Amer8-Nov-05 8:37 
GeneralMinor bug for Server Name control Pinmembertystent27-Oct-05 5:17 
GeneralRe: Minor bug for Server Name control PinmemberHossam El-Deen27-Oct-05 6:25 
Thanks for mentioning this minor bug Smile | :) .
but to solve it add the following 3 lines
 
_ServerInfo.ServerName=txtServerName.Text;
comboDatabases.DataSource=null;
comboDatabases.Items.Clear();

 
in the method comboDatabases_DropDown(...) before _ServerInfo.UserName=txtUserName.Text;
 
The method will became : -
private void comboDatabases_DropDown(object sender, System.EventArgs e)
{
if(txtServerName.Text!=_ServerInfo.ServerName || txtPassword.Text!=_ServerInfo.Password || txtUserName.Text!=_ServerInfo.UserName)
{
_ServerInfo.ServerName=txtServerName.Text;
comboDatabases.DataSource=null;
comboDatabases.Items.Clear();
_ServerInfo.UserName=txtUserName.Text;
_ServerInfo.Password=txtPassword.Text;
FillComboDatabases();
}
}

NewsRe: Minor bug for Server Name control PinmemberWael Amer27-Oct-05 8:37 
GeneralRe: Minor bug for Server Name control -- another one Pinmembertystent31-Oct-05 11:44 
GeneralRe: Minor bug for Server Name control -- another one PinmemberHossam El-Deen1-Nov-05 5:08 
GeneralRe: Minor bug for Server Name control -- another one PinmemberWael Amer1-Nov-05 6:26 
QuestionHave you heard of typed DataSet ? PinsussAnonymous24-Oct-05 20:16 
AnswerRe: Have you heard of typed DataSet ? PinmemberHossam El-Deen25-Oct-05 9:38 
GeneralTry an OR/M PinmemberCharly7724-Oct-05 3:04 
GeneralRe: Try an OR/M PinmemberHossam El-Deen25-Oct-05 1:07 
GeneralRe: Try an OR/M PinmemberMeisterBiber25-Oct-05 9:20 
GeneralRe: Try an OR/M PinsussAnonymous26-Oct-05 3:36 
GeneralDownloads appear to be corrupted PinmemberThe Last Gunslinger18-Oct-05 16:28 
NewsRe: Downloads appear to be corrupted PinmemberWael Amer18-Oct-05 20:28 
GeneralRe: Downloads appear to be corrupted PinmemberThe Last Gunslinger18-Oct-05 20:54 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 8 Nov 2005
Article Copyright 2005 by Wael Amer, Hossam El-Deen
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid