/// <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); } }
/// <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); }
/// <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); } }