文件生成模板：TempleteManager.ttinclude
<#@ assembly name="System.Core"
#><#@ assembly name="System.Data.Linq"
#><#@ assembly name="EnvDTE"
#><#@ assembly name="System.Xml.Linq"
#><#@ import namespace="System.Collections.Generic"
#><#@ import namespace="System.IO"
#><#@ import namespace="System.Text"
#><#@ import namespace="Microsoft.VisualStudio.TextTemplating"
#><#+
// https://raw.github.com/damieng/DamienGKit
// http://damieng.com/blog/2009/11/06/multiple-outputs-from-t4-made-easy-revisited // Manager class records the various blocks so it can split them up
class Manager {
private class Block {
public String Name;
public int Start, Length;
public bool IncludeInDefault;
} private Block currentBlock;
private readonly List<Block> files = new List<Block>();
private readonly Block footer = new Block();
private readonly Block header = new Block();
private readonly ITextTemplatingEngineHost host;
private readonly StringBuilder template;
protected readonly List<String> generatedFileNames = new List<String>(); public static Manager Create(ITextTemplatingEngineHost host, StringBuilder template) {
return (host is IServiceProvider) ? new VSManager(host, template) : new Manager(host, template);
} public void StartNewFile(String name) {
if (name == null)
throw new ArgumentNullException("name");
CurrentBlock = new Block { Name = name };
} public void StartFooter(bool includeInDefault = true) {
CurrentBlock = footer;
footer.IncludeInDefault = includeInDefault;
} public void StartHeader(bool includeInDefault = true) {
CurrentBlock = header;
header.IncludeInDefault = includeInDefault;
} public void EndBlock() {
if (CurrentBlock == null)
return;
CurrentBlock.Length = template.Length - CurrentBlock.Start;
if (CurrentBlock != header && CurrentBlock != footer)
files.Add(CurrentBlock);
currentBlock = null;
} public virtual void Process(bool split, bool sync = true) {
if (split) {
EndBlock();
String headerText = template.ToString(header.Start, header.Length);
String footerText = template.ToString(footer.Start, footer.Length);
String outputPath = Path.GetDirectoryName(host.TemplateFile);
files.Reverse();
if (!footer.IncludeInDefault)
template.Remove(footer.Start, footer.Length);
foreach(Block block in files) {
String fileName = Path.Combine(outputPath, block.Name);
String content = headerText + template.ToString(block.Start, block.Length) + footerText;
generatedFileNames.Add(fileName);
CreateFile(fileName, content);
template.Remove(block.Start, block.Length);
}
if (!header.IncludeInDefault)
template.Remove(header.Start, header.Length);
}
} protected virtual void CreateFile(String fileName, String content) {
if (IsFileContentDifferent(fileName, content))
File.WriteAllText(fileName, content);
} public virtual String GetCustomToolNamespace(String fileName) {
return null;
} public virtual String DefaultProjectNamespace {
get { return null; }
} protected bool IsFileContentDifferent(String fileName, String newContent) {
return !(File.Exists(fileName) && File.ReadAllText(fileName) == newContent);
} private Manager(ITextTemplatingEngineHost host, StringBuilder template) {
this.host = host;
this.template = template;
} private Block CurrentBlock {
get { return currentBlock; }
set {
if (CurrentBlock != null)
EndBlock();
if (value != null)
value.Start = template.Length;
currentBlock = value;
}
} private class VSManager: Manager {
private readonly EnvDTE.ProjectItem templateProjectItem;
private readonly EnvDTE.DTE dte;
private readonly Action<String> checkOutAction;
private readonly Action<List<String>> projectSyncAction; public override String DefaultProjectNamespace {
get {
return templateProjectItem.ContainingProject.Properties.Item("DefaultNamespace").Value.ToString();
}
} public override String GetCustomToolNamespace(string fileName) {
return dte.Solution.FindProjectItem(fileName).Properties.Item("CustomToolNamespace").Value.ToString();
} public override void Process(bool split, bool sync) {
if (templateProjectItem.ProjectItems == null)
return;
base.Process(split, sync);
if (sync)
projectSyncAction.EndInvoke(projectSyncAction.BeginInvoke(generatedFileNames, null, null));
} protected override void CreateFile(String fileName, String content) {
if (IsFileContentDifferent(fileName, content)) {
CheckoutFileIfRequired(fileName);
File.WriteAllText(fileName, content);
}
} internal VSManager(ITextTemplatingEngineHost host, StringBuilder template)
: base(host, template) {
var hostServiceProvider = (IServiceProvider)host;
if (hostServiceProvider == null)
throw new ArgumentNullException("Could not obtain IServiceProvider");
dte = (EnvDTE.DTE) hostServiceProvider.GetService(typeof(EnvDTE.DTE));
if (dte == null)
throw new ArgumentNullException("Could not obtain DTE from host");
templateProjectItem = dte.Solution.FindProjectItem(host.TemplateFile);
checkOutAction = fileName => dte.SourceControl.CheckOutItem(fileName);
projectSyncAction = keepFileNames => ProjectSync(templateProjectItem, keepFileNames);
} private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, List<String> keepFileNames) {
var keepFileNameSet = new HashSet<String>(keepFileNames);
var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>();
var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.FileNames[0]) + ".";
foreach (EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems)
projectFiles.Add(projectItem.FileNames[0], projectItem); // Remove unused items from the project
foreach (var pair in projectFiles)
if (!keepFileNames.Contains(pair.Key) && !(Path.GetFileNameWithoutExtension(pair.Key) + ".").StartsWith(originalFilePrefix))
pair.Value.Delete(); // Add missing files to the project
foreach(String fileName in keepFileNameSet)
if (!projectFiles.ContainsKey(fileName))
templateProjectItem.ProjectItems.AddFromFile(fileName);
} private void CheckoutFileIfRequired(String fileName) {
var sc = dte.SourceControl;
if (sc != null && sc.IsItemUnderSCC(fileName) && !sc.IsItemCheckedOut(fileName))
checkOutAction.EndInvoke(checkOutAction.BeginInvoke(fileName, null, null));
}
}
} #>
数据库访问模板：TemplateDbhelper.ttinclude
<#@ assembly name="System.Core"#>
<#@ assembly name="System.Data.Linq"#>
<#@ assembly name="System.Data.DataSetExtensions.dll" #>
<#@ assembly name="System.Data.dll" #>
<#@ assembly name="System.Xml.dll" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Xml" #>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.IO" #> <#+
public class DbHelper
{
public static List<DbTable> GetDbTableViews(string connectionString, string database,string tables = null)
{
string typeParam="";
if (tables=="V*")
typeParam=" type='V'";
else if (tables=="U*")
typeParam=" type='U'";
else if(tables.IndexOf(",")>=)
typeParam=string.Format(" (type='U' or type='V') and obj.name in ('{0}')", tables.Replace(",", "','"));
else if(tables.IndexOf("%")>=){
string[] tablenames=tables.Split(';');
typeParam+=" (type='U' or type='V') and (";
int index=;
foreach (string name in tablenames)
{
if (!string.IsNullOrWhiteSpace(name)){
if(index!=){typeParam+=" or ";}
typeParam+=" obj.name like '"+name+"'";
index++;
}
}
typeParam+=")";
}
else
typeParam=" type='U' or type='V'"; #region SQL
string sql = string.Format(@"select obj.name tablename,schem.name schemname,(
select c.name+',' from {0}.sys.objects objs
inner join {0}.sys.index_columns ic on objs.object_id=ic.object_id
inner join {0}.sys.indexes i on i.index_id=ic.index_id and i.object_id=objs.object_id and i.is_primary_key=1
inner join {0}.sys.all_columns c on c.column_id=ic.column_id and c.object_id=objs.object_id
where objs.object_id=obj.object_id
FOR XML PATH('')
) primarykey,
CAST
(
CASE
WHEN (SELECT COUNT(1) FROM {0}.sys.indexes WHERE object_id= obj.OBJECT_ID AND is_primary_key=1) >=1 THEN 1
ELSE 0
END
AS BIT) HasPrimaryKey from {0}.sys.objects obj
INNER JOIN {0}.sys.schemas schem ON obj.schema_id=schem.schema_id
where {1}
order by obj.name", database,typeParam);
#endregion
DataTable dt = GetDataTable(connectionString, sql);
List<DbTable> listdt=new List<DbTable>();
foreach (DataRow row in dt.Rows)
{
DbTable dbt=new DbTable();
dbt.TableName = row.Field<string>("tablename");
dbt.SchemaName = row.Field<string>("schemname");
dbt.HasPrimaryKey = row.Field<bool>("HasPrimaryKey");
string pkstr=row.Field<string>("primarykey");
if(pkstr!=null){
pkstr=pkstr.Substring(,pkstr.Length-);
List<string> listpk=pkstr.Split(',').ToList();
dbt.PrimaryKey=listpk.ToArray();
}
listdt.Add(dbt);
}
return listdt;
} public static List<DbColumn> GetDbColumns(string connectionString, string database, string tableName, string schema = "dbo")
{
#region SQL
string sql = string.Format(@"
WITH indexCTE AS
(
SELECT
ic.column_id,
ic.index_column_id,
ic.object_id
FROM {0}.sys.indexes idx
INNER JOIN {0}.sys.index_columns ic ON idx.index_id = ic.index_id AND idx.object_id = ic.object_id
WHERE idx.object_id =OBJECT_ID('{1}') AND idx.is_primary_key=1
)
select
colm.column_id ColumnID,
CAST(CASE WHEN indexCTE.column_id IS NULL THEN 0 ELSE 1 END AS BIT) IsPrimaryKey,
colm.name ColumnName,
systype.name ColumnType,
colm.is_identity IsIdentity,
colm.is_nullable IsNullable,
cast(colm.max_length as int) ByteLength,
(
case
when systype.name='nvarchar' and colm.max_length>0 then colm.max_length/2
when systype.name='nchar' and colm.max_length>0 then colm.max_length/2
when systype.name='ntext' and colm.max_length>0 then colm.max_length/2
else colm.max_length
end
) CharLength,
cast(colm.precision as int) Precision,
cast(colm.scale as int) Scale,
prop.value Remark
from {0}.sys.columns colm
inner join {0}.sys.types systype on colm.system_type_id=systype.system_type_id and colm.user_type_id=systype.user_type_id
left join {0}.sys.extended_properties prop on colm.object_id=prop.major_id and colm.column_id=prop.minor_id
LEFT JOIN indexCTE ON colm.column_id=indexCTE.column_id AND colm.object_id=indexCTE.object_id
where colm.object_id=OBJECT_ID('{1}')
order by colm.column_id", database,string.Format("{0}.{1}.{2}", database, schema, tableName));
#endregion
DataTable dt = GetDataTable(connectionString, sql);
return dt.Rows.Cast<DataRow>().Select(row => new DbColumn()
{
ColumnID = row.Field<int>("ColumnID"),
IsPrimaryKey = row.Field<bool>("IsPrimaryKey"),
ColumnName = row.Field<string>("ColumnName"),
ColumnType = row.Field<string>("ColumnType"),
IsIdentity = row.Field<bool>("IsIdentity"),
IsNullable = row.Field<bool>("IsNullable"),
ByteLength = row.Field<int>("ByteLength"),
CharLength = row.Field<int>("CharLength"),
Scale = row.Field<int>("Scale"),
Remark = row["Remark"].ToString()
}).ToList();
} public static DataTable GetDataTable(string connectionString, string commandText)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = commandText;
SqlDataAdapter adapter = new SqlDataAdapter(command); DataTable dt = new DataTable();
adapter.Fill(dt); return dt;
}
} } #region DbTable
/// <summary>
/// 表结构
/// </summary>
public sealed class DbTable
{
/// <summary>
/// 表名称
/// </summary>
public string TableName { get; set; }
/// <summary>
/// 表的架构
/// </summary>
public string SchemaName { get; set; }
/// <summary>
/// 是否含有主键
/// </summary>
public bool HasPrimaryKey { get; set; }
/// <summary>
/// 主键数组
/// </summary>
public string[] PrimaryKey { get; set; }
}
#endregion #region DbColumn
/// <summary>
/// 表字段结构
/// </summary>
public sealed class DbColumn
{
/// <summary>
/// 字段ID
/// </summary>
public int ColumnID { get; set; } /// <summary>
/// 是否主键
/// </summary>
public bool IsPrimaryKey { get; set; } /// <summary>
/// 字段名称
/// </summary>
public string ColumnName { get; set; } /// <summary>
/// 字段类型
/// </summary>
public string ColumnType { get; set; } /// <summary>
/// 数据库类型对应的C#类型
/// </summary>
public string CSharpType
{
get
{
return SqlServerDbTypeMap.MapCsharpType(ColumnType);
}
}
/// <summary>
/// 数据库类型对应的C#类型转换
/// </summary>
public string CSharpConvert
{
get
{
return SqlServerDbTypeMap.MapSqlConvert(ColumnType,IsNullable);
}
}
/// <summary>
///
/// </summary>
public Type CommonType
{
get
{
return SqlServerDbTypeMap.MapCommonType(ColumnType);
}
}
public string SqlDbType_ColumnType
{
get
{
return SqlServerDbTypeMap.MapSqlDbType_ColumnType(ColumnType);
} } /// <summary>
/// 字节长度
/// </summary>
public int ByteLength { get; set; } /// <summary>
/// 字符长度
/// </summary>
public int CharLength { get; set; } /// <summary>
/// 小数位
/// </summary>
public int Scale { get; set; } /// <summary>
/// 是否自增列
/// </summary>
public bool IsIdentity { get; set; } /// <summary>
/// 是否允许空
/// </summary>
public bool IsNullable { get; set; } /// <summary>
/// 描述
/// </summary>
public string Remark { get; set; }
}
#endregion #region SqlServerDbTypeMap public class SqlServerDbTypeMap
{
public static string MapCsharpType(string dbtype)
{
if (string.IsNullOrEmpty(dbtype)) return dbtype;
dbtype = dbtype.ToLower();
string csharpType = "object";
switch (dbtype)
{
case "bigint": csharpType = "long"; break;
case "binary": csharpType = "byte[]"; break;
case "bit": csharpType = "bool"; break;
case "char": csharpType = "string"; break;
case "date": csharpType = "DateTime"; break;
case "datetime": csharpType = "DateTime"; break;
case "datetime2": csharpType = "DateTime"; break;
case "datetimeoffset": csharpType = "DateTimeOffset"; break;
case "decimal": csharpType = "decimal"; break;
case "float": csharpType = "double"; break;
case "image": csharpType = "byte[]"; break;
case "int": csharpType = "int"; break;
case "money": csharpType = "decimal"; break;
case "nchar": csharpType = "string"; break;
case "ntext": csharpType = "string"; break;
case "numeric": csharpType = "decimal"; break;
case "nvarchar": csharpType = "string"; break;
case "real": csharpType = "Single"; break;
case "smalldatetime": csharpType = "DateTime"; break;
case "smallint": csharpType = "short"; break;
case "smallmoney": csharpType = "decimal"; break;
case "sql_variant": csharpType = "object"; break;
case "sysname": csharpType = "object"; break;
case "text": csharpType = "string"; break;
case "time": csharpType = "TimeSpan"; break;
case "timestamp": csharpType = "byte[]"; break;
case "tinyint": csharpType = "byte"; break;
case "uniqueidentifier": csharpType = "Guid"; break;
case "varbinary": csharpType = "byte[]"; break;
case "varchar": csharpType = "string"; break;
case "xml": csharpType = "string"; break;
default: csharpType = "object"; break;
}
return csharpType;
}
public static string MapSqlConvert(string dbtype,bool IsNullable){
if (string.IsNullOrEmpty(dbtype)) return dbtype;
dbtype = dbtype.ToLower();
string defaultConvert = "IsString";
switch (dbtype)
{
case "bigint": defaultConvert =IsNullable?"IsLongNull": "IsLong"; break;
case "binary": defaultConvert =IsNullable?"IsByteNull":"IsByte"; break;
case "bit": defaultConvert =IsNullable?"IsBitNull": "IsBit"; break;
case "char": defaultConvert = "IsString"; break;
case "date": defaultConvert =IsNullable?"IsDateTimeNull": "IsDateTime"; break;
case "datetime": defaultConvert =IsNullable?"IsDateTimeNull": "IsDateTime"; break;
case "datetime2": defaultConvert =IsNullable?"IsDateTimeNull": "IsDateTime"; break;
case "datetimeoffset": defaultConvert = "DateTimeOffset"; break;
case "decimal": defaultConvert =IsNullable?"IsDecimalNull": "IsDecimal"; break;
case "float": defaultConvert =IsNullable?"IsDoubleNull": "IsDouble"; break;
case "image": defaultConvert =IsNullable?"IsByteNull": "IsByte"; break;
case "int": defaultConvert =IsNullable?"IsIntNull": "IsInt"; break;
case "money":defaultConvert =IsNullable?"IsDecimalNull":"IsDecimal"; break;
case "nchar": defaultConvert = "IsString"; break;
case "ntext": defaultConvert = "IsString"; break;
case "numeric": defaultConvert =IsNullable?"IsDecimalNull": "IsDecimal"; break;
case "nvarchar": defaultConvert = "IsString"; break;
case "real": defaultConvert = "Single"; break;
case "smalldatetime": defaultConvert =IsNullable?"IsDateTimeNull":"IsDateTime"; break;
case "smallint": defaultConvert =IsNullable?"IsSmallIntNull": "IsSmallInt"; break;
case "smallmoney":defaultConvert =IsNullable?"IsDecimalNull": "IsDecimal"; break;
case "sql_variant": defaultConvert = "object"; break;
case "sysname": defaultConvert = "object"; break;
case "text": defaultConvert = "IsString"; break;
case "time": defaultConvert =IsNullable?"IsTimeSpanNull": "IsTimeSpan"; break;
case "timestamp": defaultConvert =IsNullable?"IsByteNull":"IsByte"; break;
case "tinyint": defaultConvert =IsNullable?"IsByteNull": "IsByte"; break;
case "uniqueidentifier": defaultConvert =IsNullable?"IsGuidNull": "IsGuid"; break;
case "varbinary": defaultConvert =IsNullable?"IsByteNull": "IsByte"; break;
case "varchar": defaultConvert = "IsString"; break;
case "xml": defaultConvert = "IsString"; break;
default: defaultConvert = "object"; break;
}
return defaultConvert;
}
public static Type MapCommonType(string dbtype)
{
if (string.IsNullOrEmpty(dbtype)) return Type.Missing.GetType();
dbtype = dbtype.ToLower();
Type commonType = typeof(object);
switch (dbtype)
{
case "bigint": commonType = typeof(long); break;
case "binary": commonType = typeof(byte[]); break;
case "bit": commonType = typeof(bool); break;
case "char": commonType = typeof(string); break;
case "date": commonType = typeof(DateTime); break;
case "datetime": commonType = typeof(DateTime); break;
case "datetime2": commonType = typeof(DateTime); break;
case "datetimeoffset": commonType = typeof(DateTimeOffset); break;
case "decimal": commonType = typeof(decimal); break;
case "float": commonType = typeof(double); break;
case "image": commonType = typeof(byte[]); break;
case "int": commonType = typeof(int); break;
case "money": commonType = typeof(decimal); break;
case "nchar": commonType = typeof(string); break;
case "ntext": commonType = typeof(string); break;
case "numeric": commonType = typeof(decimal); break;
case "nvarchar": commonType = typeof(string); break;
case "real": commonType = typeof(Single); break;
case "smalldatetime": commonType = typeof(DateTime); break;
case "smallint": commonType = typeof(short); break;
case "smallmoney": commonType = typeof(decimal); break;
case "sql_variant": commonType = typeof(object); break;
case "sysname": commonType = typeof(object); break;
case "text": commonType = typeof(string); break;
case "time": commonType = typeof(TimeSpan); break;
case "timestamp": commonType = typeof(byte[]); break;
case "tinyint": commonType = typeof(byte); break;
case "uniqueidentifier": commonType = typeof(Guid); break;
case "varbinary": commonType = typeof(byte[]); break;
case "varchar": commonType = typeof(string); break;
case "xml": commonType = typeof(string); break;
default: commonType = typeof(object); break;
}
return commonType;
} public static string MapSqlDbType_ColumnType(string dbtype)
{
if (string.IsNullOrEmpty(dbtype)) return "";
dbtype = dbtype.ToLower();
string commonType="";
switch (dbtype)
{
case "bigint": commonType = "BigInt"; break;
case "binary": commonType = "Binary"; break;
case "bit": commonType = "Bit"; break;
case "char": commonType ="Char"; break;
case "date": commonType ="Date"; break;
case "datetime": commonType = "DateTime"; break;
case "datetime2": commonType = "DateTime2"; break;
case "datetimeoffset": commonType = "DateTimeOffset"; break;
case "decimal": commonType ="Decimal"; break;
case "float": commonType = "Float"; break;
case "image": commonType = "Image"; break;
case "int": commonType = "Int"; break;
case "money": commonType ="Money"; break;
case "nchar": commonType = "NChar"; break;
case "ntext": commonType ="NText"; break;
case "numeric": commonType = ""; break;
case "nvarchar": commonType = "NVarChar"; break;
case "real": commonType ="Real"; break;
case "smalldatetime": commonType ="SmallDateTime"; break;
case "smallint": commonType ="SmallInt"; break;
case "smallmoney": commonType = "SmallMoney"; break;
case "sql_variant": commonType = ""; break;
case "sysname": commonType = ""; break;
case "text": commonType = "Text"; break;
case "time": commonType = "Time"; break;
case "timestamp": commonType = "Timestamp"; break;
case "tinyint": commonType ="TinyInt"; break;
case "uniqueidentifier": commonType ="UniqueIdentifier"; break;
case "varbinary": commonType ="VarBinary"; break;
case "varchar": commonType = "VarChar"; break;
case "xml": commonType ="Xml"; break;
default: commonType =""; break;
}
return commonType;
}
}
#endregion #> <#+
public class config
{
public static readonly string ViewConnectionString="Data Source=192.168.1.2;Initial Catalog=VIEW_ABS;User ID=sa;Password=123456;";
public static readonly string ViewDbDatabase="VIEW_ABS";
/// <summary>
/// 全部表和视图： *
/// 全部表：U*
/// 全部视图： V*
/// 部分表或视图精确匹配：table1,table2
/// 部分表或视图模糊匹配（sql模糊查询规则）：%table1%;%table2%
/// </summary>
public static readonly string ViewDbTables=@"*"; public static readonly string WFConnectionString="Data Source=192.168.1.2;Initial Catalog=WF_ABS;User ID=sa;Password=123456;";
public static readonly string WFDbDatabase="WF_ABS";
public static readonly string WFDbTables="*";
}
#>
DB Code
实体生成模板：Model.tt
<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Configuration" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq" #> <#@include file="../../ABS.WF.Repository/TempleteManager.ttinclude"#>
<#@ include file="../../ABS.WF.Repository/TemplateDbhelper.ttinclude" #>
<# var manager = Manager.Create(Host, GenerationEnvironment); #> <#
foreach(DbTable table in DbHelper.GetDbTableViews(MyConfig.ConStr,MyConfig.DbBaseStr,MyConfig.DbTableStr)){
manager.StartNewFile(table.TableName+".cs");
#>
//------------------------------------------------------------------------------
// <auto-generated>
// 此代码已从模板生成。
// 手动更改此文件可能导致应用程序出现意外的行为。
// 如果重新生成代码，将覆盖对此文件的手动更改。
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
namespace ABS.WF.Model
{
/// <summary>
/// 实体-<#=table.TableName#>
/// </summary>
public partial class <#=table.TableName#>
{
<#
PushIndent(" ");
foreach(DbColumn column in DbHelper.GetDbColumns(MyConfig.ConStr, MyConfig.DbBaseStr, table.TableName,table.SchemaName)){
WriteClassAttr(column);
}
PopIndent();
#>
}
}
<#
manager.EndBlock();
}
#>
<# manager.Process(true); #> <#+
//生成类的行
public void WriteClassAttr(DbColumn column){
string CShareType=column.CSharpType;
if(column.CommonType.IsValueType&& column.IsNullable){CShareType="Nullable<"+CShareType+">";}
WriteLine("/// <summary>");
WriteLine("/// "+column.Remark.Replace(Environment.NewLine, ""));
WriteLine("/// </summary>");
WriteLine("public " + CShareType+" "+column.ColumnName + " { get; set; }");
}
#> <#+
public class MyConfig
{
public static readonly string ConStr=config.WFConnectionString;
public static readonly string DbBaseStr=config.WFDbDatabase;
public static readonly string DbTableStr=config.WFDbTables;
}
#>
数据访问模板：Repository.tt
<#@ template debug="false" hostspecific="true" language="C#" #>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Configuration" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System.Linq" #> <#@include file="../TempleteManager.ttinclude"#>
<#@ include file="../TemplateDbhelper.ttinclude" #>
<# var manager = Manager.Create(Host, GenerationEnvironment); #> <#
foreach(DbTable table in DbHelper.GetDbTableViews(MyConfig.ConStr,MyConfig.DbBaseStr,MyConfig.DbTableStr)){
manager.StartNewFile(table.TableName+"Repository.cs");
string InsertStr,AllColumnStr, ModifyStr, AllAttrParam,ModifyAttrParam,PrimaryKeyParam,InsertReturnId;
CommonSql(table,out InsertStr,out AllColumnStr,out ModifyStr,out AllAttrParam,out ModifyAttrParam,out PrimaryKeyParam,out InsertReturnId);
#>
//------------------------------------------------------------------------------
// <auto-generated>
// 此代码已从模板生成。
// 手动更改此文件可能导致应用程序出现意外的行为。
// 如果重新生成代码，将覆盖对此文件的手动更改。
// </auto-generated>
//------------------------------------------------------------------------------
using System;
using System.Collections.Generic;
using ABS.WF.Model;
using System.Data.SqlClient;
using ABS.WF.Repository.DBUtility;
using System.Data;
using ABS.WF.Core.IRepository; namespace ABS.WF.Repository
{
public partial class <#=table.TableName#>Repository:I<#=table.TableName#>Repository
{ public int AddReturnId(<#=table.TableName#> model)
{
<#
WriteLine(InsertReturnId);
#>
SqlParameter[] param = { <#=AllAttrParam#> };
return Convert.ToInt32(SqlHelper.ExecuteScalar(SqlHelper.RwWfConnString, CommandType.Text, sql, param));
} public int AddReturnId(<#=table.TableName#> model, SqlTransaction trans)
{
<#
WriteLine(InsertReturnId);
#>
SqlParameter[] param = { <#=AllAttrParam#> };
return Convert.ToInt32(SqlHelper.ExecuteScalar(trans, CommandType.Text, sql, param));
} public int Add(<#=table.TableName#> model)
{
<#
WriteLine(InsertStr);
#>
SqlParameter[] param = { <#=AllAttrParam#> };
return SqlHelper.ExecuteNonQuery(SqlHelper.RwWfConnString, CommandType.Text, sql, param);
} public int Add(<#=table.TableName#> model, SqlTransaction trans)
{
<#
WriteLine(InsertStr);
#>
SqlParameter[] param = { <#=AllAttrParam#> };
return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, param);
} public int Modify(<#=table.TableName#> model)
{
string sql = "<#=ModifyStr#> where <#=GetCondiByPK(table)#>";
SqlParameter[] param = { <#=ModifyAttrParam#> };
return SqlHelper.ExecuteNonQuery(SqlHelper.RwWfConnString, CommandType.Text, sql, param);
} public int Modify(<#=table.TableName#> model, SqlTransaction trans)
{
string sql = "<#=ModifyStr#> where <#=GetCondiByPK(table)#>";
SqlParameter[] param = { <#=ModifyAttrParam#> };
return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, param);
} public int Delete(<#=table.TableName#> model)
{
string sql = "delete <#=table.TableName#> where <#=GetCondiByPK(table)#>";
SqlParameter[] param = { <#=GetParamByPK(table)#> };
return SqlHelper.ExecuteNonQuery(SqlHelper.RwWfConnString, CommandType.Text, sql, param);
} public int Delete(int id)
{
string sql = "delete <#=table.TableName#> where <#=GetCondiByPK(table,"id")#>";
SqlParameter[] param = { <#=GetParamByPK(table,"id")#> };
return SqlHelper.ExecuteNonQuery(SqlHelper.RwWfConnString, CommandType.Text, sql, param);
} public int Delete(int id, SqlTransaction trans)
{
string sql = "delete <#=table.TableName#> where <#=GetCondiByPK(table,"id")#>";
SqlParameter[] param = { <#=GetParamByPK(table,"id")#> };
return SqlHelper.ExecuteNonQuery(trans, CommandType.Text, sql, param);
}
/// <summary>
/// 只读库读取数据
/// </summary>
public <#=table.TableName#> Get(int id)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> WITH(NOLOCK) where <#=GetCondiByPK(table,"id")#>";
SqlParameter[] param = { <#=GetParamByPK(table,"id")#> };
<#=table.TableName#> model = new <#=table.TableName#>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RoWfConnString, CommandType.Text, sql, param))
{
while (reader.Read())
{
int i = ;
<# ReadToModel(table);#>
}
}
return model;
}
/// <summary>
/// 读写库读取数据
/// </summary>
public <#=table.TableName#> GetRw(int id)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> where <#=GetCondiByPK(table,"id")#>";
SqlParameter[] param = { <#=GetParamByPK(table,"id")#> };
<#=table.TableName#> model = new <#=table.TableName#>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RwWfConnString, CommandType.Text, sql, param))
{
while (reader.Read())
{
int i = ;
<# ReadToModel(table);#>
}
}
return model;
} /// <summary>
/// 带事物库读取数据
/// </summary>
/// <param name="id">主键</param>
/// <param name="trans"></param>
/// <returns></returns>
public <#=table.TableName#> Get(int id, SqlTransaction trans)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> where <#=GetCondiByPK(table,"id")#>";
SqlParameter[] param = { <#=GetParamByPK(table,"id")#> };
<#=table.TableName#> model = new <#=table.TableName#>();
using (SqlDataReader reader = SqlHelper.ExecuteReader(trans, CommandType.Text, sql, param))
{
while (reader.Read())
{
int i = ;
<# ReadToModel(table);#>
}
}
return model;
} /// <summary>
/// 只读库读取数据
/// </summary>
public List<<#=table.TableName#>> GetAll()
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> WITH(NOLOCK)";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RoWfConnString, CommandType.Text, sql))
{
return SqlDataReaderToListObject(reader);
}
} /// <summary>
/// 读写库读取数据
/// </summary>
public List<<#=table.TableName#>> GetAllRw()
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#>";
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RwWfConnString, CommandType.Text, sql))
{
return SqlDataReaderToListObject(reader);
}
}
/// <summary>
/// 只读库读取数据
/// </summary>
/// <param name="trans"></param>
/// <param name="Condi">条件</param>
/// <param name="param">参数</param>
/// <returns></returns>
public List<<#=table.TableName#>> GetAll(string Condi, params SqlParameter[] param)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> WITH(NOLOCK) "+Condi;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RoWfConnString, CommandType.Text, sql,param))
{
return SqlDataReaderToListObject(reader);
}
}
/// <summary>
/// 读写库读取数据
/// </summary>
/// <param name="trans"></param>
/// <param name="Condi">条件</param>
/// <param name="param">参数</param>
/// <returns></returns>
public List<<#=table.TableName#>> GetAllRw(string Condi, params SqlParameter[] param)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> "+Condi;
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RwWfConnString, CommandType.Text, sql,param))
{
return SqlDataReaderToListObject(reader);
}
}
/// <summary>
/// 只读库读取数据 分页
/// </summary>
/// <param name="Condi">条件</param>
/// <param name="order">排序字段</param>
/// <param name="start">开始条数</param>
/// <param name="limit">读取条数</param>
/// <param name="param">参数</param>
/// <returns></returns>
public List<<#=table.TableName#>> GetAll(string Condi, string order, long start, long limit, out long count, params SqlParameter[] param)
{
string sqlData = "select * from(select <#=AllColumnStr#>,ROW_NUMBER()over(order by "+order+") ROW_NUMBERId from <#=table.TableName#> WITH(NOLOCK) "+Condi+") resultTemp where ROW_NUMBERId between "+start+" and "+ (start+limit) ;
string sqlCount = "select COUNT(1) from <#=table.TableName#> WITH(NOLOCK) " + Condi;
count = long.Parse(SqlHelper.ExecuteScalar(SqlHelper.RoWfConnString, CommandType.Text, sqlCount, param).ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RoWfConnString, CommandType.Text, sqlData, param))
{
return SqlDataReaderToListObject(reader);
}
}
/// <summary>
/// 读写库读取数据 分页
/// </summary>
/// <param name="Condi">条件</param>
/// <param name="order">排序字段</param>
/// <param name="start">开始条数</param>
/// <param name="limit">读取条数</param>
/// <param name="param">参数</param>
/// <returns></returns>
public List<<#=table.TableName#>> GetAllRw(string Condi, string order, long start, long limit, out long count, params SqlParameter[] param)
{
string sqlData = "select * from(select <#=AllColumnStr#>,ROW_NUMBER()over(order by "+order+") ROW_NUMBERId from <#=table.TableName#>"+Condi+") resultTemp where ROW_NUMBERId between "+start+" and "+ (start+limit) ;
string sqlCount = "select COUNT(1) from <#=table.TableName#>" + Condi;
count = long.Parse(SqlHelper.ExecuteScalar(SqlHelper.RwWfConnString, CommandType.Text, sqlCount, param).ToString());
using (SqlDataReader reader = SqlHelper.ExecuteReader(SqlHelper.RwWfConnString, CommandType.Text, sqlData, param))
{
return SqlDataReaderToListObject(reader);
}
}
/// <summary>
/// 带事物库读取数据
/// </summary>
public List<<#=table.TableName#>> GetAll(SqlTransaction trans)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#>";
using (SqlDataReader reader = SqlHelper.ExecuteReader(trans, CommandType.Text, sql))
{
return SqlDataReaderToListObject(reader);
}
} /// <summary>
/// 带事物库读取数据
/// </summary>
/// <param name="trans"></param>
/// <param name="Condi">条件</param>
/// <param name="param">参数</param>
/// <returns></returns>
public List<<#=table.TableName#>> GetAll(SqlTransaction trans,string Condi, params SqlParameter[] param)
{
string sql = "select <#=AllColumnStr#> from <#=table.TableName#> "+Condi;
using (SqlDataReader reader = SqlHelper.ExecuteReader(trans, CommandType.Text, sql,param))
{
return SqlDataReaderToListObject(reader);
}
} /// <summary>
/// sqlDataReader转成该类的集合
/// </summary>
/// <param name="reader"></param>
/// <returns></returns>
public static List<<#=table.TableName#>> SqlDataReaderToListObject(SqlDataReader reader)
{
List<<#=table.TableName#>> listresult = new List<<#=table.TableName#>>();
while (reader.Read())
{
int i = ;
<#=table.TableName#> model = new <#=table.TableName#>();
<# ReadToModel(table);#>
listresult.Add(model);
}
return listresult;
}
}
}
<#
manager.EndBlock();
}
#>
<# manager.Process(true); #> <#+
/// <summary>
/// 读取结果映射封装
/// </summary>
public void ReadToModel(DbTable table){
PushIndent(" ");
foreach (DbColumn column in DbHelper.GetDbColumns(MyConfig.ConStr,MyConfig.DbBaseStr, table.TableName))
{
WriteLine("model."+column.ColumnName+" = DbLib."+column.CSharpConvert+"(reader,i++);");
}
PopIndent();
} /// <summary>
/// 根据主键进行操作的条件语句
///@table table
///@id 函数id名，不传自动获取所有id为条件
/// </summary>
public string GetCondiByPK(DbTable table,string id=null){
string sqlcondi="";
if (table.HasPrimaryKey) //有主键
{
if (id==null)
{
for (int i = ; i < table.PrimaryKey.Length; i++)
{
string columnName=table.PrimaryKey[i];
sqlcondi+=columnName+"=@"+columnName;
if(i!=table.PrimaryKey.Length-){
sqlcondi+=" and ";
}
}
}else{
string columnName=table.PrimaryKey[];
sqlcondi+=columnName+"=@"+columnName;
}
}
return sqlcondi;
} /// <summary>
/// 根据主键进行操作的参数
///@table table
///@id 函数id名，不传自动获取所有id为条件
/// </summary>
public string GetParamByPK(DbTable table,string id=null){
string param="";
if (table.HasPrimaryKey) //有主键
{
if (id==null)
{
for (int i = ; i < table.PrimaryKey.Length; i++)
{
string columnName=table.PrimaryKey[i];
param+="new SqlParameter(\"@"+columnName+"\", model."+columnName+")";
if(i!=table.PrimaryKey.Length-){
param+=",";
}
}
}else{ //只根据id删除
string columnName=table.PrimaryKey[];
param+="new SqlParameter(\"@"+columnName+"\","+id+")";
}
}
return param;
} public void CommonSql(DbTable table,out string InsertStr,out string AllColumnStr,out string ModifyStr,out string AllAttrParam,out string ModifyAttrParam,out string PrimaryKeyParam,out string InsertReturnId){
string InsertColumunStr="";
AllColumnStr="";
string InsertValueStr="";
ModifyStr="update "+table.TableName+" set ";
AllAttrParam="";
PrimaryKeyParam="";
ModifyAttrParam="";
foreach (DbColumn column in DbHelper.GetDbColumns(MyConfig.ConStr,MyConfig.DbBaseStr, table.TableName))
{
AllColumnStr+=column.ColumnName+",";
//ModifyAttrParam+="new SqlParameter(\"@"+column.ColumnName+"\", model."+column.ColumnName+"),";ColumnType
ModifyAttrParam+="new SqlParameter(\"@"+column.ColumnName+"\", SqlDbType."+column.SqlDbType_ColumnType+","+column.CharLength+") { Value = "+(column.IsNullable?"SqlHelper.SqlParamterNullValue(model."+column.ColumnName+")":"model."+column.ColumnName+"")+" },";
if (!column.IsIdentity)
{
InsertColumunStr+=column.ColumnName+",";
InsertValueStr+="@"+column.ColumnName+",";
AllAttrParam+="new SqlParameter(\"@"+column.ColumnName+"\", SqlDbType."+column.SqlDbType_ColumnType+","+column.CharLength+") { Value = "+(column.IsNullable?"SqlHelper.SqlParamterNullValue(model."+column.ColumnName+")":"model."+column.ColumnName+"")+" },";
}
if(column.IsPrimaryKey){
PrimaryKeyParam+="new SqlParameter(\"@"+column.ColumnName+"\", SqlDbType."+column.SqlDbType_ColumnType+","+column.CharLength+") { Value = "+(column.IsNullable?"SqlHelper.SqlParamterNullValue(model."+column.ColumnName+")":"model."+column.ColumnName+"")+" },";
}else{
ModifyStr+=column.ColumnName+"=@"+column.ColumnName+",";
}
}
InsertColumunStr=InsertColumunStr.Substring(,InsertColumunStr.Length-);
AllColumnStr=AllColumnStr.Substring(,AllColumnStr.Length-);
ModifyAttrParam=ModifyAttrParam.Substring(,ModifyAttrParam.Length-);
InsertValueStr=InsertValueStr.Substring(,InsertValueStr.Length-);
ModifyStr=ModifyStr.Substring(,ModifyStr.Length-);
if(PrimaryKeyParam!="")
PrimaryKeyParam=PrimaryKeyParam.Substring(,PrimaryKeyParam.Length-);
AllAttrParam=AllAttrParam.Substring(,AllAttrParam.Length-);
InsertStr="string sql=\"insert into "+table.TableName+"("+InsertColumunStr+") values("+InsertValueStr+")\";";
InsertReturnId="string sql=\"insert into "+table.TableName+"("+InsertColumunStr+") values("+InsertValueStr+");select @@IDENTITY AS ID\";";
}
#>
<#+
public class MyConfig
{
public static readonly string ConStr=config.WFConnectionString;
public static readonly string DbBaseStr=config.WFDbDatabase;
public static readonly string DbTableStr=config.WFDbTables;
}
#>
Repository Code