三层架构中的DAL

来源：互联网发布：灵格斯翻译家 mac版本编辑：程序博客网时间：2024/06/08 17:13

public int Update(Mycollect.Model.AdUserModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update AdUser set ");
strSql.Append("Quota=@Quota,");
strSql.Append("Url=@Url,");
strSql.Append("Title=@Title,");
strSql.Append("Intro=@Intro,");
strSql.Append("ImgUrl=@ImgUrl");
strSql.Append(" where UserID=@UserID");
SqlParameter[] parameters = {
new SqlParameter("@UserID", SqlDbType.Int,4),
new SqlParameter("@Quota", SqlDbType.Money,8),
new SqlParameter("@Url", SqlDbType.VarChar,100),
new SqlParameter("@Title", SqlDbType.NVarChar,100),
new SqlParameter("@Intro", SqlDbType.NVarChar,500),
new SqlParameter("@ImgUrl", SqlDbType.VarChar,100)};
parameters[0].Value = model.UserID;
parameters[1].Value = model.Quota;
parameters[2].Value = model.Url;
parameters[3].Value = model.Title;
parameters[4].Value = model.Intro;
parameters[5].Value = model.ImgUrl;
return SqlHelper.Query(strSql.ToString(), parameters);

}

/// <summary>
/// 添加/更新一条数据
/// </summary>
/// <param name="model"></param>
/// <returns></returns>
public int AddOrUpdate(Mycollect.Model.ShopAttributeModel model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(" if exists(select * from shopAttribute where ShopID=@ShopID) ");
strSql.Append(" update shopAttribute set Type=@Type, DateIn=@DateIn where ShopID=@ShopID;");
strSql.Append(" else");
strSql.Append(" insert into shopAttribute (ShopID,Type,DateIn) values (@ShopID,@Type,@DateIn)");
strSql.Append(";select @RowID=@@IDENTITY");
SqlParameter[] parameters ={
new SqlParameter("@RowID",SqlDbType.Int),
new SqlParameter("@ShopID",SqlDbType.Int,4),
new SqlParameter("@Type",SqlDbType.Int,4),
new SqlParameter("@DateIn",SqlDbType.DateTime)};
parameters[0].Direction = ParameterDirection.Output;
parameters[1].Value = model.ShopID;
parameters[2].Value = model.Type;
parameters[3].Value = model.DateIn;
SqlHelper.Query(strSql.ToString(), parameters);
return (int)parameters[0].Value;

}

分页：

/// <summary>
/// 得到所以商店的信息(小钟)
/// </summary>
/// <param name="Title"></param>
/// <param name="flag"></param>
/// <param name="State"></param>
/// <param name="PageIndex"></param>
/// <param name="PageSize"></param>
/// <param name="Row"></param>
/// <returns></returns>
public DataTable GetList(string Title, string flag, string Type, string time, int PageIndex, int PageSize, out int Row)
{
StringBuilder sql = new StringBuilder();
sql.Append("select * from (select *,row_number() over(order by Id desc) row from shop where 1=1 ");
if (Title != null && Title != "")
{
switch (flag)
{
case "1":
sql.Append(" and Id=@title");
break;
case "2":
sql.Append(" and ShopName like '%'+@title+'%'");
break;
case "3":
sql.Append(" and fldUserName like '%'+@title+'%'");
break;
case "4":
sql.Append(" and ShopClass<5 and ShopClass>1 and datediff(MONTH,OpenTime,GETDATE())=11");
break;
}
}
if (Type != "all")
{
sql.AppendFormat(" and ShopClass={0}", Type);
}
sql.Append(" and Id in (SELECT ShopID from [shopAttribute] where type=1 )");
sql.Append(" )t where row between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize");
sql.Append(";select @row=count(0) from shop where 1=1");
sql.Append(" and Id in (SELECT ShopID from [shopAttribute] where type=1 )");
if (!string.IsNullOrEmpty(Title))
{
switch (flag)
{
case "1":
sql.Append(" and Id=@title");
break;
case "2":
sql.Append(" and ShopName like '%'+@title+'%'");
break;
case "3":
sql.Append(" and fldUserName like '%'+@title+'%'");
break;
case "4":
sql.Append(" and ShopClass<5 and ShopClass>1 and datediff(MONTH,OpenTime,GETDATE())=11");
break;
}
}
if (Type != "all")
{
sql.AppendFormat(" and ShopClass={0}", Type);
}
SqlParameter[] sqlparameter =
{
new SqlParameter("@title",SqlDbType.VarChar,50),
new SqlParameter("@pageindex",SqlDbType.Int,4),
new SqlParameter("@pagesize",SqlDbType.Int,4),
new SqlParameter("@row",SqlDbType.Int,4),
new SqlParameter("@time",SqlDbType.VarChar,50)
};
if (Title != null && Title != "")
{
sqlparameter[0].Value = Title;
}
else
{
sqlparameter[0].Value = "%";
}

sqlparameter[1].Value = PageIndex;
sqlparameter[2].Value = PageSize;
sqlparameter[3].Direction = ParameterDirection.Output;
sqlparameter[4].Value = time;

DataTable dt = SqlHelper.Query(sql.ToString(), sqlparameter, "table");
Row = Convert.ToInt32(sqlparameter[3].Value);
return dt;
}

select * from (select *,row_number() over(order by Id desc) row from shop where 1=1 and Id in (SELECT ShopID from [shopAttribute] where type=1 ) )t where row between (@pageindex-1)*@pagesize+1 and @pageindex*@pagesize;select @row=count(0) from shop where 1=1 and Id in (SELECT ShopID from [shopAttribute] where type=1 )

System.Web.HttpContext.Current.Response.Write(strSql.ToString());// 随处显示内容
System.Web.HttpContext.Current.Response.End();

0 0