• T4模板生成代码。 数据实体层与数据仓储层。备注


    文件生成模板: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="*";
        }
    #>
    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 = 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;
        }
    #>
    Repository Code

    文件下载连接 http://download.csdn.net/download/drawdream/9916255

  • 相关阅读:
    shell中定义变量用双引号和单引号以及不用引号的区别
    如何优雅的解决mac安装zsh不执行.bash_profile
    Spring在非web应用中关闭IoC容器 (registerShutdownHook)
    【Quartz】配置最简单的集群
    【Quartz】将定时任务持久化到数据库
    Mysql字符串字段判断是否包含某个字符串的3种方法
    函数:MySQL中字符串匹配函数LOCATE和POSITION使用方法
    MySQL字符串函数substring:字符串截取
    logback logback.xml常用配置详解 <filter>
    logback 常用配置详解<appender>
  • 原文地址:https://www.cnblogs.com/bluesummer/p/7262482.html
Copyright © 2020-2023  润新知