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

Hamidreza-Ghasemkhah

Rate me:

4.48/5 (27 votes)

8 Dec 2007CPOL2 min read

133K

13.7K

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

Using the Code

Class Structure

Property
Insert method
Update method
Delete method
Select method

Description

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; }
}

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.

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();
    }
}

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();
    }
}

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:

Insert

SQL

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
)

Update

SQL

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
)

Delete

SQL

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

Select

SQL

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

Application Manual

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.

Left Zone

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

You see the generated class in this window. To use it later, you should save the content in a *.cs file using toolbar.
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

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)

Written By

Hamidreza-Ghasemkhah

Web Developer

Iran (Islamic Republic of)

This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

the following includes the following fixs:
1) C# code connection missing ;
2) sql - edit stored procedure now ignores the key fields in the set part of the sql

using System;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data;
using System.Data.SqlClient;

namespace SUNCodeGenerator.Classes
{
    class GeneratorClass
    {
        private string mTableName = "";
        private string connStr = "";

        public GeneratorClass(string connectionString, string TableName)
        {
            mTableName = TableName;
            connStr = connectionString;
        }
        private string CreateProperty(string type)
        {
            string ret = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;

            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (type == "C#")
                    {
                        ret += "\t\tprivate string m_" + rdr[0].ToString() + ";" +
                                "\n" +
                                "\t\tpublic string " + rdr[0].ToString() + "\n" +
                                "\t\t{\n" +
                                "\t\t\tget { return m_" + rdr[0].ToString() + "; }\n" +
                                "\t\t\tset { m_" + rdr[0].ToString() + " = value; }\n" +
                                "\t\t}\n";
                    }

                    else if (type == "VB")
                    {
                        ret += "\t\tPrivate m_" + rdr[0].ToString() + " As String" +
                                "\n" +
                                "\t\tProperty " + rdr[0].ToString() + " As String\n" +
                                "\t\t\tGet\n" +
                                "\t\t\t\tReturn m_" + rdr[0].ToString() + "\n" +
                                "\t\t\tEnd Get\n" +
                                "\t\t\tSet(ByVal value As String)\n" +
                                "\t\t\t\tm_" + rdr[0].ToString() + " = value\n" +
                                "\t\t\tEnd Set\n" +
                                "\t\tEnd Property\n\n";
                    }
                }
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }


            return ret;
        }

        private string CreateInsert(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {
                string tValue = "";
                string m_value = "string ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " , string ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 10);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic void Insert(" + m_value + ")\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Insert_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                tValue +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                string tValue = "";
                string m_value = "ByVal ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " As String , ByVal ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 9);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tPublic Sub Insert(" + m_value + ")\n" +
                        "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                        "\t\t\tDim cmd As New SqlCommand(\"Insert_" + mTableName + "\", conn)\n" +
                        "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                        tValue +
                        "\n" +
                        "\t\t\tTry\n" +
                        "\t\t\t\tconn.Open()\n" +
                        "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                        "\t\t\tCatch\n" +
                        "\t\t\tFinally\n" +
                        "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                        "\t\t\t\tconn.Dispose()\n" +
                        "\t\t\t\tcmd.Dispose()\n" +
                        "\t\t\tEnd Try\n" +
                        "\t\tEnd Sub\n";

            }

            return ret;
        }

        private string CreateUpdate(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {


                string tValue = "";
                string m_value = "string ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " , string ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(@\"" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " );\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 10);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tpublic void Update(" + m_value + ")\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Update_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                tValue +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                string tValue = "";
                string m_value = "ByVal ";

                SqlConnection conn = new SqlConnection(connStr);
                SqlCommand cmd = new SqlCommand("SELECT Column_Name From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
                SqlDataReader rdr;
                try
                {
                    conn.Open();
                    rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        m_value += rdr[0].ToString() + " As String , ByVal ";
                        tValue += "\t\t\tcmd.Parameters.AddWithValue(\"@" + rdr[0].ToString() + "\" ," + rdr[0].ToString() + " )\n";
                    }
                    m_value = m_value.Substring(0, m_value.Length - 9);
                }
                catch
                { }
                finally
                {
                    if (conn.State == ConnectionState.Open)
                    {
                        conn.Close();
                    }
                    conn.Dispose();
                    cmd.Dispose();
                }


                ret += "\t\tPublic Sub Update(" + m_value + ")\n" +
                        "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                        "\t\t\tDim cmd As New SqlCommand(\"Update_" + mTableName + "\", conn)\n" +
                        "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                        tValue +
                        "\n" +
                        "\t\t\tTry\n" +
                        "\t\t\t\tconn.Open()\n" +
                        "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                        "\t\t\tCatch\n" +
                        "\t\t\tFinally\n" +
                        "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                        "\t\t\t\tconn.Dispose()\n" +
                        "\t\t\t\tcmd.Dispose()\n" +
                        "\t\t\tEnd Try\n" +
                        "\t\tEnd Sub\n";
            }

            return ret;
        }

        private string CreateDelete(string LanguageType)
        {
            string ret = "";
            if (LanguageType == "C#")
            {

                ret += "\t\tpublic void Delete(string ID)\n" +
                                "\t\t{\n" +
                                "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
                                "\t\t\tSqlCommand cmd = new SqlCommand(\"Delete_" + mTableName + "\", conn);\n" +
                                "\t\t\tcmd.CommandType = CommandType.StoredProcedure;\n" +
                                "\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID);\n" +
                                "\n" +
                                "\t\t\ttry\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tconn.Open();\n" +
                                "\t\t\t\tcmd.ExecuteNonQuery();\n" +
                                "\t\t\t}\n" +
                                "\t\t\tcatch\n" +
                                "\t\t\t{}\n" +
                                "\t\t\tfinally\n" +
                                "\t\t\t{\n" +
                                "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                                "\t\t\t\tconn.Dispose();\n" +
                                "\t\t\t\tcmd.Dispose();\n" +
                                "\t\t\t}\n" +
                                "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                ret += "\t\tPublic Sub Delete(ByVal ID As String)\n" +
                           "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                           "\t\t\tDim cmd As New SqlCommand(\"Delete_" + mTableName + "\", conn)\n" +
                           "\t\t\tcmd.CommandType = CommandType.StoredProcedure\n" +
                           "\t\t\tcmd.Parameters.AddWithValue(\"@ID\",ID)\n" +
                           "\n" +
                           "\t\t\tTry\n" +
                           "\t\t\t\tconn.Open()\n" +
                           "\t\t\t\tcmd.ExecuteNonQuery()\n" +
                           "\t\t\tCatch\n" +
                           "\t\t\tFinally\n" +
                           "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                           "\t\t\t\tconn.Dispose()\n" +
                           "\t\t\t\tcmd.Dispose()\n" +
                           "\t\t\tEnd Try\n" +
                           "\t\tEnd Sub\n";
            }

            return ret;
        }

        public string ReturnSQLDelete()
        {
            string ret = "";
            ret = "CREATE PROCEDURE [delete_" + mTableName + "]\n" +
                    "\t(@ID 	[int])\n" +
                    "AS DELETE [" + mTableName + "]\n" +
                    "WHERE\n" +
                    "\t( [ID]	 = @ID)\n";
            return ret;
        }

        public string ReturnSQLSelect()
        {
            string ret = "";
            string m_value = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr[0].ToString().Length > 4)
                    {
                        if (rdr[0].ToString().Substring(rdr[0].ToString().Length - 4, 4) == "Code")
                            m_value += "\t\t(Select Title From " + rdr[0].ToString().Substring(0, rdr[0].ToString().Length - 5) + " Where Code = " + rdr[0].ToString() + ")\tAS\t'" + rdr[0].ToString() + "',\n";
                        else
                            m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
                    }
                    else
                        m_value += "\t\t" + rdr[0].ToString() + "\tAS\t'" + rdr[0].ToString() + "',\n";
                }
                m_value = m_value.Substring(0, m_value.Length - 2);

            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

            ret = "CREATE View [Select_" + mTableName + "]\n" +
                    "AS\n" +
                    "\nSelect " + m_value + "\n\n" +
                    "From\t" + mTableName + "";


            return ret;
        }

        public string ReturnSQLUpdate()
        {
            string ret = "";
            string tValue = "";
            string tValue1 = "";
            string m_value = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand(@"
                    SELECT     INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME, INFORMATION_SCHEMA.COLUMNS.DATA_TYPE, 
                      INFORMATION_SCHEMA.COLUMNS.CHARACTER_MAXIMUM_LENGTH, INFORMATION_SCHEMA.KEY_COLUMN_USAGE.CONSTRAINT_NAME
                    FROM         INFORMATION_SCHEMA.COLUMNS LEFT OUTER JOIN
                      INFORMATION_SCHEMA.KEY_COLUMN_USAGE ON 
                      INFORMATION_SCHEMA.COLUMNS.TABLE_CATALOG = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_CATALOG AND 
                      INFORMATION_SCHEMA.COLUMNS.TABLE_SCHEMA = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_SCHEMA AND 
                      INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.TABLE_NAME AND 
                      INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = INFORMATION_SCHEMA.KEY_COLUMN_USAGE.COLUMN_NAME 
                    Where INFORMATION_SCHEMA.COLUMNS.Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    if (rdr[3].ToString()=="")//not key field
                        m_value += "\t\t" + rdr[0].ToString() + "\t\t\t=\t@" + rdr[0].ToString() + ",\n";
                    if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
                    else
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
                    tValue1 += "\t@" + rdr[0].ToString() + ",\n";
                }
                m_value = m_value.Substring(0, m_value.Length - 2);
                tValue = tValue.Substring(0, tValue.Length - 2);
                tValue1 = tValue1.Substring(0, tValue1.Length - 2);
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

            ret = "CREATE PROCEDURE [update_" + mTableName + "]\n" +
                    "(\n" + tValue + "\n)\n" +
                    "AS UPDATE " + mTableName + "\n" +
                    "SET\n" + m_value + "\n" +
                    "WHERE\n" +
                    "(\n\tID\t=\t@ID\n)";

            return ret;
        }

        public string ReturnSQLInsert()
        {
            string ret = "";

            string tValue = "";
            string tValue1 = "";
            string m_value = "";

            SqlConnection conn = new SqlConnection(connStr);
            SqlCommand cmd = new SqlCommand("SELECT Column_Name,Data_Type,Character_Maximum_Length From information_Schema.columns Where Table_Name='" + mTableName + "'", conn);
            SqlDataReader rdr;
            try
            {
                conn.Open();
                rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    m_value += "\t" + rdr[0].ToString() + ",\n";
                    if (rdr[2].ToString() == "" || rdr[1].ToString() == "ntext" || rdr[1].ToString() == "image")
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "]" + ",\n";
                    else
                        tValue += "\t@" + rdr[0].ToString() + "\t\t\t\t\t\t[" + rdr[1].ToString() + "](" + rdr[2].ToString() + ")" + ",\n";
                    tValue1 += "\t@" + rdr[0].ToString() + ",\n";
                }
                m_value = m_value.Substring(0, m_value.Length - 2);
                tValue = tValue.Substring(0, tValue.Length - 2);
                tValue1 = tValue1.Substring(0, tValue1.Length - 2);
            }
            catch
            { }
            finally
            {
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                }
                conn.Dispose();
                cmd.Dispose();
            }

            ret = "CREATE PROCEDURE [insert_" + mTableName + "]\n" +
                    "\t(\n" + tValue + "\n\t)\n" +
                    "AS INSERT INTO " + mTableName + "\n" +
                    "\t(\n" + m_value + "\n\t)\n" +
                    "VALUES\n" +
                    "\t(\n" + tValue1 + "\n\t)\n";

            return ret;
        }

        public string CreateSelect(string LanguageType)
        {
            string ret = "";

            if (LanguageType == "C#")
            {
                ret = "\t\tpublic DataSet SelectRecords()\n" +
                       "\t\t{\n" +
                       "\t\t\tSqlConnection conn = new SqlConnection(ConnectionString);\n" +
                       "\t\t\tSqlDataAdapter cmd = new SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn);\n" +
                       "\t\t\tDataSet dts = new DataSet();\n" +
                       "\t\t\ttry\n" +
                       "\t\t\t{\n" +
                       "\t\t\t\tconn.Open();\n" +
                       "\t\t\t\tcmd.Fill(dts);\n" +
                       "\t\t\t\treturn dts;\n" +
                       "\t\t\t}\n" +
                       "\t\t\tcatch\n" +
                       "\t\t\t{ }\n" +
                       "\t\t\tfinally\n" +
                       "\t\t\t{\n" +
                       "\t\t\t\tif (conn.State == ConnectionState.Open) conn.Close();\n" +
                       "\t\t\t\tcmd.Dispose();\n" +
                       "\t\t\t}\n" +
                        "\t\t\treturn dts;\n" +
                       "\t\t}\n";
            }
            else if (LanguageType == "VB")
            {
                ret += "\t\tPublic Function SelectRecords() As DataSet\n" +
                           "\t\t\tDim conn As New SqlConnection(ConnectionString)\n" +
                           "\t\t\tDim cmd As New SqlDataAdapter(\"SELECT * FROM Select_" + mTableName + "\", conn)\n" +
                           "\t\t\tDim dts As New DataSet()\n" +
                           "\n" +
                           "\t\t\tTry\n" +
                           "\t\t\t\tconn.Open()\n" +
                           "\t\t\t\tcmd.Fill(dts)\n" +
                           "\t\t\t\tReturn dts\n" +
                           "\t\t\tCatch\n" +
                           "\t\t\t\tReturn Null\n" +
                           "\t\t\tFinally\n" +
                           "\t\t\t\tIf conn.State = ConnectionState.Open Then conn.Close()\n" +
                           "\t\t\t\tconn.Dispose()\n" +
                           "\t\t\t\tcmd.Dispose()\n" +
                           "\t\t\tEnd Try\n" +
                           "\t\tEnd Function\n";
            }

            return ret;
        }

        public string ReturnClass(string type)
        {
            string ret = "";


            if (type == "C#")
            {
                ret = "using System;\n" +
                        "using System.Collections.Generic;\n" +
                        "using System.Text;\n" +
                        "using System.Data;\n" +
                        "using System.Data.SqlClient;\n" +
                        "using System.Collections;\n" +
                        "using System.Configuration;\n" +
                        "using System.Web;\n" +
                        "using System.Web.Security;\n" +
                        "using System.Web.UI;\n" +
                        "using System.Web.UI.WebControls;\n" +
                        "using System.Web.UI.WebControls.WebParts;\n" +
                        "using System.Web.UI.HtmlControls;\n" +
                        "\n" +
                        "namespace DAL\n" +
                        "{\n" +
                        "\tclass " + mTableName + "\n" +
                        "\t{\n" +
                        "\n" +
                        "\t\tprivate string ConnectionString;\n" +
                        "\t\tpublic " + mTableName + "(string ConnStr)\n" +
                        "\t\t{\n" +
                        "\t\t\tConnectionString = ConnStr;\n" +
                        "\t\t}\n" +
                        "\n" +
                        CreateProperty("C#") +
                        "\n" +
                        CreateUpdate("C#") +
                        "\n" +
                        CreateInsert("C#") +
                        "\n" +
                        CreateDelete("C#") +
                        "\n" +
                        CreateSelect("C#") +
                        "\t}\n" +
                        "}\n";
            }
            else if (type == "VB")
            {
                ret = "Imports System\n" +
                        "Imports System.Collections.Generic\n" +
                        "Imports System.Text\n" +
                        "Imports System.Data\n" +
                        "Imports System.Data.SqlClient\n" +
                        "Imports System.Collections\n" +
                        "Imports System.Configuration\n" +
                        "Imports System.Web\n" +
                        "Imports System.Web.Security\n" +
                        "Imports System.Web.UI\n" +
                        "Imports System.Web.UI.WebControls\n" +
                        "Imports System.Web.UI.WebControls.WebParts\n" +
                        "Imports System.Web.UI.HtmlControls\n" +
                        "\n" +
                        "Namespace SUNCodeGenerator\n" +
                        "\tPublic Class " + mTableName + "\n" +
                        "\n" +
                        "\t\tPrivate ConnectionString As String\n" +
                        "\n" +
                        CreateProperty("VB") +
                        "\n" +
                        CreateUpdate("VB") +
                        "\n" +
                        CreateInsert("VB") +
                        "\n" +
                        CreateDelete("VB") +
                        "\n" +
                        CreateSelect("VB") +
                        "\tEnd Class\n" +
                        "End Namespace\n";
            }

            return ret;
        }

    }
}

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