数据库: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; } } }}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; } }}设计中的作品,相当乱.
主要的只是想说个想法.