文件生成模板：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(",")>=0) typeParam=string.Format(" (type='U' or type='V') and obj.name in ('{0}')", tables.Replace(",", "','")); else if(tables.IndexOf("%")>=0){ string[] tablenames=tables.Split(';'); typeParam+=" (type='U' or type='V') and ("; int index=0; foreach (string name in tablenames) { if (!string.IsNullOrWhiteSpace(name)){ if(index!=0){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(0,pkstr.Length-1); 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="*"; } #>
实体生成模板：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 = 0; <# 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 = 0; <# 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 = 0; <# 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 = 0; <#=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 = 0; i < table.PrimaryKey.Length; i++) { string columnName=table.PrimaryKey[i]; sqlcondi+=columnName+"=@"+columnName; if(i!=table.PrimaryKey.Length-1){ sqlcondi+=" and "; } } }else{ string columnName=table.PrimaryKey[0]; 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 = 0; i < table.PrimaryKey.Length; i++) { string columnName=table.PrimaryKey[i]; param+="new SqlParameter("@"+columnName+"", model."+columnName+")"; if(i!=table.PrimaryKey.Length-1){ param+=","; } } }else{ //只根据id删除 string columnName=table.PrimaryKey[0]; 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(0,InsertColumunStr.Length-1); AllColumnStr=AllColumnStr.Substring(0,AllColumnStr.Length-1); ModifyAttrParam=ModifyAttrParam.Substring(0,ModifyAttrParam.Length-1); InsertValueStr=InsertValueStr.Substring(0,InsertValueStr.Length-1); ModifyStr=ModifyStr.Substring(0,ModifyStr.Length-1); if(PrimaryKeyParam!="") PrimaryKeyParam=PrimaryKeyParam.Substring(0,PrimaryKeyParam.Length-1); AllAttrParam=AllAttrParam.Substring(0,AllAttrParam.Length-1); 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; } #>