使用必讀 · 旺財.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)