public static int BatchGetSequence(string sSeqName, int iSeqCount, string connection)
{
string sTableName = sSeqName.StartsWith("SEQ_") ? sSeqName.Substring(4) : sSeqName;
if (true)//trans == null)
{
object obj = I3DbHelper.ExecuteScalar(DBServerType.SqlServer, connection, CommandType.Text, "Select NEXT_VALUE From INS_SEQUENCE Where TABLE_NAME=@W_TABLE_NAME;Update INS_SEQUENCE Set NEXT_VALUE=NEXT_VALUE+:W_ITEMCOUNT Where TABLE_NAME=@W_TABLE_NAME;", new SqlParameter("@W_TABLE_NAME", sTableName), new SqlParameter("@W_ITEMCOUNT", iSeqCount));
if (obj == null || obj == DBNull.Value)
{
I3DbHelper.ExecuteNonQuery(DBServerType.SqlServer, connection, CommandType.Text, "Insert INS_SEQUENCE(TABLE_NAME,NEXT_VALUE) Values(:V_TABLE_NAME,:W_ITEMCOUNT)", new SqlParameter("@V_TABLE_NAME", sTableName), new SqlParameter("@W_ITEMCOUNT", iSeqCount));
return(1);
}
return(I3DBUtil.ToInt(obj));
}
////else
////{
//// object obj = DbHelper.ExecuteScalar(trans, CommandType.Text, "Select NEXT_VALUE From INS_SEQUENCE Where TABLE_NAME=@W_TABLE_NAME;Update INS_SEQUENCE Set NEXT_VALUE=NEXT_VALUE+1 Where TABLE_NAME=@W_TABLE_NAME;", new SqlParameter("@W_TABLE_NAME", sTableName));
//// if (obj == null || obj == DBNull.Value)
//// {
//// DbHelper.ExecuteNonQuery(trans, CommandType.Text, "Insert INS_SEQUENCE(TABLE_NAME,NEXT_VALUE) Values(:V_TABLE_NAME,1)", new SqlParameter("@V_TABLE_NAME", sTableName));
//// return 1;
//// }
//// return iId = Int32.Parse(obj + "");
////}
}
/// <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();
}
}