//Model.tt文件，这个文件只要按Ctrl+S就能自动运行此文件，然后生成实体类，生成的是整个数据库的所有表
<#@ template debug="true" hostspecific="true" language="C#" #>
<#@ assembly name="System.Data" #>
<#@ assembly name="System.xml" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.SqlClient" #>
<#@ import namespace="System.Data" #>
<#@ assembly name="System.Core" #>
<#@ import namespace="System.Linq" #>
<#@ include file="MultipleOutputHelper.ttinclude" #>
<#
string connectionString = "Data Source=100.20.100.44;Initial Catalog=test;User ID=test;Password=123456;";
SqlConnection conn = new SqlConnection(connectionString);
conn.Open();
string selectQuery ="SET FMTONLY ON; select * from @tableName; SET FMTONLY OFF;";
SqlCommand command = new SqlCommand(selectQuery,conn);
SqlDataAdapter ad = new SqlDataAdapter(command);
System.Data.DataSet ds = new DataSet();
var manager = Manager.Create(Host, GenerationEnvironment);
System.Data.DataTable schema = conn.GetSchema("Tables");
foreach(System.Data.DataRow row in schema.Rows)
{
ds.Tables.Clear();
string tb_name2= row["TABLE_NAME"].ToString();
//类名首字母大写
string tb_name = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(tb_name2);
command.CommandText = selectQuery.Replace("@tableName",row["TABLE_NAME"].ToString());
ad.FillSchema(ds, SchemaType.Mapped,tb_name);
manager.StartNewFile(tb_name+".cs");#>using System;
using Dapper;
namespace Mall.Entity
{
/// <summary>
/// 实体-<#=tb_name#>
/// </summary>
[Table("<#=tb_name#>")]
public partial class <#=tb_name#>
{
<#
PushIndent(" ");
foreach (DataColumn dc in ds.Tables[0].Columns)
{
//根据表名称和字段名称查出字段描述 comment
string sql = @"select d.value from sys.syscolumns a left outer join sys.extended_properties d on a.id = d.major_id and a.colid = d.minor_id and d.name = 'MS_Description' where object_name(a.id) = '{0}' and a.name='{1}' ";
SqlCommand cmd = new SqlCommand(string.Format(sql, row["TABLE_NAME"],dc.ColumnName),conn);
object comment= cmd.ExecuteScalar();
DataColumn[] keys = ds.Tables[0].PrimaryKey;
string columnName = dc.ColumnName;
char ch = columnName.First();
string columnNameUp =columnName;
if (ch >= 65 && ch <= 90) {
//这个是大写字母
}else{
columnNameUp = System.Threading.Thread.CurrentThread.CurrentCulture.TextInfo.ToTitleCase(columnName);
}
if (keys.Contains(dc))
{
WriteLine("\r\n ///<summary>\r\n ///" + comment + "\r\n///</summary>\r\n[Key]\r\n[Column(\"" + columnName + "\")] \r\n public " + dc.DataType.Name + (dc.AllowDBNull && dc.DataType.Name.ToLower() != "string" ? "? " : " ") + columnNameUp + " { get; set; }");
}
else
{
WriteLine("\r\n ///<summary>\r\n ///" + comment + "\r\n ///</summary>\r\n[Column(\"" + columnName + "\")] \r\n public " + dc.DataType.Name + (dc.AllowDBNull && dc.DataType.Name.ToLower() != "string" ? "? " : " ") + columnNameUp + " { get; set; }");
}
}
//查询一个表的信息 当前表的所有字段,字段中没有查自增健
string files = "select a.name, a.isnullable, a.iscomputed,case when d.value is null then a.name else d.value end as comment, type_name(a.xusertype) as type, a.length, case when (a.status & 0x80) = 0x80 then 1 else 0 end as is_identity, case when index_col (OBJECT_NAME(a.id), b.indid, a.colid) = a.name then 1 else 0 end as is_primary, case when ( TYPE_NAME(a.xusertype) IN ('xml','text','ntext','image') OR (TYPE_NAME(a.xusertype) IN ('varchar','nvarchar','varbinary') AND a.length = -1) ) then 1 else 0 end as is_lob, case when ( c.ctext is null ) then 'null' else substring(c.text, 2, len(c.text) - 2 ) end as defaultValue from sys.syscolumns a left outer join sys.extended_properties d on a.id = d.major_id and a.colid = d.minor_id and d.name = 'MS_Description' left outer join sys.sysindexes b on a.id = b.id and ( b.status & 0x800) = 0x800 left outer join sys.syscomments c on a.cdefault = c.id where object_name(a.id) = '"+row["TABLE_NAME"].ToString()+"' and a.status<>128 order by a.colorder";
SqlCommand cmd1 = new SqlCommand(files, conn);
SqlDataAdapter adFiles = new SqlDataAdapter(cmd1);
DataSet dsFiles = new DataSet();
adFiles.MissingSchemaAction = MissingSchemaAction.AddWithKey;
adFiles.Fill(dsFiles);
PopIndent();
#>
}
}
<#
manager.EndBlock();
}
conn.Close();
manager.Process(true);
#>
//文件处理
MultipleOutputHelper.ttinclude
<#@ assembly name="System.Core"
#><#@ assembly name="System.Data.Linq"
#><#@ assembly name="EnvDTE"
#><#@ assembly name="System.Xml"
#><#@ assembly name="System.Xml.Linq"
#><#@ import namespace="System"
#><#@ import namespace="System.CodeDom"
#><#@ import namespace="System.CodeDom.Compiler"
#><#@ import namespace="System.Collections.Generic"
#><#@ import namespace="System.Data.Linq"
#><#@ import namespace="System.Data.Linq.Mapping"
#><#@ import namespace="System.IO"
#><#@ import namespace="System.Linq"
#><#@ import namespace="System.Reflection"
#><#@ import namespace="System.Text"
#><#@ import namespace="System.Xml.Linq"
#><#@ import namespace="Microsoft.VisualStudio.TextTemplating"
#><#+
// 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 List<Block> files = new List<Block>();
private Block footer = new Block();
private Block header = new Block();
private ITextTemplatingEngineHost host;
private StringBuilder template;
protected 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 EnvDTE.ProjectItem templateProjectItem;
private EnvDTE.DTE dte;
private Action<String> checkOutAction;
private Action<IEnumerable<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 = (String fileName) => dte.SourceControl.CheckOutItem(fileName);
projectSyncAction = (IEnumerable<String> keepFileNames) => ProjectSync(templateProjectItem, keepFileNames);
}
private static void ProjectSync(EnvDTE.ProjectItem templateProjectItem, IEnumerable<String> keepFileNames) {
var keepFileNameSet = new HashSet<String>(keepFileNames);
var projectFiles = new Dictionary<String, EnvDTE.ProjectItem>();
var originalFilePrefix = Path.GetFileNameWithoutExtension(templateProjectItem.get_FileNames(0)) + ".";
foreach(EnvDTE.ProjectItem projectItem in templateProjectItem.ProjectItems)
projectFiles.Add(projectItem.get_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));
}
}
} #>