3C数码商城——三层架构DAL层

DAL层前面的创建与Model层一样，不一样的是多出来了SqlHelper。SqlHelper我们前面有写。

ProductCategoryService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Model
{
    /// <summary>
    /// 产品类别表
    /// </summary>
    public class ProductCategory
    {
        /// <summary>
        /// 主键Id
        /// </summary>
        public int Id { get; set; }
        /// <summary>
        /// 类别名称
        /// </summary>
        public string Name { get; set; }
    }
}

 ProductService.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Model;

namespace DAL
{
    public class ProductService
    {
        /// <summary>
        /// 查询
        /// </summary>
        /// <param name="name"></param>
        /// <param name="category"></param>
        /// <returns></returns>
        public static DataTable Select(string name = "", int category = 0)
        {
            var sql = "select Product.*,ProductCategory.Name from ProductCategory,Product where ProductCategory.Id=Product.CategoryId ";
            if (category > 0)
            {
                sql += "and Product.CategoryId= " + category;
            }

            if (!string.IsNullOrEmpty(name))
            { 
                sql+=string.Format( "and ProductName like'%{0}%'",name);
            }
            return SqlHelp.Query(sql);
        }
        /// <summary>
        /// 根据id查找
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static Product Selectid(int id)
        {
            string sql = string.Format(" select * from Product where Id = {0} ", id);
            var table = SqlHelp.Query(sql);
            if (table.Rows.Count < 1)
                return null;
            var row = table.Rows[0];
            return new Product()
            {
                Id =Convert.ToInt32(row["Id"]),
                AddTime = Convert.ToDateTime(row["AddTime"]),
                CategoryId = Convert.ToInt32(row["CategoryId"]),
                Introduction = Convert.ToString(row["Introduction"]),
                IsOnSale = Convert.ToInt32(row["IsOnSale"]),
                MarketPrice = Convert.ToDouble(row["MarketPrice"]),
                ProductName = Convert.ToString(row["ProductName"]),
                SellingPrice = Convert.ToDouble(row["SellingPrice"]),
            };
        }
        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int Delete(int id)
        {
            var sql = "delete from product where Id=" + id;
            return SqlHelp.NoneQuery(sql);
        }
        /// <summary>
        /// 修改
        /// </summary>
        /// <param name="product"></param>
        /// <returns></returns>
        public static int Update(Product product)
        {
            var sql = string.Format("update Product set ProductName='{0}',MarketPrice='{1}',SellingPrice='{2}',CategoryId='{3}',Introduction='{4}',IsOnSale='{5}' where Id='{6}'",
                                                product.ProductName,product.MarketPrice,product.SellingPrice,product.CategoryId,product.Introduction,product.IsOnSale,product.Id);
            return SqlHelp.NoneQuery(sql); 
        }

    }
}

 DAL是数据库中的重点，增删改查都需要写在里面，如果测试sql语句不确定可在数据库中先试一下。