I have been told in the past that a programmer by definition is a paid problem solver. Which I find to be very true, most of the programs or websites I have made are the result to solve a problem or to make a task automated and/or easier for someone else. Something I find to be boring while solving these problems is making class objects for my database structure. You create the structure in your database just to have to recreate it in the coding language you’re using, including CRUD functions. This can take time away from the creative part of the job and become boring.

 

I came up with a solution for this. I created code that does a lot of the work for me after I have created my database structure. I created three files of code:

 

1)    DataBaseHelper.cs: This file allows me to use store procedures in either MySQL or MSSQL.

2)    CreateTableCSharpClassAndCRUD.sql: This file generates the CSharp class code for the table and the CRUD functions to create stored procedures in the MSSQL database. Also generates the manager functions to use the MSSQL CRUD procedures to Add/Update, Delete, GetByID,GetAll.

3)    CreateTableCSharpClassMySQL.sql: This file is not giving as the MSSQl version. It only generates the CSharp class. But I did make it easier to create the CRUD functions using the DataBaseHelper.

 

DataBaseHelper.cs

 

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Data.Odbc;
using System;
using System.Collections;

public class MySqlHelper
{
    public MySqlHelper() { }

    public static string connectionString()
    {
        return ConfigurationManager.ConnectionStrings["MySqlConnectionString"].ConnectionString;
    }

    public DataSet RunSelectQuery(string tableName)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT * FROM " + tableName, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunSelectQuery(string tableName, string whereClause)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT * FROM " + tableName + " WHERE " + whereClause, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunSelectQuery(string tableName, string whereClause, string orderBy)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT * FROM " + tableName + " WHERE " + whereClause + " Order By " + orderBy, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunCustomSelectQuery(string tablesJoinStatement, string selectVariables)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT " + selectVariables + " FROM " + tablesJoinStatement, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunCustomSelectQuery(string tablesJoinStatement, string selectVariables, string whereClause)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT " + selectVariables + " FROM " + tablesJoinStatement + " WHERE " + whereClause, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunCustomSelectQuery(string tablesJoinStatement, string selectVariables, string whereClause, string orderBy)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand("SELECT " + selectVariables + " FROM " + tablesJoinStatement + " WHERE " + whereClause + " Order By " + orderBy, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public DataSet RunCustomQuery(string tablesStatement)
    {
        DataSet ds = new DataSet();

        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand select = new OdbcCommand(tablesStatement, db);

            OdbcDataAdapter adapter = new OdbcDataAdapter();
            adapter.SelectCommand = select;
            try
            {
                db.Open();
                adapter.Fill(ds);
                db.Close();
            }
            catch (Exception ex) { }
        }

        return ds;
    }

    public void RunUpdateQuery(string tableUpdateName, string setStatement, string whereClause)
    {
        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand update = new OdbcCommand("Update " + tableUpdateName + " " + setStatement + " WHERE " + whereClause, db);
            
            try
            {
                db.Open();
                update.ExecuteNonQuery();
                db.Close();
            }
            catch (Exception) { }
        }
    }

    public String RunInsertQuery(string insertTableName, string columnName, string values)
    {
        string id = "";
        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand insert = new OdbcCommand("INSERT INTO " + insertTableName + " (" + columnName + ") VALUES(" + values + ")",db );
            
            try
            {
                db.Open();
                //insert.ExecuteNonQuery();
                id = insert.ExecuteScalar() + String.Empty;
                
                db.Close();
            }
            catch (Exception) { }
        }
        return id;
    }

    public int RunTransactionInsertQuery(string insertTableName, string columnName, string values)
    {
        int id = 0;
        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcTransaction transaction = null;
            try
            {
                db.Open();
                transaction = db.BeginTransaction();

                OdbcCommand insert = new OdbcCommand();
                insert.Connection = db;
                insert.Transaction = transaction;

                insert.CommandText = "INSERT INTO " + insertTableName + " (" + columnName + ") VALUES(" + values + ")";
                id = insert.ExecuteNonQuery();
                transaction.Commit();
                db.Close();
            }
            catch (Exception) { }
        }
        return id;
    }

    public void RunDeleteQuery(string tableName, string whereClause)
    {
        using (OdbcConnection db = new OdbcConnection(connectionString()))
        {
            OdbcCommand delete = new OdbcCommand("DELETE FROM " + tableName + " WHERE " + whereClause, db);
            try
            {
                db.Open();
                delete.ExecuteScalar();
                db.Close();
            }
            catch (Exception ex) { }
        }
    }
}

public class MSSqlHelper
{
    public MSSqlHelper() { }

    public static string connectionStringAccCata()
    {
        return ConfigurationManager.ConnectionStrings["MSSqlConnectionString"].ConnectionString;
    }

    public DataSet RunStoredProc(String procName, Hashtable data)
    {
        IDictionaryEnumerator e = data.GetEnumerator();
        SqlCommand cmd = new SqlCommand();
        while (e.MoveNext())
            cmd.Parameters.AddWithValue(e.Key.ToString(), e.Value);

        return this.RunStoredProc(procName, cmd);
    }

    public DataSet RunStoredProc(string procName, SqlCommand cmd)
    {
        DataSet ds = new DataSet();

        using (SqlConnection con = new SqlConnection(connectionStringAccCata()))
        {
            try
            {
                cmd.CommandTimeout = 60;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                con.Open();

                cmd.Connection = con;
                SqlDataAdapter da = new SqlDataAdapter(cmd);
                da.Fill(ds, "Results");
                con.Close();
            }
            catch (Exception ex) { }
            return ds;
        }
    }
}

 

CreateTableCSharpClassAndCRUD.sql

 

--class creation 
DECLARE @table nvarchar(250) Set @table = 'tablename here' 
--class 
SELECT 'public class ' + @table + ' {' AS 'C# Class', 0 SortOrder 
union 
--private variables 
SELECT '//private class variables', 1 SortOrder 
union 
SELECT CASE 
  WHEN DATA_TYPE = 'int' THEN 'private Int32 _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'nchar' THEN 'private String _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'varchar' THEN 'private String _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'nvarchar' THEN 'private String _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'date' THEN 'private DateTime _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'datetime' THEN 'private DateTime _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'bit' THEN 'private Boolean _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'decimal' THEN 'private Decimal _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'float' THEN 'private float _' + LOWER( COLUMN_NAME ) + ';' 
  WHEN DATA_TYPE = 'image' THEN 'private Byte _' + LOWER( COLUMN_NAME ) + ';' 
    END, 2 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table union SELECT ' ', 3 SortOrder 
union 
SELECT '//GETTERS AND SETTERS', 4 SortOrder 
union 
--getters/setters 
SELECT CASE 
  WHEN DATA_TYPE = 'int' THEN 'public Int32 ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'nchar' THEN 'public String ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'varchar' THEN 'public String ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'nvarchar' THEN 'public String ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'date' THEN 'public DateTime ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'datetime' THEN 'public DateTime ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'bit' THEN 'public Boolean ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'decimal' THEN 'public Decimal ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'float' THEN 'public float ' + COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
  WHEN DATA_TYPE = 'image' THEN 'public Byte '+ COLUMN_NAME + ' { get{ return _' + LOWER( COLUMN_NAME ) + '; } set{ _' + LOWER( COLUMN_NAME ) + ' = value; } }' 
    END , 5 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table union SELECT ' ', 6 SortOrder 
union 
--default constructor 
SELECT '//Default Constructor', 7 SortOrder 
union SELECT 'public ' + @table + '(){}', 8 SortOrder 
union 
--Constructor using DataRow 
SELECT '//Constructor using DataRow', 9 SortOrder 
union 
SELECT 'public ' + @table + '(DataRow dr){', 10 SortOrder 
union 
SELECT CASE 
  WHEN DATA_TYPE = 'int' THEN '_' + LOWER( COLUMN_NAME ) + ' = Int32.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);' 
  WHEN DATA_TYPE = 'nchar' THEN '_' + LOWER( COLUMN_NAME ) + ' = dr["' + COLUMN_NAME + '"] + String.Empty;' 
  WHEN DATA_TYPE = 'varchar' THEN '_' + LOWER( COLUMN_NAME ) + ' = dr["' + COLUMN_NAME + '"] + String.Empty;' 
  WHEN DATA_TYPE = 'nvarchar' THEN '_' + LOWER( COLUMN_NAME ) + ' = dr["' + COLUMN_NAME + '"] + String.Empty;' 
  WHEN DATA_TYPE = 'date' THEN '_' + LOWER( COLUMN_NAME ) + ' = DateTime.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);' 
  WHEN DATA_TYPE = 'datetime' THEN '_' + LOWER( COLUMN_NAME ) + ' = DateTime.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);' 
  WHEN DATA_TYPE = 'bit' THEN '_' + LOWER( COLUMN_NAME ) + ' = Boolean.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);' 
  WHEN DATA_TYPE = 'decimal' THEN '_' + LOWER( COLUMN_NAME ) + ' = Decimal.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);'
  WHEN DATA_TYPE = 'float' THEN '_' + LOWER( COLUMN_NAME ) + ' = float.Parse(dr["' + COLUMN_NAME + '"] + String.Empty);' 
  WHEN DATA_TYPE = 'image' THEN '_' + LOWER( COLUMN_NAME ) + ' = Byte.Parse(dr["' + COLUMN_NAME + '"] + String.EMpty);' 
    END, 11 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table 
union 
SELECT '}', 12 SortOrder 
--close class 
union 
SELECT '}', 13 SortOrder 
ORDER BY SortOrder 
--create CRUD 
SELECT 'public static Int32 AddUpdate(' + @table + ' obj){' AS 'C# Manager Functions', 0 SortOrder 
union 
SELECT 'Int32 id = 0;', 1 SortOrder 
union 
SELECT 'MSSqlHelper helper = new MSSqlHelper;', 2 SortOrder 
union 
SELECT 'Hashtable prms = new Hashtable();', 3 SortOrder 
union 
SELECT CASE 
  WHEN DATA_TYPE = 'int' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ' == 0 ? System.Data.SqlTypes.SqlInt32.Null : obj.' + COLUMN_NAME + ');' 
  WHEN DATA_TYPE = 'nchar' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' 
  WHEN DATA_TYPE = 'varchar' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' 
  WHEN DATA_TYPE = 'nvarchar' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' 
  WHEN DATA_TYPE = 'date' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ' == new DateTime() ? System.Data.SqlTypes.SqlDateTime.Null : obj.' + COLUMN_NAME + ' );' 
  WHEN DATA_TYPE = 'datetime' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ' == new DateTime() ? System.Data.SqlTypes.SqlDateTime.Null : obj.' + COLUMN_NAME + ' );' 
  WHEN DATA_TYPE = 'bit' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' WHEN DATA_TYPE = 'decimal' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' 
  WHEN DATA_TYPE = 'float' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' WHEN DATA_TYPE = 'image' THEN 'prms.Add("' + COLUMN_NAME + '", obj.' + COLUMN_NAME + ');' 
    END, 4 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table 
union 
SELECT 'DataSet ds = helper.RunStoredProc("sp_' + @table + '_AddUpdate",prms);', 5 SortOrder 
union 
SELECT 'if(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0){', 6 SortOrder 
union 
SELECT 'Int32.TryParse(ds.Tables[0].Rows[0]["ID"] + String.Empty, out id);', 7 SortOrder 
union 
SELECT '}', 8 SortOrder 
union 
SELECT 'return id;', 9 SortOrder 
union 
SELECT '}', 10 SortOrder 
--Delete from table 
union 
SELECT ' ', 11 SortOrder 
union 
SELECT 'public static void Delete' + @table + '(int id){', 12 SortOrder 
union 
SELECT 'MSSqlHelper helper = new MSSqlHelper();', 13 SortOrder 
union 
SELECT 'Hashtable prms = new Hashtable();', 14 SortOrder 
union 
SELECT 'prms.Add("ID", id);', 15 SortOrder 
union 
SELECT 'helper.RunStoredProc("sp_' + @table + '_DeleteByID", prms);', 16 SortOrder 
union 
SELECT '}', 17 SortOrder 
--GET from table 
union 
SELECT ' ', 18 SortOrder 
union 
SELECT 'public static ' + @table + ' Get' + @table + '(Int32 id){', 19 SortOrder 
union 
SELECT @table + ' obj = new ' + @table + '();', 20 SortOrder 
union 
SELECT 'MSSqlHelper helper = new MSSqlHelper();', 21 SortOrder 
union 
SELECT 'Hashtable prms = new Hashtable();', 22 SortOrder 
union 
SELECT 'prms.Add("ID",id);', 23 SortOrder 
union 
SELECT 'DataSet ds = helper.RunStoredProc("sp_' + @table + '_GetByID",prms);', 24 SortOrder 
union 
SELECT 'if(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0){', 25 SortOrder 
union 
SELECT ' obj = new ' + @table + '(ds.Tables[0].Rows[0]);', 26 SortOrder 
union 
SELECT '}', 27 SortOrder 
union 
SELECT 'return obj;', 28 SortOrder 
union 
SELECT '}', 29 SortOrder 
--GET ALL FROM TABLE 
union 
SELECT ' ', 30 SortOrder 
union 
SELECT 'public static List<' + @table + '> GetAll(){', 31 SortOrder 
union 
SELECT 'List<' + @table + '> temps = new List<' + @table + '>();', 32 SortOrder 
union 
SELECT 'MSSqlHelper helper = new MSSqlHelper();', 33 SortOrder 
union 
SELECT 'DataSet ds = helper.RunStoredProc("sp_' + @table + '_GetAll",new Hashtable());', 34 SortOrder
union 
SELECT 'if(ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0){', 35 SortOrder 
union 
SELECT 'foreach(DataRow dr in ds.Tables[0].Rows){', 36 SortOrder 
union 
SELECT 'temps.Add(new ' + @table + '(dr));', 37 SortOrder 
union 
SELECT '}', 38 SortOrder 
union 
SELECT '}', 39 SortOrder 
union 
SELECT 'return temps;', 40 SortOrder 
union 
SELECT '}', 41 SortOrder ORDER BY SortOrder 
--Create store procedures 
--AddUpdate
SELECT 'CREATE PROCEDURE [dbo].[sp_' + @table + '_AddUpdate]' AS 'Store Procedures', 0 SortOrder 
union 
SELECT CASE 
  WHEN DATA_TYPE = 'int' THEN '@' + COLUMN_NAME + ' int = null,' 
  WHEN DATA_TYPE = 'nchar' AND CHARACTER_MAXIMUM_LENGTH = -1 THEN '@' + COLUMN_NAME + ' nchar(MAX),' 
  WHEN DATA_TYPE = 'nchar' AND CHARACTER_MAXIMUM_LENGTH > -1 THEN '@' + COLUMN_NAME + ' nchar('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10))+'),' 
  WHEN DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH = -1 THEN '@' + COLUMN_NAME + ' varchar(MAX),' 
  WHEN DATA_TYPE = 'varchar' AND CHARACTER_MAXIMUM_LENGTH > -1 THEN '@' + COLUMN_NAME + ' varchar('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10))+'),' 
  WHEN DATA_TYPE = 'nvarchar' AND CHARACTER_MAXIMUM_LENGTH = -1 THEN '@' + COLUMN_NAME + ' nvarchar(MAX),' 
  WHEN DATA_TYPE = 'nvarchar' AND CHARACTER_MAXIMUM_LENGTH > -1 THEN '@' + COLUMN_NAME + ' nvarchar('+CAST(CHARACTER_MAXIMUM_LENGTH AS NVARCHAR(10))+'),' 
  WHEN DATA_TYPE = 'date' THEN '@' + COLUMN_NAME + ' date,' 
  WHEN DATA_TYPE = 'datetime' THEN '@' + COLUMN_NAME + ' datetime,' 
  WHEN DATA_TYPE = 'bit' THEN '@' + COLUMN_NAME + ' bit,' 
  WHEN DATA_TYPE = 'decimal' THEN '@' + COLUMN_NAME + ' decimal,'
  WHEN DATA_TYPE = 'float' THEN '@' + COLUMN_NAME + ' float,' 
  WHEN DATA_TYPE = 'image' THEN '@' + COLUMN_NAME + ' image,' 
    END, 1 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table union SELECT 'AS', 2 SortOrder 
union 
SELECT 'BEGIN', 3 SortOrder 
union 
SELECT 'SET NOCOUNT ON;', 4 SortOrder 
union 
SELECT ' ', 5 SortOrder 
union 
SELECT 'if @ID IS NULL BEGIN', 6 SortOrder
union 
SELECT 'INSERT INTO [dbo].[' + @table + '](', 7 SortOrder 
union 
SELECT COLUMN_NAME + ',', 8 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME <> 'ID' union SELECT ')VALUES(', 9 SortOrder 
union 
SELECT '@' + COLUMN_NAME + ',', 10 SortOrder
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME <> 'ID' union SELECT ') SET @ID = SCOPE_IDENTITY()', 11 SortOrder 
union 
SELECT 'END ELSE BEGIN', 12 SortOrder 
union 
SELECT 'UPDATE [dbo].[' + @table + '] SET', 13 SortOrder 
union 
SELECT COLUMN_NAME + ' = @' + COLUMN_NAME + ',', 14 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME <> 'ID' union SELECT 'WHERE ID = @ID;', 15 SortOrder 
union 
SELECT 'END', 16 SortOrder 
union 
SELECT 'SELECT ID = @ID;', 17 SortOrder 
union 
SELECT 'END', 18 SortOrder 
--DELETE 
union 
SELECT '-- DELETE FROM ' + @table, 19 SortOrder 
union 
SELECT 'CREATE PROCEDURE [dbo].[sp_' + @table + '_DeleteByID]',20 SortOrder 
union 
SELECT '@ID int', 21 SortOrder 
union 
SELECT 'AS BEGIN', 22 SortOrder 
union 
SELECT 'SET NOCOUNT ON;', 23 SortOrder 
union 
SELECT 'UPDATE [dbo].[' + @table + '] SET IsDeleted = 1 WHERE ID = @ID', 24 SortOrder 
--SELECT 'DELETE ' + @table + ' WHERE ID = @ID', 24 SortOrder 
union 
SELECT 'END', 25 SortOrder 
--GET BY ID 
union 
SELECT '--GET FROM ' + @table + ' By ID', 26 SortOrder 
union 
SELECT 'CREATE PROCEDURE [dbo].[sp_' + @table + '_GetByID]', 27 SortOrder 
union 
SELECT '@ID int', 28 SortOrder 
union 
SELECT 'AS BEGIN', 29 SortOrder 
union 
SELECT 'SET NOCOUNT ON;', 30 SortOrder 
union 
SELECT 'SELECT * FROM [dbo].[' + @table + '] WHERE ID = @ID', 31 SortOrder 
union 
SELECT 'END', 32 SortOrder 
--Get ALL 
union 
SELECT '--GET All FROM ' + @table, 33 SortOrder 
union 
SELECT 'CREATE PROCEDURE [dbo].[sp_' + @table + '_GetAll]', 34 SortOrder 
union 
SELECT 'AS BEGIN', 35 SortOrder 
union 
SELECT 'SET NOCOUNT ON;', 36 SortOrder 
union 
SELECT 'SELECT * FROM [dbo].[' + @table + ']', 37 SortOrder 
union 
SELECT 'END', 38 SortOrder ORDER BY SortOrder

 

 

Notes:

 

  • Replace the 'tablename here' text with your actual table name.
  • Will create three result windows: CSharp Class structure, CSharp CRUD functions and SQL to create the stored procedures.
  • The AddUpdate procedure needs a few comma's removed from it to work.
  • The delete stored procedure is also a soft delete. It is generated to set a IsDeleted value.
  • DataSet and DataRow will cause an error just add the library to the class file as well for Hashtable.
  • All integer values are set to null if equals zero in the CSharp functions. This is for the AddUpdate function you may want to remove this logic off of other integer values. Same with DateTime field maybe need to be adjusted to your preference.

 


CreateTableCSharpClassMySQL.sql

 

SET @table = 'table_name';
SELECT CONCAT('public class ', @table, ' {') AS 'C# Class', 0 SortOrder 
union 
#private variables 
SELECT '//private class variables', 1 SortOrder 
union 
SELECT CASE DATA_TYPE
  WHEN 'int' THEN CONCAT('private Int32 _' , LOWER( COLUMN_NAME ) , ';') 
        WHEN 'tinyint' THEN CONCAT('private Int32 _' , LOWER( COLUMN_NAME ) , ';') 
  WHEN 'char' THEN CONCAT('private String _' , LOWER( COLUMN_NAME ) , ';')
  WHEN 'decimal' THEN CONCAT('private Decimal _' , LOWER( COLUMN_NAME ) , ';') 
        WHEN 'date' THEN CONCAT('private DateTime _' , LOWER( COLUMN_NAME ) , ';')
    END, 2 SortOrder        
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table 
union 
SELECT ' ', 3 SortOrder 
union 
SELECT '//GETTERS AND SETTERS', 4 SortOrder 
union 
#getters/setters 
SELECT CASE DATA_TYPE
  WHEN 'int' THEN CONCAT('public Int32 ' , COLUMN_NAME , ' { get{ return _' , LOWER( COLUMN_NAME ) , '; } set{ _' , LOWER( COLUMN_NAME ) , ' = value; } }' )
        WHEN 'tinyint' THEN CONCAT('public Int32 ' , COLUMN_NAME , ' { get{ return _' , LOWER( COLUMN_NAME ) , '; } set{ _' , LOWER( COLUMN_NAME ) , ' = value; } }' )
  WHEN 'char' THEN CONCAT('public String ' , COLUMN_NAME , ' { get{ return _' , LOWER( COLUMN_NAME ) , '; } set{ _' , LOWER( COLUMN_NAME ) , ' = value; } }') 
  WHEN 'date' THEN CONCAT('public DateTime ' , COLUMN_NAME , ' { get{ return _' , LOWER( COLUMN_NAME ) , '; } set{ _' , LOWER( COLUMN_NAME ) , ' = value; } }') 
  WHEN 'decimal' THEN CONCAT('public Decimal ' , COLUMN_NAME , ' { get{ return _' , LOWER( COLUMN_NAME ) , '; } set{ _' , LOWER( COLUMN_NAME ) , ' = value; } }') 
    END , 5 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table union SELECT ' ', 6 SortOrder 

union 
#default constructor 
SELECT '//Default Constructor', 7 SortOrder 
union SELECT CONCAT('public ' , @table , '(){}'), 8 SortOrder 
union 
#Constructor using DataRow 
SELECT '//Constructor using DataRow', 9 SortOrder 
union 
SELECT CONCAT('public ' , @table , '(DataRow dr){'), 10 SortOrder 
union 
SELECT CASE DATA_TYPE
  WHEN 'int' THEN CONCAT('_' , LOWER( COLUMN_NAME ) , ' = Int32.Parse(dr["' , COLUMN_NAME , '"] + String.Empty);') 
        WHEN 'tinyint' THEN CONCAT('_' , LOWER( COLUMN_NAME ) , ' = Int32.Parse(dr["' , COLUMN_NAME , '"] + String.Empty);') 
  WHEN 'char' THEN CONCAT('_' , LOWER( COLUMN_NAME ) , ' = dr["' , COLUMN_NAME , '"] + String.Empty;') 
  WHEN 'date' THEN CONCAT('_' , LOWER( COLUMN_NAME ) , ' = DateTime.Parse(dr["' , COLUMN_NAME , '"] + String.Empty);') 
  WHEN 'decimal' THEN CONCAT('_' , LOWER( COLUMN_NAME ) , ' = Decimal.Parse(dr["' , COLUMN_NAME , '"] + String.Empty);')
    END, 11 SortOrder 
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table 
union 
SELECT '}', 12 SortOrder 
#close class 
union 
SELECT '}', 13 SortOrder 
ORDER BY SortOrder 

 

 

Notes:

 

  • Replace table_name with your actual table name you want to create.
  • This version only makes the CSharp class not the CRUD functions. But with the helper database class it makes it easier to make your functions.

 

With the provided code above I have saved a lot of time of create class structure and can focus on the user interface more. I have finished projects in probably half the time it would have taken me. Also using this allows for consistency across my coding projects.

 

Hopes this helps someone.