我们现有的项目没有采用任何ORM，所有的数据读取与操作都是基于存储过程的，在代码端使用 Enterprise Library 5 。 在 EntLib 和数据库之间，是基于我原来写的一个 T4 实体生成的模板，之前也没有详细的去整，反正能运行出结果就行了，总之，代码很乱。
最近一期项目告一段落，后续项目还没有上马，一手把这个部门建立起来的总监(经理)又离开了这个团队，我们几个老一批的员工也在思索着是否换换。趁着这个便当，我把这个东西在整出来，算是给我增加一个砝码吧。
什么是 T4 模板，自己去搜吧，怎么用也请自己搜吧。懂就懂，不懂我也懒得解释。
我将要贴出的T4模板是将 SQLServer 2008 的 Table, View , TableType, Procedure 解析为 C# 里的对应实体，形如下：
Table/View/TableType
复制
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; using System.ComponentModel; using System.Runtime.Serialization; namespace AsNum.MySecret.Entity.Database { /// <summary> /// Table : dbo.LoginPolicy /// 登陆策略 /// 数据实体 /// </summary> [Serializable] [DataContract] public class LoginPolicyEntity { /// <summary> /// 自动编号 /// dbo.LoginPolicy.PolicyID /// 默认值 /// </summary> public int PolicyID{ get;set; } private int _UnfreezeTime = 30; /// <summary> /// 解冻时间 /// dbo.LoginPolicy.UnfreezeTime /// 默认值((30)) /// </summary> public int UnfreezeTime{ get{ return _UnfreezeTime; } set{ _UnfreezeTime = value; } } private int _MaxFailedCount = 5; /// <summary> /// 最大失败次数 /// dbo.LoginPolicy.MaxFailedCount /// 默认值((5)) /// </summary> public int MaxFailedCount{ get{ return _MaxFailedCount; } set{ _MaxFailedCount = value; } } private bool? _EnableLoginPolicy = true; /// <summary> /// 是否启用登陆策略 /// dbo.LoginPolicy.EnableLoginPolicy /// 默认值((1)) /// </summary> public bool? EnableLoginPolicy{ get{ return _EnableLoginPolicy; } set{ _EnableLoginPolicy = value; } } private DateTime _CreateTime = new DateTime(); /// <summary> /// /// dbo.LoginPolicy.CreateTime /// 默认值(getdate()) /// </summary> public DateTime CreateTime{ get{ return _CreateTime; } set{ _CreateTime = value; } } /// <summary> /// /// dbo.LoginPolicy.Creator /// 默认值 /// </summary> public string Creator{ get;set; } } }
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.
86.
87.
88.
89.
90.
91.
Procedure
复制
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Collections; using System.ComponentModel; using System.Data.SqlClient; using Microsoft.Practices.EnterpriseLibrary.Data; using AsNum.MySecret.Entity.Database; using AsNum.Common.Extend; namespace AsNum.MySecret.DB { /// /// /// public partial class SPs { /// /// 发送消息 /// /// /// @Title 消息标题 /// @Ctx 消息内容 /// @UserID 用户ID /// @FromIP 发消息的IP /// @IntranetIP 发消息的内网IP，用于扩展 /// @IsPublic 是否公开 /// @Receiver 消息接收者，表变量 /// public static SqlCommand SendMsg(Database db, string title , string ctx , int? userID , long? fromIP , long? intranetIP , long? isPublic , List receiver){ if(db == null) throw new ArgumentNullException("db"); SqlCommand sc = new SqlCommand("SendMsg"); sc.CommandType = CommandType.StoredProcedure; db.AddParameter(sc , "@Title" , DbType.String , 100 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , title); db.AddParameter(sc , "@Ctx" , DbType.String , 500 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , ctx); db.AddParameter(sc , "@UserID" , DbType.Int32 , 4 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , userID); db.AddParameter(sc , "@FromIP" , DbType.Int64 , 8 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , fromIP); db.AddParameter(sc , "@IntranetIP" , DbType.Int64 , 8 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , intranetIP); db.AddParameter(sc , "@IsPublic" , DbType.Int64 , 8 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , isPublic); db.AddParameter(sc , "@Receiver" , DbType.Object , -1 , ParameterDirection.Input , true , 0 , 0 , String.Empty , DataRowVersion.Default , receiver.ToDataTable()); return sc; } } }
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.
可以看到产生的存储过程调用方法并没有获取到参数的默认值，这个是因为(http://msdn.microsoft.com/en-us/library/ms176074.aspx)：
SQL Server only maintains default values for CLR objects in this catalog view;
不过，有牛人做出来了，前提是存储过程没有加密：
http://www.codeproject.com/KB/database/FindDefaultValueSPParams.aspx
#p#
在声明 Table / View / TableType 的时候：
复制
CREATE TYPE TMessageReceiver AS TABLE( SendType VARCHAR(10) NOT NULL, Receiver NVARCHAR(128) NOT NULL ) GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息接收人, 相同SendType 和Recever 只能出现一次' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'TMessageReceiver' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息发送类型EML(QQ, MSN留扩展)' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'TMessageReceiver', @level2type=N'COLUMN',@level2name=N'SendType' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'接收地址,跟据SendType' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TYPE',@level1name=N'TMessageReceiver', @level2type=N'COLUMN',@level2name=N'Receiver' GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
------------
复制
CREATE TABLE LoginPolicy( PolicyID INT IDENTITY(1,1) NOT NULL, UnfreezeTime INT NOT NULL DEFAULT 30, MaxFailedCount INT NOT NULL DEFAULT 5, EnableLoginPolicy BIT DEFAULT 1, -- 0 : 不启用, 1:启用 CreateTime DATETIME NOT NULL DEFAULT GETDATE(), Creator NVARCHAR(30), CONSTRAINT LoginPolicy_PK PRIMARY KEY (PolicyID) ) GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'登陆策略' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LoginPolicy' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'自动编号' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LoginPolicy', @level2type=N'COLUMN',@level2name=N'PolicyID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'解冻时间' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LoginPolicy', @level2type=N'COLUMN',@level2name=N'UnfreezeTime' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'最大失败次数' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LoginPolicy', @level2type=N'COLUMN',@level2name=N'MaxFailedCount' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否启用登陆策略' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'LoginPolicy', @level2type=N'COLUMN',@level2name=N'EnableLoginPolicy' GO
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
声明存储过程：
复制
CREATE PROCEDURE SendMsg @Title NVARCHAR(100), @Ctx NVARCHAR(500), @UserID INT, @FromIP BIGINT, @IntranetIP BIGINT, @IsPublic BIGINT, @Receiver TMessageReceiver READONLY AS BEGIN -- 表变量，用以存储新增的主表ID DECLARE @T AS TABLE (ID INT) BEGIN TRAN NewMsg BEGIN TRY -- 写入主表 INSERT INTO [Message] (Title, Ctx, UserID, FromIP, IntranetIP, IsPublic) OUTPUT INSERTED.MessageID INTO @T VALUES (@Title, @Ctx, @UserID, @FromIP, @IntranetIP , @IsPublic ) -- 取出新增数据的ID DECLARE @MessageID INT SELECT TOP 1 @MessageID = ID FROM @T -- 写子表, 这里要改动一下，相同的只保留一条 INSERT INTO MessageReceiver (MessageID, SendType, Receiver ) SELECT @MessageID, R.SendType, R.Receiver FROM @Receiver R END TRY BEGIN CATCH ROLLBACK TRAN NewMsg RETURN 2 -- DatabaseError END CATCH COMMIT TRAN NewMsg RETURN 0 END GO EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发送消息' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息标题' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@Title' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息内容' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@Ctx' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'用户ID' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@UserID' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发消息的IP' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@FromIP' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'发消息的内网IP，用于扩展' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@IntranetIP' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'是否公开' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@IsPublic' EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'消息接收者，表变量' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'PROCEDURE',@level1name=N'SendMsg', @level2type=N'PARAMETER', @level2name = '@Receiver' GO
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.
这些 MS_Description 的 value 会做为注释写到生成的代码里。
实现T4模板就很简单了，就是把下面几个SQL的结果取出来，在自由的组合一下：
复制
WITH Entities AS ( SELECT Table_catalog AS [Database] , table_schema [Owner] , table_name [Name] , 'Table' [Type] ,OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) ID FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' UNION ALL SELECT Table_catalog , table_schema , table_name , 'View' , OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME) ID FROM information_schema.views UNION ALL SELECT DB_NAME() , SCHEMA_NAME(schema_id) , name , 'TableType' , user_type_id FROM sys.table_types ) SELECT E.* , P.value AS [DESC] FROM Entities E LEFT JOIN sys.extended_properties P ON E.ID = P.major_id AND P.minor_id = 0
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.
Table / View / TableType
字段：
复制
SELECT DB_NAME() AS [Database] ,COALESCE( SCHEMA_NAME(T.Schema_id), OBJECT_SCHEMA_NAME(C.object_id)) AS Owner ,COALESCE( T.Name, OBJECT_NAME(c.OBJECT_ID)) AS Parent ,C.column_id AS OrdinalPosition ,C.name AS Name ,TYPE_NAME(c.user_type_id) AS DataType ,D.definition AS DefaultSetting , C.is_nullable AS IsNullable , C.max_length AS MaxLength , COLUMNPROPERTY( C.OBJECT_ID , C.Name ,'PRECISION') AS [Precision] -- 用于判断NVARCHAR 实际长度的 --,C.Object_id, P.major_id, P.minor_id ,P.Value AS [DESC] FROM sys.columns C LEFT JOIN sys.table_types T ON T.type_table_object_id = C.object_ID LEFT JOIN sys.default_constraints D ON C.object_id = D.parent_object_id AND D.parent_column_id = C.Column_id LEFT JOIN sys.extended_properties P ON (P.major_id = C.OBJECT_ID OR P.major_id = T.User_type_id ) AND P.minor_id = C.COLUMN_ID AND P.name = 'MS_Description' WHERE COALESCE( SCHEMA_NAME(T.Schema_id), OBJECT_SCHEMA_NAME(C.object_id)) = 'dbo'
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
存储过程：
复制
SELECT SCHEMA_NAME(P.schema_id) AS Owner , P.Name , PP.Value AS [Desc] FROM sys.procedures P LEFT JOIN sys.extended_properties PP ON P.object_id = PP.major_id AND PP.minor_id = 0
1.
2.
3.
4.
5.
6.
7.
存储过程的参数：
复制
SELECT SCHEMA_NAME( P.[schema_id] ) AS [Schema] ,P.Name AS [Proc] ,PA.Name , (SELECT COUNT(1) FROM sys.table_types WHERE user_type_id = PA.user_type_id) AS IsTableType ,TYPE_NAME(PA.user_type_id) AS DataType , PA.max_length AS MaxLength , COLUMNPROPERTY( PA.OBJECT_ID , PA.Name ,'PRECISION') AS [Precision] -- 用于判断NVARCHAR 实际长度的 , PA.is_output AS IsOutput , PP.value AS [Desc] FROM sys.procedures P INNER JOIN sys.parameters PA ON P.object_id = PA.object_id LEFT JOIN sys.extended_properties PP ON PA.object_id = PP.major_id AND PA.parameter_id = PP.minor_id ORDER BY PA.Object_id, PA.Parameter_id
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
源代码包：http://files.cnblogs.com/xling/ExecuteT4.7z
这个包里有一个如何用程序运行T4模板的示例，加这个是因为同事问我如何用代码去执行T4模板，昨晚K歌完到家12点半(跟经理道别)，洗完澡后，我从以前写的代码里翻出来的，稍稍做了点修改。还有一份数据字典导出模板.
另外需要注意的是：
1, Microsoft.VisualStudio.TextTemplating.dll 这个 DLL，如果你装的是VS2010 就默认装了这个。如果是VS2008, 需要从：Visual Studio 2008 SDK 1.1 里找
2, 如图
遗留的问题：
有些默认值没有处理好，比如对DateTime字段只处理了这种情况： GETDATE() 或 ‘2011-01-01’ , 其它的我暂时没有遇到，没有做处理。
存储过程的 TableType 参数，我简单的映射为 DbType.Object ，还没有验证这样是否可行。
后面我会把这些都修正的，如果你有兴趣的话，可以留意。
原文链接：http://www.cnblogs.com/xling/archive/2011/05/24/2055503.html