对于C#面向对象的思想，我们习惯于将数据库中的表创建对应的数据模型；
但假如数据表很多时，我们手动增加模型类会显得很浪费时间；
这个时候有些人会用微软提供的EntityFrameWork，这个框架很强大，编写代码效率也很高，但很由于性能差，在复杂查询的时候生成的sql脚本效率不是很高，所以有的时候不会去使用它；
这个时候就会有CodeSmith来协助我们去完成那些费时费力的工作：
CodeSmith如何使用，网上也有很详细的介绍了，下面代码只是简单介绍
属性SourceDataBase是连接的数据库，CodeSmith提供连接数据库的方法很方便
属性NameSpace顾名思义就是命名空间
<%--
Name:批量生成实体类
Author: TitanChen
Description:批量将数据库中的表结构生成数据模型
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="C#" Description="Template description here." %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Property Name="SourceDatabase" Type="SchemaExplorer.DatabaseSchema" Category="Context" %>
<%@ Property Name="NameSpace" Type="String" Category="参数" Description="命名空间" Default="Blog.Core.Model" Optional="True"%>
<%@ Import Namespace="System.Text.RegularExpressions" %>
using System;
using System.Collections.Generic;
using System.Text; namespace <%=NameSpace%>
{
<% foreach(TableSchema SourceTable in SourceDatabase.Tables) { %>
/// <summary>
/// <%=GetClassName(SourceTable) +"模型"%>
/// </summary>
[Serializable]
public class <%=GetClassName(SourceTable) %> : BaseModel
{
/// <summary>
/// 表名
/// </summary>
public static readonly string TableName = "<%=GetClassName(SourceTable) %>"; /// <summary>
/// 构造函数
/// </summary>
public <%=GetClassName(SourceTable) %>() : base(TableName)
{
} private Guid Id = Guid.Empty;
<% foreach (ColumnSchema column in SourceTable.Columns) {%>
/// <summary>
/// <%=column.Description %>
/// </summary>
<% if(column.IsPrimaryKeyMember){ %>
public Guid <%= GetPascalName(column) %>
{
get{ return Id;}
set
{
Id = value;
if (value != null)
{
base.BaseId = value;
}
}
} <% }else{ %>
public <%=GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; } <% } %>
<% }%>
} /// <summary>
/// <%=GetClassName(SourceTable) +"数据模型"%>
/// </summary>
[Serializable]
public class <%=GetClassName(SourceTable)+"ListData" %>
{
/// <summary>
/// 总记录数
/// </summary>
public int RecordCount { get; set; } /// <summary>
/// 数据列表
/// </summary>
public List<<%=GetClassName(SourceTable)+"ListModel" %>> RecordList { get; set; }
} /// <summary>
/// <%=GetClassName(SourceTable) +"列表模型"%>
/// </summary>
[Serializable]
public class <%=GetClassName(SourceTable)+"ListModel" %>
{
<% foreach (ColumnSchema column in SourceTable.Columns) {%>
<%if(new string[]{"IsDeleted"}.Contains(column.Name)){continue;} %>
/// <summary>
/// <%=column.Description %>
/// </summary>
public <%=GetCSharpVariableType(column)=="Guid" || GetCSharpVariableType(column)=="DateTime"?"string":GetCSharpVariableType(column) %> <%=GetPascalName(column) %> { get; set; } <% }%>
}
<%} %>
}
<script runat="template"> public string MakeSingle(string name)
{
return name;
}
public string GetCamelName(ColumnSchema column)
{
return column.Name.Substring(, ).ToLower() + column.Name.Substring();
}
public string GetCamelName(string value)
{
return value.Substring(, ).ToLower() + value.Substring();
}
public string GetPascalName(ColumnSchema column)
{
return column.Name.Substring(, ).ToUpper() + column.Name.Substring();
}
public string GetPascalName(string value)
{
return value.Substring(, ).ToUpper() + value.Substring();
}
public string GetClassName(TableSchema table)
{
return GetPascalName(MakeSingle(table.Name));
}
public string GetForeignKeyColumnType(ColumnSchema column)
{
return column.Table.ForeignKeys[].PrimaryKeyTable.Name;
}
public string GetForeignKeyColumnName(ColumnSchema column)
{
if(column.Name.Substring(column.Name.Length-).ToLower() == "id")
{
return column.Name.Substring(,column.Name.Length-);
}
else
{
return column.Name;
}
}
public string GetPrimaryKeyType(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return GetCSharpVariableType(table.PrimaryKey.MemberColumns[]);
}
else
{
throw new ApplicationException("This template will not work on primary keys with more than one member column.");
}
}
else
{
throw new ApplicationException("This template will only work on tables with a primary key.");
}
}
public string GetCSharpVariableType(ColumnSchema column)
{
if (column.Name.EndsWith("TypeCode")) return column.Name; switch (column.DataType)
{
case DbType.AnsiString: return "string";
case DbType.AnsiStringFixedLength: return "string";
case DbType.Binary: return "byte[]";
case DbType.Boolean: return "bool";
case DbType.Byte: return "byte";
case DbType.Currency: return "decimal";
case DbType.Date: return "DateTime";
case DbType.DateTime: return "DateTime";
case DbType.Decimal: return "decimal";
case DbType.Double: return "double";
case DbType.Guid: return "Guid";
case DbType.Int16: return "short";
case DbType.Int32: return "int";
case DbType.Int64: return "long";
case DbType.Object: return "object";
case DbType.SByte: return "sbyte";
case DbType.Single: return "float";
case DbType.String: return "string";
case DbType.StringFixedLength: return "string";
case DbType.Time: return "TimeSpan";
case DbType.UInt16: return "ushort";
case DbType.UInt32: return "uint";
case DbType.UInt64: return "ulong";
case DbType.VarNumeric: return "decimal";
default:
{
return "__UNKNOWN__" + column.NativeType;
}
}
}
</script>
CodeSmith虽然方便，但是要安装和激活，这个是很麻烦的；而且每次生成都要打开CodeSmith去生成，不是很方便；
于是我就照着原先在CodeSmith上模板写了个控制台应用程序，可以改写配合着bat使用，贼方便
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;
using System.Linq;
using System.IO; namespace Blog.Core.Test
{
public class Program
{
/// <summary>
/// 数据库连接字符串
/// </summary>
private static string _connstr = "Data Source=localhost;Initial Catalog=Test;User Id=sa;Password=123456"; /// <summary>
/// 主函数
/// </summary>
/// <param name="args"></param>
static void Main(string[] args)
{
Console.Write("命名空间：");
string namespaces = Console.ReadLine();
Console.Write("文件名：");
string filename = Console.ReadLine();
Console.WriteLine("开始生成，请等待...");
new Program().Generate(namespaces, filename);
Console.WriteLine("生成成功...");
Console.ReadKey();
} /// <summary>
/// 生成Model文件
/// </summary>
/// <param name="namespaces"></param>
/// <param name="filename"></param>
private void Generate(string namespaces, string filename)
{
byte[] myByte = Encoding.UTF8.GetBytes(BuildTemplete(namespaces));
string filepath = Environment.CurrentDirectory + "\\" + filename;
if (File.Exists(filepath))
{
File.Delete(filepath);
}
using (FileStream fsWrite = new FileStream(filepath, FileMode.Append))
{
fsWrite.Write(myByte, , myByte.Length);
};
} /// <summary>
/// 创建模板
/// </summary>
/// <param name="namespaces"></param>
/// <returns></returns>
private string BuildTemplete(string namespaces)
{
StringBuilder templete = new StringBuilder("using System;");
templete.Append("using System.Collections.Generic;\n\n");
templete.AppendFormat("namespace {0}\n{{\n", namespaces);
List<TableModel> tables = GetTables();
foreach (var table in tables)
{
templete.AppendFormat(" #region {0}\n", table.name);
templete.Append(" /// <summary>\n");
templete.AppendFormat(" /// {0}模型\n", table.name);
templete.Append(" /// </summary>\n");
templete.Append(" [Serializable]\n");
templete.AppendFormat(" public class {0} : BaseModel\n {{", table.name);
templete.Append("\n");
templete.Append(" /// <summary>\n");
templete.Append(" /// 表名\n");
templete.Append(" /// </summary>\n");
templete.AppendFormat(" public static readonly string TableName = \"{0}\";\n", table.name);
templete.Append("\n");
templete.Append(" /// <summary>\n");
templete.Append(" /// 构造函数\n");
templete.Append(" /// </summary>\n");
templete.AppendFormat(" public {0}() : base(TableName) {{ }}\n", table.name);
templete.Append(" private Guid Id = Guid.Empty;\n");
table.columns.ForEach(columu =>
{
templete.Append("\n");
templete.Append(" /// <summary>\n");
templete.AppendFormat(" /// {0}\n", columu.ColComment);
templete.Append(" /// </summary>\n");
if (columu.IsPk)
{
templete.AppendFormat(" public Guid {0}\n", columu.ColName);
templete.Append(" {\n");
templete.Append(" get { return Id; }\n");
templete.Append(" set\n");
templete.Append(" {\n");
templete.Append(" Id = value;\n");
templete.Append(" if (value != null)\n");
templete.Append(" {\n");
templete.Append(" base.BaseId = value;\n");
templete.Append(" }\n");
templete.Append(" }\n");
templete.Append(" }\n");
}
else
{
templete.AppendFormat(" public {0} {1} {{ get; set; }} {2}\n", GetCSType(columu.ColType), columu.ColName, GetCSDefault(columu.ColDefault));
}
});
templete.Append(" }"); templete.Append("\n"); templete.Append(" /// <summary>\n");
templete.AppendFormat(" /// {0}数据模型\n", table.name);
templete.Append(" /// </summary>\n");
templete.Append(" [Serializable]\n");
templete.AppendFormat(" public class {0}ListData\n {{", table.name);
templete.Append("\n");
templete.Append(" /// <summary>\n");
templete.Append(" /// 总记录数\n");
templete.Append(" /// </summary>\n");
templete.Append(" public int RecordCount { get; set; }\n");
templete.Append(" /// <summary>\n");
templete.Append("\n");
templete.Append(" /// 数据列表\n");
templete.Append(" /// </summary>\n");
templete.AppendFormat(" public List<{0}ListModel> RecordList {{ get; set; }}\n", table.name);
templete.Append(" }"); templete.Append("\n"); templete.Append(" /// <summary>\n");
templete.AppendFormat(" /// {0}列表模型\n", table.name);
templete.Append(" /// </summary>\n");
templete.Append(" [Serializable]\n");
templete.AppendFormat(" public class {0}ListModel\n {{", table.name);
templete.Append("\n");
table.columns.ForEach(columu =>
{
if (columu.ColName != "IsDeleted")
{
templete.Append("\n");
templete.Append(" /// <summary>\n");
templete.AppendFormat(" /// {0}\n", columu.ColComment);
templete.Append(" /// </summary>\n");
if (new string[] { "Guid", "DateTime" }.Contains(GetCSType(columu.ColType)))
{
templete.AppendFormat(" public string {0} {{ get; set; }}\n", columu.ColName);
}
else
{
templete.AppendFormat(" public {0} {1} {{ get; set; }}\n", GetCSType(columu.ColType), columu.ColName);
}
}
});
templete.Append(" }\n");
templete.Append(" #endregion\n");
templete.Append("\n");
}
templete = templete.Remove(templete.Length - , );
templete.Append("}");
return templete.ToString();
} /// <summary>
/// 获取表数据
/// </summary>
/// <returns></returns>
private List<TableModel> GetTables()
{
List<TableModel> tables = new List<TableModel>();
DataTable tabName = Query("SELECT name AS TableName FROM sysobjects WHERE xtype = 'U'");
DataTable colName = Query(@"--获取表名、字段名称、字段类型、字段说明、字段默认值
SELECT obj.name AS TableName,--表名
col.name AS ColName,--列名
typ.name AS ColType,--字段类型
cmt.value AS ColComment,--字段说明
dft.text AS ColDefault--字段默认值
FROM syscolumns col--字段
INNER JOIN sysobjects obj--表
ON col.id = obj.id
AND obj.xtype = 'U'--表示用户表
LEFT JOIN systypes typ--类型
ON col.xtype = typ.xusertype
LEFT JOIN sys.extended_properties cmt--字段说明
ON col.id = cmt.major_id--表Id
AND col.colid = cmt.minor_id--字段Id
LEFT JOIN syscomments dft--默认值
ON col.cdefault = dft.id
ORDER BY obj.name,
col.id ASC
");
DataTable pk = Query(@"--获取表的主键字段名
SELECT CCU.COLUMN_NAME,
TC.TABLE_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = 'PRIMARY KEY'
");
foreach (DataRow row in tabName.Rows)
{
TableModel table = new TableModel();
table.name = row["TableName"].ToString(); ;
table.columns = new List<ColumnModel>();
DataRow[] cols = colName.Select(string.Format("TableName = '{0}'", row["TableName"].ToString()));
DataRow[] pks = pk.Select(string.Format("TABLE_NAME = '{0}'", row["TableName"].ToString()));
string primarykey = pks == null || pks.Length == ? "" : pks[]["COLUMN_NAME"].ToString();
foreach (DataRow col in cols)
{
ColumnModel column = new ColumnModel();
column.IsPk = primarykey == col["ColName"].ToString();
column.ColName = col["ColName"].ToString();
column.ColType = col["ColType"].ToString();
column.ColComment = col["ColComment"].ToString();
column.ColDefault = col["ColDefault"].ToString();
table.columns.Add(column);
}
tables.Add(table);
}
return tables; } /// <summary>
/// 简单的SQL查询
/// </summary>
/// <param name="sqlString"></param>
/// <returns></returns>
private DataTable Query(string sqlString)
{
DataTable dt = new DataTable();
using (SqlConnection conn = new SqlConnection(_connstr))
{
using (SqlCommand command = conn.CreateCommand())
{
command.CommandText = sqlString;
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = command;
adapter.Fill(dt);
}
}
return dt;
} /// <summary>
/// 获取C#类型
/// </summary>
/// <param name="sqlType"></param>
/// <returns></returns>
private string GetCSType(string sqlType)
{
switch (sqlType)
{
case "datetime":
return "DateTime";
case "int":
return "int";
case "nchar":
return "string";
case "nvarchar":
return "string";
case "varchar":
return "string";
case "text":
return "string";
case "ntext":
return "string";
case "uniqueidentifier":
return "Guid";
case "decimal":
return "decimal";
case "float":
return "float";
case "bit":
return "byte";
case "binary":
return "byte []";
case "varbinary":
return "byte []";
case "timestamp":
return "int";
default:
return "";
}
} /// <summary>
/// 获取C#默认值
/// </summary>
/// <param name="sqlValue"></param>
/// <returns></returns>
private string GetCSDefault(string sqlValue)
{
switch (sqlValue)
{
case "((0))":
return "= 0;";
case "('')":
return "= string.Empty;";
case "('00000000-0000-0000-0000-000000000000')":
return "= Guid.Empty;";
default:
return "";
}
}
} /// <summary>
/// 表模型
/// </summary>
public class TableModel
{
/// <summary>
/// 表名
/// </summary>
public string name { get; set; } /// <summary>
/// 表字段
/// </summary>
public List<ColumnModel> columns { get; set; }
} /// <summary>
/// 字段模型
/// </summary>
public class ColumnModel
{
/// <summary>
/// 是否主键
/// </summary>
public bool IsPk { get; set; } /// <summary>
/// 列名
/// </summary>
public string ColName { get; set; } /// <summary>
/// 列类型
/// </summary>
public string ColType { get; set; } /// <summary>
/// 列说明
/// </summary>
public string ColComment { get; set; } /// <summary>
/// 列默认值
/// </summary>
public string ColDefault { get; set; }
}
}