ProductS类中写的是product表调用的方法
分别有:按条件查询(条件为:productname 产品名称 ,CategoryId类别编号)
查询表(条件为:PID)
删除语句(条件为:PID)
更新语句(条件为:product表)
首先在该类中调用
using System.Data;
using System.Data.SqlClient;
using Model;
然后条件查询方法为:
public static DataTable Select(string ProductName = "", int CategoryId = 0)
{
string sql = " select * from Product ,ProductCategory where Product.CategoryId = ProductCategory.id ";
if (ProductName != "")
{
sql += string.Format(" and Product.ProductName like '%{0}%' ", ProductName);
}
if (CategoryId != 0)
{
sql += string.Format(" and Product.CategoryId = {0} ", CategoryId);
}
return SQLhelper.Query(sql);
}
首先进行联合查询表中的内容,然后判断productname 和 categoryid 是否为空值 如果不为空则进行条件增加
productname 的条件为like模糊查询,categoryid为常规查询
紧接着查询单表的内容:
public static Product Select(int PID)
{
string sql = string.Format(" select * from Product where PID = {0} ", PID);
var dt = SQLhelper.Query(sql);
if (dt.Rows.Count < 1)
return null;
var row = dt.Rows[0];
return new Product()
{
PID = Convert.ToInt32(row["PID"]),
Addtime = Convert.ToDateTime(row["Addtime"]),
CategoryId = Convert.ToInt32(row["CategoryId"]),
IsOnSale = Convert.ToInt32(row["IsOnSale"]),
ProductName = Convert.ToString(row["ProductName"]),
MarketPrice = Convert.ToDecimal(row["MarketPrice"]),
SellingPrice = Convert.ToDecimal(row["SellingPrice"]),
Introduction = Convert.ToString(row["Introduction"]),
};
}
首先根据pid查询表中的内容,定义dt获取表
根据dt进行判断是否包含有表,查询到后 定义row赋值为行内容
最后将获取到的product赋值给model层使其可以调用
赋值时注意变量接收时的转换类型
删除语句:
public static int Delete(int PID)
{
string sql = string.Format(" delete from Product where PID = {0} ", PID);
return SQLhelper.NoQuery(sql);
}
常规删除语句
修改语句:
public static int Update(Product product)
{
string sql = string.Format(" UPDATE Product SET ProductName = '{0}',MarketPrice = {1},SellingPrice = {2},CategoryId = {3} ,Introduction='{4}',IsOnSale = {5} WHERE PID = {6} ",
product.ProductName, product.MarketPrice, product.SellingPrice, product.CategoryId, product.Introduction, product.IsOnSale, product.PID);
return SQLhelper.NoQuery(sql);
}
常规修改语句
这样productS表就写完了
紧接着写ProductCategoryS表
与常规一样调用
public static DataTable Select()
{ //sql语句
string sql = " select * from ProductCategory ";
return SQLhelper.Query(sql);
}