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);
}