CodeSmith模板（数据访问类）

来源：互联网发布：sql特殊字符编辑：程序博客网时间：2024/06/17 13:17

<%@ CodeTemplate Inherits="ToolsCodeTemplate" Language="C#" Src="ToolsCodeTemplate.cs" TargetLanguage="C#" Description="" Debug="True" ResponseEncoding="UTF-8"%>
<%@ Property Name="TargetTable" Type="SchemaExplorer.TableSchema" Category="Context" Description="表名" %>

<%@ Property Name="ModelNamespace" Default="CX.Model" Type="System.String" Category="Context" Description="实体类命名空间" %>
<%@ Property Name="DBUtilityNamespace" Default="CX.DBUtility" Type="System.String" Category="Context" Description="助手类命名空间" %>
<%@ Property Name="DALNamespace" Default="CX.DAL" Type="System.String" Category="Context" Description="服务类命名空间" %>

<%@ Property Name="DALClassNameSurfix" Default="Service" Type="System.String" Category="Context" Description="服务类后缀(xxxService)" %>

<%@ Assembly Name="SchemaExplorer" %>
<%@ Assembly Name="System.Data" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<% PrintHeader(); %>
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using <%= ModelNamespace %>;
using <%= DBUtilityNamespace %>;

namespace <%= DALNamespace %>
{
public partial class <%= GetDALClassName() %>
{
  private DBHelper helper=new DBHelper();
        /// <summary>
        /// 添加<%= GetModelClassName() %>
        /// </summary>
        public bool Add<%= GetModelClassName() %>(<%= GetModelClassNameWithInfo() %> <%= GetModelParamName() %>)
  {
   bool b=false;

   <%if(IsIdentityPK()){%>
   string sql ="<%= GetAutoIncInsertSQLLine()%>";
   SqlParameter[] param = new SqlParameter[]{
              <%
              for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
              {
               ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
              %>
              new SqlParameter("@<%= column.Name %>", helper.ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
              <%
              }
              %>
             };
   <%}else{%>
   string sql ="<%= GetCommonInsertSQLLine()%>";
   SqlParameter[] param = new SqlParameter[]{
              <%
              for(int i=0; i<TargetTable.Columns.Count; i++)
              {
               ColumnSchema column = TargetTable.Columns[i];
              %>
              new SqlParameter("@<%= column.Name %>", helper.ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>)),
              <%
              }
              %>
             };

   <% }%>
   if(helper.Execute(sql, param)==1)
   {
    b=true;
   }
   return b;
  }

  /// <summary>
        /// 删除<%= GetModelClassName() %>
        /// </summary>
        public bool Delete<%= GetModelClassName() %>By<%= GetPKName() %>(<%= GetPKPropertyType() %> <%= GetPKParamName() %>)
  {
   bool b=false;

            string sql = "DELETE FROM <%= TargetTable.Name %> WHERE <%= GetPKName() %> = @<%= GetPKName() %>";

            SqlParameter[] param = new SqlParameter[]{
              new SqlParameter("@<%= GetPKName() %>", <%= GetPKParamName() %>)
             };

            if(helper.Execute(sql, param)==1)
   {
    b=true;
   }
   return b;
  }

  /// <summary>
        /// 更新<%= GetModelClassName() %>
        /// </summary>
        public bool Update<%= GetModelClassName() %>(<%= GetModelClassNameWithInfo() %> <%= GetModelParamName() %>)
        {
   bool b=false;
            string sql ="UPDATE <%= TargetTable.Name %> SET <%= TargetTable.NonPrimaryKeyColumns[0].Name %> = @<%= TargetTable.NonPrimaryKeyColumns[0].Name %> <%for(int i=1; i<TargetTable.NonPrimaryKeyColumns.Count; i++){ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i]; %>, <%= column.Name %> = @<%= column.Name %> <%}%>WHERE <%= GetPKName() %> = @<%= GetPKName() %>";

   SqlParameter[] param = new SqlParameter[]{
              new SqlParameter("@<%= GetPKName() %>", <%= GetModelParamName() %>.<%= GetPKName() %>)
             <%
             for(int i=0; i<TargetTable.NonPrimaryKeyColumns.Count; i++)
             {
              ColumnSchema column = TargetTable.NonPrimaryKeyColumns[i];
             %>
              ,new SqlParameter("@<%= column.Name %>", helper.ToDBValue(<%= GetModelParamName() %>.<%= column.Name %>))
             <%
             }
             %>
             };

   if(helper.Execute(sql, param)==1)
   {
    b=true;
   }
   return b;
        }

  /// <summary>
        /// 根据<%= GetModelClassName() %>的主键获取单条记录
        /// </summary>
        public <%= GetModelClassNameWithInfo() %> Get<%= GetModelClassName() %>By<%= GetPKName() %>(<%= GetPKPropertyType() %> <%= GetPKParamName() %>)
        {
            string sql = "SELECT * FROM <%= TargetTable.Name %> WHERE <%= GetPKName() %> = @<%= GetPKName() %>";
   SqlParameter[] param = new SqlParameter[]{new SqlParameter("@<%= GetPKName() %>", <%= GetPKParamName() %>)};
            using(SqlDataReader reader = helper.GetDataReader(sql, param))
   {
    if (reader.Read())
    {
     return ToModel(reader);
    }
    else
    {
     return null;
    }
         }
        }

  /// <summary>
        /// 获取<%= GetModelClassName() %>的所有记录
        /// </summary>
  public List<<%= GetModelClassNameWithInfo() %>> GetAll<%= GetModelClassName() %>()
  {
   string sql = "SELECT * FROM <%= TargetTable.Name %>";
   using(SqlDataReader reader = helper.GetDataReader(sql))
   {
    return ToModels(reader);
   }
  }

  /// <summary>
        /// 获取<%= GetModelClassName() %>的所有记录总数
        /// </summary>
  public int GetTotalCount()
  {
   string sql = "SELECT count(*) FROM <%= TargetTable.Name %>";
   return (int)helper.GetScalar(sql);
  }

  /// <summary>
        /// 分页获取<%= GetModelClassName() %>的记录
        /// </summary>
  public List<<%= GetModelClassNameWithInfo() %>> Get<%= GetModelClassName() %>ByPage(int minrownum,int maxrownum)
  {
   string sql = "SELECT * from(SELECT *,row_number() over(order by <%=this.GetPKName()%>) rownum FROM <%= TargetTable.Name %>) t where rownum>=@minrownum and rownum<=@maxrownum";
   SqlParameter[] param = new SqlParameter[]{new SqlParameter("@minrownum",minrownum),new SqlParameter("@maxrownum",maxrownum)};
   using(SqlDataReader reader = helper.GetDataReader(sql,param))
   {
    return ToModels(reader);
   }
  }

  /// <summary>
        /// 将获取的单条数据转封装成对象返回
        /// </summary>
  public <%= GetModelClassNameWithInfo() %> ToModel(SqlDataReader reader)
  {
   <%= GetModelClassNameWithInfo() %> <%= GetModelParamName() %> = new <%= GetModelClassNameWithInfo() %>();

   <% foreach(ColumnSchema column in TargetTable.Columns) %>
   <% { %>
   <%= GetModelParamName() %>.<%= GetPropertyName(column) %> = (<%=GetPropertyType(column)%>)helper.ToModelValue(reader,"<%=column.Name%>");
   <% } %>
   return <%= GetModelParamName() %>;
  }

  /// <summary>
        /// 将获取的多条数据转换成对象并添加到泛型集合返回
        /// </summary>
  protected List<<%= GetModelClassNameWithInfo() %>> ToModels(SqlDataReader reader)
  {
   var list = new List<<%= GetModelClassNameWithInfo() %>>();
   while(reader.Read())
   {
    list.Add(ToModel(reader));
   }
   return list;
  }


}
}
<script runat="template">

  /*因为每个服务类文件都会调用这两个方法，所有将这两个方法加入DBHelper中
  public object ToDBValue(object value)
  {
   if(value==null)
   {
    return DBNull.Value;
   }
   else
   {
    return value;
   }
  }

  public object ToModelValue(SqlDataReader reader,string columnName)
  {
   if(reader.IsDBNull(reader.GetOrdinal(columnName)))
   {
    return null;
   }
   else
   {
    return reader[columnName];
   }
  }
  */
//判断是否存在自动增长的主键
public bool IsIdentityPK()
{
  foreach(ColumnSchema column in TargetTable.Columns)
  {
   if((bool)column.ExtendedProperties["CS_IsIdentity"].Value)
   {
    return true;
   }
  }
  return false;
}

//拼接类名，如： UserService
public string GetDALClassName()
{
return GetModelClassName() + DALClassNameSurfix;
}

//获取类名 User，只为增加可读性，不是真正的类名
public string GetModelClassName()
{
return GetModelClassName(TargetTable);
}

//获取类名 UserInfo
public string GetModelClassNameWithInfo()
{
  return GetModelClassName(TargetTable)+"Info";
}

//参数名，首字母小写如：user
public string GetModelParamName()
{
  return MakeCamel(GetModelClassName());
}

//存在自动增长的主键时调用此方法拼接字符串
public string GetAutoIncInsertSQLLine()
{
  string result;
  result = "INSERT INTO " + TargetTable.Name + " (";
  foreach(ColumnSchema column in TargetTable.NonPrimaryKeyColumns)
  {
   result += column.Name + ", ";
  }
  result = result.Substring(0, result.Length-2);
  result += ") ";
  result += " VALUES (";
  foreach(ColumnSchema column in TargetTable.NonPrimaryKeyColumns)
  {
   result += "@" + column.Name + ", ";
  }
  result = result.Substring(0, result.Length-2);
  result += ")";
  return result;
}

//不存在自动增长的主键时调用此方法拼接字符串
public string GetCommonInsertSQLLine()
{
  string result;
  result = "INSERT INTO " + TargetTable.Name + " (";
  foreach(ColumnSchema column in TargetTable.Columns)
  {
   result += column.Name + ", ";
  }
  result = result.Substring(0, result.Length-2);
  result += ") ";
  result += " VALUES (";
  foreach(ColumnSchema column in TargetTable.Columns)
  {
   result += "@" + column.Name + ", ";
  }
  result = result.Substring(0, result.Length-2);
  result += ")";
  return result;
}

//获取属性类型 int
public string GetPKPropertyType()
{
return GetPKType(TargetTable);
}

//获取主键名：Id
public string GetPKName()
{
return MakePascal(GetPKName(TargetTable));
}

//获取主键参数名：id
public string GetPKParamName()
{
return MakeCamel(GetPKName());
}

</script>