使用必读 · 旺财.Net企业信息化快速开发平台

旺财C# .NET代码生成器使用必读 DTcms用户请直接看Readme.DTcms.docx # 一、业务数据表自建的业务表建议含有以下12个字段： 1) Id 自增主键 2) SortCode 3) DeletionStateCode 4) Enabled 5) CreateOn 6) CreateUserId 7) CreateBy 8) CreateIp 9) ModifiedOn 10) ModifiedUserId 11) ModifiedBy 12) ModifiedIp 如果你使用MSSQL数据库，可参考如下sql创建 USE \[YOURDB\] GO /\*\*\*\*\*\* Object: Table \[dbo\].\[MeetingType\] Script Date: 02/16/2017 16:15:28 \*\*\*\*\*\*/ SET ANSI\_NULLS ON GO SET QUOTED\_IDENTIFIER ON GO CREATE TABLE \[dbo\].\[MeetingType\]( \[Id\] \[int\] IDENTITY(1,1) NOT NULL, \[Name\] \[nvarchar\](50) NOT NULL, \[Description\] \[nvarchar\](max) NULL, \[SortCode\] \[int\] NULL, \[DeletionStateCode\] \[smallint\] NULL, \[Enabled\] \[smallint\] NULL, \[CreateOn\] \[datetime\] NULL, \[CreateUserId\] \[int\] NULL, \[CreateBy\] \[nvarchar\](50) NULL, \[CreateIp\] \[nvarchar\](50) NULL, \[ModifiedOn\] \[datetime\] NULL, \[ModifiedUserId\] \[int\] NULL, \[ModifiedBy\] \[nvarchar\](50) NULL, \[ModifiedIp\] \[nvarchar\](50) NULL, CONSTRAINT \[PK\_MeetingType\] PRIMARY KEY CLUSTERED ( \[Id\] ASC )WITH (PAD\_INDEX \= OFF, STATISTICS\_NORECOMPUTE \= OFF, IGNORE\_DUP\_KEY \= OFF, ALLOW\_ROW\_LOCKS \= ON, ALLOW\_PAGE\_LOCKS \= ON) ON \[PRIMARY\] ) ON \[PRIMARY\] GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'编号' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'Id' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'名称' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'Name' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'描述' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'Description' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'排序编号' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'SortCode' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'删除状态代码' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'DeletionStateCode' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'是否有效' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'Enabled' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'创建日期' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'CreateOn' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'创建人编号' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'CreateUserId' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'创建人姓名' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'CreateBy' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'创建IP' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'CreateIp' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'最近修改日期' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'ModifiedOn' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'最近修改人编号' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'ModifiedUserId' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'最近修改人姓名' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'ModifiedBy' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'最近修改IP' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType', @level2type\=N'COLUMN',@level2name\=N'ModifiedIp' GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'会议类型' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'MeetingType' GO ALTER TABLE \[dbo\].\[MeetingType\] ADD CONSTRAINT \[DF\_MeetingType\_SortCode\] DEFAULT ((0)) FOR \[SortCode\] GO ALTER TABLE \[dbo\].\[MeetingType\] ADD CONSTRAINT \[DF\_MeetingType\_DeletionStateCode\] DEFAULT ((0)) FOR \[DeletionStateCode\] GO ALTER TABLE \[dbo\].\[MeetingType\] ADD CONSTRAINT \[DF\_MeetingType\_EnabledCode\] DEFAULT ((1)) FOR \[Enabled\] GO ALTER TABLE \[dbo\].\[MeetingType\] ADD CONSTRAINT \[DF\_MeetingType\_CreateOn\] DEFAULT (getdate()) FOR \[CreateOn\] GO # 二、自定义序列表如果自建业务表中含有SortCode字段，并打算使用自己的序列号表，请参照UserCenter库中的BaseSequence结构创建同名、同结构的表。 USE \[YOURDB\] GO /\*\*\*\*\*\* Object: Table \[dbo\].\[BaseSequence\] Script Date: 04/27/2020 20:09:12 \*\*\*\*\*\*/ SET ANSI\_NULLS ON GO SET QUOTED\_IDENTIFIER ON GO CREATE TABLE \[dbo\].\[BaseSequence\]( \[Id\] \[nvarchar\](50) NOT NULL, \[FullName\] \[nvarchar\](50) NOT NULL, \[Prefix\] \[nvarchar\](50) NULL, \[Delimiter\] \[nvarchar\](50) NULL, \[Sequence\] \[int\] NOT NULL, \[Reduction\] \[int\] NOT NULL, \[Step\] \[int\] NOT NULL, \[IsVisible\] \[int\] NOT NULL, \[Description\] \[nvarchar\](max) NULL, \[CreateOn\] \[datetime\] NULL, \[CreateUserId\] \[nvarchar\](50) NULL, \[CreateBy\] \[nvarchar\](50) NULL, \[ModifiedOn\] \[datetime\] NULL, \[ModifiedUserId\] \[nvarchar\](50) NULL, \[ModifiedBy\] \[nvarchar\](50) NULL, CONSTRAINT \[PK\_Base\_Sequence\] PRIMARY KEY CLUSTERED ( \[Id\] ASC )WITH (PAD\_INDEX \= OFF, STATISTICS\_NORECOMPUTE \= OFF, IGNORE\_DUP\_KEY \= OFF, ALLOW\_ROW\_LOCKS \= ON, ALLOW\_PAGE\_LOCKS \= ON) ON \[PRIMARY\] ) ON \[PRIMARY\] TEXTIMAGE\_ON \[PRIMARY\] GO EXEC sys.sp\_addextendedproperty @name\=N'MS\_Description', @value\=N'序列' , @level0type\=N'SCHEMA',@level0name\=N'dbo', @level1type\=N'TABLE',@level1name\=N'BaseSequence' GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_Base\_Sequence\_Sequence\] DEFAULT ((10000000)) FOR \[Sequence\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_Base\_Sequence\_Degression\] DEFAULT ((9999999)) FOR \[Reduction\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_Base\_Sequence\_Step\] DEFAULT ((1)) FOR \[Step\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_BaseSequence\_IsVisible\] DEFAULT ((1)) FOR \[IsVisible\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_BaseSequence\_Description\] DEFAULT ((0)) FOR \[Description\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_Base\_Sequence\_CreateOn\] DEFAULT (getdate()) FOR \[CreateOn\] GO ALTER TABLE \[dbo\].\[BaseSequence\] ADD CONSTRAINT \[DF\_Base\_Sequence\_ModifiedOn\] DEFAULT (getdate()) FOR \[ModifiedOn\] GO # 三、分页存储过程如果将代码用在原有业务数据库中，请先创建分页存储过程。 USE \[YOURDB\] GO /\*\*\*\*\*\* Object: StoredProcedure \[dbo\].\[GetRecordByPage\] Script Date: 04/27/2020 20:05:59 \*\*\*\*\*\*/ SET ANSI\_NULLS ON GO SET QUOTED\_IDENTIFIER ON GO \-- ============================================= \-- Author: Troy Cui 崔文远 \-- Create date: 2012年月日 \-- Update date: 2017年月日 \-- Description: 分页存储过程 \-- ============================================= CREATE PROCEDURE \[dbo\].\[GetRecordByPage\] @TableName NVARCHAR(MAX), \-- 表名 @SelectField NVARCHAR(MAX) \= '\*', \-- 要显示的字段名(注意：不要加SELECT) @WhereConditional NVARCHAR(MAX), \-- 查询条件(注意: 不要加WHERE) @SortExpression NVARCHAR(MAX) \= 'Id', \-- 排序索引字段名（注意：仅支持一个，多个时用Id DESC, Name格式） @PageSize INT \= 20, \-- 页大小 @PageIndex INT \= 1, \-- 页码 @RecordCount INT OUTPUT, \-- 返回记录总数 @SortDire NVARCHAR(MAX) \= 'DESC' \-- 设置排序类型（注意：仅支持ASC或DESC） AS BEGIN DECLARE @CommandText NVARCHAR(MAX) \-- 主语句 DECLARE @PageCount INT \-- 总共会是几页 DECLARE @SQLRowCount NVARCHAR(MAX) \-- 用于查询记录总数的语句 DECLARE @BeginRow INT \-- 开始记录 DECLARE @EndRow INT \-- 结束记录 DECLARE @TempLimit VARCHAR(MAX) \-- 结果范围 SET @SortExpression \= LTRIM(RTRIM(@SortExpression)) SET @SortDire \= UPPER(LTRIM(RTRIM(@SortDire))) \--DECLARE @TimeDiff datetime \--不返回计数（表示受Transact-SQL 语句影响的行数） SET NOCOUNT ON \--SELECT @TimeDiff=getdate() --记录时间 \-- 这里是计算整体记录行数 IF @WhereConditional != '' BEGIN SET @SQLRowCount \= 'SELECT @RecordCount=COUNT(\*) FROM ' + @TableName + ' WHERE ' + @WhereConditional END ELSE BEGIN SET @SQLRowCount \= 'SELECT @RecordCount=COUNT(\*) FROM ' + @TableName END \--输出参数为总记录数 EXEC sp\_executesql @SQLRowCount, N'@RecordCount INT OUT', @RecordCount OUT \-- 这里是控制页数最多少 SET @PageCount \= @RecordCount / @PageSize + 1 \-- 这里检查当前页的有效性 IF (@PageIndex < 1) BEGIN SET @PageIndex \= 1 END \-- 这里限制最后一页的有效性 IF (@PageIndex \> @PageCount) BEGIN SET @PageIndex \= @PageCount END SET @BeginRow \= (@PageIndex \- 1) \* @pageSize + 1 SET @EndRow \= @PageIndex \* @pageSize SET @TempLimit \= 'ROWS BETWEEN ' + CAST(@BeginRow AS NVARCHAR) +' AND '+CAST(@EndRow AS NVARCHAR) \--主查询返回结果集 IF @PageIndex \= 1 BEGIN \-- 第一页的显示效率提高 IF @WhereConditional != '' BEGIN SET @CommandText \= 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' +@SelectField+ ' FROM ' + @TableName + ' WHERE '+@WhereConditional+' ORDER BY '+@SortExpression+' '+@SortDire END ELSE BEGIN SET @CommandText \= 'SELECT TOP ' + CAST(@PageSize AS NVARCHAR) + ' ' +@SelectField+ ' FROM ' + @TableName + ' ORDER BY '+@SortExpression+' '+@SortDire END END ELSE BEGIN IF @WhereConditional != '' BEGIN SET @CommandText \= 'SELECT \* FROM (SELECT ROW\_NUMBER() OVER (ORDER BY '+@SortExpression+' '+@SortDire+') AS ROWS ,'+@SelectField+' FROM '+@TableName+' WHERE '+@WhereConditional+') AS T WHERE '+@TempLimit END ELSE BEGIN SET @CommandText \= 'SELECT \* FROM (SELECT ROW\_NUMBER() OVER (ORDER BY '+@SortExpression+' '+@SortDire+') AS ROWS ,'+@SelectField+' FROM '+@TableName+') AS T WHERE '+@TempLimit END END \--PRINT @CommandText EXECUTE (@CommandText) \--SELECT DATEDIFF(ms,@TimeDiff,getdate()) AS 耗时 \-- 这个是调试程序用的 \--SELECT @CommandText \--INSERT INTO Temp\_GetRecordByPage (CommandText) SELECT @CommandText \--返回计数 SET NOCOUNT OFF \--在存储过程的头部加上SET NOCOUNT ON 这样的话，在退出存储过程的时候加上SET NOCOUNT OFF，以达到优化存储过程的目的。 END GO # 四、表前缀和表分隔符 a) 表前缀代表：表名的前缀。 b) 分隔符：表名和字段名的分隔符 c) 举例：如DTcms4中前缀是dt\_，没有分隔符；旺财通用权限管理系统中UserCenter库前缀为Base，没有分隔符。（有些Oracle数据库用户会以\_为分隔符） # 五、附件目录 WebApplication如使用系统附件自定义附件控件应注意的问题所上传的附件默认存放在UploadFiles\\tableName\\attachmentField\\目录下，以主键Id为子目录。 # 六、在线培训视频在线播单：[http://list.youku.com/albumlist/show/id\_50289517.html](http://list.youku.com/albumlist/show/id_50289517.html) # 七、联系方式付费用户使用中有任何问题，请通过以下方式联系我。 d) 姓名：崔文远 e) 手机/微信：13818699609 f) QQ：17185490 g) 个人网站：[http://www.cuiwenyuan.com](http://www.cuiwenyuan.com) h) 旺财软件：[http://www.wangcaisoft.com](http://www.wangcaisoft.com)