Beispiel #1
0
        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 + "");
            ////}
        }
Beispiel #2
0
        /// <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();
            }
        }