新建一个C#模版。
model类的模版代码如下：
<%--
Name: 模型层代码生成模版
Author: XX
Description: 根据数据库的内容生成模型层代码
Version: V1. 新规初成
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" ResponseEncoding="UTF-8"%>
<%@ Property Name="Namespace" Type="String" Default="Model" Category="内容" Description="命名空间名称" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="内容" Description="数据源表" %>
<%@ Property Name="ObjectName" Type="String" Category="注释" Description="对象名称，为生成注释而用" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
using System;
using System.Collections.Generic;
using System.Text; namespace <%=Namespace%>
{
/// <summary>
/// <%=ObjectName%>的模型
/// </summary>
[Serializable()]
public class <%=GetClassName(SourceTable)%>
{
private <%=GetPrimaryKeyType(SourceTable)%> <%=GetPrimaryKeyFieldName(SourceTable)%>;
<%
// 循环输出非主键列的定义
foreach(ColumnSchema colum in SourceTable.NonPrimaryKeyColumns)
{
%>
private <%=GetDataTypeByColumn(colum)%> <%=GetFieldNameByColumn(colum)%>;
<%
}
%> public <%= GetClassName(SourceTable)%>() {} public <%=GetPrimaryKeyType(SourceTable)%> <%=GetPrimaryKeyPropertyName(SourceTable)%>
{
get{ return this.<%=GetPrimaryKeyFieldName(SourceTable)%>; }
set{ this.<%=GetPrimaryKeyFieldName(SourceTable)%> = value; }
} <%
// 循环输出非主键列的属性
foreach(ColumnSchema colum in SourceTable.NonPrimaryKeyColumns)
{
%>
public <%=GetDataTypeByColumn(colum)%> <%=GetPropertyNameByColumn(colum)%>
{
get{ return this.<%=GetFieldNameByColumn(colum)%>; }
set{ this.<%=GetFieldNameByColumn(colum)%> = value; }
} <%
}
%>
}
} <script runat="template"> // 根据表对象获得类名称
public string GetClassName(TableSchema table)
{
string tempTable;
if (table.Name.EndsWith("s"))
{
tempTable = table.Name.Substring(,table.Name.Length-);
}
else
{
tempTable = table.Name;
}
return ConvertToPascal(tempTable);
} // 根据表对象获得主键的类型
public string GetPrimaryKeyType(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return GetCSharpDataTypeByDBColumn(table.PrimaryKey.MemberColumns[]);
}
else
{
throw new ApplicationException("此模板只支持单个列的主键");
}
}
else
{
throw new ApplicationException("此模板需要有主键的表");
}
} // 根据表对象获得主键的名称(原始)
public string GetPrimaryKeyName(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return ConvertToCamel(table.PrimaryKey.MemberColumns[].Name);
}
else
{
throw new ApplicationException("此模板只支持单个列的主键");
}
}
else
{
throw new ApplicationException("此模板需要有主键的表");
}
} // 根据表对象获得主键的字段名(骆驼命名)
public string GetPrimaryKeyFieldName(TableSchema table)
{
return ConvertToCamel(GetPrimaryKeyName(table));
} // 根据表对象获得主键的属性名(帕斯卡命名)
public string GetPrimaryKeyPropertyName(TableSchema table)
{
return ConvertToPascal(GetPrimaryKeyName(table));
} // 根据列对象获得列的类型
public string GetDataTypeByColumn(ColumnSchema column)
{
return GetCSharpDataTypeByDBColumn(column);
} // 根据列对象获得列的字段名(骆驼命名)
public string GetFieldNameByColumn(ColumnSchema column)
{
return ConvertToCamel(column.Name);
} // 根据列对象获得列的属性名(帕斯卡命名)
public string GetPropertyNameByColumn(ColumnSchema column)
{
return ConvertToPascal(column.Name);
} // 根据列对象获得CSharp中类型
public string GetCSharpDataTypeByDBColumn(ColumnSchema column)
{
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;
}
}
} // 帕斯卡命名转换
public string ConvertToPascal(string str)
{
return str.Substring(,).ToUpper() + str.Substring();
} // 骆驼命名转换
public string ConvertToCamel(string str)
{
return str.Substring(,).ToLower() + str.Substring();
} // 重写获得文件名的方法
public override string GetFileName()
{
return GetClassName(SourceTable) + ".cs";
}
</script>
代码写好后，连接SQL数据库。
点击图中红色处。
（注：该数据库类型为sqlserver类型）
添加完成后效果如下图：（该添加数据库的表全部显示出来了。）
再列表中选择你要生成的实体类的表，单击select按钮。
再将实体类名称写入。
点击红色框中的按钮，进行生成。生成页面如下图：
最后再保存到自己想要保存的地方即可。
业务逻辑层的模版如下：
<%--
Name: 数据访问层代码生成模版
Author: Fencer
Description: 根据数据库的内容生成模型层代码
Version: V1. 新规初成
--%>
<%@ CodeTemplate Language="C#" TargetLanguage="Text" ResponseEncoding="UTF-8"%>
<%@ Property Name="Namespace" Type="String" Default="SQLServerDAL" Category="内容" Description="命名空间名称" %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="内容" Description="数据源表" %>
<%@ Property Name="ObjectName" Type="String" Category="注释" Description="对象名称，为生成注释而用" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;
namespace <%=Namespace%>
{ /// <summary>
/// <%=ObjectName%>的数据访问操作
/// </summary>
public class <%=GetModelName(SourceTable)%>BLL
{
/// <summary>
/// 添加<%=ObjectName%>
/// </summary>
/// <param name="<%=GetModelParameterName(SourceTable)%>"><%=ObjectName%></param>
/// <returns>最新编号</returns>
public int Add<%=GetModelName(SourceTable)%>(<%=GetModelName(SourceTable)%> <%=GetModelParameterName(SourceTable)%>)
{
string sql = <%=GetInsertSql()%>; SqlParameter[] pas = new SqlParameter[]
{
<%
for(int i=; i<SourceTable.NonPrimaryKeyColumns.Count; i++)
{
ColumnSchema column = SourceTable.NonPrimaryKeyColumns[i];
%>
new SqlParameter("@<%=column.Name%>",SqlDbType.<%=GetSqlDbType(column)%>,<%=column.Size%>)<%= (i==(SourceTable.NonPrimaryKeyColumns.Count-)? "":",")%>
<%
}
%>
}; <%
for(int i=; i<SourceTable.NonPrimaryKeyColumns.Count; i++)
{
%>
pas[<%=i%>].Value = <%=GetModelParameterName(SourceTable)%>.<%=ConvertToPascal(SourceTable.NonPrimaryKeyColumns[i].Name)%>;
<%
}
%> return Convert.ToInt32(SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pas));
} /// <summary>
/// 更新<%=ObjectName%>
/// </summary>
/// <param name="<%=GetModelParameterName(SourceTable)%>"><%=ObjectName%></param>
/// <returns>bool</returns>
public bool Update<%=GetModelName(SourceTable)%>(<%=GetModelName(SourceTable)%> <%=GetModelParameterName(SourceTable)%>)
{
string sql = <%=GetUpdateSql()%>; SqlParameter[] pas = new SqlParameter[]
{
<%
for(int i=; i<SourceTable.NonPrimaryKeyColumns.Count; i++)
{
ColumnSchema column = SourceTable.NonPrimaryKeyColumns[i];
%>
new SqlParameter("@<%=column.Name%>",SqlDbType.<%=GetSqlDbType(column)%>,<%=column.Size%>),
<%
}
%>
new SqlParameter("@<%=GetPrimaryKeyName(SourceTable)%>",SqlDbType.<%=GetSqlDbType(GetPrimaryKeyColumn(SourceTable))%>,<%=GetPrimaryKeySize(SourceTable)%>)
}; <%
for(int i=; i<=SourceTable.NonPrimaryKeyColumns.Count; i++)
{
if (i < SourceTable.NonPrimaryKeyColumns.Count)
{
%>
pas[<%=i%>].Value = <%=GetModelParameterName(SourceTable)%>.<%=ConvertToPascal(SourceTable.NonPrimaryKeyColumns[i].Name)%>;
<%
}
else
{
%>
pas[<%=i%>].Value = <%=GetModelParameterName(SourceTable)%>.<%=ConvertToPascal(GetPrimaryKeyName(SourceTable))%>;
<%
}
}
%> int rows = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pas);
return (rows == );
} /// <summary>
/// 根据ID删除<%=ObjectName%>
/// </summary>
/// <param name="<%=GetPrimaryKeyFieldName(SourceTable)%>"><%=ObjectName%>ID</param>
/// <returns>bool</returns>
public bool Delete<%=GetModelName(SourceTable)%>By<%=GetPrimaryKeyPropertyName(SourceTable)%>(<%=GetPrimaryKeyType(SourceTable)%> <%=GetPrimaryKeyFieldName(SourceTable)%>)
{
string sql = "DELETE FROM <%=SourceTable.Name%> WHERE <%=GetPrimaryKeyName(SourceTable)%>=@<%=GetPrimaryKeyName(SourceTable)%>"; SqlParameter pa = new SqlParameter("@<%=GetPrimaryKeyName(SourceTable)%>",SqlDbType.<%=GetPrimaryKeySqlDbType(SourceTable)%>,<%=GetPrimaryKeySize(SourceTable)%>);
pa.Value = <%=GetPrimaryKeyFieldName(SourceTable)%>; int rows = SqlHelper.ExecuteNonQuery(sql, CommandType.Text, pa); return (rows == );
} /// <summary>
/// 根据ID查询<%=ObjectName%>
/// </summary>
/// <param name="<%=GetPrimaryKeyFieldName(SourceTable)%>"><%=ObjectName%>ID</param>
/// <returns><%=ObjectName%>对象</returns>
public <%=GetModelName(SourceTable)%> Get<%=GetModelName(SourceTable)%>By<%=GetPrimaryKeyPropertyName(SourceTable)%>(<%=GetPrimaryKeyType(SourceTable)%> <%=GetPrimaryKeyFieldName(SourceTable)%>)
{
string sql = <%=GetSelectOneModelSql()%>; SqlParameter pa = new SqlParameter("@<%=GetPrimaryKeyName(SourceTable)%>",SqlDbType.<%=GetPrimaryKeySqlDbType(SourceTable)%>,<%=GetPrimaryKeySize(SourceTable)%>);
pa.Value = <%=GetPrimaryKeyFieldName(SourceTable)%>; using(SqlDataReader rdr = SqlHelper.ExecuteReader(sql, CommandType.Text,pa))
{
if (rdr.Read())
{
<%=GetModelName(SourceTable)%> <%=GetModelParameterName(SourceTable)%> = new <%=GetModelName(SourceTable)%>();
<%
foreach(ColumnSchema column in SourceTable.Columns)
{
%>
<%=GetModelParameterName(SourceTable)%>.<%=ConvertToPascal(column.Name)%> = (<%=GetCSharpDataTypeByDBColumn(column)%>)rdr["<%=column.Name%>"];
<%
}
%> return <%=GetModelParameterName(SourceTable)%>;
}
} return null;
} /// <summary>
/// 获得所有<%=ObjectName%>
/// </summary>
/// <returns><%=ObjectName%>集合</returns>
public List<<%=GetModelName(SourceTable)%>> GetAll<%=GetModelName(SourceTable)%>s()
{
string sql = <%=GetSelectAllModelSql()%>; List<<%=GetModelName(SourceTable)%>> all<%=GetModelName(SourceTable)%>s = new List<<%=GetModelName(SourceTable)%>>(); using(SqlDataReader rdr = SqlHelper.ExecuteReader(sql, CommandType.Text))
{
while (rdr.Read())
{
<%=GetModelName(SourceTable)%> <%=GetModelParameterName(SourceTable)%> = new <%=GetModelName(SourceTable)%>();
<%
foreach(ColumnSchema column in SourceTable.Columns)
{
%>
<%=GetModelParameterName(SourceTable)%>.<%=ConvertToPascal(column.Name)%> = (<%=GetCSharpDataTypeByDBColumn(column)%>)rdr["<% =column.Name%>"];
<%
}
%> all<%=GetModelName(SourceTable)%>s.Add(<%=GetModelParameterName(SourceTable)%>);
}
} return all<%=GetModelName(SourceTable)%>s;
}
}
} <script runat="template">
public string GetClassName(TableSchema table)
{
string tempTable;
if (table.Name.EndsWith("s"))
{
tempTable = table.Name.Substring(,table.Name.Length-);
}
else
{
tempTable = table.Name;
}
return tempTable;
} public string GetModelName(TableSchema table)
{
return ConvertToPascal(GetClassName(table));
} public string GetModelParameterName(TableSchema table)
{
return ConvertToCamel(GetClassName(table));
} public string GetPrimaryKeyType(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return GetCSharpDataTypeByDBColumn(table.PrimaryKey.MemberColumns[]);
}
else
{
throw new ApplicationException("此模板只支持单个列的主键");
}
}
else
{
throw new ApplicationException("此模板需要有主键的表");
}
} public ColumnSchema GetPrimaryKeyColumn(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return table.PrimaryKey.MemberColumns[];
}
else
{
throw new ApplicationException("此模板只支持单个列的主键");
}
}
else
{
throw new ApplicationException("此模板需要有主键的表");
}
} public string GetPrimaryKeyName(TableSchema table)
{
if (table.PrimaryKey != null)
{
if (table.PrimaryKey.MemberColumns.Count == )
{
return table.PrimaryKey.MemberColumns[].Name;
}
else
{
throw new ApplicationException("此模板只支持单个列的主键");
}
}
else
{
throw new ApplicationException("此模板需要有主键的表");
}
} public string GetPrimaryKeySize(TableSchema table)
{
return table.PrimaryKey.MemberColumns[].Size.ToString();
} public string GetPrimaryKeySqlDbType(TableSchema table)
{
return GetSqlDbType(table.PrimaryKey.MemberColumns[]);
} public string GetPrimaryKeyFieldName(TableSchema table)
{
return ConvertToCamel(GetPrimaryKeyName(table));
} public string GetPrimaryKeyPropertyName(TableSchema table)
{
return ConvertToPascal(GetPrimaryKeyName(table));
} public string GetInsertSql()
{
string sql = "\"INSERT INTO " + SourceTable.Name + "(";
foreach(ColumnSchema column in SourceTable.NonPrimaryKeyColumns)
{
sql += column.Name + ",";
}
sql = sql.Substring(,sql.Length-);
sql += ")\" +\n"; sql += "\t\t\t\t\" values(";
foreach(ColumnSchema column in SourceTable.NonPrimaryKeyColumns)
{
sql += "@" + column.Name + ",";
}
sql = sql.Substring(,sql.Length-);
sql += ")\""; return sql;
} public string GetUpdateSql()
{
string sql = "\"UPDATE " + SourceTable.Name + " \" +\n";
sql += "\t\t\t\t\"SET ";
foreach(ColumnSchema column in SourceTable.NonPrimaryKeyColumns)
{
sql += column.Name + "=@" + column.Name + ",";
}
sql = sql.Substring(,sql.Length-);
sql += " \" +\n"; sql += "\t\t\t\t\"WHERE " + GetPrimaryKeyName(SourceTable) + "=@" + GetPrimaryKeyName(SourceTable) + "\""; return sql;
} public string GetSelectOneModelSql()
{
string sql = "\"SELECT ";
foreach(ColumnSchema column in SourceTable.Columns)
{
sql += column.Name + ",";
}
sql = sql.Substring(,sql.Length-);
sql += " \" +\n"; sql += "\t\t\t\t\"FROM " + SourceTable.Name + " \" +\n";
sql += "\t\t\t\t\"WHERE " + GetPrimaryKeyName(SourceTable) + "=@" + GetPrimaryKeyName(SourceTable) + "\""; return sql;
} public string GetSelectAllModelSql()
{
string sql = "\"SELECT ";
foreach(ColumnSchema column in SourceTable.Columns)
{
sql += column.Name + ",";
}
sql = sql.Substring(,sql.Length-);
sql += " \" +\n"; sql += "\t\t\t\t\"FROM " + SourceTable.Name + "\""; return sql;
} public string GetCSharpDataTypeByDBColumn(ColumnSchema column)
{
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;
}
}
} public string GetSqlDbType(ColumnSchema column)
{
switch (column.NativeType)
{
case "bigint": return "BigInt";
case "binary": return "Binary";
case "bit": return "Bit";
case "char": return "Char";
case "datetime": return "DateTime";
case "decimal": return "Decimal";
case "float": return "Float";
case "image": return "Image";
case "int": return "Int";
case "money": return "Money";
case "nchar": return "NChar";
case "ntext": return "NText";
case "numeric": return "Decimal";
case "nvarchar": return "NVarChar";
case "real": return "Real";
case "smalldatetime": return "SmallDateTime";
case "smallint": return "SmallInt";
case "smallmoney": return "SmallMoney";
case "sql_variant": return "Variant";
case "sysname": return "NChar";
case "text": return "Text";
case "timestamp": return "Timestamp";
case "tinyint": return "TinyInt";
case "uniqueidentifier": return "UniqueIdentifier";
case "varbinary": return "VarBinary";
case "varchar": return "VarChar";
default: return "__UNKNOWN__" + column.NativeType;
}
} public string ConvertToPascal(string str)
{
return str.Substring(,).ToUpper() + str.Substring();
} public string ConvertToCamel(string str)
{
return str.Substring(,).ToLower() + str.Substring();
} public override string GetFileName()
{
return GetModelName(SourceTable) + "Service.cs";
} public string ConvertDataType(string datatype,string dataname)
{
string result = "";
if(datatype=="int")
{
result="Convert.ToInt32("+dataname+")";
}else if(datatype=="string")
{
result =dataname.ToString();
}else if(datatype=="DateTime")
{
result ="Convert.ToDateTime("+dataname+")";
}else if(datatype=="bool")
{
result = "Convert.ToBoolean("+dataname+")";
}else if(datatype=="byte")
{
result = "Convert.ToByte("+dataname+")";
}else
{
result = "("+datatype+")"+dataname;
}
return result;
}
</script>