用T4 模板生成数据实体

我们现有的项目没有采用任何ORM，所有的数据读取与操作都是基于存储过程的，在代码端使用 Enterprise Library 5 。在 EntLib 和数据库之间，是基于我原来写的一个 T4 实体生成的模板，之前也没有详细的去整，反正能运行出结果就行了，总之，代码很乱。

最近一期项目告一段落，后续项目还没有上马，一手把这个部门建立起来的总监(经理)又离开了这个团队，我们几个老一批的员工也在思索着是否换换。趁着这个便当，我把这个东西在整出来，算是给我增加一个砝码吧。

什么是 T4 模板，自己去搜吧，怎么用也请自己搜吧。懂就懂，不懂我也懒得解释。

我将要贴出的T4模板是将 SQLServer 2008 的 Table, View , TableType, Procedure 解析为 C# 里的对应实体，形如下：

Table/View/TableType

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

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 {
	/// <summary>
	///
	///</summary>
    public partial class SPs {
	
		/// <summary>
		/// 发送消息
		/// </summary>
		/// <param name="db"></param>
		/// <param name="title">@Title 消息标题</param>
		/// <param name="ctx">@Ctx 消息内容</param>
		/// <param name="userID">@UserID 用户ID</param>
		/// <param name="fromIP">@FromIP 发消息的IP</param>
		/// <param name="intranetIP">@IntranetIP 发消息的内网IP，用于扩展</param>
		/// <param name="isPublic">@IsPublic 是否公开</param>
		/// <param name="receiver">@Receiver 消息接收者，表变量</param>
		/// <returns></returns>
		public static SqlCommand SendMsg(Database db, string title , string ctx , int? userID , long? fromIP , long? intranetIP , long? isPublic , List<TMessageReceiverEntity> 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;
		}
	}
	
	
}

可以看到产生的存储过程调用方法并没有获取到参数的默认值，这个是因为（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

在声明 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

 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

声明存储过程：

 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

这些 MS_Description 的 value 会做为注释写到生成的代码里。
实现T4模板就很简单了，就是把下面几个SQL的结果取出来，在自由的组合一下：

Table / View / TableType

 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

字段：

 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'

存储过程：

 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

存储过程的参数：

 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

源代码包：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 ，还没有验证这样是否可行。

后面我会把这些都修正的，如果你有兴趣的话，可以留意。

作者: xling 发表于 2011-05-24 15:03 原文链接

Tag 用t4,模板生成数据实体

推荐.NET配套的通用数据层ORM框架：CYQ.Data 通用数据层框架

IT Technology Blog

Post Comment

Bulletin

Article Search

Article Categories

Article Archive

New Article

New Comment

Hits Order

Comment Order

Links