DBUtility.SqlHelper.cs

using Model;
    using System;
    using System.Collections;
    using System.Collections.Generic;
    using System.Configuration;
    using System.Data;
    using System.Data.SqlClient;
    using System.Runtime.InteropServices;
namespace DBUtility
{
    public abstract class SqlHelper
    {
        protected SqlHelper()
        {
        }

        private static SqlCommand BuildIntCommand(SqlConnection

connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = BuildQueryCommand(connection,

storedProcName, parameters);
            command.Parameters.Add(new SqlParameter("ReturnValue",

SqlDbType.Int, 4, ParameterDirection.ReturnValue, false, 0, 0,

string.Empty, DataRowVersion.Default, null));
            return command;
        }

        private static SqlCommand BuildQueryCommand(SqlConnection

connection, string storedProcName, IDataParameter[] parameters)
        {
            SqlCommand command = new SqlCommand(storedProcName,

connection);
            command.CommandType = CommandType.StoredProcedure;
            if (parameters != null)
            {
                foreach (SqlParameter parameter in parameters)
                {
                    if ((parameter.SqlDbType ==

SqlDbType.DateTime) && (((DateTime)parameter.Value) ==

DateTime.MinValue))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    command.Parameters.Add(parameter);
                }
            }
            return command;
        }

        public static int ExecuteCountSql(string connectionString,

string SQLString)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                using (SqlCommand command = new SqlCommand

(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        SqlDataReader reader =

command.ExecuteReader();
                        reader.Read();
                        return int.Parse(reader[0].ToString());
                    }
                    catch (SqlException exception)
                    {
                        connection.Close();
                        throw new Exception(exception.Message);
                    }
                }
            }
            return num2;
        }

        public static SqlDataReader ExecuteReader(string

connectionString, string strSQL)
        {
            SqlDataReader reader2;
            SqlConnection connection = new SqlConnection

(connectionString);
            SqlCommand command = new SqlCommand(strSQL,

connection);
            try
            {
                connection.Open();
                reader2 = command.ExecuteReader();
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        public static SqlDataReader ExecuteReader(string

connectionString, string SQLString, params SqlParameter[]

cmdParms)
        {
            SqlDataReader reader2;
            SqlConnection conn = new SqlConnection

(connectionString);
            SqlCommand cmd = new SqlCommand();
            try
            {
                PrepareCommand(cmd, conn, null, SQLString,

cmdParms);
                SqlDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                reader2 = reader;
            }
            catch (SqlException exception)
            {
                throw new Exception(exception.Message);
            }
            return reader2;
        }

        public static int ExecuteSql(string connectionString,

string SQLString)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                using (SqlCommand command = new SqlCommand

(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        return command.ExecuteNonQuery();
                    }
                    catch (SqlException exception)
                    {
                        connection.Close();
                        throw new Exception(exception.Message);
                    }
                }
            }
            return num2;
        }

        public static int ExecuteSql(string connectionString,

string SQLString, params SqlParameter[] cmdParms)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(command, connection, null,

SQLString, cmdParms);
                        int num = command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        return num;
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                }
            }
            return num2;
        }

        public static int ExecuteSql(string connectionString,

string SQLString, string content)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                SqlCommand command = new SqlCommand(SQLString,

connection);
                SqlParameter parameter = new SqlParameter

("@content", SqlDbType.NText);
                parameter.Value = content;
                command.Parameters.Add(parameter);
                try
                {
                    connection.Open();
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                    connection.Close();
                }
            }
            return num2;
        }

        public static int ExecuteSqlInsertImg(string

connectionString, string strSQL, byte[] fs)
        {
            int num2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                SqlCommand command = new SqlCommand(strSQL,

connection);
                SqlParameter parameter = new SqlParameter("@fs",

SqlDbType.Image);
                parameter.Value = fs;
                command.Parameters.Add(parameter);
                try
                {
                    connection.Open();
                    num2 = command.ExecuteNonQuery();
                }
                catch (SqlException exception)
                {
                    throw new Exception(exception.Message);
                }
                finally
                {
                    command.Dispose();
                    connection.Close();
                }
            }
            return num2;
        }

        public static void ExecuteSqlTran(string connectionString,

List<string> SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                connection.Open();
                SqlCommand command = new SqlCommand();
                command.Connection = connection;
                SqlTransaction transaction =

connection.BeginTransaction();
                command.Transaction = transaction;
                try
                {
                    for (int i = 0; i < SQLStringList.Count; i++)
                    {
                        string str = SQLStringList[i].ToString();
                        if (str.Trim().Length > 1)
                        {
                            command.CommandText = str;
                            command.ExecuteNonQuery();
                        }
                    }
                    transaction.Commit();
                }
                catch (SqlException exception)
                {
                    transaction.Rollback();
                    throw new Exception(exception.Message);
                }
            }
        }

        public static void ExecuteSqlTran(string connectionString,

Hashtable SQLStringList)
        {
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                connection.Open();
                using (SqlTransaction transaction =

connection.BeginTransaction())
                {
                    SqlCommand cmd = new SqlCommand();
                    try
                    {
                        foreach (DictionaryEntry entry in

SQLStringList)
                        {
                            string cmdText = entry.Key.ToString();
                            SqlParameter[] cmdParms =

(SqlParameter[])entry.Value;
                            PrepareCommand(cmd, connection,

transaction, cmdText, cmdParms);
                            int num = cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                        transaction.Commit();
                    }
                    catch
                    {
                        transaction.Rollback();
                        throw;
                    }
                }
            }
        }

        public static bool Exists(string connectionString, string

strSql, params SqlParameter[] cmdParms)
        {
            return (int.Parse(Query(connectionString, strSql,

cmdParms).Tables[0].Rows[0][0].ToString()) > 0);
        }

        public static int GetMaxID(string connectionString, string

FieldName, string TableName)
        {
            string sQLString = "select max(" + FieldName + ") from

" + TableName;
            DataSet set = Query(connectionString, sQLString);
            if (set.Tables[0].Rows[0][0] != DBNull.Value)
            {
                return int.Parse(set.Tables[0].Rows[0]

[0].ToString());
            }
            return 0;
        }

        public static object GetSingle(string connectionString,

string SQLString)
        {
            object obj3;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                using (SqlCommand command = new SqlCommand

(SQLString, connection))
                {
                    try
                    {
                        connection.Open();
                        object objA = command.ExecuteScalar();
                        if (object.Equals(objA, null) ||

object.Equals(objA, DBNull.Value))
                        {
                            return null;
                        }
                        return objA;
                    }
                    catch (SqlException exception)
                    {
                        connection.Close();
                        throw new Exception(exception.Message);
                    }
                }
            }
            return obj3;
        }

        public static object GetSingle(string connectionString,

string SQLString, params SqlParameter[] cmdParms)
        {
            object obj3;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                using (SqlCommand command = new SqlCommand())
                {
                    try
                    {
                        PrepareCommand(command, connection, null,

SQLString, cmdParms);
                        object objA = command.ExecuteScalar();
                        command.Parameters.Clear();
                        if (object.Equals(objA, null) ||

object.Equals(objA, DBNull.Value))
                        {
                            return null;
                        }
                        return objA;
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                }
            }
            return obj3;
        }

        public static bool IsCharClassType(Field field)
        {
            return ((((field.FieldType == DataType.charType) ||

(field.FieldType == DataType.ncharType)) || (field.FieldType ==

DataType.nvarcharType)) || (field.FieldType ==

DataType.varcharType));
        }

        public static bool IsDataTimeClassType(Field field)
        {
            return ((field.FieldType == DataType.datetimeType) ||

(field.FieldType == DataType.smalldatetimeType));
        }

        public static bool IsIntClassType(Field field)
        {
            return ((((field.FieldType == DataType.bigintType) ||

(field.FieldType == DataType.intType)) || (field.FieldType ==

DataType.smallintType)) || (field.FieldType ==

DataType.tinyintType));
        }

        public static bool IsStringClassType(Field field)
        {
            return (((((field.FieldType == DataType.charType) ||

(field.FieldType == DataType.ncharType)) || ((field.FieldType ==

DataType.ntextType) || (field.FieldType ==

DataType.nvarcharType))) || (field.FieldType ==

DataType.textType)) || (field.FieldType == DataType.varcharType));
        }

        public static SqlParameter MakeInParam(string ParamName,

SqlDbType DbType, object Value)
        {
            return MakeParam(ParamName, DbType, 0,

ParameterDirection.Input, Value);
        }

        public static SqlParameter MakeInParam(string ParamName,

SqlDbType DbType, int Size, object Value)
        {
            return MakeParam(ParamName, DbType, Size,

ParameterDirection.Input, Value);
        }

        public static SqlParameter MakeOutParam(string ParamName,

SqlDbType DbType, int Size)
        {
            return MakeParam(ParamName, DbType, Size,

ParameterDirection.Output, null);
        }

        public static SqlParameter MakeParam(string ParamName,

SqlDbType DbType, int Size, ParameterDirection Direction, object

Value)
        {
            SqlParameter parameter;
            if (Size > 0)
            {
                parameter = new SqlParameter(ParamName, DbType,

Size);
            }
            else
            {
                parameter = new SqlParameter(ParamName, DbType);
            }
            parameter.Direction = Direction;
            if ((Direction != ParameterDirection.Output) || (Value

!= null))
            {
                parameter.Value = Value;
            }
            return parameter;
        }

        public static DataSet PageList(string connectionString,

string tblName, string fldName, int pageSize, int pageIndex,

string fldSort, bool Sort, string strCondition, string ID, bool

Dist, out int pageCount, out int Counts, out string strSql)
        {
            SqlParameter[] parameters = new SqlParameter[] { new

SqlParameter("@tblName", SqlDbType.NVarChar, 200), new

SqlParameter("@fldName", SqlDbType.NVarChar, 500), new

SqlParameter("@pageSize", SqlDbType.Int), new SqlParameter

("@page", SqlDbType.Int), new SqlParameter("@fldSort",

SqlDbType.NVarChar, 200), new SqlParameter("@Sort",

SqlDbType.Bit), new SqlParameter("@strCondition",

SqlDbType.NVarChar, 0x3e8), new SqlParameter("@ID",

SqlDbType.NVarChar, 150), new SqlParameter("@Dist",

SqlDbType.Bit), new SqlParameter("@pageCount", SqlDbType.Int), new

SqlParameter("@Counts", SqlDbType.Int), new SqlParameter

("@strSql", SqlDbType.NVarChar, 0x3e8) };
            parameters[0].Value = tblName;
            parameters[1].Value = (fldName == null) ? "*" :

fldName;
            parameters[2].Value = (pageSize == 0) ? int.Parse

(ConfigurationManager.AppSettings["PageSize"]) : pageSize;
            parameters[3].Value = pageIndex;
            parameters[4].Value = fldSort;
            parameters[5].Value = Sort;
            parameters[6].Value = (strCondition == null) ? "" :

strCondition;
            parameters[7].Value = ID;
            parameters[8].Value = Dist;
            parameters[9].Direction = ParameterDirection.Output;
            parameters[10].Direction = ParameterDirection.Output;
            parameters[11].Direction = ParameterDirection.Output;
            DataSet set = RunProcedure(connectionString,

"PageList", parameters, "ds");
            pageCount = (int)parameters[9].Value;
            Counts = (int)parameters[10].Value;
            strSql = parameters[11].Value.ToString();
            return set;
        }

        private static void PrepareCommand(SqlCommand cmd,

SqlConnection conn, SqlTransaction trans, string cmdText,

SqlParameter[] cmdParms)
        {
            if (conn.State != ConnectionState.Open)
            {
                conn.Open();
            }
            cmd.Connection = conn;
            cmd.CommandText = cmdText;
            if (trans != null)
            {
                cmd.Transaction = trans;
            }
            cmd.CommandType = CommandType.Text;
            if (cmdParms != null)
            {
                foreach (SqlParameter parameter in cmdParms)
                {
                    if ((parameter.SqlDbType ==

SqlDbType.DateTime) && (((DateTime)parameter.Value) ==

DateTime.MinValue))
                    {
                        parameter.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(parameter);
                }
            }
        }

        public static DataSet Query(string connectionString,

string SQLString)
        {
            if ((SQLString != null) && (SQLString.Trim() != ""))
            {
                using (SqlConnection connection = new

SqlConnection(connectionString))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        connection.Open();
                        new SqlDataAdapter(SQLString,

connection).Fill(dataSet, "ds");
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    return dataSet;
                }
            }
            return null;
        }

        public static DataSet Query(string connectionString,

string SQLString, params SqlParameter[] cmdParms)
        {
            DataSet set2;
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                SqlCommand cmd = new SqlCommand();
                PrepareCommand(cmd, connection, null, SQLString,

cmdParms);
                using (SqlDataAdapter adapter = new

SqlDataAdapter(cmd))
                {
                    DataSet dataSet = new DataSet();
                    try
                    {
                        adapter.Fill(dataSet, "ds");
                        cmd.Parameters.Clear();
                    }
                    catch (SqlException exception)
                    {
                        throw new Exception(exception.Message);
                    }
                    set2 = dataSet;
                }
            }
            return set2;
        }

        public static SqlDataReader RunProcedure(string

connectionString, string storedProcName, IDataParameter[]

parameters)
        {
            SqlConnection connection = new SqlConnection

(connectionString);
            connection.Open();
            SqlCommand command = BuildQueryCommand(connection,

storedProcName, parameters);
            command.CommandType = CommandType.StoredProcedure;
            return command.ExecuteReader();
        }

        public static DataSet RunProcedure(string

connectionString, string storedProcName, IDataParameter[]

parameters, string tableName)
        {
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                DataSet dataSet = new DataSet();
                connection.Open();
                SqlDataAdapter adapter = new SqlDataAdapter();
                adapter.SelectCommand = BuildQueryCommand

(connection, storedProcName, parameters);
                adapter.Fill(dataSet, tableName);
                connection.Close();
                return dataSet;
            }
        }

        public static int RunProcedure(string connectionString,

string storedProcName, IDataParameter[] parameters, out int

rowsAffected)
        {
            using (SqlConnection connection = new SqlConnection

(connectionString))
            {
                connection.Open();
                SqlCommand command = BuildIntCommand(connection,

storedProcName, parameters);
                rowsAffected = command.ExecuteNonQuery();
                int num = (int)command.Parameters

["ReturnValue"].Value;
                connection.Close();
                return num;
            }
        }
    }
}