CodeSmith 创建Ado.Net自定义模版（三）

接上一篇：

写数据访问层模版：Step3_DAL.cst （接口一这步在这套模版中省略，可以自行补充）

写几个通用方法，比如：得到全部实体集合、通过ID得实体、添加、删除、修改几个方法

我这里没有使用sqlhelper之类的帮助类

<%@ CodeTemplate Language="C#" TargetLanguage="C#" ResponseEncoding="UTF-8" Description="实体类" %> <%@ Property Name="NameSpace" Type="System.String" Default="Service" Category="Property" Description="命名空间" %> <%@ Property Name="ModelNameSpace" Type="System.String" Default="Model" Category="String" Description="实体层的命名空间" %> <%@ Property Name="UtilityNameSpace" Type="System.String" Default="Utility" Category="String" Description="工具层的命名空间" %> <%@ Property Name="Author" Type="System.String" Default="Wilson" Category="Property" Description="作者名" %> <%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Optional="True" Category="db" Description="表映射文件" %> <%@ Assembly Name="SchemaExplorer" %> <%@ Assembly Name="System.Data" %> <%@ Import Namespace="SchemaExplorer" %> <%@ Import Namespace="System.Data" %>  using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.Data.SqlClient; using <%=ModelNameSpace%> using <%=UtilityNameSpace%> namespace <%=NameSpace%> {
   /// /// 功能：<%=SourceTable.Name%>类数据访问 /// 创建人：<%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %>   ///
     public class <%=SourceTable.Name%>Service : IDisposable     {
   #region 公共 private System.ComponentModel.Component component = new System.ComponentModel.Component(); private bool disposed = false;         ~<%=SourceTable.Name%>Service()         {
               Dispose(false);         } #endregion #region IDisposable 成员 public void Dispose()         {
               Dispose(true);             GC.SuppressFinalize(this);         } private void Dispose(bool disposing)         {
   if (!this.disposed)             {
   if (disposing)                 {
                       component.Dispose();                 }                 disposed = true;             }         } #endregion         <% if(SourceTable.HasPrimaryKey) %>         <%{%> #region##通过主键ID得到<%=SourceTable.Name%> /// /// 功能：通过ID得到<%=SourceTable.Name%> /// 创建人：  <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %>   ///
 ///主键ID ///
              public <%=SourceTable.Name%> Get<%=SourceTable.Name%>ById(<%=DataType2CSharpType(SourceTable.PrimaryKey.MemberColumns[0].DataType)%> <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>)         {                        <%=SourceTable.Name%> obj = null; string sql = "select * from <%=SourceTable.Name%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @PK";             SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             SqlParameter[] sp = new SqlParameter[]             {
   new SqlParameter("@PK",<%=SourceTable.PrimaryKey.MemberColumns[0].Size%>)             };             sp[0].Value = <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>;             com.Parameters.AddRange(sp);             SqlDataReader reader = com.ExecuteReader(); if (reader.Read())             {
                   obj = new <%=SourceTable.Name%>();                                <%for(int i=0; i < SourceTable.Columns.Count; i ++)%>                 <%{%>                 obj.<%=SourceTable.Columns[i].Name%> = (<%=DataType2CSharpType(SourceTable.Columns[i].DataType)%>)reader["<%=SourceTable.Columns[i].Name%>"];                 <%}%>             }             reader.Close();             reader.Dispose();             com.Dispose();             conn.Close();             conn.Dispose(); return material;         } #endregion #region##通过主键ID删除<%=SourceTable.Name%> /// /// 功能：通过主键ID删除<%=SourceTable.Name%> /// 创建人：  <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %>   ///
 ///主键ID ///
              public int DeleteById(<%=DataType2CSharpType(SourceTable.PrimaryKey.MemberColumns[0].DataType)%> <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>)         {
   int i = 0; string sql = "delete from <%=SourceTable.Name%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @PK";             SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             SqlParameter[] sp = new SqlParameter[]             {
   new SqlParameter("@PK",<%=SourceTable.PrimaryKey.MemberColumns[0].Size%>)             };             sp[0].Value = <%=ToLowercase(SourceTable.PrimaryKey.MemberColumns[0].Name)%>;             com.Parameters.AddRange(sp);             i = com.ExecuteNonQuery();             com.Dispose();             conn.Close();             conn.Dispose(); return i;         } #endregion                    <%}%> #region##添加<%=SourceTable.Name%> /// /// 功能：添加<%=SourceTable.Name%> /// 创建人：  <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %> ///
 /// ///
              public int Add<%=SourceTable.Name%>(<%=SourceTable.Name%> <%=ToLowercase(SourceTable.Name)%>)         {
   int result = 0; string sql = "insert into <%=SourceTable.Name%>(<%for(int i = 0; i < SourceTable.Columns.Count ; i++)%><%{%><%=SourceTable.Columns[i].Name%><%if(i
     
      <%{%>,<%}%><%}%>) values(<%for(int i = 0; i < SourceTable.Columns.Count ; i++)%><%{%>@<%=SourceTable.Columns[i].Name%><%if(i
      
       <%{%>,<%}%><%}%>);                        SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             SqlParameter[] sp = new SqlParameter[]             {
                   <%for(int i=0;i < SourceTable.Columns.Count; i++)%>                 <%{%> new SqlParameter("@<%=SourceTable.Columns[i].Name%>",<%=SourceTable.Columns[i].Size%>)<%if(i
       
        <%{%>,<%}%>                 <%}%>             };             <%for(int i=0;i < SourceTable.Columns.Count; i++)%>             <%{%>             sp[<%=i%>].Value = <%=ToLowercase(SourceTable.Name)%>.<%=SourceTable.Columns[i].Name%>;             <%}%>             com.Parameters.AddRange(sp);             result = com.ExecuteNonQuery();             com.Dispose();             conn.Close();             conn.Dispose(); return result;         } #endregion #region##得到<%=SourceTable.Name%>集合 ///
         /// 功能：得到<%=SourceTable.Name%>集合 /// 创建人：  <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %> ///
       ///
                 public IList<%=ToGeneric(SourceTable.Name)%> GetList()         {
               IList<%=ToGeneric(SourceTable.Name)%> list = new List<%=ToGeneric(SourceTable.Name)%>();             <%=SourceTable.Name%> obj = null;                string sql = "select * from <%=SourceTable.Name%>";                        SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             SqlDataReader reader = com.ExecuteReader(); while(reader.Read())             {
                   obj = new <%=SourceTable.Name%>();                    <%for(int i=0; i < SourceTable.Columns.Count; i ++)%>                 <%{%>                 obj.<%=SourceTable.Columns[i].Name%> = (<%=DataType2CSharpType(SourceTable.Columns[i].DataType)%>)reader["<%=SourceTable.Columns[i].Name%>"];                 <%}%>                                list.Add(obj);                            }             reader.Close();             reader.Dispose();             com.Dispose();             conn.Close();             conn.Dispose(); return list;         } #endregion #region##修改<%=SourceTable.Name%> ///
         /// 功能：修改<%=SourceTable.Name%> /// 创建人：  <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %> ///
 ///
         ///
                 public int Mod<%=SourceTable.Name%>(<%=SourceTable.Name%> <%=ToLowercase(SourceTable.Name)%>)         {
   int result = 0; string sql = "update <%=SourceTable.Name%> set <%for(int i=0;i
        
         <%{%><%=SourceTable.Columns[i].Name%> = @<%=SourceTable.Columns[i].Name%><%if(i < SourceTable.Columns.Count - 1)%><%{%>,<%}%><%}%> where <%=SourceTable.PrimaryKey.MemberColumns[0].Name%> = @<%=SourceTable.PrimaryKey.MemberColumns[0].Name%>";             SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             SqlParameter[] sp = new SqlParameter[]             {                       <%for(int i=0;i < SourceTable.Columns.Count; i++)%>                 <%{%> new SqlParameter("@<%=SourceTable.Columns[i].Name%>",<%=SourceTable.Columns[i].Size%>)<%if(i
         
          <%{%>,<%}%>                 <%}%>             };             <%for(int i=0;i < SourceTable.Columns.Count; i++)%>             <%{%>             sp[<%=i%>].Value = <%=ToLowercase(SourceTable.Name)%>.<%=SourceTable.Columns[i].Name%>;             <%}%>             com.Parameters.AddRange(sp);             result = com.ExecuteNonQuery();             com.Dispose();             conn.Close();             conn.Dispose(); return result;         } #endregion #region##修改<%=SourceTable.Name%> ///
           /// 修改<%=SourceTable.Name%> /// 创建人： <%=Author%> /// 创建时间：<%=DateTime.Now.ToShortDateString() %> ///
 ///
           ///
                   public int SelectCount()         {
   int i = 0; string sql = "select count(*) from <%=SourceTable.Name%>";             SqlConnection conn = new SqlConnection(WebConn.connString);             conn.Open();             SqlCommand com = new SqlCommand(sql, conn);             i = (int)com.ExecuteScalar();             com.Dispose();             conn.Dispose();             conn.Close(); return i;         } #endregion     } }