IMEX.mes.wcf/DK.Basics/DataAccess/OracleDB.cs

/*******************************************************************************
 * Copyright(c) 2019 dongke All rights reserved. / Confidential
 * 类的信息：
 *      1.程序名称：OracleDB.cs
 *      2.功能描述：操作oracle的简易ORM，提高编写效率，提高代码可读性
 *      3.依赖：IDBConnection.cs，IDBTransaction.cs
 * 编辑履历：
 *      作者            日期                版本            修改内容
 *      徐伟            2019-09-02          1.00            新建
 *******************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Collections;
using Dongke.IBOSS.PRD.WCF.DataModels;
using Dongke.IBOSS.PRD.Basics.BaseResources;
using Oracle.ManagedDataAccess.Client;

namespace Dongke.IBOSS.PRD.Basics.DataAccess
{
    public static class OracleDB
    {
        public static IDBTransaction GetConn()
        {
            return ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
        }

        /// <summary>
        /// 通用SQL语句执行方法；
        /// 不指定数据连接时，默认执行单条语句，执行完毕直接提交；
        /// 指定数据连接时，可执行多条语句，默认不提交，通过指定commit提交事务；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <param name="conn">数据连接</param>
        /// <param name="commit">是否提交</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sqlStr, IDataParameter[] sqlPara = null, IDBTransaction conn = null, bool commit = false)
        {
            int result = 0;
            try
            {
                if (conn == null) conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                if (conn.ConnState == ConnectionState.Closed) conn.Connect();
                result = conn.ExecuteNonQuery(sqlStr, sqlPara);
                if (conn == null || commit) conn.Commit();
            }
            catch (Exception e)
            {
                if (conn == null || commit) conn.Rollback();
                throw e;
            }
            finally
            {
                if (conn == null || commit)
                    if (conn.ConnState == ConnectionState.Open) conn.Disconnect();
            }
            return result;
        }
        public static int ExecuteNonQuery(string sqlStr, ClientRequestEntity cre, IDBTransaction conn = null, bool commit = false)
        {
            return ExecuteNonQuery(sqlStr, GetParameter(cre), conn, commit);
        }

        /// <summary>
        /// 通用SQL语句执行方法，ServiceResultEntity格式；
        /// 不指定数据连接时，默认执行单条语句，执行完毕直接提交；
        /// 指定数据连接时，可执行多条语句，默认不提交，通过指定commit提交事务；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <param name="conn">数据连接</param>
        /// <param name="commit">是否提交</param>
        /// <returns></returns>
        public static ServiceResultEntity ExecuteNonQuerySRE(string sqlStr, IDataParameter[] sqlPara = null, IDBTransaction conn = null, bool commit = false)
        {
            ServiceResultEntity result = new ServiceResultEntity();
            try
            {
                bool finish = (conn == null || commit) ? true : false;
                if (conn == null) conn = ClsDbFactory.CreateDBTransaction(DataBaseType.ORACLE, DataManager.ConnectionString);
                if (conn.ConnState == ConnectionState.Closed) conn.Connect();
                result.Result = conn.ExecuteNonQuery(sqlStr, sqlPara);
                result.Status = Constant.ServiceResultStatus.Success;
                result.Message = "操作成功！";
                if (finish) conn.Commit();
            }
            catch (Exception e)
            {
                if (conn == null || commit) conn.Rollback();
                //result.Status = Constant.ServiceResultStatus.SystemError;
                //result.Status = "操作失败！";
                //return result;
                throw e;
            }
            finally
            {
                if (conn.ConnState == ConnectionState.Open) conn.Disconnect();
            }
            return result;
        }
        public static ServiceResultEntity ExecuteNonQuerySRE(string sqlStr, ClientRequestEntity cre, IDBTransaction conn = null, bool commit = false)
        {
            return ExecuteNonQuerySRE(sqlStr, GetParameter(cre), conn, commit);
        }

        /// <summary>
        /// 通用SQL语句执行方法，ServiceResultEntity格式；
        /// 不指定数据连接时，默认执行单条语句，执行完毕直接提交；
        /// 指定数据连接时，可执行多条语句，默认不提交，通过指定commit提交事务；
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="opt">操作：insert,update,delete</param>
        /// <param name="fields">字段键值对</param>
        /// <param name="conn">数据连接</param>
        /// <param name="commit">是否提交</param>
        /// <returns></returns>
        public static ServiceResultEntity ExecuteNonQuerySRE(string tableName, string opt, IDataParameter[] sqlPara, IDBTransaction conn = null, bool commit = false)
        {
            string sqlStr = MakeSql(tableName, opt, sqlPara);
            return ExecuteNonQuerySRE(sqlStr, sqlPara, conn, commit);
        }
        public static ServiceResultEntity ExecuteNonQuerySRE(string tableName, string opt, ClientRequestEntity cre, IDBTransaction conn = null, bool commit = false)
        {
            string sqlStr = MakeSql(tableName, opt, GetParameter(cre));
            return ExecuteNonQuerySRE(sqlStr, GetParameter(cre), conn, commit);
        }

        /// <summary>
        /// 通用SQL语句执行获取DataSet方法，ServiceResultEntity格式；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <returns></returns>
        public static ServiceResultEntity ExecuteDataSetSRE(string sqlStr, IDataParameter[] sqlPara = null)
        {
            IDBConnection conn = null;
            ServiceResultEntity result = new ServiceResultEntity();
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                conn.Open();
                result.Data = conn.GetSqlResultToDs(sqlStr, sqlPara);
                if (result.Data.Tables[0].Rows.Count == 0)
                    result.Status = Constant.ServiceResultStatus.NoSearchResults;
                else
                    result.Status = Constant.ServiceResultStatus.Success;
                result.Message = "操作成功！";
                return result;
            }
            catch (Exception e)
            {
                //result.Status = Constant.ServiceResultStatus.SystemError;
                //result.Message = "操作失败！";
                //return result;
                throw e;
            }
            finally
            {
                if (conn.ConnState == ConnectionState.Open) conn.Close();
            }
        }
        public static ServiceResultEntity ExecuteDataSetSRE(string sqlStr, ClientRequestEntity cre)
        {
            return ExecuteDataSetSRE(sqlStr, GetParameter(cre));
        }

        /// <summary>
        /// 通用SQL语句执行获取DataSet方法，ServiceResultEntity格式；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <returns></returns>
        public static ServiceResultEntity ExecuteDataSetSRE(string tableName, string opt, IDataParameter[] sqlPara, string orderBy = "")
        {
            string sqlStr = MakeSql(tableName, opt, sqlPara, orderBy);
            return ExecuteDataSetSRE(sqlStr, sqlPara);
        }
        public static ServiceResultEntity ExecuteDataSetSRE(string tableName, string opt, ClientRequestEntity cre, string orderBy = "")
        {
            string sqlStr = MakeSql(tableName, opt, GetParameter(cre), orderBy);
            return ExecuteDataSetSRE(sqlStr, GetParameter(cre));
        }

        /// <summary>
        /// 通用SQL语句执行获取DataSet方法；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <returns></returns>
        public static DataSet ExecuteDataSet(string sqlStr, IDataParameter[] sqlPara = null)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                conn.Open();
                return conn.GetSqlResultToDs(sqlStr, sqlPara);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn.ConnState == ConnectionState.Open) conn.Close();
            }
        }
        public static DataSet ExecuteDataSet(string sqlStr,ClientRequestEntity cre)
        {
            return ExecuteDataSet(sqlStr, GetParameter(cre));
        }

        /// <summary>
        /// 通用SQL语句执行获取DataTable方法；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sqlStr, IDataParameter[] sqlPara = null)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                conn.Open();
                return conn.GetSqlResultToDt(sqlStr, sqlPara);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn.ConnState == ConnectionState.Open) conn.Close();
            }
        }
        public static DataTable ExecuteDataTable(string sqlStr, ClientRequestEntity cre)
        {
            return ExecuteDataTable(sqlStr, GetParameter(cre));
        }

        /// <summary>
        /// 通用SQL语句执行获取单行单列值方法；
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <param name="sqlPara">SQL参数</param>
        /// <returns></returns>
        public static object ExecuteScalar(string sqlStr, IDataParameter[] sqlPara = null)
        {
            IDBConnection conn = null;
            try
            {
                conn = ClsDbFactory.CreateDBConnection(DataBaseType.ORACLE, DataManager.ConnectionString);
                conn.Open();
                return conn.GetSqlResultToObj(sqlStr, sqlPara);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                if (conn.ConnState == ConnectionState.Open) conn.Close();
            }
        }
        public static object ExecuteScalar(string sqlStr, ClientRequestEntity cre)
        {
            return ExecuteScalar(sqlStr, GetParameter(cre));
        }

        /// <summary>
        /// 按IDataParameter定义的字段，生成sql语句，不支持多表关联，可用视图代替
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="opt">操作：insert、update、delete</param>
        /// <param name="fields">字段键值对</param>
        /// <returns></returns>
        public static string MakeSql(string tableName, string opt, IDataParameter[] sqlPara,string orderBy = "")
        {
            string sqlStr = "";

            //生成插入语句
            if (opt.ToLower() == "insert")
            {
                string insertField = "";
                string insertValue = "";
                for(int i=0;i<sqlPara.Length;i++)
                {
                    if (insertField != "") insertField += ",";
                    insertField += sqlPara[i].ParameterName.ToString().Replace(":", "");
                    if (insertValue != "") insertValue += ",";
                    insertValue += sqlPara[i].ParameterName.ToString();
                }
                sqlStr = "insert into " + tableName + "(" + insertField + ") values (" + insertValue + ")";
            }

            //生成更新语句
            if (opt.ToLower() == "update")
            {
                string updateStr = "";
                string keyStr = GetKey(tableName);
                for (int i = 0; i < sqlPara.Length; i++)
                {
                    if (updateStr != "") updateStr += ",";
                    updateStr += sqlPara[i].ParameterName.ToString().Replace(":", "") + " = " + sqlPara[i].ParameterName;
                }
                sqlStr = "update " + tableName + " set " + updateStr;
                sqlStr += " where " + keyStr + " = :" + keyStr;
            }

            //生成删除语句
            if (opt.ToLower() == "delete")
            {
                string deleteStr = "";
                for (int i = 0; i < sqlPara.Length; i++)
                {
                    if (deleteStr != "") deleteStr += " and ";
                    deleteStr += sqlPara[i].ParameterName.ToString().Replace(":", "") + " = " + sqlPara[i].ParameterName;
                }
                sqlStr = "delete " + tableName + " where " + deleteStr;
            }

            //生成搜索语句
            if(opt.ToLower() == "search")
            {
                sqlStr = "select * from " + tableName;
                if(sqlPara!=null)
                {
                    string searchStr = "";
                    for (int i = 0; i < sqlPara.Length; i++)
                    {
                        if (searchStr != "") searchStr += " and ";
                        //如果定义最小值或最大值
                        if (sqlPara[i].ParameterName.ToLower().IndexOf("min") + 3 == sqlPara[i].ParameterName.Length)
                        {
                            searchStr += sqlPara[i].ParameterName.Replace(":", "").Substring(0, sqlPara[i].ParameterName.Length - 4)
                                      + " >= " + sqlPara[i].ParameterName;
                        }
                        else if (sqlPara[i].ParameterName.ToLower().IndexOf("max") + 3 == sqlPara[i].ParameterName.Length)
                        {
                            searchStr += sqlPara[i].ParameterName.ToString().Replace(":", "").Substring(0, sqlPara[i].ParameterName.Length - 4)
                                      + " <= " + sqlPara[i].ParameterName;
                        }
                        else
                        {
                            if (sqlPara[i].DbType == DbType.String)
                                //字符串类型处理
                                searchStr += sqlPara[i].ParameterName.ToString().Replace(":", "") + " like '%' || " + sqlPara[i].ParameterName + " || '%'";
                            else
                                //数值和bool类型处理
                                searchStr += sqlPara[i].ParameterName.ToString().Replace(":", "") + " = " + sqlPara[i].ParameterName;
                        }

                    }
                    sqlStr += " where " + searchStr;
                }
                if (orderBy != "") sqlStr += " order by " + orderBy;

            }

            return sqlStr;
        }

        /// <summary>
        /// 将cre.Properties转换为IDataParameter[]
        /// </summary>
        /// <param name="cre">ClientRequestEntity</param>
        /// <returns>IDataParameter[]</returns>
        public static IDataParameter[] GetParameter(ClientRequestEntity cre)
        {
            if (cre.Properties.Count>0)
            {
                List<OracleParameter> para = new List<OracleParameter>();
                foreach (var item in cre.Properties)
                {
                    para.Add(new OracleParameter(":" + item.Key.ToString(), item.Value));
                }
                return para.ToArray();
            }
            else
                return null;

        }

        /// <summary>
        /// 获取表主键列名
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>主键列名</returns>
        public static string GetKey(string tableName)
        {
            object key = ExecuteScalar($@"SELECT column_name 
                                         FROM user_cons_columns 
                                         WHERE constraint_name = ( SELECT constraint_name 
                                                                   FROM user_constraints 
                                                                   WHERE table_name = '{tableName}' 
                                                                   AND constraint_type = 'P' )");
            return key is object ? key.ToString() : "";
        }
    }
}