CodeSmith生成三层代码之数据访问层(3)

<%@ CodeTemplate Language="C#" ResponseEncoding="UTF-8" TargetLanguage="Text" Src="" Debug="False" Description="Template description here." %>
<%@ Property Name="SourceTable" Type="SchemaExplorer.TableSchema" Category="Context"  Description="请选择数据表！" %>
<%@ Property Name="NameSpace" Type="System.String" Default="" Optional="False" Category="Description" Description="请输入命名空间" %>
<%@ Property Name="DefaultDatabase" Type="System.Boolean" Default="True" Optional="False" Category="Description" Description="请输入命名空间" %>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
/*************************************************************/
/*                   CodeSmith模板生成数据访问                */
/*************************************************************/
using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.SqlClient;
using <%=NameSpace%>.DBUtility;

namespace <%=NameSpace%>.SQLServerDAL
{
 /// <summary>
 /// 数据访问类<%=SourceTable.Name%>
 /// </summary>
 public class <%=SourceTable.Name%> : <%=NameSpace%>.IDAL.I<%=SourceTable.Name%>
 {
  <% if(!DefaultDatabase) {%>DbHelperSQLP DbHelperSQL = new DbHelperSQL();<%}%>
  #region CodeSmith自动生成
  /// <summary>
  /// 得到一个DataSet
  /// </summary>
  public DataSet GetData(string strWhere)
  {
   StringBuilder strSql=new StringBuilder();
   strSql.Append("select <%=GetColumnNameList(SourceTable)%> ");
   strSql.Append(" FROM <%=SourceTable.Name%> ");
   if(strWhere.Trim()!="")
   {
    strSql.Append(" where "+strWhere);
   }
   return DbHelperSQL.Query(strSql.ToString());
  }
  
  /// <summary>
  /// 增加一条数据
  /// </summary>
  public void Add(<%=NameSpace%>.Model.<%=SourceTable.Name%> model)
  {
   StringBuilder strSql=new StringBuilder();
   strSql.Append("insert into <%=SourceTable.Name%>(");
   strSql.Append("<%=GetColumnNameList(SourceTable)%>)");
   strSql.Append(" values (");
   strSql.Append("<%=GetColumnNameListPara(SourceTable)%>)");
   SqlParameter[] parameters = {
     <%for(int i=0; i<SourceTable.Columns.Count; i++) {%>
     <%if(i == SourceTable.Columns.Count -1){%>new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>)<%}else{%>new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>),<%}%>
     <%}%>
    };
   <%for(int i=0; i<SourceTable.Columns.Count; i++) {%>
   parameters[<%=i%>].Value = model.<%=SourceTable.Columns[i].Name%>;
   <%}%>

   DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
  }
  /// <summary>
  /// 更新一条数据
  /// </summary>
  public void Update(<%=NameSpace%>.Model.<%=SourceTable.Name%> model)
  {
   StringBuilder strSql=new StringBuilder();
   strSql.Append("update <%=SourceTable.Name%> set ");
   strSql.Append("<%=GetUpdateColumn(SourceTable)%>");
   strSql.Append(" where <%=GetKeyColumn(SourceTable).Name%>=@<%=GetKeyColumn(SourceTable).Name%> ");
   SqlParameter[] parameters = {
     <%for(int i=0; i<SourceTable.Columns.Count; i++) {%>
     <%if(i == SourceTable.Columns.Count -1){%>new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>)<%}else{%>new SqlParameter("@<%=SourceTable.Columns[i].Name%>", <%=GetSqlDbType(SourceTable.Columns[i])%>,<%=SourceTable.Columns[i].Size%>),<%}%>
     <%}%>
    };
   <%for(int i=0; i<SourceTable.Columns.Count; i++) {%>
   parameters[<%=i%>].Value = model.<%=SourceTable.Columns[i].Name%>;
   <%}%>

   DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
  }
  
  /// <summary>
  /// 删除数据
  /// </summary>
  public void Delete(<% =GetPrimaryKeyType(SourceTable) + " " + GetKeyColumn(SourceTable).Name.ToLower()%>)
  {
   StringBuilder strSql=new StringBuilder();
   strSql.Append("delete <% =SourceTable.Name%> ");
   strSql.Append(" where <% =GetKeyColumn(SourceTable).Name %>=@<% =GetKeyColumn(SourceTable).Name %> ");
   SqlParameter[] parameters = {
     new SqlParameter("@<% =GetKeyColumn(SourceTable).Name %>", <%=GetKeyColumnSqlType(SourceTable)%>, <%=GetKeyColumnSqlLength(SourceTable)%>)};
   parameters[0].Value = <% =GetKeyColumn(SourceTable).Name.ToLower() %>;

   DbHelperSQL.ExecuteSql(strSql.ToString(),parameters);
  }
  
  /// <summary>
  /// 得到一个泛型集合
  /// </summary>
  public List<<%=NameSpace%>.Model.<%=SourceTable.Name%>> GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select <%=GetColumnNameList(SourceTable)%> ");
            strSql.Append(" FROM <%=SourceTable.Name%> ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }

            List<<%=NameSpace%>.Model.<%=SourceTable.Name%>> list = new List<<%=NameSpace%>.Model.<%=SourceTable.Name%>>();

            using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql.ToString()))
            {
                while (reader.Read())
                {
                    <%=NameSpace%>.Model.<%=SourceTable.Name%> model = new <%=NameSpace%>.Model.<%=SourceTable.Name%>();
     <% for(int i=0; i<SourceTable.Columns.Count; i++){%>
     model.<%=SourceTable.Columns[i].Name%> = <%=GetSqlReader(SourceTable.Columns[i])%>
     <%}%>
                    list.Add(model);
                }
                reader.Close();
            }

            return list;
        }
  
  /// <summary>
  /// 得到一个对象实体
  /// </summary>
  public <%=NameSpace%>.Model.<%=SourceTable.Name%> GetModel(<% =GetPrimaryKeyType(SourceTable) + " " + GetKeyColumn(SourceTable).Name.ToLower()%>)
  {
   StringBuilder strSql=new StringBuilder();
   strSql.Append("select <%=GetColumnNameList(SourceTable)%> from <%=SourceTable.Name%> ");
   strSql.Append(" where <%=GetKeyColumn(SourceTable).Name%>=@<%=GetKeyColumn(SourceTable).Name%> ");
   SqlParameter[] parameters = {
     new SqlParameter("@<% =GetKeyColumn(SourceTable).Name %>", <%=GetKeyColumnSqlType(SourceTable)%>, <%=GetKeyColumnSqlLength(SourceTable)%>)};
   parameters[0].Value = <%=GetKeyColumn(SourceTable).Name.ToLower()%>;

   <%=NameSpace%>.Model.<%=SourceTable.Name%> model = new <%=NameSpace%>.Model.<%=SourceTable.Name%>();
   DataSet ds=DbHelperSQL.Query(strSql.ToString(),parameters);
   if(ds.Tables[0].Rows.Count>0)
   {
    <% for(int i=0; i<SourceTable.Columns.Count; i++){%>
    model.<%=SourceTable.Columns[i].Name%> = <%=GetDataSet(SourceTable.Columns[i])%>
    <%}%>
    return model;
   }
   else
   {
    return null;
   }
  }
  #endregion
 }
}
<script runat="template">
public string GetUpdateColumn(TableSchema table)
{
 string strUpdate = "";
 for(int i=0; i<table.Columns.Count; i++)
 {
  if(!table.Columns[i].IsPrimaryKeyMember)
  {
   if(strUpdate == string.Empty)
    strUpdate = "[" + table.Columns[i].Name + "]=@" + table.Columns[i].Name;
   else
    strUpdate = strUpdate + "," + "[" + table.Columns[i].Name + "]=@" + table.Columns[i].Name;
  }
 }
 return strUpdate;
}

public string GetDataSet(ColumnSchema column)
{
 string sqlReader = "ds.Tables[0].Rows[0][/"" + column.Name + "/"]";
 
 string csharpType = GetCSharpType(column);
 if(csharpType.ToLower() == "string")
  return sqlReader + ".ToString()";
 
 string temp = "(" + GetCSharpType(column) + ")" + sqlReader;
 if(column.AllowDBNull)
 {
  temp = sqlReader + ".ToString() == Strimg.Empty ? null : " + temp;
 }
 
 temp = temp + ";";
 return temp;
}

public string GetSqlReader(ColumnSchema column)
{
 string sqlReader = "reader[/"" + column.Name + "/"]";
 
 string csharpType = GetCSharpType(column);
 if(csharpType.ToLower() == "string")
  return sqlReader + ".ToString()";
 
 string temp = "(" + GetCSharpType(column) + ")" + sqlReader;
 if(column.AllowDBNull)
 {
  temp = sqlReader + ".ToString() == Strimg.Empty ? null : " + temp;
 }
 
 temp = temp + ";";
 return temp;
}

public string GetColumnNameListPara(TableSchema table)
{
 string columnList = "";
 
 for(int i=0; i<table.Columns.Count; i++)
 {
  if(columnList == string.Empty)
   columnList = "@" + table.Columns[i].Name;
  else
   columnList = columnList + ",@" + table.Columns[i].Name;
 }
 
 return columnList;
}

public string GetColumnNameList(TableSchema table)
{
 string columnList = "";
 
 for(int i=0; i<table.Columns.Count; i++)
 {
  if(columnList == string.Empty)
   columnList = "[" + table.Columns[i].Name + "]";
  else
   columnList = columnList + ",[" + table.Columns[i].Name + "]";
 }
 
 return columnList;
}

public ColumnSchema GetKeyColumn(TableSchema table)
{
 ColumnSchema column = null;
 for(int i=0; i<table.Columns.Count; i++)
 {
  if(table.Columns[i].IsPrimaryKeyMember)
   column = table.Columns[i];
 }
 return column;
}

public string GetKeyColumnSqlType(TableSchema table)
{
 ColumnSchema column = GetKeyColumn(table);
 if(column == null)
  return string.Empty;
  
 return GetSqlDbType(column);
}

public int GetKeyColumnSqlLength(TableSchema table)
{
 ColumnSchema column = GetKeyColumn(table);
 if(column == null)
  return 0;
  
 return column.Size;
}

public string GetPrimaryKeyType(TableSchema table)
{
 int columnIndex = 0;
 for(int i=0; i<table.Columns.Count; i++)
 {
  if(table.Columns[i].IsPrimaryKeyMember)
   columnIndex = i;
 }
 
 return GetCSharpType(table.Columns[columnIndex]);
}

public string GetSqlDbType(ColumnSchema column) 
{
 switch (column.NativeType.ToLower())
 {
  case "bigint": return "SqlDbType.BigInt";
  case "binary": return "SqlDbType.Binary";
  case "bit": return "SqlDbType.Bit";
  case "char": return "SqlDbType.Char";
  case "datetime": return "SqlDbType.DateTime";
  case "decimal": return "SqlDbType.Decimal";
  case "float": return "SqlDbType.Float";
  case "image": return "SqlDbType.Image";
  case "int": return "SqlDbType.Int";
  case "money": return "SqlDbType.Money";
  case "nchar": return "SqlDbType.NChar";
  case "ntext": return "SqlDbType.NText";
  case "numeric": return "SqlDbType.Decimal";
  case "nvarchar": return "SqlDbType.NVarChar";
  case "real": return "SqlDbType.Real";
  case "smalldatetime": return "SqlDbType.SmallDateTime";
  case "smallint": return "SqlDbType.SmallInt";
  case "smallmoney": return "SqlDbType.SmallMoney";
  case "sql_variant": return "SqlDbType.Variant";
  case "sysname": return "SqlDbType.NChar";
  case "text": return "SqlDbType.Text";
  case "timestamp": return "SqlDbType.Timestamp";
  case "tinyint": return "SqlDbType.TinyInt";
  case "uniqueidentifier": return "SqlDbType.UniqueIdentifier";
  case "varbinary": return "SqlDbType.VarBinary";
  case "varchar": return "SqlDbType.VarChar";
  default: return "__UNKNOWN__" + column.NativeType;
}
}

public string GetCSharpType(ColumnSchema column)
{
 string para = "";
 if (column.Name.EndsWith("TypeCode"))
  return column.Name;
  
 switch (column.DataType)
 {
  case DbType.AnsiString:
   para = "string";
   break;
  case DbType.AnsiStringFixedLength:
   para = "string";
   break;
  case DbType.Binary:
   para = "byte[]";
   break;
  case DbType.Boolean:
   para = "bool";
   break;
  case DbType.Byte:
   para = "int";
   break;
  case DbType.Currency:
   para = "decimal";
   break;
  case DbType.Date:
   para = "DateTime";
   break;
  case DbType.DateTime:
   para = "DateTime";
   break;
  case DbType.Decimal:
   para = "decimal";
   break;
  case DbType.Double:
   para = "double";
   break;
  case DbType.Guid:
   para = "Guid";
   break;
  case DbType.Int16:
   para = "short";
   break;
  case DbType.Int32:
   para = "int";
   break;
  case DbType.Int64:
   para = "long";
   break;
  case DbType.Object:
   para = "object";
   break;
  case DbType.SByte:
   para = "sbyte";
   break;
  case DbType.Single:
   para = "float";
   break;
  case DbType.String:
   para = "string";
   break;
  case DbType.StringFixedLength:
   para = "string";
   break;
  case DbType.Time:
   para = "TimeSpan";
   break;
  case DbType.UInt16:
   para = "ushort";
   break;
  case DbType.UInt32:
   para = "uint";
   break;
  case DbType.UInt64:
   para = "ulong";
   break;
  case DbType.VarNumeric:
   para = "decimal";
   break;
  default:
   para = "__UNKNOWN__" + column.NativeType;
   break;
 }
  
 return para;
}
</script>