/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, WebSiteProductInfoModel model, string CompanyCD, string productName)
{
SqlCommand comm = new SqlCommand();
StringBuilder sql = new StringBuilder(@"SELECT wspi.ID,wspi.ProductID,wspi.Description,wspi.Price,wspi.ImgDIr,wspi.Status,wspi.DiscountStatus,pi1.ProductName
FROM websitedba.WebSiteProductInfo wspi
INNER JOIN officedba.ProductInfo pi1 ON ProductID=pi1.ID AND pi1.CompanyCD='" + CompanyCD + "'");
sql.Append(" WHERE 1=1 ");
if (!String.IsNullOrEmpty(productName))
{
sql.AppendFormat(" AND pi1.ProductName LIKE '%{0}%' ", productName);
}
if (model.Price.HasValue)
{
sql.AppendFormat(" AND wspi.Price={0} ", model.Price.Value);
}
if (!String.IsNullOrEmpty(model.Status))
{
sql.AppendFormat(" AND wspi.Status='{0}' ", model.Status);
}
if (!String.IsNullOrEmpty(model.DiscountStatus))
{
sql.AppendFormat(" AND wspi.DiscountStatus='{0}' ", model.DiscountStatus);
}
comm.CommandText = sql.ToString();
return(SqlHelper.PagerWithCommand(comm, pageIndex, pageCount, orderBy, ref TotalCount));
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改操作的执行命令</returns>
public static SqlCommand UpdateCommand(WebSiteProductInfoModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_UPDATE;
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
return(comm);
}
/// <summary>
/// 修改数据记录
/// </summary>
/// <param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(WebSiteProductInfoModel model)
{
bool returnValue = false; // 返回值
// SQL语句
StringBuilder sqlSentence = new StringBuilder(C_UPDATE);
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
//执行SQL
returnValue = SqlHelper.ExecuteTransSql(sqlSentence.ToString(), parameters) > 0;
return(returnValue);
}
/// <summary>
/// 插入操作的执行命令
/// </summary>
/// <param name="model">实体类</param>
/// <returns>插入操作的执行命令</returns>
public static SqlCommand InsertCommand(WebSiteProductInfoModel model)
{
// SQL语句
SqlCommand comm = new SqlCommand();
comm.CommandText = C_INSERT + " SET @IndexID = @@IDENTITY ";
// 参数设置
SqlParameter[] parameters = SetInsertAndUpdateParameters();
parameters = SetInsertAndUpdateParametersValue(parameters, model);
comm.Parameters.AddRange(parameters);
SqlParameter IndexID = new SqlParameter("@IndexID", SqlDbType.Int);
IndexID.Direction = ParameterDirection.Output;
comm.Parameters.Add(IndexID);
return(comm);
}
/// <summary>
/// 插入数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>插入数据是否成功:true成功,false不成功</returns>
public static bool Insert(WebSiteProductInfoModel model)
{
ArrayList sqlList = new ArrayList();
SqlCommand cmd = WebSiteProductInfoDBHelper.InsertCommand(model);
sqlList.Add(cmd);
if (SqlHelper.ExecuteTransWithArrayList(sqlList))
{
int i = 0;
if (int.TryParse(cmd.Parameters["@IndexID"].Value.ToString(), out i))
{
model.ID = i;
}
return(true);
}
else
{
return(false);
}
}
/// <summary>
/// 设置新增和修改的参数数组的值
/// </summary>
/// <param name="parameters">参数数组</param>
/// <param name="model">实体类</param>
/// <returns>返回参数数组</returns>
private static SqlParameter[] SetInsertAndUpdateParametersValue(SqlParameter[] parameters, WebSiteProductInfoModel model)
{
if (!model.ID.HasValue)
{
parameters[m_iDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_iDCol].Value = model.ID; // 主键，自动生成
}
if (!model.ProductID.HasValue)
{
parameters[m_productIDCol].Value = System.DBNull.Value;
}
else
{
parameters[m_productIDCol].Value = model.ProductID; // 物品ID,对应表Officedba.ProductInfo
}
parameters[m_descriptionCol].Value = model.Description; // 商品描述
if (!model.Price.HasValue)
{
parameters[m_priceCol].Value = System.DBNull.Value;
}
else
{
parameters[m_priceCol].Value = model.Price; // 商品价格
}
parameters[m_imgDIrCol].Value = model.ImgDIr; // 图片路径
parameters[m_statusCol].Value = model.Status; // 状态1 启用0 禁用
parameters[m_discountStatusCol].Value = model.DiscountStatus; // 折扣状态1 启用0 禁用
return(parameters);
}
/// <summary>
/// 修改数据记录
/// </summary>
///<param name="model">实体类</param>
/// <returns>修改数据是否成功:true成功,false不成功</returns>
public static bool Update(WebSiteProductInfoModel model)
{
return(WebSiteProductInfoDBHelper.Update(model));
}
/// <summary>
/// 列表界面查询方法
/// </summary>
/// <param name="pageIndex">当前页</param>
/// <param name="pageCount">每页记录数</param>
/// <param name="orderBy">排序方法</param>
/// <param name="TotalCount">总记录数</param>
/// <param name="model">实体类</param>
/// <returns></returns>
public static DataTable SelectListData(int pageIndex, int pageCount, string orderBy, ref int TotalCount
, WebSiteProductInfoModel model, string CompanyCD, string productName)
{
return(WebSiteProductInfoDBHelper.SelectListData(pageIndex, pageCount, orderBy, ref TotalCount, model, CompanyCD, productName));
}
