MESProductionWeb/AntORM/Ant.Data/RunDataBaise/SQLiteDataAccess.cs

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using Ant.Common;

namespace Ant.Data
{

    public sealed class SQLiteDataAccess : BaseDataAccess
    {
        private SQLiteConnection DbConnection;
        private SQLiteTransaction trans;
        private DataAccess da;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connString">数据库连接字符串</param>
        public SQLiteDataAccess(string connString)
        {
            this.DbConnection = new SQLiteConnection(connString);
        }


        #region 数据库连接
        /// <summary>
        /// 数据库连接
        /// </summary>
        public override string ConnectionString
        {
            get
            {
                if (this.DbConnection.IsNull())
                    return "";
                else
                    return this.DbConnection.ConnectionString;
            }
            set
            {
                this.DbConnection.ConnectionString = value;
            }
        }
        public override DatabaseType DatabaseType
        {
            get { return DatabaseType.SQLite; }
        }

        /// <summary>
        /// 获取事务
        /// </summary>
        public override DbTransaction Trans
        {
            get
            {
                if (this.trans != null)
                    return trans;
                else
                    return null;
            }
        }

        /// <summary>
        /// 数据库连接是否不可用
        /// </summary>
        public override bool IsUnEnable
        {
            get
            {
                if (this.DbConnection == null)
                {
                    return true;
                }
                else
                {
                    if (this.DbConnection.ConnectionString.IsEmpty())
                    {
                        return true;
                    }
                    else
                    {
                        return false;
                    }

                }

            }
        }

        /// <summary>
        /// 数据库是否关闭
        /// </summary>
        public override bool IsClosed
        {
            get
            {
                if (this.DbConnection.IsNull())
                {
                    return true;
                }
                if (this.DbConnection.State == ConnectionState.Closed)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
        }

        /// <summary>
        /// 打开数据库
        /// </summary>
        public override void Open()
        {
            if (string.IsNullOrEmpty(DbConnection.ConnectionString))
            {
                da = DataAccessFactory.GetWriteDataDefault;
                this.DbConnection = new SQLiteConnection(da.ConnectionString);
            }
            if (this.DbConnection.State != ConnectionState.Open)
            {
                this.DbConnection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        public override void Close()
        {
            if (this.DbConnection != null && this.DbConnection.State != ConnectionState.Closed && trans == null)
            {
                this.DbConnection.Close();
                //this.DbConnection.Dispose();
                //this.DbConnection = null;
            }
        }

        /// <summary>
        /// 释放资源
        /// </summary>
        public override void Dispose()
        {
            if (this.DbConnection != null)
            {
                this.DbConnection.Dispose();
                this.DbConnection = null;
            }

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

        /// <summary>
        /// 开户数据库事务
        /// </summary>
        public override void BeginTransaction()
        {
            this.Open();
            if (trans == null) trans = this.DbConnection.BeginTransaction(IsolationLevel.ReadCommitted);
        }

        /// <summary>
        /// 提交数据库事务
        /// </summary>
        public override void Commit()
        {
            try
            {
                if (null != this.trans)
                {
                    this.trans.Commit();
                    this.trans.Dispose();
                    this.trans = null;
                }
            }
            finally
            {
                this.Close();
            }
        }

        /// <summary>
        /// 回滚数据库事务
        /// </summary>
        public override void RollBack()
        {

            try
            {
                if (trans != null)
                {
                    this.trans.Rollback();
                    this.trans.Dispose();
                    this.trans = null;
                }
            }
            finally
            {
                this.Close();
            }

        }


        #endregion

        #region 执行SQL语句和存储过程


        #region int ExecuteNonQuery

        /// <summary>
        /// 执行SQL命令,并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="Parameters">命令参数集合</param>
        /// <returns>返回受影响的行数</returns>
        public override int ExecuteNonQuery(string commandText, System.Data.CommandType commandType, QueryParameterCollection Parameters)
        {
            try
            {
                SQLiteCommand cmd = new SQLiteCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.Connection = this.DbConnection;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                Open();
                if (Parameters != null)
                {
                    foreach (QueryParameter q in Parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(q));
                    }
                }
                int tmpValue = cmd.ExecuteNonQuery();
                //Parameters.Clear();
                //foreach (SqlParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return tmpValue;
            }
            finally
            {
                Close();
            }
        }


        #endregion



        /// <summary>
        /// 执行SQL命令,并返回受影响的行数
        /// </summary>
        /// <param name="commandText">SQL命令</param>
        /// <param name="commandType">SQL命令类型</param>
        /// <param name="Parameters">命令参数集合</param>
        /// <returns>返回受影响的行数</returns>
        public override int ExecuteNonQuery(string commandText, DbParam[] parameters, System.Data.CommandType commandType)
        {
            //try
            //{
            //    SqlCommand cmd = this.DbConnection.CreateCommand();
            //    if (trans != null)
            //    {
            //        cmd.Transaction = trans;
            //    }
            //    Open();
            //    this.PrepareCommand(cmd, commandText, parameters, commandType);
            //    cmd.CommandTimeout = 60;
            //    int tmpValue = cmd.ExecuteNonQuery();
            //    return tmpValue;
            //}
            //finally
            //{
            //    Close();
            //}
            //return 1;
            try
            {
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                Open();
                this.PrepareCommand(cmd, commandText, parameters, commandType);
                cmd.CommandTimeout = 60;
                int tmpValue = cmd.ExecuteNonQuery();
                return tmpValue;
            }
            finally
            {
                Close();
            }
        }


        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override object ExecuteScalar(string commandText, DbParam[] parameters, CommandType commandType)
        {
            try
            {
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                Open();
                this.PrepareCommand(cmd, commandText, parameters, commandType);
                cmd.CommandTimeout = 60;
                return cmd.ExecuteScalar();
            }
            finally
            {
                Close();
            }
        }

        public override DbDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            try
            {
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                this.PrepareCommand(cmd, cmdText, parameters, cmdType);
                cmd.CommandTimeout = 60;
                SQLiteDataReader reader = cmd.ExecuteReader();
                return reader;
            }
            finally { }

        }


        #region ExecuteDataReader


        /// <summary>
        /// 
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="Parameters"></param>
        /// <returns></returns>
        public override DbDataReader ExecuteDataReader(string commandText, CommandType commandType, QueryParameterCollection Parameters)
        {

            try
            {
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (Parameters != null)
                {
                    foreach (QueryParameter p in Parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(p));
                    }
                }
                SQLiteDataReader reader = cmd.ExecuteReader();
                //foreach (SqlParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return reader;
            }
            finally
            {
                //Close();
            }
        }



        #endregion

        #region ExecuteDataTable
        /// <summary>
        /// 返回DataTable
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="Parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public override DataTable ExecuteDataTable(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
        {
            try
            {

                DataTable dt = new DataTable(tableName);
                SQLiteDataAdapter sda = new SQLiteDataAdapter();
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (Parameters != null)
                {
                    foreach (QueryParameter q in Parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(q));
                    }
                }
                sda.SelectCommand = cmd;
                //sda.Fill(startRecord, maxRecords, dt);
                sda.Fill(dt);
                //foreach (SqlParameter p in sda.SelectCommand.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return dt;
            }
            finally
            {
                Close();
            }
        }


        #endregion

        #region ExecuteDataSet
        /// <summary>
        /// 返回DataSet数据集
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="Parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public override DataSet ExecuteDataSet(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
        {
            try
            {

                DataSet ds = new DataSet();
                SQLiteDataAdapter sda = new SQLiteDataAdapter();
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }

                if (Parameters != null)
                {
                    foreach (QueryParameter q in Parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(q));
                    }
                }
                sda.SelectCommand = cmd;
                if (maxRecords > 0)
                {
                    sda.Fill(ds, startRecord, maxRecords, tableName);
                }
                else
                {
                    if (!string.IsNullOrEmpty(tableName))
                        sda.Fill(ds, tableName);
                    else
                        sda.Fill(ds);
                }
                //foreach (SqlParameter p in sda.SelectCommand.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return ds;
            }
            finally
            {
                Close();
            }
        }


        /// <summary>
        /// 获取视图
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="Parameters"></param>
        /// <param name="tableName"></param>
        /// <param name="startRecord"></param>
        /// <param name="maxRecords"></param>
        /// <returns></returns>
        public override DataView ExecuteDataView(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
        {
            try
            {
                DataSet ds = new DataSet();
                SQLiteDataAdapter sda = new SQLiteDataAdapter();
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }

                if (Parameters != null)
                {
                    foreach (QueryParameter q in Parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(q));
                    }
                }
                sda.SelectCommand = cmd;
                if (maxRecords > 0)
                {
                    sda.Fill(ds, startRecord, maxRecords, tableName);
                }
                else
                {
                    if (!string.IsNullOrEmpty(tableName))
                        sda.Fill(ds, tableName);
                    else
                        sda.Fill(ds);
                }
                //foreach (SqlParameter p in sda.SelectCommand.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return ds.Tables[0].DefaultView;
            }
            finally
            {
                Close();
            }
        }

        #endregion

        #region object ExecuteScalar

        /// <summary>
        /// 执行查询，并返回查询所返回的结果集中第一行的第一列
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="commandType"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public override object ExecuteScalar(string commandText, CommandType commandType, QueryParameterCollection parameters)
        {

            try
            {
                Open();
                SQLiteCommand cmd = this.DbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (!object.Equals(parameters, null))
                {
                    foreach (QueryParameter p in parameters)
                    {
                        cmd.Parameters.Add(ToSqlParameter(p));
                    }
                }
                return cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw (ex);
            }

            finally
            {
                Close();
            }

        }
        #endregion

        /// <summary>
        /// C#代码验证sql语句是否正确(只验证不执行sql)的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public override bool ValidateSQL(string sql)
        {
            bool bResult;
            SQLiteCommand cmd = this.DbConnection.CreateCommand();
            cmd.CommandText = "SET PARSEONLY ON";
            cmd.ExecuteNonQuery();
            try
            {
                cmd.CommandText = sql;
                cmd.ExecuteNonQuery();
                bResult = true;
            }
            catch (Exception ex)
            {

                bResult = false;
            }

            finally
            {
                cmd.CommandText = "SET PARSEONLY OFF";
                cmd.ExecuteNonQuery();
            }
            return bResult;

        }

        #endregion

        #region 参数类转换

        /// <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 (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);
                }
            }
        }


        /// <summary>
        /// 参数类转换
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private SQLiteParameter ToSqlParameter(QueryParameter parameter)
        {
            SQLiteParameter sqlParameter = new SQLiteParameter();
            sqlParameter.DbType = parameter.DbType;
            sqlParameter.Direction = parameter.Direction;
            sqlParameter.IsNullable = parameter.IsNullable;
            sqlParameter.ParameterName = parameter.ParameterName;
            //sqlParameter.Precision = parameter.Precision;
            //sqlParameter.Scale = parameter.Scale;
            sqlParameter.Size = parameter.Size;
            sqlParameter.SourceColumn = parameter.SourceColumn;
            sqlParameter.SourceVersion = parameter.SourceVersion;
            sqlParameter.Value = parameter.Value;
            return sqlParameter;
        }
        /// <summary>
        /// 将SqlParameter转化为QueryParameter
        /// </summary>
        /// <param name="parameter"></param>
        /// <returns></returns>
        private QueryParameter ToQueryParameter(SQLiteParameter parameter)
        {
            QueryParameter queryParameter = new QueryParameter();
            queryParameter.DbType = parameter.DbType;
            queryParameter.Direction = parameter.Direction;
            queryParameter.IsNullable = parameter.IsNullable;
            queryParameter.ParameterName = parameter.ParameterName;
            //queryParameter.Precision = parameter.Precision;
            //queryParameter.Scale = parameter.Scale;
            queryParameter.Size = parameter.Size;
            queryParameter.SourceColumn = parameter.SourceColumn;
            queryParameter.SourceVersion = parameter.SourceVersion;
            queryParameter.Value = parameter.Value;
            return queryParameter;
        }
        #endregion



    }

}