最近在做一个管理系统,发现DAL层的分页方法都是一个模子,只是要查询的表名和条件不一样而已,而按照三层架构的方法你就得每个DAL文件里都写一遍分页方法,感觉很繁琐。于是我就灵活一旦,把这些可以封装的通用方法拿出来,放到一个名为SQLServerDALHelper的 文件里,这样就不用再每一个DAL层里面写了。由于我使用的动软代码生成器生成的代码,有的方法就不用拿出去了,这里封装的是代码生成器没有生成的。有“分页获取列表”和“更新”两个方法,其他方法以此类推,后续用到添加。
using Maticsoft.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
namespace trip.Common
{
/// <summary>
/// DAL层通用方法
/// </summary>
public class SQLServerDALHelper
{
/// <summary>
/// 分页获取数据列表
/// </summary>
/// <param name="PageSize">每页显示数据条数</param>
/// <param name="PageIndex">当前页</param>
/// <param name="strWhere">查询条件</param>
/// <param name="Table">查询表</param>
/// <param name="Query">要查询的字段</param>
/// <returns></returns>
public static DataSet GetList( int PageSize, int PageIndex, string strWhere, string Table,string Query="*")
{
StringBuilder strSql = new StringBuilder();
strSql.Append("select top " + PageSize);
strSql.Append(" " + Query);
strSql.Append(" from " + Table + " where ID not in(select top ");
strSql.Append(PageSize * (PageIndex - 1));
strSql.Append(" ID from " + Table);
if (!string.IsNullOrEmpty(strWhere))
{
strSql.Append(" where " + strWhere);
strSql.Append(") and " + strWhere);
}
else
{
strSql.Append(")");
}
return DbHelperSQL.Query(strSql.ToString());
}
/// <summary>
/// 更新
/// </summary>
/// <param name="set"></param>
/// <param name="strWhere"></param>
/// <param name="Table"></param>
/// <returns></returns>
public bool Update(string set, string strWhere,string Table)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update "+Table+" set");
strSql.Append(set);
strSql.Append(" where " + strWhere);
int rows = DbHelperSQL.ExecuteSql(strSql.ToString());
if (rows > 0)
{
return true;
}
else
{
return false;
}
}
}
}
这样,我们就可以像下面这样调用了,是不是很方便?
/// <summary>
/// 绑定repeater数据
/// </summary>
public void RepeaterDataBind()
{
try
{
StringBuilder sqlWhere = new StringBuilder();
if (!string.IsNullOrEmpty(Number))
{//维护
sqlWhere.Append(" Del!=0 and ParentNumber='");
sqlWhere.Append(Number + "'");
btn_Return.Visible = true;
}
else
{//默认
sqlWhere.Append(" Del!=0 and ParentNumber='0'");
}
if (Session["where"] != null)
{//查询
sqlWhere.Append(Session["where"].ToString());
}
rep_Dictionary.DataSource = SQLServerDALHelper.GetList(this.AspNetPager1.PageSize, this.AspNetPager1.CurrentPageIndex, sqlWhere.ToString(), "trip_sys_Dictionary"); //绑定数据
this.rep_Dictionary.DataBind();
this.AspNetPager1.RecordCount = sys_DictionaryBLL.Instance.GetRecordCount(sqlWhere.ToString());//总记录数
AspNetPager1.CustomInfoHTML = "记录总数:<b>" + AspNetPager1.RecordCount.ToString() + "</b> ";
AspNetPager1.CustomInfoHTML += "总页数:<b>" + AspNetPager1.PageCount.ToString() + "</b> ";
AspNetPager1.CustomInfoHTML += "当前页:<font color='red'><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>";
}
catch (Exception)
{
}
}