public static object ExecuteScalar(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
LogSql("ExecuteScalar", commandType, commandText, commandParameters);
object result = null;
if (transaction is SqlTransaction)
{
result = I3SqlHelper.ExecuteScalar(transaction as SqlTransaction, commandType, commandText, ToDBParamArray <SqlParameter>(commandParameters));
}
else if (transaction is OracleTransaction)
{
result = I3OracleHelper.ExecuteScalar(transaction as OracleTransaction, commandType, commandText, ToDBParamArray <OracleParameter>(commandParameters));
}
else
{
result = I3MySqlHelper.ExecuteScalar(transaction as MySqlTransaction, commandType, commandText, ToDBParamArray <MySqlParameter>(commandParameters));
}
LogSqlResult(result == null ? "null" : result.ToString());
return(result);
}
public static int ExecuteNonQuery(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
LogSql("ExecuteNonQuery", commandType, commandText, commandParameters);
int result = I3DBUtil.INVALID_ID;
if (transaction is OracleTransaction)
{
result = I3SqlHelper.ExecuteNonQuery(transaction as SqlTransaction, commandType, commandText, ToDBParamArray <SqlParameter>(commandParameters));
}
else if (transaction is OracleTransaction)
{
result = I3OracleHelper.ExecuteNonQuery(transaction as OracleTransaction, commandType, commandText, ToDBParamArray <OracleParameter>(commandParameters));
}
else
{
result = I3MySqlHelper.ExecuteNonQuery(transaction as MySqlTransaction, commandType, commandText, ToDBParamArray <MySqlParameter>(commandParameters));
}
LogSqlResult(result.ToString());
return(result);
}
private static DbDataReader ExecuteReader(IDbConnection connection, IDbTransaction transaction, CommandType commandType, string commandText, IDbDataParameter[] commandParameters, I3DbConnectionOwnership connectionOwnership)
{
LogSql("ExecuteReader", commandType, commandText, commandParameters);
DbDataReader reader = null;
if (connection is SqlConnection)
{
reader = I3SqlHelper.ExecuteReader(connection as SqlConnection, transaction as SqlTransaction, commandType, commandText, ToDBParamArray <SqlParameter>(commandParameters), connectionOwnership);
}
else if (connection is OracleConnection)
{
reader = I3OracleHelper.ExecuteReader(connection as OracleConnection, transaction as OracleTransaction, commandType, commandText, ToDBParamArray <OracleParameter>(commandParameters), connectionOwnership);
}
else
{
reader = I3MySqlHelper.ExecuteReader(connection as MySqlConnection, transaction as MySqlTransaction, commandType, commandText, ToDBParamArray <MySqlParameter>(commandParameters), connectionOwnership);
}
LogSqlResult(reader.HasRows.ToString());
return(reader);
}
public static DataSet ExecuteDataset(IDbTransaction transaction, CommandType commandType, string commandText, params IDbDataParameter[] commandParameters)
{
LogSql("ExecuteDataset", commandType, commandText, commandParameters);
DataSet dataSet = null;
if (transaction is SqlTransaction)
{
dataSet = I3SqlHelper.ExecuteDataset(transaction as SqlTransaction, commandType, commandText, ToDBParamArray <SqlParameter>(commandParameters));
}
else if (transaction is OracleTransaction)
{
dataSet = I3OracleHelper.ExecuteDataset(transaction as OracleTransaction, commandType, commandText, ToDBParamArray <OracleParameter>(commandParameters));
}
else
{
dataSet = I3MySqlHelper.ExecuteDataset(transaction as MySqlTransaction, commandType, commandText, ToDBParamArray <MySqlParameter>(commandParameters));
}
LogSqlResult(dataSet == null || dataSet.Tables.Count == 0 ? "0" : dataSet.Tables[0].Rows.Count.ToString());
return(dataSet);
}
/// <summary>
/// 连续增加序列计数，返回最后一个值
/// 快速处理序列的方法
/// </summary>
/// <param name="sSeqName">序列名称</param>
/// <param name="iSeqCount">系列计数</param>
/// <returns>返回最后一个值</returns>
public static int BatchGetSequence(string sSeqName, int iSeqCount, string connection)
{
if (iSeqCount == 1)
{
try
{
return(I3DBUtil.ToInt(I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "Select " + sSeqName + ".NextVal From Dual")));
}
catch
{
I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "create sequence " + sSeqName);
return(I3DBUtil.ToInt(I3OracleHelper.ExecuteScalar(connection, CommandType.Text, "Select " + sSeqName + ".NextVal From Dual")));
}
}
//通过存储过程增加序列值
string sSql = "select BatchUpdateSequence(:W_SEQ_NAME,:W_SEQ_COUNT) from dual";
OracleConnection sqlCon = I3DBUtil.CreateAndOpenDbConnection(connection) as OracleConnection;
sqlCon.Open();
try
{
OracleCommand sqlCom = new OracleCommand(sSql, sqlCon);
sqlCom.Parameters.Add(":W_SEQ_NAME", OracleType.VarChar);
sqlCom.Parameters.Add(":W_SEQ_COUNT", OracleType.Int32);
sqlCom.Parameters[0].Value = sSeqName;
sqlCom.Parameters[1].Value = iSeqCount;
int iLastSeq = -1;
try
{
//选择序列值
iLastSeq = I3DBUtil.ToInt(sqlCom.ExecuteScalar());
}
catch (Exception ex)
{
//添加存储过程
StringBuilder sDDLSql = new StringBuilder();
sDDLSql.Append("create or replace function BatchUpdateSequence(seqName in varchar2, iCount in integer) return integer is ").
Append("Result1 integer; ").
Append("iIndex integer; ").
Append("sSql varchar2(200); ").
Append("begin ").
Append("iIndex := 0; ").
Append("LOOP ").
Append("sSql := 'Select '||seqName||'.NextVal From Dual' ; ").
Append("execute immediate sSql ").
Append(" into Result1; ").
Append("iIndex := iIndex+1; ").
Append("EXIT When iIndex=iCount; ").
Append("End Loop; ").
Append("return (Result1); ").
Append("end BatchUpdateSequence; ");
OracleCommand sqlCom1 = new OracleCommand(sDDLSql.ToString(), sqlCon);
sqlCom1.ExecuteNonQuery();
iLastSeq = I3DBUtil.ToInt(sqlCom.ExecuteScalar());
}
return(iLastSeq - iSeqCount + 1);
}
finally
{
sqlCon.Close();
}
}