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

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using System.Xml;
using System.Data.SqlClient;
using Microsoft.ApplicationBlocks.OleDb;
using System.Configuration;
namespace Ant.Data
{
    public sealed class OleDbDataAccess : BaseDataAccess
    {

        #region DataAccess 成员
        private OleDbConnection OleDbConnection;
        private OleDbTransaction trans;
        public static string connstring = string.Empty;
        private DataAccess da;
        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="connString"></param>
        public OleDbDataAccess(string connString)
        {
            this.OleDbConnection = new OleDbConnection(connString);
            connstring = connString;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        public OleDbDataAccess()
        {
            this.OleDbConnection = new OleDbConnection();
        }

        #region int ExecuteNonQuery

        /// <summary>
        /// 执行SQL命令
        /// </summary>
        /// <param name="commandText">SQL文本</param>
        /// <param name="commandType">命令类型</param>
        /// <param name="Parameters">命令执行时所需要的参数</param>
        /// <returns>受影响的行数</returns>
        public override int ExecuteNonQuery(string commandText, System.Data.CommandType commandType, QueryParameterCollection Parameters)
        {

            try
            {
                this.Open();
                OleDbCommand cmd = this.OleDbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                foreach (QueryParameter p in Parameters)
                {
                    if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) &&
                                (p.Value == null))
                    {
                        p.Value = DBNull.Value;
                    }
                    cmd.Parameters.Add(this.ToOleDbParameter(p));
                }

                int tmpValue = cmd.ExecuteNonQuery();
                //foreach (OleDbParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return tmpValue;
            }
            catch (Exception ex)
            {

                return -1;
            }
            finally
            {
                this.Close();
            }
            #region 另一种执行方法
            //int Count = 0;
            //using (OleDbConnection conn = new OleDbConnection(connstring))
            //{
            //    conn.Open();
            //    Count = OleDbHelper.ExecuteNonQuery(OleDbConnection, CommandType.Text, commandText);
            //    return Count;

            //} 
            #endregion

        }


        #endregion

        #region DataReader

        /// <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
            {
                if (IsClosed)
                {
                    Open();
                }
                OleDbCommand cmd = this.OleDbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (Parameters != null)
                {
                    foreach (QueryParameter p in Parameters)
                    {
                        cmd.Parameters.Add(ToOleDbParameter(p));
                    }
                }

                OleDbDataReader reader = cmd.ExecuteReader();

                //foreach (OleDbParameter p in cmd.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return reader;
            }
            catch (Exception ex)
            {

                return null;
            }
            //finally
            //{
            //    Close();
            //}
        }

        #endregion

        #region ExecuteDataTable


        public override System.Data.DataTable ExecuteDataTable(string commandText, System.Data.CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
        {
            try
            {
                this.Open();
                OleDbDataAdapter da = new OleDbDataAdapter();
                OleDbCommand cmd = new OleDbCommand();
                cmd.Connection = this.OleDbConnection;
                cmd.CommandText = commandText;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (Parameters != null)
                {
                    foreach (QueryParameter p in Parameters)
                    {
                        cmd.Parameters.Add(this.ToOleDbParameter(p));
                    }
                }
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();
                da.Fill(startRecord, maxRecords, dt);
                //foreach (OleDbParameter p in da.SelectCommand.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                dt.TableName = tableName;
                return dt;
            }
            catch (Exception ex)
            {

                return null;
            }
            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();
                OleDbDataAdapter sda = new OleDbDataAdapter();

                OleDbCommand cmd = this.OleDbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                cmd.CommandTimeout = 60;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                Open();
                foreach (QueryParameter q in Parameters)
                {
                    cmd.Parameters.Add(ToOleDbParameter(q));
                }
                sda.SelectCommand = cmd;
                sda.Fill(ds, startRecord, maxRecords, tableName);
                //foreach (SqlParameter p in sda.SelectCommand.Parameters)
                //{
                //    Parameters[p.ParameterName] = ToQueryParameter(p);
                //}
                return ds;
            }
            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
            {
                if (IsClosed)
                {
                    Open();
                }
                OleDbCommand cmd = this.OleDbConnection.CreateCommand();
                cmd.CommandText = commandText;
                cmd.CommandType = commandType;
                if (trans != null)
                {
                    cmd.Transaction = trans;
                }
                if (!object.Equals(parameters, null))
                {
                    foreach (QueryParameter p in parameters)
                    {
                        cmd.Parameters.Add(ToOleDbParameter(p));
                    }
                }
                object obj = cmd.ExecuteScalar();
                //if (!object.Equals(parameters, null))
                //{
                //    parameters.Clear();
                //    foreach (OleDbParameter p in cmd.Parameters)
                //    {
                //        parameters[p.ParameterName] = ToQueryParameter(p);
                //    }
                //}
                return obj;
            }
            catch (Exception ex)
            {

                return null;
            }
            finally
            {
                Close();
            }

        }
        #endregion

        #endregion

        #region DataAccess 成员
        /// <summary>
        /// 
        /// </summary>
        public override string ConnectionString
        {
            get
            {
                if (this.OleDbConnection.IsNull())
                    return "";
                else
                    return this.OleDbConnection.ConnectionString;
            }
            set
            {
                this.OleDbConnection.ConnectionString = value;
            }
        }
        public override DatabaseType DatabaseType
        {
            get { return DatabaseType.OleDb; }
        }

        public override DbTransaction Trans
        {
            get
            {
                if (this.trans != null)
                    return trans;
                else
                    return null;
            }
        }
        /// <summary>
        /// 
        /// </summary>
        public override bool IsClosed
        {
            get
            {
                if (this.OleDbConnection.IsNull())
                {
                    return true;
                }
                if (this.OleDbConnection.State == ConnectionState.Closed)
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }

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

                }

            }
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public override void Open()
        {
            //if (this.OleDbConnection.State == ConnectionState.Closed)
            if (OleDbConnection.ConnectionString == null || OleDbConnection.ConnectionString == "")
            {
                da = DataAccessFactory.GetWriteDataDefault;
                this.OleDbConnection = new OleDbConnection(da.ConnectionString);
            }
            if (this.OleDbConnection.State != ConnectionState.Open)
            {
                this.OleDbConnection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库连接之后释放资源
        /// </summary>
        public override void Close()
        {
            if (this.OleDbConnection.State != ConnectionState.Closed)
            {
                this.OleDbConnection.Close();
                this.OleDbConnection.Dispose();
            }

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

        }
        /// <summary>
        /// 开始事务
        /// </summary>
        public override void BeginTransaction()
        {
            Open();
            if (trans == null)
                trans = this.OleDbConnection.BeginTransaction();
        }
        /// <summary>
        /// 提交事务
        /// </summary>
        public override void Commit()
        {
            try
            {
                if (null != this.trans)
                {
                    this.trans.Commit();
                    this.trans.Dispose();
                    trans = null;
                }
            }
            finally
            {
                if (this.OleDbConnection.State != ConnectionState.Closed)
                {
                    this.OleDbConnection.Close();
                    this.OleDbConnection.Dispose();
                }
            }
        }
        /// <summary>
        /// 回滚事务
        /// </summary>
        public override void RollBack()
        {
            try
            {
                if (trans != null)
                {
                    this.trans.Rollback();
                    this.trans.Dispose();
                    trans = null;
                }
            }
            finally
            {
                //this.Close();
                if (this.OleDbConnection.State != ConnectionState.Closed)
                {
                    this.OleDbConnection.Close();
                    this.OleDbConnection.Dispose();
                }
            }
        }

        #endregion

        #region private method
        /// <summary>
        /// 将QueryParameter转换成OleDbParameter
        /// </summary>
        /// <param name="parameter">要转换的QueryParamter</param>
        /// <returns>返回OleDbParameter</returns>
        private OleDbParameter ToOleDbParameter(QueryParameter parameter)
        {
            OleDbParameter oleDbParameter = new OleDbParameter();
            oleDbParameter.ParameterName = parameter.ParameterName;
            oleDbParameter.IsNullable = parameter.IsNullable;
            //oleDbParameter.DbType = parameter.DbType;

            oleDbParameter.Direction = parameter.Direction;
            oleDbParameter.Precision = parameter.Precision;
            oleDbParameter.Scale = parameter.Scale;
            oleDbParameter.Size = parameter.Size;
            oleDbParameter.SourceColumn = parameter.SourceColumn;
            oleDbParameter.SourceVersion = parameter.SourceVersion;
            oleDbParameter.Value = parameter.Value;
            return oleDbParameter;
        }
        private QueryParameter ToQueryParameter(OleDbParameter 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




        public override bool ValidateSQL(string sql)
        {
            throw new NotImplementedException();
        }

        public override DataView ExecuteDataView(string commandText, CommandType commandType, QueryParameterCollection Parameters, string tableName, int startRecord, int maxRecords)
        {
            throw new NotImplementedException();
        }



        public override DbDataReader ExecuteReader(string cmdText, DbParam[] parameters, CommandType cmdType)
        {
            throw new NotImplementedException();
        }

        public override int ExecuteNonQuery(string commandText, DbParam[] parameters, CommandType commandType)
        {
            throw new NotImplementedException();
        }

        public override object ExecuteScalar(string commandText, DbParam[] parameters, CommandType commandType)
        {
            throw new NotImplementedException();
        }
    }
}