mes-client/UAS_MesInterface/DataHelper.cs

using Oracle.ManagedDataAccess.Client;
using System;
using System.Data;
using System.Text;
using System.Runtime.InteropServices;

namespace DllService
{
    [Guid("67D63258-6F70-400C-BE1A-1AFE54384004")]
    [InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
    private interface IDataHelper
    {
        [DispId(8)]
        object getFieldDataByCondition(string TableName, string Field, string Condition);
        DataTable getFieldsDataByCondition(string TableName, string[] Fields, string Condition);
        DataTable getFieldsDatas(string TableName, string Fields);
        bool CheckExist(string TableName, string Condition);
        object ExecuteSql(string SQL, string Type, params object[] names);
        void BatchInsert(string sql, params object[][] names);
        string UpdateByCondition(string TableName, string update, string condition);
        void CallProcedure(string ProcedureName, ref string[] param);
    }

    [Guid("1886A2C6-7BDF-4990-A9B9-F3228DEC0906")]
    [ClassInterface(ClassInterfaceType.None)]
    [ComSourceInterfaces(typeof(IDataHelper))]
    [ProgId("DllService.DataHelper")]
    private class DataHelper : IDataHelper
    {
        //系统默认的的连接字符串
        private string ConnectionStrings = "";
        //用户选择的数据库的连接字符串
        private OracleConnection connection = null;
        //用户选择的数据库的连接字符串
        OracleCommand command =null;

        private DataHelper()
        {
            string Environment = "MES_TEST";
            try
            {
                //如果选择的是默认数据则直接用配置文件的信息连接，否则选择数据库的账套信息
                if (Environment.ToUpper() == "MES")
                {
                    ConnectionStrings = "Password=select!#%*(;User ID=MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=117.25.180.218)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
                    connection = new OracleConnection(ConnectionStrings);
                }
                else if (Environment.ToUpper() == "MES_TEST")
                {
                    ConnectionStrings = "Password=select!#%*(;User ID=MES_TEST;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=117.25.180.218)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
                    connection = new OracleConnection(ConnectionStrings);
                }
                command.Connection = connection;
                command.Connection.Open();
                command.CommandTimeout = 0;
            }
            catch (Exception)
            {
                if (Environment.ToUpper() == "MES")
                {
                    ConnectionStrings = "Password=select!#%*(;User ID=MES;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=117.25.180.218)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
                    connection = new OracleConnection(ConnectionStrings);
                }
                else if (Environment.ToUpper() == "MES_TEST")
                {
                    ConnectionStrings = "Password=select!#%*(;User ID=MES_TEST;Pooling=false;Data Source=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=117.25.180.218)(PORT=1521)))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=orcl)));";
                    connection = new OracleConnection(ConnectionStrings);
                }
                try
                {
                    command.Connection = connection;
                    command.Connection.Open();
                }
                catch (Exception)
                {
                    return;
                }
                command.CommandTimeout = 0;
            }
        }

        /// <summary>
        /// 获取第一行第一列的信息
        /// </summary>
        private object getFieldDataByCondition(string TableName, string Field, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select " + Field + " from " + TableName + " where " + Condition;
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            OracleDataAdapter ad = new OracleDataAdapter();
            ad.SelectCommand = command;
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                connection = new OracleConnection(ConnectionStrings);
                connection.Open();
                command = new OracleCommand(sql, connection);
                ad = new OracleDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            command.Dispose();
            if (dt.Rows.Count > 0)
            {
                return dt.Rows[0][0];
            }
            else
            {
                return "";
            }
        }


        /// <summary>
        /// 通过表名和获取单行的记录
        /// </summary>
        private DataTable getFieldsDataByCondition(string TableName, string[] Fields, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName + " where " + Condition + " and rownum=1";
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                connection = new OracleConnection(ConnectionStrings);
                connection.Open();
                command = new OracleCommand(sql, connection);
                ad = new OracleDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            command.Dispose();
            return dt;
        }

        /// <summary>
        /// 通过表名，字段和条件获取DataTable类型的数据
        /// </summary>
        private DataTable getFieldsDatasByCondition(string TableName, string[] Fields, string Condition)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += AddField(Fields);
            sql += " from " + TableName + " where " + Condition;
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                connection = new OracleConnection(ConnectionStrings);
                connection.Open();
                command = new OracleCommand(sql, connection);
                ad = new OracleDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            command.Dispose();
            return dt;
        }

        /// <summary>
        /// 通过表名，字段获取DataTable类型的数据
        /// </summary>
        private DataTable getFieldsDatas(string TableName, string Fields)
        {
            DataTable dt = new DataTable();
            string sql = "select ";
            sql += Fields;
            sql += " from " + TableName;
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            ad.SelectCommand = command;
            try
            {
                ad.Fill(dt);
            }
            catch (Exception)
            {
                connection = new OracleConnection(ConnectionStrings);
                connection.Open();
                command = new OracleCommand(sql, connection);
                ad = new OracleDataAdapter();
                ad.SelectCommand = command;
                ad.Fill(dt);
            }
            ad.Dispose();
            command.Dispose();
            return dt;
        }

        /// <summary>
        /// 检测内容是否存在
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="Condition"></param>
        /// <returns></returns>
        private bool CheckExist(string TableName, string Condition)
        {
            string sql = "select count(1) from " + TableName + " where " + Condition;
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            OracleDataAdapter ad = new OracleDataAdapter(command);
            DataTable dt = new DataTable();
            ad.Fill(dt);
            ad.Dispose();
            command.Dispose();
            return int.Parse(dt.Rows[0][0].ToString()) > 0;
        }

        /// <summary>
        /// 直接执行SQL，同时传入SQL的类型
        /// </summary>
        /// <param name="SQL"></param>
        /// <param name="Type"></param>
        /// <returns></returns>
        private object ExecuteSql(string SQL, string Type, params object[] names)
        {
            object result = null;
            command = new OracleCommand(SQL, connection);
            Reconnect(command);
            //用来拼接参数的
            if (names.Length > 0)
            {
                string[] par = SQL.Split(':');
                //用来存参数的数组
                StringBuilder[] addpar = new StringBuilder[par.Length - 1];
                for (int i = 0; i < par.Length - 1; i++)
                {
                    //新建一个char类型的数组用来存储每个字节的变量
                    char[] c = par[i + 1].ToCharArray();
                    addpar[i] = new StringBuilder();
                    for (int j = 0; j < c.Length; j++)
                    {
                        if (c[j] != ' ' && c[j] != ',')
                        {
                            addpar[i].Append(c[j]);
                        }
                        else
                        {
                            break;
                        }
                    }
                }
                for (int i = 0; i < addpar.Length; i++)
                    command.Parameters.Add(new OracleParameter(addpar[i].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input));
            }
            switch (Type.ToUpper())
            {
                case "SELECT":
                    OracleDataAdapter ad = new OracleDataAdapter(command);
                    result = new DataTable();
                    try
                    {
                        ad.Fill((DataTable)result);
                    }
                    catch (Exception)
                    {
                        connection = new OracleConnection(ConnectionStrings);
                        connection.Open();
                        command = new OracleCommand(SQL, connection);
                        ad = new OracleDataAdapter();
                        ad.SelectCommand = command;
                        ad.Fill((DataTable)result);
                    }
                    break;
                case "DELETE":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new OracleConnection(ConnectionStrings);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
                case "UPDATE":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new OracleConnection(ConnectionStrings);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
                case "INSERT":
                    try
                    {
                        result = command.ExecuteNonQuery();
                    }
                    catch (Exception)
                    {
                        command.Connection = new OracleConnection(ConnectionStrings);
                        command.Connection.Open();
                        result = command.ExecuteNonQuery();
                    }
                    break;
            }
            command.Dispose();
            return result;
        }

        /// <summary>
        /// 批量通过SQL来执行插入操作 ，参数的第一个数一个string[]数组，用来传递需要添加的参数的名称
        /// 之后的是名称参数数组对应的 ，所有的插入参数数据长度必须是一致的
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="names"></param>
        private void BatchInsert(string sql, params object[][] names)
        {
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            command.ArrayBindCount = names[1].Length;
            //因为第一个数组保存的是参数的名称，所以循环从1而不是0开始
            //将第一个数组的下标固定为0作为循环添加的参数的名称
            for (int i = 1; i <= names[0].Length; i++)
            {
                command.Parameters.Add(new OracleParameter(names[0][i - 1].ToString(), OracleDbType.Varchar2, names[i], ParameterDirection.Input));
            }
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                command.Connection = new OracleConnection(ConnectionStrings);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            command.Dispose();
        }

        /// <summary>
        /// 通过条件更新
        /// </summary>
        /// <param name="TableName"></param>
        /// <param name="update"></param>
        /// <param name="condition"></param>
        private string UpdateByCondition(string TableName, string update, string condition)
        {
            string sql = "update " + TableName + " set " + update + " where " + condition;
            command = new OracleCommand(sql, connection);
            Reconnect(command);
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                command.Connection = new OracleConnection(ConnectionStrings);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            command.Dispose();
            return sql;
        }

        /// <summary>
        /// 调用存储过程
        /// </summary>
        /// <param name="ProcedureName"></param>    
        /// <param name="param"></param>
        private void CallProcedure(string ProcedureName, ref string[] param)
        {
            command = new OracleCommand(ProcedureName);
            command.Connection = connection;
            Reconnect(command);
            command.CommandText = ProcedureName;
            command.CommandType = CommandType.StoredProcedure;
            for (int i = 0; i < param.Length; i++)
                command.Parameters.Add(new OracleParameter(param[i].ToString(), OracleDbType.Varchar2, 200, param[i], ParameterDirection.InputOutput));
            try
            {
                command.ExecuteNonQuery();
            }
            catch (Exception)
            {
                command.Connection = new OracleConnection(ConnectionStrings);
                command.Connection.Open();
                command.ExecuteNonQuery();
            }
            for (int i = 0; i < command.Parameters.Count; i++)
                param[i] = command.Parameters[i].Value.ToString();
        }

        /// <summary>
        /// 用于将string 的数组转换成SQL的查询内容
        /// </summary>
        /// <param name="Fields"></param>
        /// <returns></returns>
        private string AddField(string[] Fields)
        {
            string sql = " ";
            foreach (string field in Fields)
            {
                sql += field + ",";
            }
            return sql.Substring(0, sql.Length - 1);
        }

        /// <summary>
        /// 通过查询的语句获取查询的字段
        /// </summary>
        /// <param name="field"></param>
        /// <returns></returns>
        private string[] GetField(string field)
        {
            string[] fields = field.Split(',');
            for (int i = 0; i < fields.Length; i++)
            {
                fields[i] = fields[i].Substring(fields[i].LastIndexOf("as") + 2, fields[i].Length - fields[i].LastIndexOf("as") - 2).Trim();
            }
            return fields;
        }

        private void Reconnect(OracleCommand cmd)
        {
            if (cmd.Connection.State == ConnectionState.Closed)
            {
                cmd.Connection.Open();
            }
        }
    }
}