XmlDocumentAnalysis/XmlDocumentAnalysis/OdbcHelper.cs

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Odbc;
using System.Data.OracleClient;
using XmlDucumentAnalysis;

namespace DataPumpWindowsService
{
    public class OdbcHelper
    {
        private string _message = "";//返回信息
        public OdbcConnection _con_SQL;//连接对象

        public string Message { get { return _message; } }

        public OdbcHelper(string connecteString)
        {
            _con_SQL = new OdbcConnection(connecteString);
        }

        #region 命令、事务、连接
        /// <summary>
        /// 创建命令对象
        /// </summary>
        /// <returns></returns>
        private OdbcCommand CreateCommand()
        {
            OdbcCommand cmdObj = _con_SQL.CreateCommand();
            cmdObj.CommandTimeout = int.MaxValue;
            return cmdObj;
        }

        /// <summary>
        /// 打开连接
        /// </summary>
        /// <returns></returns>
        public bool OpenConnection()
        {
            //检测连接是否已打开
            if (_con_SQL.State == System.Data.ConnectionState.Open)
                return true;
            try
            {
                _con_SQL.Open();
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return false;
            }

            //检测连接状态
            if (_con_SQL.State != System.Data.ConnectionState.Open)
            {
                _message = "打开连接失败";
                return false;
            }
            return true;
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        /// <returns></returns>
        public bool CloseConnection()
        {
            if (_con_SQL.State == System.Data.ConnectionState.Closed)
                return true;

            try
            {
                _con_SQL.Close();
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return false;
            }

            if (_con_SQL.State != System.Data.ConnectionState.Closed)
            {
                _message = "关闭连接失败";
                return false;
            }
            return true;
        }
        #endregion

        #region 执行无返回值的sql语句
        /// <summary>
        /// 执行无返回值的sql语句
        /// </summary>
        /// <param name="commandString"></param>
        /// <returns></returns>
        public bool ExecuteNonQuery(string commandString)
        {
            //打开连接失败，不执行查询
            if (!OpenConnection())
                return false;
            try
            {
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = commandString;
                cmdObj.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                CloseConnection();
                _message = ex.Message;
                Log.Debug(ex);
                Log.Debug("ex:" + commandString);
                return false;
            }
            CloseConnection();
            return true;
        }
        #endregion

        #region 执行返回值的sql
        /// <summary>
        /// 执行返回值的sql
        /// </summary>
        /// <param name="commandString"></param>
        /// <returns></returns>
        public object ExecuteResult(string commandString)
        {
            object reobj = null;

            //打开连接失败，不执行查询
            if (!OpenConnection()) return reobj;

            try
            {
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = commandString;
                reobj = cmdObj.ExecuteScalar();
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
            }
            finally
            {
                CloseConnection();
            }
            return reobj;
        }
        #endregion

        #region 执行sql命令组
        /// <summary>
        /// 执行sql命令组
        /// </summary>
        /// <param name="arrSql"></param>
        /// <returns></returns>
        public bool ExecuteSqls(ArrayList arrSql)
        {
            if (!OpenConnection()) return false;

            try
            {
                OdbcCommand cmdObj = CreateCommand();
                foreach (var sql in arrSql)
                {
                    cmdObj.CommandText = sql.ToString();
                    cmdObj.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return false;
            }
            finally
            {
                CloseConnection();
            }
            return true;
        }
        #endregion

        #region 事务控制执行sql命令组
        /// <summary>
        /// 事务控制执行sql命令组
        /// </summary>
        /// <param name="arrSql"></param>
        /// <returns></returns>
        public bool ExecuteSqlByAffair(ArrayList arrSql)
        {
            if (!OpenConnection()) return false;
            OdbcTransaction transObj = _con_SQL.BeginTransaction();//开始事务操作
            try
            {
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.Transaction = transObj;//指定command的事务对象
                foreach (var sql in arrSql)
                {
                    cmdObj.CommandText = sql.ToString();
                    cmdObj.ExecuteNonQuery();
                }
                transObj.Commit();
            }
            catch (Exception ex)
            {
                transObj.Rollback();
                _message = ex.Message;
                Log.Debug(ex);
                foreach (var sql in arrSql)
                {
                    Log.Debug("ex:" + sql);
                }
                return false;
            }
            finally
            {
                CloseConnection();
            }
            return true;
        }

        /// <summary>
        /// 事务控制执行sql命令组
        /// </summary>
        /// <param name="dicSql"></param>
        /// <returns></returns>
        public bool ExecuteSqlsByAffair(Dictionary<string, string> dicSql)
        {
            if (!OpenConnection())
                return false;
            OdbcTransaction transObj = _con_SQL.BeginTransaction();
            string sqlPath = string.Empty;
            try
            {
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.Transaction = transObj;
                foreach (var item in dicSql)
                {
                    sqlPath = item.Key;
                    string[] commands = System.Text.RegularExpressions.Regex.Split(item.Value + " ",
                        "[ |\r\n]go[ |\r\n]", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    foreach (string sql in commands)
                        if (sql.Trim().Length > 0)
                        {
                            cmdObj.CommandText = sql;
                            cmdObj.ExecuteNonQuery();
                        }
                }
                transObj.Commit();
            }
            catch (Exception ex)
            {
                transObj.Rollback();
                _message = string.Format("执行 {0} 出错；\r\n", sqlPath);
                _message += ex.Message;
                Log.Debug(_message);
                return false;
            }
            finally
            {
                CloseConnection();
            }
            return true;
        }

        /// <summary>
        /// 事务控制执行sql命令组
        /// </summary>
        /// <param name="dicSql">sql命令组</param>
        /// <param name="dicRecord">返回的日志</param>
        /// <returns></returns>
        public bool ExecuteSqlsByAffair(Dictionary<string, string> dicSql, ref Dictionary<string, string> dicRecord)
        {
            if (!OpenConnection()) return false;
            OdbcTransaction transObj = _con_SQL.BeginTransaction();
            string sqlPath = string.Empty;
            try
            {
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.Transaction = transObj;
                foreach (var item in dicSql)
                {
                    sqlPath = item.Key;
                    string[] commands = System.Text.RegularExpressions.Regex.Split(item.Value + " ",
                        "[ |\r\n]go[ |\r\n]", System.Text.RegularExpressions.RegexOptions.IgnoreCase);
                    foreach (string sql in commands)
                        if (sql.Trim().Length > 0)
                        {
                            cmdObj.CommandText = sql;
                            cmdObj.ExecuteNonQuery();
                        }

                    if (dicRecord != null)
                        dicRecord.Add(sqlPath, DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss", System.Globalization.DateTimeFormatInfo.InvariantInfo));
                }
                transObj.Commit();
            }
            catch (Exception ex)
            {
                _message = string.Format("执行{0}出错:\r\n", sqlPath);
                _message += ex.Message;
                Log.Debug(_message);
                try { transObj.Rollback(); }
                catch { }
                return false;
            }
            return true;
        }
        #endregion

        #region 获取数据表
        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Log.Debug(sql);
            if (!OpenConnection()) return null;
            try
            {
                DataTable dt = new DataTable();
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = sql;
                OdbcDataAdapter daObj = new OdbcDataAdapter(cmdObj);
                daObj.Fill(dt);
                return dt;
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                Log.Debug("ex:" + sql);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        /// <summary>
        /// 获取数据表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql, string tableName)
        {
            if (!OpenConnection()) return null;
            try
            {
                DataTable dt = new DataTable();
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = sql;
                OdbcDataAdapter daObj = new OdbcDataAdapter(cmdObj);
                daObj.Fill(dt);
                dt.TableName = tableName;
                return dt;
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }

        public DataTable GetDataTable(string sql, string tableName, int currentPage, int pageSize)
        {
            if (!OpenConnection()) return null;
            try
            {
                DataSet ds = new DataSet();
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = sql;
                OdbcDataAdapter daObj = new OdbcDataAdapter(cmdObj);
                daObj.Fill(ds, (currentPage - 1) * pageSize, pageSize, tableName);
                return ds.Tables[0];
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion

        #region 获取数据集
        /// <summary>
        /// 获取数据集
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetDataSet(string sql)
        {
            if (!OpenConnection()) return null;
            try
            {
                DataSet ds = new DataSet();
                OdbcCommand cmdObj = CreateCommand();
                cmdObj.CommandText = sql;
                OdbcDataAdapter daObj = new OdbcDataAdapter(cmdObj);
                daObj.Fill(ds);
                return ds;
            }
            catch (Exception ex)
            {
                _message = ex.Message;
                Log.Debug(ex);
                return null;
            }
            finally
            {
                CloseConnection();
            }
        }
        #endregion
    }
}