MESProductionWeb/AntORM/Ant.ORM/Core/SqlDataAccess.cs

using Ant.Common;
using Ant.Core;
using Ant.Data;
using Ant.Exceptions;
using Ant.ORM;
using Ant.Utility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace Ant.Core
{

    public class SqlDataAccess : IDisposable
    {
        public IDbConnection _dbConnection;
        IDbTransaction _dbTransaction;
        IDbCommand _dbCommand;
        bool _isInTransaction;
        bool _disposed = false;

        public SqlDataAccess(IDbConnection conn)
        {
            this._dbConnection = conn;
        }

        /// <summary>
        /// 连接数据库字符串
        /// </summary>
        public string ConnectionString
        {
            get { return _dbConnection.ConnectionString; }
        }

        IDbCommand DbCommand
        {
            get
            {
                this.CheckDisposed();
                if (this._dbCommand == null)
                    this._dbCommand = this._dbConnection.CreateCommand();
                return this._dbCommand;
            }
        }
        /// <summary>
        /// 是否启动事务
        /// </summary>
        public bool IsInTransaction
        {
            get { return this._isInTransaction; }
        }

        /// <summary>
        /// 打开数据库连接对象
        /// </summary>
        void Open()
        {
            this.CheckDisposed();

            if (this._dbConnection.State == ConnectionState.Broken)
            {
                this._dbConnection.Close();
            }

            if (this._dbConnection.State == ConnectionState.Closed)
            {
                this._dbConnection.Open();
            }
        }
        /// <summary>
        /// 表示一次查询完成。在事务中的话不关闭连接，交给 CommitTransaction() 或者 RollbackTransaction() 控制，否则调用 IDbConnection.Close() 关闭连接
        /// </summary>
        public void Close()
        {
            //在事务中的话不关闭连接  交给CommitTransaction()或者RollbackTransaction()
            if (!this._isInTransaction)
            {
                if (this._dbConnection.State == ConnectionState.Open)
                {
                    this._dbConnection.Close();
                }
            }
        }
        /// <summary>
        /// 开启事务
        /// </summary>
        public void BeginTransaction()
        {
            this.Open();
            this._dbTransaction = _dbConnection.BeginTransaction();
            this._isInTransaction = true;
        }
        public void BeginTransaction(IsolationLevel il)
        {
            this.Open();
            this._dbTransaction = this._dbConnection.BeginTransaction(il);
            this._isInTransaction = true;
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            if (!this._isInTransaction)
            {
                throw new AntORMException("当前会话不能打开一个事务");
                //throw new AntORMException("Current session does not open a transaction.");
            }
            this._dbTransaction.Commit();
            this._dbTransaction.Dispose();
            this._isInTransaction = false;
            this.Close();
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public void RollbackTransaction()
        {
            if (!this._isInTransaction)
            {
                throw new AntORMException("当前会话不能打开一个事务");
                //throw new AntORMException("Current session does not open a transaction.");
            }
            this._dbTransaction.Rollback();
            this._dbTransaction.Dispose();
            this._isInTransaction = false;
            this.Close();
        }

        public IDataReader ExecuteReader(string cmdText, DbParam[] parameters)
        {
            return this.ExecuteReader(cmdText, parameters, CommandBehavior.Default, CommandType.Text);
        }
        public IDataReader ExecuteDataReader(string commandText, QueryParameterCollection Parameters)
        {
            return this.ExecuteDataReader(commandText, CommandType.Text, CommandBehavior.Default, Parameters);
        }
        public IDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            return this.ExecuteReader(cmdText, parameters, CommandBehavior.Default, cmdType);
        }
        public IDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandBehavior behavior)
        {
            return this.ExecuteReader(cmdText, parameters, behavior, CommandType.Text);
        }
        public DataTable ExecuteDataTable(string commandText, QueryParameterCollection Parameters)
        {
            return ExecuteDataTable(commandText, CommandType.Text, Parameters);
        }

        public int ExecuteNonQuery(string commandText, QueryParameterCollection Parameters)
        {
            return ExecuteNonQuery(commandText, CommandType.Text, Parameters);
        }

        /// <summary>
        /// 执行SQL语句返回DataTable
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public DataTable ExecuteDataTable(string commandText, CommandType commandType, QueryParameterCollection Parameters)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlDataAdapter sda = new SqlDataAdapter();
                Open();
                IDbCommand cmd = this.DbCommand;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (Parameters != null)
                {
                    cmd = ToSqlParameter(cmd, Parameters);
                }
                IDataReader reader = cmd.ExecuteReader(CommandBehavior.Default);
                dt = reader.GetSchemaTable();
                cmd.Parameters.Clear();
                reader.Close();
                return dt;
            }
            finally
            {
                Close();
            }

        }


        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(string cmdText, CommandType commandType, QueryParameterCollection parameters)
        {

            try
            {

                IDbCommand cmd = this.DbCommand;
                cmd.CommandText = cmdText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;

                Open();
                if (parameters != null)
                {
                    cmd = ToSqlParameter(cmd, parameters);
                }
                int tmpValue = cmd.ExecuteNonQuery();
                //Parameters.Clear();
                //foreach (SqlParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return tmpValue;
            }
            finally
            {
                Close();
            }
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public object ExecuteScalar(string cmdText, QueryParameterCollection Parameters)
        {
            return null;
        }

        public IDataReader ExecuteDataReader(string commandText, CommandType commandType, CommandBehavior behavior, QueryParameterCollection Parameters)
        {

            try
            {
                Open();
                IDbCommand cmd = this.DbCommand;
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;

                if (Parameters != null)
                {
                    cmd = ToSqlParameter(cmd, Parameters);
                }
                IDataReader reader = cmd.ExecuteReader(behavior);
                cmd.Parameters.Clear();

                //foreach (SqlParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return reader;
            }
            finally
            {
                //Close();
            }
        }

        /// <summary>
        /// 参数类转换
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private IDbCommand ToSqlParameter(IDbCommand cmd, QueryParameterCollection Parameters)
        {
            foreach (QueryParameter p in Parameters)
            {
                var sqlParameter = cmd.CreateParameter();
                sqlParameter.DbType = p.DbType;
                sqlParameter.Direction = p.Direction;
                //sqlParameter.IsNullable = p.IsNullable;
                sqlParameter.ParameterName = p.ParameterName;
                sqlParameter.Precision = p.Precision;
                sqlParameter.Scale = p.Scale;
                sqlParameter.Size = p.Size;
                sqlParameter.SourceColumn = p.SourceColumn;
                sqlParameter.SourceVersion = p.SourceVersion;
                sqlParameter.Value = p.Value;
                cmd.Parameters.Add(sqlParameter);
            }
            return cmd;
        }

        public IDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandBehavior behavior, CommandType cmdType)
        {
            this.CheckDisposed();

#if DEBUG
            System.Diagnostics.Debug.WriteLine(AppendDbCommandInfo(cmdText, parameters));
#endif

            IDbCommand cmd = this.DbCommand;

            this.PrepareCommand(cmd, cmdText, parameters, cmdType);

            this.Open();//打开数据

            IDataReader reader = cmd.ExecuteReader(behavior);
            cmd.Parameters.Clear();
            return reader;
        }

        public int ExecuteNonQuery(string cmdText, DbParam[] parameters)
        {
            return this.ExecuteNonQuery(cmdText, parameters, CommandType.Text);
        }
        public int ExecuteNonQuery(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            this.CheckDisposed();

#if DEBUG
            System.Diagnostics.Debug.WriteLine(AppendDbCommandInfo(cmdText, parameters));
#endif

            try
            {
                IDbCommand cmd = this.DbCommand;

                this.PrepareCommand(cmd, cmdText, parameters, cmdType);

                this.Open();
                int r = cmd.ExecuteNonQuery();
                cmd.Parameters.Clear();
                return r;
            }
            finally
            {
                this.Close();
            }
        }

        public object ExecuteScalar(string cmdText, DbParam[] parameters)
        {
            return this.ExecuteScalar(cmdText, parameters, CommandType.Text);
        }
        public object ExecuteScalar(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            this.CheckDisposed();

#if DEBUG
            System.Diagnostics.Debug.WriteLine(AppendDbCommandInfo(cmdText, parameters));
#endif

            try
            {
                IDbCommand cmd = this.DbCommand;

                this.PrepareCommand(cmd, cmdText, parameters, cmdType);

                this.Open();
                object r = cmd.ExecuteScalar();
                cmd.Parameters.Clear();
                return r;
            }
            finally
            {
                this.Close();
            }
        }

        internal InternalDataReader ExecuteInternalReader(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            IDataReader reader = this.ExecuteReader(cmdText, parameters, cmdType);
            return new InternalDataReader(this, reader);
        }
        public void Dispose()
        {
            if (this._disposed)
                return;

            if (this._dbTransaction != null)
            {
                if (this._isInTransaction)
                {
                    try
                    {
                        this._dbTransaction.Rollback();
                    }
                    catch
                    {
                    }
                }

                this._dbTransaction.Dispose();
                this._dbTransaction = null;
                this._isInTransaction = false;
            }

            if (this._dbCommand != null)
            {
                this._dbCommand.Dispose();
                this._dbCommand = null;
            }

            if (this._dbConnection != null)
            {
                this._dbConnection.Dispose();
            }

            this._disposed = true;
        }

        /// <summary>
        /// 获取参数
        /// </summary>
        /// <param name="cmd"></param>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <param name="cmdType"></param>
        void PrepareCommand(IDbCommand cmd, string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            cmd.CommandText = cmdText;
            cmd.CommandType = cmdType;

            if (this.IsInTransaction)
                cmd.Transaction = this._dbTransaction;

            if (parameters != null)
            {
                for (int i = 0; i < parameters.Length; i++)
                {
                    var param = parameters[i];
                    if (param == null)
                        continue;

                    var parameter = cmd.CreateParameter();
                    parameter.ParameterName = param.Name;

                    Type parameterType;
                    if (param.Value == null || param.Value == DBNull.Value)
                    {
                        parameter.Value = DBNull.Value;
                        parameterType = param.Type;
                    }
                    else
                    {
                        parameter.Value = param.Value;
                        parameterType = param.Value.GetType();
                    }

                    if (param.Precision != null)
                        parameter.Precision = param.Precision.Value;

                    if (param.Scale != null)
                        parameter.Scale = param.Scale.Value;

                    if (param.Size != null)
                        parameter.Size = param.Size.Value;

                    DbType? dbType = AntUtils.TryGetDbType(parameterType);
                    if (dbType != null)
                        parameter.DbType = dbType.Value;

                    cmd.Parameters.Add(parameter);
                }
            }
        }

        void CheckDisposed()
        {
            if (this._disposed)
            {
                throw new ObjectDisposedException(this.GetType().FullName);
            }
        }

        /// <summary>
        /// 组装SQL语句
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static string AppendDbCommandInfo(string cmdText, DbParam[] parameters)
        {
            StringBuilder sb = new StringBuilder();
            if (parameters != null)
            {
                foreach (var param in parameters)
                {
                    if (param == null)
                        continue;

                    string typeName = null;
                    object value = null;
                    Type parameterType;
                    if (param.Value == null || param.Value == DBNull.Value)
                    {
                        parameterType = param.Type;
                        value = "NULL";
                    }
                    else
                    {
                        value = param.Value;
                        parameterType = param.Value.GetType();

                        if (parameterType == typeof(string) || parameterType == typeof(DateTime))
                            value = "'" + value + "'";
                    }

                    if (parameterType != null)
                        typeName = GetTypeName(parameterType);

                    sb.AppendFormat("{0} {1} = {2};", typeName, param.Name, value);
                    sb.AppendLine();
                }
            }

            sb.AppendLine(cmdText);

            return sb.ToString();
        }
        /// <summary>
        /// 获取类型
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        static string GetTypeName(Type type)
        {
            Type unType;
            if (AntUtils.IsNullable(type, out unType))
            {
                return string.Format("Nullable<{0}>", GetTypeName(unType));
            }

            return type.Name;
        }
    }
}