ZLPlugin_LisPacs_MR_NEW/LisPacsDataUpload/OracleHelper.cs

using Common;
using Npgsql;
using Oracle.ManagedDataAccess.Client;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace LisPacsDataUpload
{
    public class OracleHelper<T> where T : class
    {
        //数据库连接变量
        //private static OracleConnection conn = null;

        //数据库事务变量
        //private static OracleTransaction transaction = null;

        //连接字符串
        public static string GetConnStr()
        {
            return ConfigurationManager.ConnectionStrings["conn"].ConnectionString;

        }

        //连接字符串
        public static string GetPgConnStr()
        {
            return ConfigurationManager.ConnectionStrings["pgConn"].ConnectionString;

        }
        /// <summary>
        /// 轮询间隔时间
        /// </summary>
        /// <returns></returns>
        public static int Time()
        {
            return Convert.ToInt32(ConfigurationManager.AppSettings["time"]) * 60 * 1000;

        }
        /// <summary>
        /// 数据上传开始时间
        /// </summary>
        /// <returns></returns>
        public static int StartTime()
        {
            return Convert.ToInt32(ConfigurationManager.AppSettings["start_time"]);

        }

        public static int UploadPacs()
        {
            return Convert.ToInt32(ConfigurationManager.AppSettings["pacs"]);

        }

        public static int UploadTime()
        {
            return Convert.ToInt32(ConfigurationManager.AppSettings["upload_time"]);

        }

        //public static OracleConnection getConnection()
        //{
        //    OracleConnection conn = null;
        //    if (conn == null || conn.State == ConnectionState.Closed)
        //        openConnection();
        //    return conn;
        //}
        private static object openLock = new object();
        //打开数据库连接
        private static bool openConnection(OracleConnection conn)
        {
            lock (openLock)
            {
                try
                {
                    conn = new OracleConnection(GetConnStr());
                    conn.Close();
                    if (conn.State != ConnectionState.Open)
                    {
                        conn.Open();
                        return true;
                    }
                }
                catch (Exception ex)
                {
                    LogHelper.Info("打开数据库出错，" + ex.Message);
                }
                finally { }
                return false;
            }

            //lock (openLock)
            //{
            //    try
            //    {
            //        //打开连接
            //        if (conn == null || conn.State == ConnectionState.Closed)
            //        {
            //            conn = new OracleConnection(GetConnStr());
            //            conn.Open();

            //        }
            //    }
            //    catch (Exception ex)
            //    {
            //        LogHelper.Error(ex, ex.Message);
            //        //throw new Exception(ex.Message, ex);
            //    }


        }
        private static object closeLock = new object();
        //关闭数据库连接
        private static void closeConnection()
        {
            lock (closeLock)
            {
                OracleConnection conn = null;
                if (conn != null)
                {
                    if (conn.State == ConnectionState.Open)
                        conn.Close();
                }
            }

            //lock(closeLock)
            //{
            //    try
            //    {
            //        if (conn != null)
            //        {
            //            if (conn.State == ConnectionState.Open)
            //                conn.Close();
            //        }

            //    }
            //    catch (Exception ex)
            //    {
            //        LogHelper.Error(ex, ex.Message);
            //        //throw new Exception(ex.Message, ex);
            //    }
            //}

        }
        private static object DSLock = new object();
        /// <summary>
        /// 查询,返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable RunQueryDS(string sql)
        {
            //lock (DSLock)
            //{
                DataTable dt = new DataTable();
                using (OracleConnection conn = new OracleConnection(GetConnStr()))
                {
                    try
                    {
                        conn.Open();
                        OracleDataAdapter oda = new OracleDataAdapter(sql, conn);
                        //DataSet ds = new DataSet();
                        oda.Fill(dt);
                        //dt = ds.Tables[0];
                    }
                    catch (Exception ex)
                    {
                        LogHelper.Info("查询出错sql：" + sql);
                        LogHelper.Error(ex, ex.Message);
                        //throw new Exception(ex.Message);
                    }
                    finally
                    {
                        //closeConnection();
                        conn.Close();
                    }
                }
                return dt;
            //}
        }

        /// <summary>
        /// 查询,返回DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataTable PgQueryDS(string sql)
        {
            DataTable dataTable = new DataTable();
            using (NpgsqlConnection connection = new NpgsqlConnection(GetPgConnStr()))
            {
                connection.Open();
                using (NpgsqlCommand command = new NpgsqlCommand(sql, connection))
                {
                    using (NpgsqlDataReader reader = command.ExecuteReader())
                    {
                        dataTable.Load(reader);
                    }
                }
            }
            return dataTable;
        }


        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procName">存储过程名</param>
        /// <param name="commandParameters">参数列表</param>
        public static void ExecuteProcedure(string procName, params OracleParameter[] commandParameters)
        {
            //initConnection();
            //initTransaction();
            try
            {
                OracleConnection conn = null;
                //执行命令
                OracleCommand cmd = new OracleCommand();
                cmd.Connection = conn;
                cmd.CommandText = procName;
                cmd.CommandType = CommandType.StoredProcedure;
                // 绑定传入的参数
                if (commandParameters != null)
                {
                    foreach (OracleParameter parm in commandParameters)
                    {
                        cmd.Parameters.Add(parm);
                    }
                }
                cmd.ExecuteNonQuery();
                //commitTransaction();
                cmd.Parameters.Clear();

            }
            catch (Exception ex)
            {
                //rollbackTransaction();
                LogHelper.Error(ex, ex.Message);
                throw new Exception(ex.Message, ex);
            }
            finally
            {
                //关闭数据库连接
                closeConnection();
            }
        }

        private static object ExtObjectLock = new object();
        /// <summary>
        /// 执行一个update，insert，delete，drop等SQL
        /// </summary>
        /// <param name="cmdText">SQL值</param>
        /// <returns>执行是否成功</returns>
        public static int ExecuteNonQuery(string sql)
        {
            //lock (ExtObjectLock)
            //{
                int val = 0;
                //initTransaction();
                OracleTransaction tran = null;
                using (OracleConnection conn = new OracleConnection(GetConnStr()))
                {
                    //创建连接
                    try
                    {
                        conn.Open();
                        if (conn.State != ConnectionState.Connecting)
                        {
                            bool isb = false;
                            try
                            {
                                tran = conn.BeginTransaction();
                                isb = true;
                            }
                            catch
                            { }
                            finally
                            {
                                if (isb)
                                {
                                    //创建一个新的命令
                                    OracleCommand cmd = new OracleCommand();
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Connection = conn;
                                    cmd.Transaction = tran;
                                    //cmd.Connection = tran.Connection;
                                    cmd.CommandText = sql;

                                    //执行命令
                                    val = cmd.ExecuteNonQuery();
                                    tran.Commit();
                                }
                            }
                        }

                    }
                    catch (System.Exception ex)
                    {
                        val = 0;
                        //rollbackTransaction();
                        LogHelper.Info("执行出错sql：" + sql);
                        LogHelper.Error(ex, ex.Message);
                        if (tran != null) { tran.Rollback(); tran.Dispose(); tran = null; }
                    }
                    finally
                    {
                        tran.Dispose();
                        //关闭数据库连接
                        //closeConnection();
                        conn.Close();
                    }
                }

                return val;
            //}
        }

        public static int PgExecuteNonQuery(string query)
        {
            int newId;
            try {

                using (var conn = new NpgsqlConnection(GetPgConnStr()))
                {
                    using (var command = new NpgsqlCommand(query, conn))
                    {

                        conn.Open();
                        newId = Convert.ToInt32(command.ExecuteScalar());

                    }
                }
            }
            catch (Exception e) {
                LogHelper.Info(e.Message + e.InnerException);
                return 0;
            }
            //, params object[] parameters 这是入参 暂时不用
            
            return newId;
        }

        /// <summary>
        /// 将DataSet转换为泛型集合List
        /// </summary>
        /// <param name="ds">要转换的DataSet数据集</param>
        /// <returns>转换之后的泛型集合</returns>
        private static List<T> DataSetToList(DataSet ds)
        {
            List<T> list = new List<T>();
            try
            {
                // 根据数据量进行遍历
                for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                {
                    // 通过类型创建该类型的实例
                    T _t = (T)Activator.CreateInstance(typeof(T));
                    // 通过创建的实例获取该实例下的所有公共字段
                    PropertyInfo[] propertys = _t.GetType().GetProperties();
                    // 使用嵌套循环遍历数据列，匹配数据列名称与获取的公共字段名称，对符合的字段进行赋值
                    foreach (PropertyInfo pi in propertys)
                    {
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                            if (pi.Name.Equals(ds.Tables[0].Columns[j].ColumnName))
                            {
                                //LogHelper.Info("666:" + ds.Tables[0].Columns[j].ColumnName);
                                if (ds.Tables[0].Rows[i][j] != DBNull.Value)
                                    pi.SetValue(_t, ds.Tables[0].Rows[i][j], null);
                                else
                                    // 对于数据库中的Null值需要进行单独处理
                                    pi.SetValue(_t, "", null);
                            }
                        }
                    }
                    list.Add(_t);
                }
            }
            catch (Exception ex)
            {
                // 抛出异常，上层进行异常捕获和处理并记录错误日志
                throw new Exception(ex.Message);
            }
            return list;
        }
        private static object ListLock = new object();
        /// <summary>
        /// 查询操作（返回泛型集合）当有查询操作时，应优先考虑使用RunQueryList方法
        /// </summary>
        /// <param name="sql">要执行的查询语句</param>
        /// <returns>对应数据实体的泛型集合</returns>
        public static List<T> RunQueryList(string sql)
        {
            //lock (ListLock)
            //{
                List<T> list = new List<T>();
                using (OracleConnection conn = new OracleConnection(GetConnStr()))
                {
                    try
                    {
                        conn.Open();
                        DataSet ds = new DataSet();
                        OracleDataAdapter oda = new OracleDataAdapter(sql, conn);
                        oda.Fill(ds);
                        // 若没有查询到任何数据，则返回空
                        if (ds == null || ds.Tables.Count < 0)
                            return null;
                        // 调用DataSet转List的转换方法
                        list = DataSetToList(ds);

                    }
                    catch (Exception ex)
                    {
                        //conn.Close();
                        LogHelper.Error(ex, ex.Message);
                        LogHelper.Info("查询出错sql：" + sql);
                        //throw new Exception(ex.Message);
                    }
                    finally
                    {
                        conn.Close();
                        //closeConnection();
                    }
                }
                return list;
            //}

        }


        public static List<T> PgRunQueryList(string sql)
        {
            List<T> ts = new List<T>();
            try
            {
                using (var conn = new NpgsqlConnection(GetPgConnStr())) 
                { 
                    if (conn.State != ConnectionState.Open)
                    {
                    conn.Open();
                    }
                    T t = default(T);
                    using (var nda = new NpgsqlDataAdapter(sql, conn))
                    {
                        DataTable dt = new DataTable();
                        nda.Fill(dt);
                        // 获得此模型的类型  
                        //  Type type = typeof(McDevice);
                        string tempName = "";
                        foreach (DataRow dr in dt.Rows)
                        {
                            t = Activator.CreateInstance<T>();
                            // 获得此模型的公共属性     
                            PropertyInfo[] propertys = t.GetType().GetProperties();
                            foreach (PropertyInfo pi in propertys)
                            {
                                tempName = pi.Name;  // 检查DataTable是否包含此列   
                                if (dt.Columns.Contains(tempName))
                                {
                                    // 判断此属性是否有Setter     
                                    if (!pi.CanWrite) continue;
                                    object value = dr[tempName];
                                    if (value != DBNull.Value)
                                        pi.SetValue(t, value, null);
                                }
                            }
                            ts.Add(t);
                        }
                    }
                }
            }
            catch (Exception ex) { LogHelper.Info("查询pg数据返回lis集合异常" + ex.Message); }
            return ts;

        }

    }
}