sql_generator.cs

数据库操作的小工具

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;
using SP_Gen.Classes;
using DatabaseHelper.Properties;

namespace DatabaseHelper
{
    class SQL_Generator
    {
        #region "Stored Procedure Generation Methods"

        public static string CreateDeleteRowSP(string spName, string TableName, DataRow[] Columns)
        {
            string SQL = string.Empty;

            SQL = String.Format(Resources.DropProcedure, spName);
            SQL += "\r\n\r\n";

            SQL += "-- ==========================================================================================";
            SQL += "\r\n-- Entity Name:\t" + spName;
            string AuthorName = Session.LoadFromSession("AuthorName").ToString();
            if (AuthorName != string.Empty)
            {
                SQL += "\r\n-- Author:\t" + AuthorName;
            }
            SQL += "\r\n-- Create date:\t" + DateTime.Now;
            SQL += "\r\n-- Description:\tThis stored procedure is intended for deleting a specific row from " +
                   TableName + " table";
            SQL +=
                "\r\n-- ==========================================================================================\r\n";

            #region "Header Definition"

            SQL += "Create Procedure " + spName + "\r\n";

            #endregion

            #region "Parameter Definition"

            bool firstParam = true;

            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIdentity"].ToString()) != 0 || int.Parse(row["IsIndex"].ToString()) != 0)
                {
                    if (firstParam == true)
                    {
                        firstParam = false;
                        SQL += "\t";
                    }
                    else
                    {
                        SQL += ",\r\n\t";
                    }

                    SQL += "@" + row["COLUMN_NAME"].ToString() + " ";

                    if (row["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    {
                        string Length = (row["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("-1")
                                             ? "MAX"
                                             : row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        SQL += row["DATA_TYPE"].ToString() + "(" + Length + ")";
                    }
                    else if (row["DATA_TYPE"].ToString().ToLower().Contains("numeric"))
                    {
                        SQL += string.Format("numeric({0:G},{1:G})",
                            row["NUMERIC_PRECISION"].ToString(),
                            row["NUMERIC_SCALE"].ToString());
                    }
                    else
                    {
                        SQL += row["DATA_TYPE"].ToString();
                    }
                }
            }

            #endregion

            #region "Delete Command / Header Definition"

            SQL += "\r\nAs\r\nBegin\r\n";
            SQL += "\tDelete " + TableName + "\r\n";

            #endregion

            #region "Primary Key Column Detection"

            string pkColumn = string.Empty;

            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIndex"].ToString()) != 0)
                {
                    pkColumn = row["COLUMN_NAME"].ToString();
                    break;
                }
            }

            #endregion

            #region "Delete Command / Where Clause Definition"

            firstParam = true;

            SQL += "\tWhere\r\n";

            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIdentity"].ToString()) != 0 || int.Parse(row["IsIndex"].ToString()) != 0)
                {
                    if (firstParam == true)
                    {
                        firstParam = false;
                        SQL += "\t\t";
                    }
                    else
                    {
                        SQL += "\r\n\t\tand ";
                    }
                    SQL += QualifyFieldName(row["COLUMN_NAME"].ToString()) + " = " + "@" + row["COLUMN_NAME"].ToString();
                }
            }
            SQL += "\r\n";

            #endregion

            SQL += "\r\nEnd\r\n\r\nGO\r\n";

            return SQL;
        }

        public static string CreateInsertSP(string spName, string TableName, DataRow[] Columns)
        {
            DataRow PkInfo = null;
            string tempTableVariable = "@t";
            string SQL = string.Empty;

            SQL = String.Format(Resources.DropProcedure, spName);
            SQL += "\r\n\r\n";

            SQL += "-- ==========================================================================================";
            SQL += "\r\n-- Entity Name:\t" + spName;
            string AuthorName = Session.LoadFromSession("AuthorName").ToString();
            if (AuthorName != string.Empty)
            {
                SQL += "\r\n-- Author:\t" + AuthorName;
            }
            SQL += "\r\n-- Create date:\t" + DateTime.Now.ToString();
            SQL += "\r\n-- Description:\tThis stored procedure is intended for inserting values to " + TableName +
                   " table";
            SQL +=
                "\r\n-- ==========================================================================================\r\n";

            #region "Header Definition"

            SQL += "Create Procedure " + spName + "\r\n";

            #endregion

            #region "Parameter Definition"

            bool firstParam = true;

            foreach (DataRow row in Columns)
            {
                if(int.Parse(row["IsIndex"].ToString()) == 1)
                    PkInfo = row;
                if (int.Parse(row["IsIdentity"].ToString()) == 0)
                {
                    if (firstParam == true)
                    {
                        firstParam = false;
                        SQL += "\t";
                    }
                    else
                    {
                        SQL += ",\r\n\t";
                    }

                    SQL += "@" + row["COLUMN_NAME"] + " ";

                    if (row["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    {
                        string Length = (row["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("-1")
                                             ? "MAX"
                                             : row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        SQL += row["DATA_TYPE"].ToString() + "(" + Length + ")";
                    }
                    else if (row["DATA_TYPE"].ToString().ToLower().Contains("numeric"))
                    {
                        SQL += string.Format("numeric({0:G},{1:G})",
                            row["NUMERIC_PRECISION"].ToString(),
                            row["NUMERIC_SCALE"].ToString());
                    }
                    else
                    {
                        SQL += row["DATA_TYPE"].ToString();
                    }
                    bool NullParamDefaultValues = bool.Parse(Session.LoadFromSession("NullParamDefaultValues").ToString());
                    if (row["IS_NULLABLE"].ToString().ToLower() == "yes" && NullParamDefaultValues == true)
                    {
                        SQL += " = NULL";
                    }
                    if (row["DATA_TYPE"].ToString() == "uniqueidentifier")
                        SQL += " OUTPUT";
                }
                else
                {
                    SQL += "\t";
                    SQL += "@" + row["COLUMN_NAME"] + " ";                    

                    if (row["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    {
                        string Length = (row["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("-1")
                                             ? "MAX"
                                             : row["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        SQL += row["DATA_TYPE"].ToString() + "(" + Length + ")";
                    }
                    else if (row["DATA_TYPE"].ToString().ToLower().Contains("numeric"))
                    {
                        SQL += string.Format("numeric({0:G},{1:G})",
                            row["NUMERIC_PRECISION"].ToString(),
                            row["NUMERIC_SCALE"].ToString());
                    }
                    else
                    {
                        SQL += row["DATA_TYPE"].ToString();
                    }
                    SQL += " OUTPUT";
                    SQL += ",\r\n";
                }               
            }
            #endregion

            #region "Insert Command / Header Definition"

            SQL += "\r\nAs\r\nBegin\r\n";
            if (PkInfo != null)
            {
                if (PkInfo["DATA_TYPE"].ToString() != "uniqueidentifier" && !PkInfo["DATA_TYPE"].ToString().ToLower().Contains("char"))
                {
                    SQL += "\tDeclare " + tempTableVariable + " Table (" + PkInfo["COLUMN_NAME"] + " ";
                    if (PkInfo["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    {
                        string Length = (PkInfo["CHARACTER_MAXIMUM_LENGTH"].ToString().Equals("-1")
                                             ? "MAX"
                                             : PkInfo["CHARACTER_MAXIMUM_LENGTH"].ToString());
                        SQL += PkInfo["DATA_TYPE"].ToString() + "(" + Length + ")";
                        SQL += ")";
                    }
                    else if (PkInfo["DATA_TYPE"].ToString().ToLower().Contains("numeric"))
                    {
                        SQL += string.Format("numeric({0:G},{1:G})",
                            PkInfo["NUMERIC_PRECISION"].ToString(),
                            PkInfo["NUMERIC_SCALE"].ToString());
                    }
                    else
                    {
                        if (PkInfo["DATA_TYPE"].ToString() != "uniqueidentifier")
                            SQL += PkInfo["DATA_TYPE"].ToString() + ")";
                    }
                }
                if (PkInfo["DATA_TYPE"].ToString() == "uniqueidentifier")
                    SQL += string.Format("\n\tIf @{0} is Null Set @{1} = newID()", PkInfo["COLUMN_NAME"], PkInfo["COLUMN_NAME"]);
                SQL += "\n\tInsert Into " + TableName + "\r\n\t\t(";
            }

            #endregion

            #region "Insert Command / Target Columns Definition"

            firstParam = true;
            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIdentity"].ToString()) == 0)
                {
                    if (firstParam == true)
                    {
                        firstParam = false;
                    }
                    else
                    {
                        SQL += ",";
                    }

                    SQL += QualifyFieldName(row["COLUMN_NAME"].ToString());
                }
            }
            SQL += ")";
            if (PkInfo != null)
            {
                if (PkInfo["DATA_TYPE"].ToString() != "uniqueidentifier" && !PkInfo["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    SQL += "\n\tOutput Inserted." + PkInfo["COLUMN_NAME"] + " Into " + tempTableVariable + "(" + PkInfo["COLUMN_NAME"] + ")";
            }
            SQL += "\r\n\tValues\r\n\t\t(";

            #endregion

            #region "Insert Command / Supplying Values Definition"

            firstParam = true;

            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIdentity"].ToString()) == 0)
                {
                    if (firstParam == true)
                    {
                        firstParam = false;
                    }
                    else
                    {
                        SQL += ",";
                    }
                    SQL += "@" + row["COLUMN_NAME"].ToString();
                }
            }
            SQL += ")\r\n";

            #endregion

            #region "Return Identity , if any identity columns found"


            if (PkInfo != null)
            {
                if (PkInfo["DATA_TYPE"].ToString() != "uniqueidentifier" && !PkInfo["DATA_TYPE"].ToString().ToLower().Contains("char"))
                    SQL += "\r\n\tSelect @" + PkInfo["COLUMN_NAME"] + " = (Select " + PkInfo["COLUMN_NAME"] + " From " + tempTableVariable + ")";
            }            

            #endregion

            #region "Primary Key Column Detection"

            string pkColumn = string.Empty;

            foreach (DataRow row in Columns)
            {
                if (int.Parse(row["IsIndex"].ToString()) != 0)
                {
                    pkColumn = row["COLUMN_NAME"].ToString();
                    break;
                }
            }

            #endregion

            //if (identityExists == true)
            //{
            //    SQL += "\r\n\tReturn @ReferenceID\r\n";
            //}

            SQL += "\r\nEnd\r\n\r\nGO\r\n";

            return SQL;
        }

        public static string CreateSelectAllSP(string spName, string TableName, DataRow[] Columns)
        {
            string SQL = string.Empty;