/// <summary>
        /// 数据阅读器
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public IDataReader ExecuteReader(CommandType cmdType, string cmdText, List <SqlParameter> spr)
        {
            List <OracleParameter> listParam = SqlConvertHelper.GetOracleParams(spr);
            OracleConnection       conn      = GetConnection(false);

            conn.Open();
            OracleCommand cmd = new OracleCommand(cmdText, conn);

            try
            {
                cmd.CommandType    = cmdType;
                cmd.CommandTimeout = 300;
                if (spr != null)
                {
                    foreach (OracleParameter s in listParam)
                    {
                        cmd.Parameters.Add(s);
                    }
                }
                OracleDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return(dr);
            }
            catch (Exception ex)
            {
                _logger.Error(ex.Message, ex);
            }
            finally
            {
                cmd.Parameters.Clear();
            }
            return(null);
        }
        /// <summary>
        /// 获取数据
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public DataSet GetDataSet(CommandType cmdType, string cmdText, List <SqlParameter> spr)
        {
            DataSet ds = new DataSet();
            List <OracleParameter> listParam = SqlConvertHelper.GetOracleParams(spr);

            using (OracleConnection conn = GetConnection(false))
            {
                conn.Open();
                OracleDataAdapter oda = new OracleDataAdapter(cmdText, conn);
                try
                {
                    oda.SelectCommand.CommandType    = cmdType;
                    oda.SelectCommand.CommandTimeout = 300;
                    if (spr != null)
                    {
                        foreach (OracleParameter s in listParam)
                        {
                            oda.SelectCommand.Parameters.Add(s);
                        }
                    }
                    oda.Fill(ds);
                }
                catch (Exception ex)
                {
                    _logger.Error(ex.Message, ex);
                }
                finally
                {
                    oda.SelectCommand.Parameters.Clear();
                    conn.Close();
                }
                return(ds);
            }
        }
예제 #3
0
        /// <summary>
        /// 带参数的存储过程
        /// </summary>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public static List <Dictionary <string, object> > ExecuteNonQueryPro(string cmdText, List <SqlParameter> spr)
        {
            List <Dictionary <string, object> > resDics = new List <Dictionary <string, object> >();
            var connDics = new ConnectionHelper().GetOrgConnDic();

            foreach (var dic in connDics)
            {
                Dictionary <string, object> resDic = new Dictionary <string, object>();
                resDic["code"] = dic["code"];
                resDic["name"] = dic["name"];

                ISqlHelper sqlHelper = SqlConvertHelper.GetInstallSqlHelper(dic["connectionstring"]);
                resDic["data"] = sqlHelper.ExecuteNonQueryPro(cmdText, spr);

                resDics.Add(resDic);
            }
            return(resDics);
        }
예제 #4
0
        /// <summary>
        /// 不带参数的文本查询
        /// </summary>
        /// <param name="cmdText"></param>
        /// <returns></returns>
        public static List <Dictionary <string, object> > GetDataSet(string cmdText)
        {
            List <Dictionary <string, object> > resDics = new List <Dictionary <string, object> >();
            var connDics = new ConnectionHelper().GetOrgConnDic();

            foreach (var dic in connDics)
            {
                Dictionary <string, object> resDic = new Dictionary <string, object>();
                resDic["code"] = dic["code"];
                resDic["name"] = dic["name"];

                ISqlHelper sqlHelper = SqlConvertHelper.GetInstallSqlHelper(dic["connectionstring"]);
                resDic["data"] = sqlHelper.GetDataSet(cmdText);

                resDics.Add(resDic);
            }
            return(resDics);
        }
        /// <summary>
        /// 返回影响的行数
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string cmdText, List <SqlParameter> spr)
        {
            int i = 0;
            List <MySqlParameter> listParam = SqlConvertHelper.GetMySqlParams(spr);

            using (MySqlConnection conn = GetConnection())
            {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand(cmdText, conn);
                //事物
                MySqlTransaction trans = conn.BeginTransaction(IsolationLevel.ReadCommitted);
                try
                {
                    cmd.Parameters.Clear();
                    cmd.CommandType    = commandType;
                    cmd.CommandTimeout = 300;
                    cmd.Transaction    = trans;
                    if (spr != null)
                    {
                        foreach (MySqlParameter s in listParam)
                        {
                            cmd.Parameters.Add(s);
                        }
                        ;
                    }
                    i = cmd.ExecuteNonQuery();
                    trans.Commit();
                }
                catch (Exception ex)
                {
                    trans.Rollback();
                    _logger.Error(ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                    cmd.Parameters.Clear();
                }
                return(i);
            }
        }
        /// <summary>
        /// 返回影响的行数
        /// </summary>
        /// <param name="commandType"></param>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public int ExecuteNonQuery(CommandType commandType, string cmdText, List <SqlParameter> spr)
        {
            int i = 0;
            List <OracleParameter> listParam = SqlConvertHelper.GetOracleParams(spr);

            using (OracleConnection conn = GetConnection())
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(cmdText, conn);
                try
                {
                    cmd.Parameters.Clear();
                    cmd.CommandType    = commandType;
                    cmd.CommandTimeout = 300;
                    if (spr != null)
                    {
                        foreach (OracleParameter s in listParam)
                        {
                            cmd.Parameters.Add(s);
                        }
                        ;
                    }
                    i = cmd.ExecuteNonQuery();
                }
                catch (Exception ex)
                {
                    _logger.Error(ex.Message, ex);
                }
                finally
                {
                    conn.Close();
                    cmd.Parameters.Clear();
                }
                return(i);
            }
        }
        /// <summary>
        /// 返回第一行第一列的值
        /// </summary>
        /// <param name="cmdType"></param>
        /// <param name="cmdText"></param>
        /// <param name="spr"></param>
        /// <returns></returns>
        public string ExecuteScalar(CommandType cmdType, string cmdText, List <SqlParameter> spr)
        {
            List <OracleParameter> listParam = SqlConvertHelper.GetOracleParams(spr);

            using (OracleConnection conn = GetConnection(false))
            {
                conn.Open();
                OracleCommand cmd = new OracleCommand(cmdText, conn);
                try
                {
                    cmd.CommandType    = cmdType;
                    cmd.CommandTimeout = 300;
                    if (spr != null)
                    {
                        foreach (OracleParameter s in listParam)
                        {
                            cmd.Parameters.Add(s);
                        }
                    }
                    if (cmd.ExecuteScalar() != null)
                    {
                        return(cmd.ExecuteScalar().ToString());
                    }
                }
                catch (Exception ex)
                {
                    _logger.Error(ex.Message, ex);
                }
                finally
                {
                    cmd.Parameters.Clear();
                    conn.Close();
                }
                return(null);
            }
        }