数据库:SQL Server 2000
Model, 即实体类,使用CodeSmith根据数据库表自动生成.这个是最常用的.
数据访问层, 最简单的增删改查操作也是使用CodeSmith根据数据库表配合SQLHelper类自动生成.
这层的设计,先贴点未完工的代码给大家参考参考.
1.DalCode.cs
using
System;
using System.Collections.Generic;
using System.Text;
using CodeSmith;
using SchemaExplorer;
using CodeSmith.CustomProperties;
namespace DalCode
... {
/**//// <summary>
/// CodeSmith的DAL模板的代码类
/// </summary>
public class DalCode
...{
//表
private TableSchema Table;
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="table"></param>
public DalCode(SchemaExplorer .TableSchema table)
...{
Table = table;
}
/**//// <summary>
/// 获取Insert方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetInsertParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach(ColumnSchema cs in Table .Columns)
...{
if(!IsIdentity (cs))
...{
if(!IsDefault (cs))
...{
parm.Add(cs.Name,ToSQLType (cs.DataType.ToString()));
}
}
}
return parm;
}
/**//// <summary>
/// 获取Delete方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetDeleteParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 获取Update方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetUpdateParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (!IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 获取Select方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetSelectParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 检查字段是否为标识符
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private bool IsIdentity(ColumnSchema cs)
...{
return (bool)cs.ExtendedProperties["CS_IsIdentity"].Value;
}
/**//// <summary>
/// 检查字段是否有默认值
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private bool IsDefault(ColumnSchema cs)
...{
if ((String)cs.ExtendedProperties["CS_Default"].Value == "")
...{
return false;
}
return true;
}
/**//// <summary>
/// 将数据类型转换成SQL数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private string ToSQLType(string type)
...{
switch (type)
...{
case "AnsiString":
return "SqlDbType.VarChar";
break;
default :
return type;
}
}
}
}
using System.Collections.Generic;
using System.Text;
using CodeSmith;
using SchemaExplorer;
using CodeSmith.CustomProperties;
namespace DalCode
... {
/**//// <summary>
/// CodeSmith的DAL模板的代码类
/// </summary>
public class DalCode
...{
//表
private TableSchema Table;
/**//// <summary>
/// 构造函数
/// </summary>
/// <param name="table"></param>
public DalCode(SchemaExplorer .TableSchema table)
...{
Table = table;
}
/**//// <summary>
/// 获取Insert方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetInsertParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach(ColumnSchema cs in Table .Columns)
...{
if(!IsIdentity (cs))
...{
if(!IsDefault (cs))
...{
parm.Add(cs.Name,ToSQLType (cs.DataType.ToString()));
}
}
}
return parm;
}
/**//// <summary>
/// 获取Delete方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetDeleteParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 获取Update方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetUpdateParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (!IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 获取Select方法所需字段名
/// </summary>
/// <returns></returns>
public NameValueCollection GetSelectParm()
...{
NameValueCollection parm = new NameValueCollection();
foreach (ColumnSchema cs in Table.Columns)
...{
if (IsIdentity(cs))
...{
parm.Add(cs.Name, cs.DataType.ToString());
}
}
return parm;
}
/**//// <summary>
/// 检查字段是否为标识符
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private bool IsIdentity(ColumnSchema cs)
...{
return (bool)cs.ExtendedProperties["CS_IsIdentity"].Value;
}
/**//// <summary>
/// 检查字段是否有默认值
/// </summary>
/// <param name="cs"></param>
/// <returns></returns>
private bool IsDefault(ColumnSchema cs)
...{
if ((String)cs.ExtendedProperties["CS_Default"].Value == "")
...{
return false;
}
return true;
}
/**//// <summary>
/// 将数据类型转换成SQL数据类型
/// </summary>
/// <param name="type"></param>
/// <returns></returns>
private string ToSQLType(string type)
...{
switch (type)
...{
case "AnsiString":
return "SqlDbType.VarChar";
break;
default :
return type;
}
}
}
}
2. dal.cst CodeSmith模板文件
<%--
Name:
Author:
Description:
--%>
<% @ CodeTemplate Language = " C# " TargetLanguage = " Text " ResponseEncoding = " UTF-8 " Description = " Template description here. " %>
<% @ Assembly Name = " SchemaExplorer " %>
<% @ Import Namespace = " SchemaExplorer " %>
<% @ Assembly Name = " CodeSmith.CustomProperties " %>
<% @ Import Namespace = " CodeSmith.CustomProperties " %>
<% @ Assembly Name = " DalCode " %>
<% @ Import Namespace = " DalCode " %>
<% @ Property Name = " Table " Type = " SchemaExplorer.TableSchema " Category = " Context " Description = " ?????. " %>
<% @ Property Name = " NameSpace " Type = " System.String " Default = " Company.Product.Module " Optional = " False " Category = "" Description = " ???? " %>
<% @ Property Name = " Author " Type = " System.String " Default = " Author " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " Description " Type = " System.String " Default = " Description " Optional = " False " Category = "" Description = " Description " %>
<% @ Property Name = " ClassName " Type = " System.String " Default = " ClassName " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " InsertParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " DeleteParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " UpdateParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " SelectParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " ModelType " Type = " System.String " Default = " ModelType " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " ModelName " Type = " System.String " Default = " ModelName " Optional = " False " Category = "" Description = " ?? " %>
using System;
namespace <%= NameSpace %>
... {
/**//// <summary>
/// 作用:<%= Description %>
/// 作者:<%= Author %>
/// 日期:<%= DateTime.Now.ToString() %>
///</summary>
public class <%= ClassName%>
...{
<%
DalCode dal = new DalCode(Table);
InsertParm = dal.GetInsertParm();
string InsertName = InsertParm[0].Key;
string InsertValue = "@" + InsertParm[0].Key;
for(int i = 1;i < InsertParm.Keys.Count;i++)
...{
InsertName += ",";
InsertName += InsertParm[i].Key;
InsertValue += ",";
InsertValue += "@" + InsertParm[i].Key;
}
DeleteParm = dal .GetDeleteParm ();
UpdateParm = dal.GetUpdateParm ();
string UpdateString = UpdateParm[0].Key + " = @" + UpdateParm[0].Key;
for(int i = 1;i < UpdateParm.Keys.Count;i++)
...{
UpdateString += ",";
UpdateString += UpdateParm[i].Key + " = @" + UpdateParm[i].Key;
}
SelectParm = dal.GetSelectParm ();
%>
private const string SQL_INSERT_TOPIC = "INSERT INTO <%=Table.Name%> (<%=InsertName%>) VALUES(<%=InsertValue%>)";
private const string SQL_DELETE_TOPIC = "DELETE FROM <%=Table.Name%> WHERE <%=DeleteParm[0].Key%> = @<%=DeleteParm[0].Key%>";
private const string SQL_UPDATE_TOPIC = "UPDATE <%=Table.Name%> SET <%=UpdateString%> WHERE <%=DeleteParm[0].Key%> = @<%=DeleteParm[0].Key%>";
private const string SQL_SELECT_TOPIC = "SELECT * FROM <%=Table.Name%> WHERE <%=SelectParm[0].Key%> = @<%=SelectParm[0].Key%>";
/**//// <summary>
/// INSERT
/// </summary>
/// <returns></returns>
public int Insert(<%=ModelType%> <%=ModelName%>)
...{
SqlParameter[] insertParm = GetInsertParameters();
<%
for(int i = 0;i < InsertParm.Keys.Count;i++)
...{
%>
insertParm[<%=i%>].Value = <%=ModelName%>.<%=InsertParm[i].Key%>;
<%
}
%>
int val = SQLHelper.ExecuteNonQuery(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_INSERT_TOPIC, insertParm);
return val;
}
/**//// <summary>
/// 获取SELECT操作的SQL参数
/// </summary>
/// <returns></returns>
public static SqlParameter[] GetInsertParameters()
...{
SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT_TOPIC);
if (parms == null)
...{
parms = new SqlParameter[]
...{
<%
for(int i = 0;i < InsertParm.Keys.Count;i++)
...{
%>
new SqlParameter("@<%=InsertParm[i].Key%>",<%=InsertParm[i].Value%>),
<%
}
%>
};
SQLHelper.CacheParameters(SQL_INSERT_TOPIC, parms);
}
return parms;
}
}
}
Name:
Author:
Description:
--%>
<% @ CodeTemplate Language = " C# " TargetLanguage = " Text " ResponseEncoding = " UTF-8 " Description = " Template description here. " %>
<% @ Assembly Name = " SchemaExplorer " %>
<% @ Import Namespace = " SchemaExplorer " %>
<% @ Assembly Name = " CodeSmith.CustomProperties " %>
<% @ Import Namespace = " CodeSmith.CustomProperties " %>
<% @ Assembly Name = " DalCode " %>
<% @ Import Namespace = " DalCode " %>
<% @ Property Name = " Table " Type = " SchemaExplorer.TableSchema " Category = " Context " Description = " ?????. " %>
<% @ Property Name = " NameSpace " Type = " System.String " Default = " Company.Product.Module " Optional = " False " Category = "" Description = " ???? " %>
<% @ Property Name = " Author " Type = " System.String " Default = " Author " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " Description " Type = " System.String " Default = " Description " Optional = " False " Category = "" Description = " Description " %>
<% @ Property Name = " ClassName " Type = " System.String " Default = " ClassName " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " InsertParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " DeleteParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " UpdateParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " SelectParm " Type = " CodeSmith.CustomProperties.NameValueCollection " Default = "" Optional = " true " Category = "" Description = "" %>
<% @ Property Name = " ModelType " Type = " System.String " Default = " ModelType " Optional = " False " Category = "" Description = " ?? " %>
<% @ Property Name = " ModelName " Type = " System.String " Default = " ModelName " Optional = " False " Category = "" Description = " ?? " %>
using System;
namespace <%= NameSpace %>
... {
/**//// <summary>
/// 作用:<%= Description %>
/// 作者:<%= Author %>
/// 日期:<%= DateTime.Now.ToString() %>
///</summary>
public class <%= ClassName%>
...{
<%
DalCode dal = new DalCode(Table);
InsertParm = dal.GetInsertParm();
string InsertName = InsertParm[0].Key;
string InsertValue = "@" + InsertParm[0].Key;
for(int i = 1;i < InsertParm.Keys.Count;i++)
...{
InsertName += ",";
InsertName += InsertParm[i].Key;
InsertValue += ",";
InsertValue += "@" + InsertParm[i].Key;
}
DeleteParm = dal .GetDeleteParm ();
UpdateParm = dal.GetUpdateParm ();
string UpdateString = UpdateParm[0].Key + " = @" + UpdateParm[0].Key;
for(int i = 1;i < UpdateParm.Keys.Count;i++)
...{
UpdateString += ",";
UpdateString += UpdateParm[i].Key + " = @" + UpdateParm[i].Key;
}
SelectParm = dal.GetSelectParm ();
%>
private const string SQL_INSERT_TOPIC = "INSERT INTO <%=Table.Name%> (<%=InsertName%>) VALUES(<%=InsertValue%>)";
private const string SQL_DELETE_TOPIC = "DELETE FROM <%=Table.Name%> WHERE <%=DeleteParm[0].Key%> = @<%=DeleteParm[0].Key%>";
private const string SQL_UPDATE_TOPIC = "UPDATE <%=Table.Name%> SET <%=UpdateString%> WHERE <%=DeleteParm[0].Key%> = @<%=DeleteParm[0].Key%>";
private const string SQL_SELECT_TOPIC = "SELECT * FROM <%=Table.Name%> WHERE <%=SelectParm[0].Key%> = @<%=SelectParm[0].Key%>";
/**//// <summary>
/// INSERT
/// </summary>
/// <returns></returns>
public int Insert(<%=ModelType%> <%=ModelName%>)
...{
SqlParameter[] insertParm = GetInsertParameters();
<%
for(int i = 0;i < InsertParm.Keys.Count;i++)
...{
%>
insertParm[<%=i%>].Value = <%=ModelName%>.<%=InsertParm[i].Key%>;
<%
}
%>
int val = SQLHelper.ExecuteNonQuery(SQLHelper.CONN_STRING_NON_DTC, CommandType.Text, SQL_INSERT_TOPIC, insertParm);
return val;
}
/**//// <summary>
/// 获取SELECT操作的SQL参数
/// </summary>
/// <returns></returns>
public static SqlParameter[] GetInsertParameters()
...{
SqlParameter[] parms = SQLHelper.GetCachedParameters(SQL_INSERT_TOPIC);
if (parms == null)
...{
parms = new SqlParameter[]
...{
<%
for(int i = 0;i < InsertParm.Keys.Count;i++)
...{
%>
new SqlParameter("@<%=InsertParm[i].Key%>",<%=InsertParm[i].Value%>),
<%
}
%>
};
SQLHelper.CacheParameters(SQL_INSERT_TOPIC, parms);
}
return parms;
}
}
}
设计中的作品,相当乱.
主要的只是想说个想法.