生成流水号

调用存储过程

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go

ALTER PROCEDURE [dbo].[GetSequenceNumber] (  @Code nvarchar(10),  @Count int = 1 ) AS BEGIN  DECLARE @NewValue nvarchar(20), @CurrentDate nvarchar(8);  DECLARE @Prefix nvarchar(5), @DateType nvarchar(8), @Infix nvarchar(5), @Suffix nvarchar(5);  DECLARE @MaxIndex int, @IndexLength tinyint, @MaxDate nvarchar(8);

 BEGIN TRAN   --读取配置信息   SELECT  @Prefix = Prefix, @Infix = Infix, @Suffix = Suffix,     @DateType = DateType, @MaxDate=MaxDate,     @MaxIndex = MaxIndex, @IndexLength = IndexLength    FROM SequenceNumber with(xlock) WHERE Code=@Code;      --取得日期部分   SET @CurrentDate= SUBSTRING(Convert(nvarchar(8), GetDate(), 112),    CASE SubString(@DateType, 1, 4)     WHEN 'yyyy' THEN 1     WHEN 'yyy' THEN 2     ELSE 3    END, LEN(@DateType));      IF(@CurrentDate = @MaxDate)       SET @MaxIndex = @MaxIndex + @Count; --累加   ELSE    SET @MaxIndex = @Count; --归1         --超过自增长度限制，自动扩展自增部分的长度   IF(@MaxIndex >= POWER(10, @IndexLength))    SET @IndexLength = @IndexLength + 1;      --测试并发   --Waitfor delay '00:00:10';       Update SequenceNumber SET MaxDate = @CurrentDate, MaxIndex=@MaxIndex, IndexLength=@IndexLength WHERE Code=@Code;  COMMIT TRAN    --取得获取到的最大值  SELECT (@Prefix + @CurrentDate + @Infix + Replace(STR(@MaxIndex, @IndexLength), ' ' , '0') + @Suffix), @IndexLength, Len(@Suffix); END

 

方法执行调用存储过程

 

 /// <summary>     /// 获得流水号     /// 使用示例：     ///foreach (string item in SequenceNumber.GetSequenceNumbers(SequenceType.Test3, 1))     ///{     ///    Response.Write(item + "<br/>");     ///}     /// </summary>     /// <param name="type"></param>     /// <param name="count"></param>     /// <returns></returns>     public static ReadOnlyCollection<string> GetSequenceNumbers(SequenceType type, int count)     {         string maxSequenceNumber = string.Empty;         byte indexLength = 0;         byte suffixLength = 0;         using (SqlConnection conn = new SqlConnection(connstring))         {             conn.Open();             using (SqlCommand cmd = new SqlCommand())             {                 cmd.CommandText = "dbo.GetSequenceNumber";                 cmd.Connection = conn;                 cmd.CommandType = CommandType.StoredProcedure;                 cmd.Parameters.Add(new SqlParameter("@Code", type.ToString("G")));                 cmd.Parameters.Add(new SqlParameter("@Count", count));

                using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection))                 {                     reader.Read();                     maxSequenceNumber = Convert.ToString(reader[0]);                     indexLength = Convert.ToByte(reader[1]);                     suffixLength = Convert.ToByte(reader[2]);

                    reader.Close();                 }             }         }

        if (count == 1)         {             return (new List<string>() { maxSequenceNumber }).AsReadOnly();         }         else         {             string prefix = maxSequenceNumber.Substring(0, maxSequenceNumber.Length - indexLength - suffixLength);             int index = Convert.ToInt32(maxSequenceNumber.Substring(prefix.Length, indexLength));             string suffix = maxSequenceNumber.Substring(maxSequenceNumber.Length - suffixLength);

            string format = "0000000000".Substring(0, indexLength);             return Enumerable.Range(index - count + 1, count)                         .Select(i => prefix + i.ToString(format) + suffix)                         .ToList()                         .AsReadOnly();         }     }   

 

调用这个方法生成流水号

 //流水号
                                                        foreach (string lsh in SequenceNumber.GetSequenceNumbers(SequenceType.Test3, 1))
                                                        {
                                                            string LSH = lsh;
                                                        }