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

DAL Class and Transact-SQL Generator for C# and VB.NET

, 8 Dec 2007 CPOL
Rate this:
Please Sign up or sign in to vote.
Automatically generates the required class and stored procedure to work with SQL databases

Introduction

This program will help you write the necessary code for doing base operations of your application's Data Layer (both Web and Windows applications). The function of this program is making a DAL class for easily selecting, manipulating and deleting records of the Database. The result is a simple class for each table in your database. The structure is described below in a schematic view.

Background

Screenshot - Diagram.jpg

Using the Code

Class Structure

  1. Property
  2. Insert method
  3. Update method
  4. Delete method
  5. Select method

Description

  1. These generated properties indicate each field of a table. You may use them to get or set values in your application:

    private string ConnectionString;
    public City(string ConnStr)
    {
        ConnectionString = ConnStr;
    }
    
    private string m_Code;
    public string Code
    {
        get { return m_Code; }
        set { m_Code = value; }
    }
    
    private string m_Title;
    public string Title
    {
        get { return m_Title; }
        set { m_Title = value; }
    }
    
    private string m_Province_Code;
    public string Province_Code
    {
        get { return m_Province_Code; }
        set { m_Province_Code = value; }
    }
  2. This method uses generated stored procedures to Insert records into the database. It passes given values of each field to that stored procedure:

    public void Insert(string Code , string Title , string Province_Code)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand("Insert_City", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue(@Code ,Code );
        cmd.Parameters.AddWithValue(@Title ,Title );
        cmd.Parameters.AddWithValue(@Province_Code ,Province_Code );
    
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch
        {}
        finally
        {
            if (conn.State==ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            cmd.Dispose();
        }
    }

    You can also use the second overload of the Insert method to insert a record using currently specified properties.

  3. The Update method manipulates a record in database using the generated stored procedures. Such as above, this method calls the proper stored procedure with the given values:

     public void Update(string Code , string Title , string Province_Code)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand("Update_City", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue(@Code ,Code );
        cmd.Parameters.AddWithValue(@Title ,Title );
        cmd.Parameters.AddWithValue(@Province_Code ,Province_Code );
    
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch
        {}
        finally
        {
            if (conn.State==ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            cmd.Dispose();
        }
    }
  4. This method uses generated stored procedures to delete a record from the database. It takes the value of the key field and calls the stored procedure using that value:

    public void Delete(string ID)
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlCommand cmd = new SqlCommand("Delete_City", conn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue(@ID,ID);
    
        try
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        catch
        {}
        finally
        {
            if (conn.State==ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            cmd.Dispose();
        }
    }
  5. Use this method to select a record set from a table to use later in your application. This method returns a dataset object using the generated stored procedure:

    private DataSet Select()
    {
        SqlConnection conn = new SqlConnection(ConnectionString);
        SqlDataAdapter cmd = new SqlDataAdapter("Select_City", conn);
        DataSet dts = new DataSet();
        try
        {
            conn.Open();
            cmd.Fill(dts);
            return dts;
        }
        catch
        {
            return null; 
        }
        finally
        {
            if (conn.State==ConnectionState.Open)
            {
                conn.Close();
            }
            conn.Dispose();
            cmd.Dispose();
        }
    }

SQL Transact

This program can generate transact-SQL to reach the nice methodology of "Fat Server and Thin Client". Below is the generated code we talk about:

  1. Insert

    CREATE PROCEDURE [insert_City]
    (
        @Code        [int],
        @Title        [nvarchar](50),
        @Province_Code    [int]
    )
    AS INSERT INTO City
    (
        Code,
        Title,
        Province_Code
    )
    VALUES
    (
        @Code,
        @Title,
        @Province_Code
    )
  2. Update

    CREATE PROCEDURE [update_City]
    (
        @Code            [int],
        @Title            [nvarchar](50),
        @Province_Code        [int]
    )
    AS UPDATE City
    SET
        Code        =    @Code,
        Title        =    @Title,
        Province_Code    =    @Province_Code
    WHERE
    (
        ID=@ID
    )
  3. Delete

    CREATE PROCEDURE [delete_City]
        (@ID     [int])
    AS 
    DELETE [City]
    WHERE
        ([ID]     = @ID)
  4. Select

    CREATE PROCEDURE [Select_City]
    AS 
    SELECT    [ID],
        [Code],
        [Title],
        (Select Title FROM Province WHERE Code=Province_Code) as Province
    FROM
        City  

Application Manual

Screenshot - LogIn.jpg

After you run the program, you must choose the destination database in the login form to generate required stored procedures.

After login, you see the main form such as the above picture.

Screenshot - Program_copy.jpg

Left Zone

  1. Database structure contains all tables and their fields. You can right click on each table and choose the desired item to view DAL class or related stored procedures from the context menu.

Content Zone

  1. You see the generated class in this window. To use it later, you should save the content in a *.cs file using toolbar.
  2. You see the generated transact-SQL codes for creating stored procedures in this window. You may parse or execute them immediately or save them for later use.

Right Zone

  1. You can see the properties of a field you select in the left zone.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Hamidreza-Ghasemkhah
Web Developer
Iran (Islamic Republic Of) Iran (Islamic Republic Of)
No Biography provided

Comments and Discussions

 
QuestionMenus not working Pinmemberhari191134-Oct-14 17:55 
QuestionThanks PinmemberMember 1046299612-Dec-13 10:11 
SuggestionUseful modification Pinmemberbenny_e15-Jun-12 11:37 
GeneralRe: Useful modification Pinmemberbenny_e15-Jun-12 11:38 
QuestionGreat Work Man....But not working properly in windows 7_ 64bit??? PinmemberMahesh Gholap19-Apr-12 0:24 
SuggestionThanks Pinmemberkzelda6-Dec-11 23:51 
QuestionError on click the menu Pinmemberingenvzla29-Oct-11 8:00 
GeneralMy vote of 4 Pinmemberingenvzla29-Oct-11 7:59 
GeneralMy vote of 5 PinmemberJonathan Fernández21-Dec-10 2:24 
GeneralMy vote of 5 Pinmember-=barmaley=-17-Dec-10 5:18 
GeneralMy vote of 5 Pinmembermiladrasouli18-Aug-10 0:35 
GeneralError when trying to run the code Pinmembermary cappello29-Jun-09 11:55 
Good Day:
 
I noticed this DAL Class Generator and thought I would give it a try. I've tried it both on my Vista machine and my Win2003 machine. I get the same issue when trying to run the program. I wanted to find out if you have a fix for the problem.
 
See below for the error... I'm able to connect to the SQL server, but when I click on the Run (Red arrow) for a table in a database or a database I get the same issue:
 
See the end of this message for details on invoking
just-in-time (JIT) debugging instead of this dialog box.
 
************** Exception Text **************
System.NullReferenceException: Object reference not set to an instance of an object.
at SUNCodeGenerator.Form1.buttonItem11_Activate(Object sender, EventArgs e)
at TD.SandBar.ButtonItemBase.OnActivate()
at TD.SandBar.ToolBar.OnItemActivate(ToolbarItemBase item)
at TD.SandBar.ToolBar.OnItemRelease(ToolbarItemBase item, Point position)
at TD.SandBar.ToolBar.OnMouseUp(MouseEventArgs e)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at TD.SandBar.ToolBar.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
 

************** Loaded Assemblies **************
mscorlib
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/Microsoft.NET/Framework/v2.0.50727/mscorlib.dll
----------------------------------------
SUNCodeGenerator
Assembly Version: 1.0.0.0
Win32 Version: 1.0.0.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SUNCodeGenerator.exe
----------------------------------------
System.Windows.Forms
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Windows.Forms/2.0.0.0__b77a5c561934e089/System.Windows.Forms.dll
----------------------------------------
System
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System/2.0.0.0__b77a5c561934e089/System.dll
----------------------------------------
System.Drawing
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Drawing/2.0.0.0__b03f5f7f11d50a3a/System.Drawing.dll
----------------------------------------
System.Configuration
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Configuration/2.0.0.0__b03f5f7f11d50a3a/System.Configuration.dll
----------------------------------------
System.Xml
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3074 (QFE.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_MSIL/System.Xml/2.0.0.0__b77a5c561934e089/System.Xml.dll
----------------------------------------
System.Data
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Data/2.0.0.0__b77a5c561934e089/System.Data.dll
----------------------------------------
System.Transactions
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.Transactions/2.0.0.0__b77a5c561934e089/System.Transactions.dll
----------------------------------------
System.EnterpriseServices
Assembly Version: 2.0.0.0
Win32 Version: 2.0.50727.3053 (netfxsp.050727-3000)
CodeBase: file:///C:/Windows/assembly/GAC_32/System.EnterpriseServices/2.0.0.0__b03f5f7f11d50a3a/System.EnterpriseServices.dll
----------------------------------------
SandBar
Assembly Version: 1.0.11.0
Win32 Version: 1.0.11.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SandBar.DLL
----------------------------------------
SandDock
Assembly Version: 1.0.2.0
Win32 Version: 1.0.2.0
CodeBase: file:///C:/Users/Mary/Desktop/VB-C/Application_Demo/SandDock.DLL
----------------------------------------
 
************** JIT Debugging **************
To enable just-in-time (JIT) debugging, the .config file for this
application or computer (machine.config) must have the
jitDebugging value set in the system.windows.forms section.
The application must also be compiled with debugging
enabled.
 
For example:
 
<configuration>
<system.windows.forms jitDebugging="true" />
</configuration>
 
When JIT debugging is enabled, any unhandled exception
will be sent to the JIT debugger registered on the computer
rather than be handled by this dialog box.
 
If there is a fix, I would love to be able to try this to see if it will do what I need.
 
Thanks so much for your help.
 
Mary.
GeneralYou missed a word Pinmember123123man9-May-09 2:52 
GeneralRe: You missed a word PinmemberHamidreza-Ghasemkhah9-May-09 3:19 
Generalgenerator class errors fixs Pinmembereyal skiba23-Aug-08 4:27 
GeneralGreat Article Pinmemberrubenben2816-Jun-08 12:11 
GeneralNice Pinmemberinetfly1239-Jan-08 9:56 
GeneralAnother Solution PinsussRyan T. Hilton5-Dec-07 7:15 
Generalcool PinmemberMilos Savara1-Dec-07 6:50 
GeneralRe: cool PinmemberHamidreza-Ghasemkhah4-Dec-07 20:46 
GeneralVery useful, thank's PinmemberClaudio Barca1-Dec-07 2:17 
GeneralCode for VB .NET Pinmembermusicinc_261-Dec-07 1:42 
GeneralRe: Code for VB .NET PinmemberHamidreza-Ghasemkhah4-Dec-07 20:50 

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 | Terms of Use | Mobile
Web04 | 2.8.141216.1 | Last Updated 8 Dec 2007
Article Copyright 2007 by Hamidreza-Ghasemkhah
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid