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