本文利用VS2010的文本模板,从数据库中生成表的实体。
<#@ template debug="false" hostspecific="false" language="C#" #>
<#@ include file="EF.Utility.CS.ttinclude" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="Microsoft.CSharp" #>
<#@ assembly name="System.Data" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Dynamic" #>
<#@ output extension=".cs" #>
//------------------------------------------------------------------------------
// <auto-generated>
// This code was generated from a template.
// Author:chwf
// Manual changes to this file may cause unexpected behavior in your application.
// Manual changes to this file will be overwritten if the code is regenerated.
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using System.IO;
using System.ComponentModel.DataAnnotations;
using System.Data.Entity;
using System.Data.Entity.Infrastructure;
<#
NamespaceBegin("Wanda.HOPSA.Models");
var query = Query();
WriteDbContext(query);
WritePOCO(query);
NamespaceEnd();
#>
<#+
void WriteDbContext(IEnumerable<dynamic> query)
{
//DbContext
WriteLine("public partial class _DbContext : DbContext");
BlockBegin(2);
Write(string.Format("public _DbContext() : base(\"{0}\")",connStr));
WriteLine("{ }");
WriteLine("");
WriteLine("protected override void OnModelCreating(DbModelBuilder modelBuilder)");
BlockBegin(3);
WriteLine("//throw new UnintentionalCodeFirstException();");
BlockEnd(3);
WriteLine("");
//DbSet
foreach (var tabName in query.Select(i => i.TableName).Distinct())
{
Write(string.Format("public DbSet<{0}> {0} ",tabName));
WriteLine("{ get; set; }");
}
BlockEnd(2);
WriteLine("");
}
void WritePOCO(IEnumerable<dynamic> query)
{
//POCO
foreach (var tabName in query.Select(i => i.TableName).Distinct())
{
Summary(tabName + "的实体描述",1);
WriteLine("[Table(\""+tabName+"\")]");
WriteLine("public class "+tabName);
BlockBegin(2);
foreach (var row in query.Where(i => i.TableName == tabName))
{
//获取表中某列的约束
SetIndent(2);
if(null!=row.ConstraintName)
WriteLine(row.ConstraintName);
WriteLine(string.Format("[Column(\"{0}\",TypeName=\"{1}\")]",row.ColumnName,row.DataType));
Write("public ");
Write(row.DataType + " " + row.ColumnName);
WriteLine(" { get; set; }");
WriteLine("");
}
BlockEnd(2);
WriteLine("/************************************************************/");
}
}
#>
<#+
void NamespaceBegin(string name = "T4")
{
PopIndent();
WriteLine("namespace " + name);
WriteLine("{");
SetIndent(1);
}
void NamespaceEnd()
{
PopIndent();
WriteLine("}");
}
void BlockBegin(int indent = 0)
{
WriteLine("{");
SetIndent(indent);
}
void BlockEnd(int indent = 0)
{
indent = indent > 0 ? indent - 1 : indent;
SetIndent(indent);
WriteLine("}");
}
void SetIndent(int indent)
{
PopIndent();
PushIndent(CodeRegion.GetIndent(indent));
}
void Summary(string text,int indent)
{
SetIndent(indent);
WriteLine("/// <summary>");
WriteLine("/// "+ text);
WriteLine("/// </summary>");
}
#>
<#+
//string connStr = @"Data Source=.\SQLExpress;Initial Catalog=Safety;User ID=sa;Password=admin*12;Connect Timeout=200;Max Pool Size=500;";
IEnumerable<dynamic> Query()
{
var sql = @"SELECT t.TABLE_NAME,c.COLUMN_NAME, c.ORDINAL_POSITION, c.COLUMN_DEFAULT,
c.IS_NULLABLE,ccu.CONSTRAINT_NAME,
CASE WHEN c.DATA_TYPE IN('tinyint') then 'short'
WHEN c.DATA_TYPE IN('smallint','int') then 'int'
WHEN c.DATA_TYPE IN('bigint') then 'long'
WHEN c.DATA_TYPE IN('numeric','decimal','smallmoney','money') then 'float'
WHEN c.DATA_TYPE IN('uniqueidentifier') then 'Guid'
WHEN c.DATA_TYPE IN('datetime','SMALLDATETIME','date','time','datetimeoffset','datetime2') then 'DateTime'
WHEN c.DATA_TYPE IN('char','varchar','text','nvarchar','nchar','ntext') then 'string'
WHEN c.DATA_TYPE IN('binary','varbinary','image') then 'byte[]'
WHEN c.DATA_TYPE= 'bit' THEN 'bool'
ELSE 'object'
END DATA_TYPE
FROM INFORMATION_SCHEMA.TABLES t
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
ON tc.TABLE_NAME = t.TABLE_NAME
INNER JOIN INFORMATION_SCHEMA.[COLUMNS] c
ON t.TABLE_NAME=c.TABLE_NAME
LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu
ON ccu.COLUMN_NAME = c.COLUMN_NAME AND ccu.TABLE_NAME = c.TABLE_NAME
WHERE t.TABLE_TYPE='BASE TABLE'";
using (var conn = new SqlConnection(connStr))
{
conn.Open();
using (var comm = conn.CreateCommand())
{
comm.CommandText = sql;
using (var reader = comm.ExecuteReader())
{
while (reader.Read())
{
dynamic obj = new ExpandoObject();
obj.TableName = (string)reader["TABLE_NAME"];
obj.ColumnName = (string)reader["COLUMN_NAME"];
obj.DataType = (string)reader["DATA_TYPE"];
obj.ConstraintName = ConvertToAttrbuite(reader["CONSTRAINT_NAME"].ToString());
yield return obj;
}
}
}
}
}
string ConvertToAttrbuite(string constraint) {
if (constraint.StartsWith("PK_"))
return "[Key]";
return null;
}
#>