1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
sql语句：
/****** 对象:  Table [dbo].[yg_SequenceNumber]    脚本日期: 11/12/2010 11:01:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[yg_SequenceNumber](
    [AutoID] [int] IDENTITY(1,1) NOT NULL,
    [snID] [uniqueidentifier] NULL,
    [SNType] [int] NULL,
    [SNKey] [varchar](200) COLLATE Chinese_PRC_CI_AS NULL,
    [SN] [int] NULL,
    [UpdateDate] [datetime] NULL,
    [EmployeeID] [uniqueidentifier] NULL,
    [AddDate] [smalldatetime] NULL,
    [Flag] [int] NULL CONSTRAINT [DF_yg_SequenceNumber_Flag]  DEFAULT ((1))
) ON [PRIMARY]
 
GO
SET ANSI_PADDING OFF
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自增序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'AutoID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'唯一标识id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'snID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序号类型' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'SNType'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序号键值' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'SNKey'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'序号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'SN'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'更新日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'UpdateDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加者id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'EmployeeID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'添加日期' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'AddDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'状态标示 ，比如锁定、删除' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'yg_SequenceNumber', @level2type=N'COLUMN',@level2name=N'Flag'
 
 
public static string GetGenerateNumber(string snkey, int snType)
            {
                try
                {
                    string strSql = @"if exists(select 1 from GS_SequenceNumber with(xlock) where SNType = @SNType and SNKey = @SNKey)
                            begin
                            update GS_SequenceNumber set SN = SN + 1, UpdateDate = @UpdateDate where SNType = @SNType and SNKey = @SNKey
                            select SN from GS_SequenceNumber where SNType = @SNType and SNKey = @SNKey
                            end
                            else
                            begin
                            insert into GS_SequenceNumber(snID, SNType, SNKey, UpdateDate, SN) values(@snID, @SNType, @SNKey, @UpdateDate,@SN)
                            select 1
                            end";
                    SqlParameter[] parameters = {
                    new SqlParameter("@snID", SqlDbType.UniqueIdentifier,16),
                    new SqlParameter("@SNKey", SqlDbType.VarChar,200),
                    new SqlParameter("@SN", SqlDbType.Int,4),
                    new SqlParameter("@UpdateDate", SqlDbType.DateTime),
                    new SqlParameter("@SNType", SqlDbType.Int,4)};
                    parameters[0].Value = Guid.NewGuid();
                    parameters[1].Value = snkey;
                    parameters[2].Value = snType;
                    parameters[3].Value = DateTime.Now;
                    parameters[4].Value = snType;
 
                    object obj = DBHelper.GetSingle(strSql.ToString(), parameters);
                    return string.Format(snkey, obj);
                }
                catch (Exception ex)
                {
                    return ex.Message;
                }
            }
 
调用：
 protected static string PreNo()
        {
            string year = DateTime.Now.Year.ToString().Substring(2);
            string month = DateTime.Now.Month.ToString().Length == 1 ? "0" + DateTime.Now.Month.ToString() : DateTime.Now.Month.ToString();
            string number = "{0:00000000}";
            return GenerateNumberBLL.GetGenerateNumber(year + month + number, 1);
        }