ASP.NET三层架构源码(CodeSmith版)之四：Table-SQLServerDAL层
动软代码生成器生成的ASP.NET三层架构代码比较规范，是学习ASP.NET的好例子
此三层架构改造自动软的工厂模式模板，使用CodeSmith进行重写，以方便大家修改模板文件
以下是针对表格的SQLServerDAL层源码：
<%@ CodeTemplate Inherits="CodeTemplate" language="C#" TargetLanguage="Text" Description="NetTiers main template."Debug="True" ResponseEncoding="UTF-8"%>
<%-- 加载访问数据库的组件SchemaExplorer,并声明其使用的命名空间 --%>
<%@ Assembly Name="SchemaExplorer" %>
<%@ Import Namespace="SchemaExplorer" %>
<%@ Import Namespace="System.Text" %>
<%@ Assembly Name="System.Data" %>
<%@ Assembly Name="CodeSmith.BaseTemplates" %>
<%@ Import Namespace="CodeSmith.BaseTemplates" %>
<%@ Assembly Name="CodeSmith.CustomProperties" %>
<%@ Import Namespace="CodeSmith.CustomProperties" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Property Name="Namespace" Type="String" Category="Context" Description="NameSpace" Default="Crs811NameSpace"%>
<%@ Property Name="Author" Type="String" Category="Context" Description="Author" Default="chenr"%>
<%@ Property Name="TablePrefix" Type="System.String" Default="T" Category="Context" Description="The prefix to remove from table names" %>
<%@ Property Name="TableName" Type="TableSchema" DeepLoad="True" Optional="False" Category="Context" Description="" %>
/*------------------------------------------------
// File Name:<%=ClearPrefix(TableName.Name) %>.cs
// File Description:<%=ClearPrefix(TableName.Name) %> SQL Server DataBase Access
// Author:<%=Author%>
// Create Time:<%= DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")%>
//------------------------------------------------*/
using System;
using System.Text;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Data;
using <%=Namespace%>.DBUtility;
using <%=Namespace%>.IDAL;
using <%=Namespace%>.Model;
namespace <%=Namespace%>.SQLServerDAL
{
///
/// <%=ClearPrefix(TableName.Name) %> DAL
///
public partial class <%=ClearPrefix(TableName.Name) %>: I<%=ClearPrefix(TableName.Name) %>
{
#region Method
///
/// 得到最大ID
///
public int GetMaxId()
{
return DbHelperSQL.GetMaxID("<%= GetKeyMax(TableName)%>", "<%=ClearPrefix(TableName.Name) %>");
//DbHelperSQL.RunProcedure("tTest_GetMaxId","",out rowsAffected);
//return rowsAffected;
}
///
/// 是否存在该记录
///
public bool Exists(<%= GetInParameter(TableName) %>)
{
int rowsAffected;
SqlParameter[] parameters ={
<%= GenerateNewSqlParameter(TableName, true) %>};
<% for (int i=0; i
parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>;
<%}%>
int result= DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Exists",parameters,out rowsAffected);
if(result==1)
{
return true;
}
else
{
return false;
}
}
///
/// 增加一条数据
///
public bool Add(m<%=ClearPrefix(TableName.Name) %> model)
{
int rowsAffected;
SqlParameter[] parameters ={
<%= GenerateNewSqlParameter(TableName, false) %>};
<%= GenerateSqlParameter(TableName, false, "C")%>
DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_ADD",parameters,out rowsAffected);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 更新一条数据
///
public bool Update(m<%=ClearPrefix(TableName.Name) %> model)
{
int rowsAffected=0;
SqlParameter[] parameters = {
<%= GenerateNewSqlParameter(TableName, false) %>};
<%= GenerateSqlParameter(TableName, false, "U") %>
DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Update",parameters,out rowsAffected);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
///
/// 删除一条数据
///
public bool Delete(<%= GetInParameter(TableName) %>)
{
int rowsAffected=0;
SqlParameter[] parameters = {
<%= GenerateNewSqlParameter(TableName, true) %>};
<% for (int i=0; i
parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>;
<%}%>
DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_Delete",parameters,out rowsAffected);
if (rowsAffected > 0)
{
return true;
}
else
{
return false;
}
}
<% if (TableName.PrimaryKey.MemberColumns.Count == 1) {%>
///
/// 批量删除数据
///
public bool DeleteList(string <%=TableName.PrimaryKey.MemberColumns[0].Name%>list)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("delete from <%=ClearPrefix(TableName.Name) %> ");
strSql.Append(" where <%=TableName.PrimaryKey.MemberColumns[0].Name%> in ("+<%=TableName.PrimaryKey.MemberColumns[0].Name%>list + ") ");
int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
<%}%>
///
/// 得到一个对象实体
///
public m<%=ClearPrefix(TableName.Name) %> GetModel(<%= GetInParameter(TableName) %>)
{
SqlParameter[] parameters = {
<%= GenerateNewSqlParameter(TableName, true) %>};
<% for (int i=0; i
parameters[<%=i%>].Value = <%= TableName.PrimaryKey.MemberColumns[i].Name %>;
<%}%>
m<%=ClearPrefix(TableName.Name) %> model=new m<%=ClearPrefix(TableName.Name) %>();
DataSet ds= DbHelperSQL.RunProcedure("<%=ClearPrefix(TableName.Name) %>_GetModel",parameters,"ds");
if(ds.Tables[0].Rows.Count>0)
{
return DataRowToModel(ds.Tables[0].Rows[0]);
}
else
{
return null;
}
}
///
/// 得到一个对象实体
///
public m<%=ClearPrefix(TableName.Name) %> DataRowToModel(DataRow row)
{
m<%=ClearPrefix(TableName.Name) %> model=new m<%=ClearPrefix(TableName.Name) %>();
if (row != null)
{
<%= GenerateModelWithRow(TableName) %>
}
return model;
}
///
/// 获得数据列表
///
public DataSet GetList(string strWhere)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select <%= GetAllRows(TableName)%>");
strSql.Append(" FROM <%=ClearPrefix(TableName.Name) %> ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
return DbHelperSQL.Query(strSql.ToString());
}
///
/// 获得前几行数据
///
public DataSet GetList(int Top,string strWhere,string filedOrder)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select ");
if(Top>0)
{
strSql.Append(" top "+Top.ToString());
}
strSql.Append(" <%= GetAllRows(TableName)%> ");
strSql.Append(" FROM <%=ClearPrefix(TableName.Name) %> ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
strSql.Append(" order by " + filedOrder);
return DbHelperSQL.Query(strSql.ToString());
}
///
/// 获取记录总数
///
public int GetRecordCount(string strWhere)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("select count(*) FROM <%=ClearPrefix(TableName.Name) %> ");
if(strWhere.Trim()!="")
{
strSql.Append(" where "+strWhere);
}
object obj = DbHelperSQL.GetSingle(strSql.ToString());
if (obj == null)
{
return 0;
}
else
{
return Convert.ToInt32(obj);
}
}
///
/// 分页获取数据列表
///
public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex)
{
StringBuilder strSql=new StringBuilder();
strSql.Append("SELECT * FROM ( ");
strSql.Append(" SELECT ROW_NUMBER() OVER (");
if (!string.IsNullOrEmpty(orderby.Trim()))
{
strSql.Append("order by T." + orderby );
}
else
{
strSql.Append("order by T.<%=TableName.PrimaryKey.MemberColumns[0].Name%> desc");
}
strSql.Append(")AS Row, T.* from <%=ClearPrefix(TableName.Name) %> T ");
if (!string.IsNullOrEmpty(strWhere.Trim()))
{
strSql.Append(" WHERE " + strWhere);
}
strSql.Append(" ) TT");
strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
return DbHelperSQL.Query(strSql.ToString());
}
/*
///
/// 分页获取数据列表
///
public DataSet GetList(int PageSize,int PageIndex,string strWhere)
{
SqlParameter[] parameters = {
new SqlParameter("@tblName", SqlDbType.VarChar, 255),
new SqlParameter("@fldName", SqlDbType.VarChar, 255),
new SqlParameter("@PageSize", SqlDbType.Int),
new SqlParameter("@PageIndex", SqlDbType.Int),
new SqlParameter("@IsReCount", SqlDbType.Bit),
new SqlParameter("@OrderType", SqlDbType.Bit),
new SqlParameter("@strWhere", SqlDbType.VarChar,1000),
};
parameters[0].Value = "<%=ClearPrefix(TableName.Name) %>";
parameters[1].Value = "<%=TableName.PrimaryKey.MemberColumns[0].Name%>";
parameters[2].Value = PageSize;
parameters[3].Value = PageIndex;
parameters[4].Value = 0;
parameters[5].Value = 0;
parameters[6].Value = strWhere;
return DbHelperSQL.RunProcedure("UP_GetRecordByPage",parameters,"ds");
}*/
#endregion Method
#region MethodEx
#endregion MethodEx
}
}