/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public override IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
try
{
cnn = (OracleConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, null, paras);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
cnn.Close();
cnn.Dispose();
}
return(reader);
}
/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public override IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
try
{
cmd = new MySqlCommand();
cmd.CommandText = sql;
if (sqlexectype == SqlExecType.SqlText)
{
cmd.CommandType = CommandType.Text;
}
else if (sqlexectype == SqlExecType.SqlProcName)
{
cmd.CommandType = CommandType.StoredProcedure;
}
cmd.Connection = (MySqlConnection)conn;
//这里可能需要兼容处理大数据字段，暂时先不处理，看看参数方式是否直接可以
this.PrepareCommand(cmd, paras);
if (trans != null)
{
cmd.Transaction = (MySqlTransaction)trans;
}
}
catch (Exception e)
{
throw new Exception("create command error, please check sql script:" + sql, e);
}
return((IDbCommand)cmd);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataSet GetDataSet(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = new DataSet();
try
{
cnn = (OracleConnection)this.GetConnection();
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, cnn, null, paras);
adapter = new OracleDataAdapter(cmd);
adapter.Fill(ds, "tableName");
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
//mqg于20180928增加，这里不在释放连接，交给连接池管理
//adapter.Dispose();
//cnn.Close();
//cnn.Dispose();
}
return(ds);
}
/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
object tempobj = 0;
try
{
if (trans == null)
{
cnn = (SqlConnection)this.GetConnection();
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}
tempobj = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
//mqg于20180928增加，这里不在释放连接，交给连接池管理
//if (trans == null && cnn != null)
//{
// cnn.Close();
// cnn.Dispose();
//}
}
return(tempobj);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataSet GetDataSet(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = new DataSet();
try
{
cnn = (MySqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, cnn, null, paras);
adapter = new MySqlDataAdapter(cmd);
adapter.Fill(ds, "tableName");
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
adapter.Dispose();
cnn.Close();
cnn.Dispose();
}
return(ds);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// 单独服务于多线程处理，多线程且高频访问的业务查询，mqg于20181106增加
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataSet GetDataSetThreadSafe(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = new DataSet();
//为了不独立实现支持多线程的处理，又能允许多线程应用，只能牺牲资源每次新建连接
SqlDataAdapter adapterTmp = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(base.ConnectionString);
try
{
connection.Open();
SqlCommand cmdTmp = (SqlCommand)this.GetCommand(sql, sqlexectype, connection, null, paras);
adapterTmp.SelectCommand = cmdTmp;
adapterTmp.Fill(ds, "tableName");
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
adapterTmp.Dispose();
connection.Close();
connection.Dispose();
}
return(ds);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataSet GetDataSet(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = new DataSet();
//mqg于20181104增加，单独处理MS SQL查询，不进入连接池管理, 不然多线程处理有时会报错
//try
//{
// cnn = (SqlConnection)this.GetConnection();
// cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, cnn, null, paras);
// adapter = new SqlDataAdapter(cmd);
// adapter.Fill(ds, "tableName");
//}
//catch (Exception err)
//{
// throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
//}
//finally
//{
// adapter.Dispose();
// cnn.Close();
// cnn.Dispose();
//}
//为了不独立实现支持多线程的处理，又能允许多线程应用，只能牺牲资源每次新建连接
//sql server需要特殊处理，必须的关闭连接才能释放command，所以不能只接用连接池的连接
SqlDataAdapter adapterTmp = new SqlDataAdapter();
SqlConnection connection = new SqlConnection(base.ConnectionString);
try
{
connection.Open();
SqlCommand cmdTmp = (SqlCommand)this.GetCommand(sql, sqlexectype, connection, null, paras);
adapterTmp.SelectCommand = cmdTmp;
adapterTmp.Fill(ds, "tableName");
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
adapterTmp.Dispose();
connection.Close();
connection.Dispose();
}
//这种方式不满足多线程
//using (SqlConnection connection = new SqlConnection(base.ConnectionString))
//{
// SqlDataAdapter adapter = new SqlDataAdapter();
// SqlCommand cmdTmp = (SqlCommand)this.GetCommand(sql, sqlexectype, connection, null, paras);
// adapter.SelectCommand = cmdTmp;
// adapter.Fill(ds);
//}
return(ds);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public override DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
DataSet ds = this.GetDataSet(sql, sqlexectype, paras);
if (ds.Tables.Count > 0)
{
return(ds.Tables[0]);
}
else
{
return(null);
}
}
/// <summary>
/// 执行SQL语句。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
public override int ExecuteNonQuery(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
int tempint = 0;
try
{
if (trans == null)
{
cnn = (OracleConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (OracleCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}
tempint = cmd.ExecuteNonQuery();
//清除掉参数，以免二次使用.net取缓冲导致报错
cmd.Parameters.Clear();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}
return(tempint);
}
/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public override Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
object tempobj = 0;
try
{
if (trans == null)
{
cnn = (MySqlConnection)this.GetConnection();
if (cnn.State == ConnectionState.Closed)
{
cnn.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, (IDbTransaction)trans, paras);
}
else
{
if (trans.Connection.State == ConnectionState.Closed)
{
trans.Connection.Open();
}
cmd = (MySqlCommand)this.GetCommand(sql, sqlexectype, trans.Connection, trans, paras);
}
tempobj = cmd.ExecuteScalar();
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
if (trans == null && cnn != null)
{
cnn.Close();
cnn.Dispose();
}
}
return(tempobj);
}
/// <summary>
/// 读写的负载均衡
/// </summary>
/// <param name="sqlExec"></param>
/// <returns></returns>
public string FZDHSqlStr(SqlExecType sqlExec, GetSqlMethod sqlMethod)
{
switch (sqlExec)
{
case SqlExecType.Read:
switch (sqlMethod)
{
case GetSqlMethod.FZ: //负载均衡
return(GetFZRead());
break;
case GetSqlMethod.QZ: //权重
GetQZRead();
break;
case GetSqlMethod.RoundRobin: //轮询
GetLXRead();
break;
default:
return(null);
break;
}
break;
case SqlExecType.Write:
return(WriteStr);
break;
default:
return(null);
break;
}
return(null);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public override IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
try
{
cnn = (SqlConnection)this.GetConnection();
cmd = (SqlCommand)this.GetCommand(sql, sqlexectype, (IDbConnection)cnn, null, paras);
reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception err)
{
throw new Exception("SQL query error!" + err.Message + "\r\n SQL script is：" + sql, err);
}
finally
{
//mqg于20180928增加，这里不在释放连接，交给连接池管理
reader.Close();
//cnn.Close();
//cnn.Dispose();
}
return(reader);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual DataTable GetDataTable(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
return(null);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到数据集中返回。
/// 单独服务于多线程处理，多线程且高频访问的业务查询，mqg于20181106增加
/// </summary>
/// <param name="sql"></param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual DataSet GetDataSetThreadSafe(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
return(null);
}
/// <summary>
/// 根据SQL语句进行查询，将查询结果保存到Read中返回。
/// </summary>
/// <param name="sql">要执行的SQL查询。</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="paras"></param>
/// <returns>返回一个包含查询结果的Read。</returns>
public virtual IDataReader GetReader(string sql, SqlExecType sqlexectype, params IDataParameter[] paras)
{
return(null);
}
/// <summary>
/// 执行SQL，返回查询结果
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns></returns>
public virtual Object ExecuteScalar(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
return(null);
}
/// <summary>
/// 执行SQL语句。
/// </summary>
/// <param name="sql"></param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
public virtual int ExecuteNonQuery(string sql, SqlExecType sqlexectype, IDbTransaction trans, params IDataParameter[] paras)
{
return(-1);
}
/// <summary>
/// 获得一个数据库命令对象。
/// </summary>
/// <param name="sql">要执行的SQL语句或存储过程名</param>
/// <param name="sqlexectype">执行SQL类型</param>
/// <param name="conn">数据库连接</param>
/// <param name="trans"></param>
/// <param name="paras"></param>
/// <returns>命令对象</returns>
public virtual IDbCommand GetCommand(string sql, SqlExecType sqlexectype, IDbConnection conn, IDbTransaction trans, params IDataParameter[] paras)
{
return(null);
}