SqlHelper

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using System.Text;

namespace MZ.Models.DAL
{

    //可更改為MySql

    public class SqlHelper
    {
        #region Fields

        private string connectString = ConfigurationManager.ConnectionStrings["connstr"].ToString();

        #endregion

        #region Constructors

        public SqlHelper()
        {

        }

        public SqlHelper(string connectString)
        {
            this.connectString = connectString;
        }

        #endregion

        #region Property ConnectionString

        public string ConnectionString
        {
            get { return this.connectString; }
        }

        #endregion

        #region Method ExecuteNonQuery

        public int ExecuteNonQuery(string commandText)
        {
            return this.ExecuteNonQuery(commandText, CommandType.Text, null);
        }

        public int ExecuteNonQuery(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteNonQuery(commandText, CommandType.Text, paras);
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            int result = 0;
            using (SqlConnection connection = new SqlConnection(this.connectString))
            {
                connection.Open();
                result = this.ExecuteNonQuery(commandText, commandType, paras, connection);
                connection.Close();
            }

            return result;
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            int result = command.ExecuteNonQuery();
            command.Dispose();

            return result;
        }

        public int ExecuteNonQuery(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            int result = command.ExecuteNonQuery();
            command.Dispose();

            return result;
        }

        #endregion

        #region Method ExecuteScalar

        public int ExecuteScalar(string commandText)
        {
            return this.ExecuteScalar(commandText, CommandType.Text, null);
        }

        public int ExecuteScalar(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteScalar(commandText, CommandType.Text, paras);
        }

        public int ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            int result = 0;
            using (SqlConnection connection = new SqlConnection(this.connectString))
            {
                connection.Open();
                result = ExecuteScalar(commandText, commandType, paras, connection);
                connection.Close();
            }

            return result;
        }

        public int ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null || result + "" == "")
                return 0;

            return Convert.ToInt32(result);
        }

        public int ExecuteScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null)
                return 0;

            return (int)result;
        }

        #endregion

        #region Method ExecuteObjectScalar

        public object ExecuteObjectScalar(string commandText)
        {
            return this.ExecuteObjectScalar(commandText, CommandType.Text, null);
        }

        public object ExecuteObjectScalar(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteObjectScalar(commandText, CommandType.Text, paras);
        }

        public object ExecuteObjectScalar(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            object result;
            using (SqlConnection connection = new SqlConnection(this.connectString))
            {
                connection.Open();
                result = ExecuteObjectScalar(commandText, commandType, paras, connection);
                connection.Close();
            }

            return result;
        }

        public object ExecuteObjectScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null || result + "" == "")
                return null;

            return result;
        }

        public object ExecuteObjectScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null)
                return null;

            return result;
        }

        #endregion

        #region Method ExecuteStringScalar

        public string ExecuteStringScalar(string commandText)
        {
            return this.ExecuteStringScalar(commandText, CommandType.Text, null);
        }

        public string ExecuteStringScalar(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteStringScalar(commandText, CommandType.Text, paras);
        }

        public string ExecuteStringScalar(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            string result = "";
            using (SqlConnection connection = new SqlConnection(this.connectString))
            {
                connection.Open();
                result = ExecuteStringScalar(commandText, commandType, paras, connection);
                connection.Close();
            }

            return result;
        }

        public string ExecuteStringScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null)
                return "";

            return result.ToString();
        }

        public string ExecuteStringScalar(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            object result = command.ExecuteScalar();
            command.Dispose();

            if (result == null)
                return "";

            return result.ToString();
        }

        #endregion

        #region Method ExecuteDataTable

        public DataTable ExecuteDataTable(string commandText)
        {
            return this.ExecuteDataTable(commandText, CommandType.Text, null);
        }

        public DataTable ExecuteDataTable(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteDataTable(commandText, CommandType.Text, paras);
        }

        public DataTable ExecuteDataTable(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            DataTable dt = null;
            using (SqlConnection connection = new SqlConnection(this.connectString))
            {
                connection.Open();
                dt = this.ExecuteDataTable(commandText, commandType, paras, connection);
                connection.Close();
            }

            return dt;
        }

        public DataTable ExecuteDataTable(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            DataTable dt = new DataTable();
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(dt);
            command.Dispose();
            adapter.Dispose();

            return dt;
        }

        public DataTable ExecuteDataTable(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            DataTable dt = new DataTable();
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(dt);
            command.Dispose();
            adapter.Dispose();

            return dt;
        }

        #endregion

        #region Method ExecuteDataSet

        public DataSet ExecuteDataSet(string commandText)
        {
            return this.ExecuteDataSet(commandText, CommandType.Text, null);
        }

        public DataSet ExecuteDataSet(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteDataSet(commandText, CommandType.Text, paras);
        }

        public DataSet ExecuteDataSet(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            DataSet ds = null;
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open();
                ds = this.ExecuteDataSet(commandText, commandType, paras, connection);
                connection.Close();
            }

            return ds;
        }

        public DataSet ExecuteDataSet(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            DataSet ds = new DataSet();
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            command.Dispose();
            adapter.Dispose();

            return ds;
        }

        public DataSet ExecuteDataSet(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            DataSet ds = new DataSet();
            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            SqlDataAdapter adapter = new SqlDataAdapter(command);
            adapter.Fill(ds);
            command.Dispose();
            adapter.Dispose();

            return ds;
        }

        #endregion

        #region Method ExecuteEntity
        private static T ExecuteDataReader<T>(SqlDataReader dr)
        {
            T obj = default(T);
            Type type = typeof(T);
            PropertyInfo[] propertyInfos = type.GetProperties();
            int columnCount = dr.FieldCount;
            obj = Activator.CreateInstance<T>();
            foreach (PropertyInfo propertyInfo in propertyInfos)
            {
                string propertyName = propertyInfo.Name;
                for (int i = 0; i < columnCount; i++)
                {
                    string columnName = dr.GetName(i);
                    if (string.Compare(propertyName, columnName, true) == 0)
                    {
                        object value = dr.GetValue(i);
                        if (value != null && value != DBNull.Value)
                        {
                            propertyInfo.SetValue(obj, value, null);
                        }
                        break;
                    }
                }
            }
            return obj;
        }

        public T ExecuteEntity<T>(string commandText)
        {
            return this.ExecuteEntity<T>(commandText, CommandType.Text, null);
        }

        public T ExecuteEntity<T>(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteEntity<T>(commandText, CommandType.Text, paras);
        }

        public T ExecuteEntity<T>(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            T obj = default(T);
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open();
                obj = this.ExecuteEntity<T>(commandText, commandType, paras, connection);
                connection.Close();
            }

            return obj;
        }

        public T ExecuteEntity<T>(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            T obj = default(T);
            using (SqlCommand cmd = new SqlCommand(commandText, connection))
            {
                cmd.CommandType = commandType;
                if(paras!=null)
                cmd.Parameters.AddRange(paras);
                connection.Close();
                connection.Open();
                using (SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (dr.Read())
                    {
                        obj = ExecuteDataReader<T>(dr);
                        break;
                    }
                }
            }
            return obj;
        }
        #endregion

        #region Method ExecuteList
        public List<T> ExecuteList<T>(string commandText)
        {
            return this.ExecuteList<T>(commandText, CommandType.Text, null);
        }

        public List<T> ExecuteList<T>(string commandText, SqlParameter[] paras)
        {
            return this.ExecuteList<T>(commandText, CommandType.Text, paras);
        }

        public List<T> ExecuteList<T>(string commandText, CommandType commandType, SqlParameter[] paras)
        {
            List<T> list = new List<T>();
            using (SqlConnection connection = new SqlConnection(connectString))
            {
                connection.Open();
                list = this.ExecuteList<T>(commandText, commandType, paras, connection);
                connection.Close();
            }

            return list;

        }

        public List<T> ExecuteList<T>(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            List<T> list = new List<T>();

            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, connection);
            using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (dr.Read())
                {
                    T obj = ExecuteDataReader<T>(dr);
                    list.Add(obj);
                }
            }
            command.Dispose();

            return list;
        }

        public List<T> ExecuteList<T>(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            List<T> list = new List<T>();

            SqlCommand command = this.CreateCommandHelper(commandText, commandType, paras, trans);
            using (SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection))
            {
                while (dr.Read())
                {
                    T obj = ExecuteDataReader<T>(dr);
                    list.Add(obj);
                }
            }
            command.Dispose();

            return list;
        }
        #endregion

        #region Method InsertBatch

        public int InsertBatch(string commandText, DataTable data, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = this.CreateCommandHelper(commandText, CommandType.Text, paras, trans);
            SqlDataAdapter adapter = new SqlDataAdapter();
            adapter.InsertCommand = command;
            int result = adapter.Update(data);

            adapter.Dispose();
            command.Dispose();

            return result;
        }

        #endregion

        #region Private Method CreateCommandHelper

        private SqlCommand CreateCommandHelper(string commandText, CommandType commandType, SqlParameter[] paras, SqlConnection connection)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Connection = connection;

            if (paras != null && paras.Length > 0)
            {
                foreach (SqlParameter p in paras)
                {
                    /*Update 修改無法使用 ParameterDirection.Output 來輸出值的Bug*/
                    //SqlParameter paraNew = new SqlParameter();
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }
                        /*
                        paraNew.ParameterName = p.ParameterName;
                        paraNew.SqlDbType = p.SqlDbType;
                        paraNew.DbType = p.DbType;
                        paraNew.SourceColumn = p.SourceColumn;
                        paraNew.Value = p.Value;
                         */

                        command.Parameters.Add(p);
                    }

                }
            }

            return command;
        }

        private SqlCommand CreateCommandHelper(string commandText, CommandType commandType, SqlParameter[] paras, SqlTransaction trans)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = commandText;
            command.CommandType = commandType;
            command.Connection = trans.Connection;
            command.Transaction = trans;

            if (paras != null && paras.Length > 0)
            {
                foreach (SqlParameter p in paras)
                {
                    SqlParameter paraNew = new SqlParameter();
                    if (p != null)
                    {
                        // Check for derived output value with no value assigned
                        if ((p.Direction == ParameterDirection.InputOutput ||
                            p.Direction == ParameterDirection.Input) &&
                            (p.Value == null))
                        {
                            p.Value = DBNull.Value;
                        }

                        paraNew.ParameterName = p.ParameterName;
                        paraNew.SqlDbType = p.SqlDbType;
                        paraNew.DbType = p.DbType;
                        paraNew.SourceColumn = p.SourceColumn;
                        paraNew.Value = p.Value;
                    }
                    command.Parameters.Add(paraNew);
                }
            }

            return command;
        }

        #endregion
    }
}