/Skywave Class Library/Skywave Core/Data/DbCommandHelper.cs

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;

using System.Data.Common;



namespace Skywave.Data

{

    public enum DbCommandHelperAdditionalCommands

    {

        None,

        Count,

        Sum,

        Average

    }

    public abstract class DbCommandHelper

    {

        public abstract string FixCommandString(string sqlQ);



        public virtual void FixCommand(DbCommand dbCommand1)

        {

            string sqlQ = dbCommand1.CommandText;

            sqlQ = FixCommandString(sqlQ);

            dbCommand1.CommandText = sqlQ;

        }



        public SqlCommandString GetSqlCommandString(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs)

        {

            return GetSqlCommandString(dc1, qInput1, additionalCommand, additionalCommandArgs, true);

        }

        public SqlCommandString GetSqlCommandString(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs, bool fixCommandString)

        {

            DbCommand dbCommand1 = GetDbCommand(dc1, qInput1, additionalCommand, additionalCommandArgs, fixCommandString);

            SqlCommandString r = new SqlCommandString();

            r.Params = new List<string>();

            r.CommandString = dbCommand1.CommandText;

            string paramType, paramValue;

            foreach (DbParameter fe1 in dbCommand1.Parameters)

            {

                int fe1_Size = (fe1.Size <= 0) ? 1 : fe1.Size;

                switch (fe1.DbType)

                {

                    case System.Data.DbType.AnsiString:

                        paramType = string.Format("VARCHAR({0})", fe1_Size);

                        paramValue = string.Format("'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.AnsiStringFixedLength:

                        paramType = string.Format("CHAR({0})", fe1_Size);

                        paramValue = string.Format("'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.Binary:

                        paramType = string.Format("BINARY", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Boolean:

                        paramType = string.Format("BIT", fe1_Size);

                        if (fe1.Value is bool)

                            paramValue = ((bool)fe1.Value) ? "1" : "0";

                        else

                            paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Byte:

                        paramType = string.Format("TINYINT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Time:

                    case System.Data.DbType.Date:

                    case System.Data.DbType.DateTime:

                    case System.Data.DbType.DateTime2:

                    case System.Data.DbType.DateTimeOffset:

                        paramType = "DATETIME";

                        if (fe1.Value is DateTime)

                            paramValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-ddTHH:mm:ss.fff"));

                        else

                            paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Decimal:

                        paramType = string.Format("DECIMAL", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Double:

                        paramType = string.Format("FLOAT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Int16:

                        paramType = string.Format("SMALLINT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Int32:

                        paramType = string.Format("INT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Int64:

                        paramType = string.Format("BIGINT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.SByte:

                        paramType = string.Format("TINYINT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Single:

                        paramType = string.Format("REAL", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.String:

                        paramType = string.Format("NVARCHAR({0})", fe1_Size);

                        paramValue = string.Format("N'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.StringFixedLength:

                        paramType = string.Format("NCHAR({0})", fe1_Size);

                        paramValue = string.Format("N'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.UInt16:

                        paramType = string.Format("INT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.UInt32:

                        paramType = string.Format("BIGINT", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.UInt64:

                        paramType = string.Format("NUMERIC(8,0)", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.VarNumeric:

                        paramType = string.Format("NUMERIC", fe1_Size);

                        paramValue = string.Format("{0}", fe1.Value);

                        break;

                    case System.Data.DbType.Xml:

                    case System.Data.DbType.Object:

                    case System.Data.DbType.Guid:

                    case System.Data.DbType.Currency:

                    default:

                        paramType = "";

                        paramValue = "";

                        break;

                }

                //

                if (fe1.Value == null)

                    paramValue = "NULL";

                //

                if (!string.IsNullOrEmpty(paramType))

                    r.Params.Add(string.Format("{0} {1} = {2}", fe1.ParameterName, paramType, paramValue));

            }

            return r;

        }



        public DbCommand GetDbCommand(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs)

        {

            return GetDbCommand(dc1, qInput1, additionalCommand, additionalCommandArgs, true);

        }

        public DbCommand GetDbCommand(System.Data.Linq.DataContext dc1, System.Linq.IQueryable qInput1, DbCommandHelperAdditionalCommands additionalCommand, object additionalCommandArgs, bool fixCommandString)

        {

            DbCommand dbCommand1 = dc1.GetCommand(qInput1);

            if (fixCommandString)

                FixCommand(dbCommand1);



            string str1;

            string sqlQ = dbCommand1.CommandText;

            switch (additionalCommand)

            {

                case DbCommandHelperAdditionalCommands.Count:

                    str1 = "*";

                    if (additionalCommandArgs is string && !string.IsNullOrEmpty((string)additionalCommandArgs))

                        str1 = (string)additionalCommandArgs;

                    sqlQ = string.Format("SELECT COUNT({0}) FROM ({1}) AS maT1", str1, sqlQ);

                    break;

                case DbCommandHelperAdditionalCommands.Sum:

                    str1 = "value";

                    if (additionalCommandArgs is string && !string.IsNullOrEmpty((string)additionalCommandArgs))

                        str1 = (string)additionalCommandArgs;

                    sqlQ = string.Format("SELECT SUM({0}) FROM ({1}) AS maT1", str1, sqlQ);

                    break;

                case DbCommandHelperAdditionalCommands.Average:

                    break;

                case DbCommandHelperAdditionalCommands.None:

                default:

                    break;

            }

            dbCommand1.CommandText = sqlQ;



            return dbCommand1;

        }



        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, DbCommand usedParametersContainer)

        { ChangeParametersNames(toChange, new List<DbCommand>() { usedParametersContainer }); }

        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, List<DbCommand> usedParametersContainers)

        {

            List<string> usedParameterNames = new List<string>();

            foreach (DbCommand fe1 in usedParametersContainers)

            {

                usedParameterNames = usedParameterNames.Union((from x1 in fe1.Parameters.Cast<DbParameter>()

                                                               select x1.ParameterName)).ToList();

            }

            ChangeParametersNames(toChange, usedParameterNames);

        }

        public void ChangeParametersNames(System.Data.Common.DbCommand toChange, List<string> usedParameterNames)

        {

            int i = 1;

            string oldParam;

            string newParam;

            Dictionary<string, string> replacements = new Dictionary<string, string>();

            int j = 1;

            foreach (System.Data.Common.DbParameter fe1 in toChange.Parameters)

            {

                oldParam = fe1.ParameterName;

                newParam = oldParam;

                while (usedParameterNames.Contains(newParam))

                {

                    newParam = string.Format("@p{0}", i);

                    i++;

                }

                usedParameterNames.Add(newParam);

                fe1.ParameterName = newParam;

                toChange.CommandText = toChange.CommandText.Replace(oldParam, string.Format("$maP{0}$", j));

                replacements.Add(string.Format("$maP{0}$", j), newParam);

                j++;

            }

            foreach (string fe1 in replacements.Keys)

            {

                toChange.CommandText = toChange.CommandText.Replace(fe1, replacements[fe1]);

            }

        }



        public object[] GetParametersArray(DbCommand paramContainer)

        {

            return GetParametersArray(new List<DbCommand>() { paramContainer });

        }

        public object[] GetParametersArray(List<DbCommand> paramContainers)

        {

            System.Collections.ArrayList r = new System.Collections.ArrayList();

            foreach (DbCommand fe1 in paramContainers)

            {

                r.AddRange((from x1 in fe1.Parameters.Cast<DbParameter>()

                            select x1.Value).ToList());

            }

            return r.ToArray();

        }



        public static string GetSqlEmbededWithParams(DbCommand dbc1)

        {

            string r = "";

            //SQL Parameteres

            string sqlTextParams = "";

            foreach (DbParameter fe1 in dbc1.Parameters)

            {

                int fe1_Size = (fe1.Size <= 0) ? 1 : fe1.Size;

                string strType = "";

                string strValue = "";

                switch (fe1.DbType)

                {

                    case System.Data.DbType.AnsiString:

                        strType = string.Format("varchar({0})", fe1_Size);

                        strValue = string.Format("'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.AnsiStringFixedLength:

                        strType = string.Format("char({0})", fe1_Size);

                        strValue = string.Format("'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.Binary:

                        break;

                    case System.Data.DbType.Boolean:

                        strType = "bit";

                        strValue = (((bool)fe1.Value) ? 1 : 0).ToString();

                        break;

                    case System.Data.DbType.Byte:

                        break;

                    case System.Data.DbType.Currency:

                        break;

                    case System.Data.DbType.Date:

                        strType = "date";

                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-dd"));

                        break;

                    case System.Data.DbType.DateTime:

                    case System.Data.DbType.DateTime2:

                        strType = "datetime";

                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("yyyy-MM-ddTHH:mm:ss.fff"));

                        break;

                    case System.Data.DbType.DateTimeOffset:

                        break;

                    case System.Data.DbType.Decimal:

                        break;

                    case System.Data.DbType.Double:

                        strType = "float";

                        strValue = fe1.Value.ToString();

                        break;

                    case System.Data.DbType.Guid:

                        break;

                    case System.Data.DbType.Int16:

                        break;

                    case System.Data.DbType.Int32:

                        strType = "int";

                        strValue = fe1.Value.ToString();

                        break;

                    case System.Data.DbType.Int64:

                        break;

                    case System.Data.DbType.Object:

                        break;

                    case System.Data.DbType.SByte:

                        break;

                    case System.Data.DbType.Single:

                        break;

                    case System.Data.DbType.String:

                        strType = string.Format("nvarchar({0})", fe1_Size);

                        strValue = string.Format("N'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.StringFixedLength:

                        strType = string.Format("nchar({0})", fe1_Size);

                        strValue = string.Format("N'{0}'", fe1.Value);

                        break;

                    case System.Data.DbType.Time:

                        strType = "time";

                        strValue = string.Format("'{0}'", ((DateTime)fe1.Value).ToString("HH:mm:ss.fff"));

                        break;

                    case System.Data.DbType.UInt16:

                        break;

                    case System.Data.DbType.UInt32:

                        break;

                    case System.Data.DbType.UInt64:

                        break;

                    case System.Data.DbType.VarNumeric:

                        break;

                    case System.Data.DbType.Xml:

                        break;

                    default:

                        break;

                }

                if (string.IsNullOrEmpty(strValue))

                    strValue = "NULL";

                if (!string.IsNullOrEmpty(strType))

                    sqlTextParams += string.Format(",{0} {1}={2}\n\t", fe1.ParameterName, strType, strValue);

            }

            sqlTextParams = sqlTextParams.Trim(new char[] { ',', '\t' });

            sqlTextParams = string.Format("DECLARE \n\t{0};", sqlTextParams);

            r = sqlTextParams + "\n" + dbc1.CommandText;

            return r;

        }

    }

}