MVC三层架构

仅留作草稿参考时候用。

SQL Server中的查询以及增删改操作的方法的定义。

（没有数据库的设计。）

Model_ItemCategoryInfo

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

namespace Model
{
    public class ItemCategoryInfo
    {
        private int _CategoryID;

        public int CategoryID
        {
            get { return _CategoryID; }
            set { _CategoryID = value; }
        }
        private string _CategoryName;

        public string CategoryName
        {
            get { return _CategoryName; }
            set { _CategoryName = value; }
        }
    }
}

Business_ItemCategoryBLL

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

namespace Business
{
    public class ItemCategoryBLL
    {
        ItemCategoryDALC itemCategoryDALC = new ItemCategoryDALC();
        public List<ItemCategoryInfo> GetAllCategoryList()
        {
            return itemCategoryDALC.GetAllCategoryList();
        }
        public List<ItemCategoryInfo> GetCategoryList(string CategoryName)
        {
           return itemCategoryDALC.GetCategoryList(CategoryName);
        }
        public int InsertCategory(string CategoryName)
        {
            return itemCategoryDALC.InsertCategory(CategoryName);
        }
        public int UpdateCategory(string NewName, int CategoryID)
        {
            return itemCategoryDALC.UpdateCategory(NewName, CategoryID);
        }
        public int DeleteCategory(int CategoryID)
        {
            return itemCategoryDALC.DeleteCategory(CategoryID);
        }
    }
}

DALC_ItemCategoryDALC

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

namespace DALC
{
    public class ItemCategoryDALC
    {
        /// <summary>
        /// create the connection
        /// </summary>
        SqlConnection _con = new SqlConnection(@"Data Source=stkwx028\sqlexpress;Initial Catalog=WalMart1;Integrated Security=True");
        List<ItemCategoryInfo> ItemCategoryList = new List<ItemCategoryInfo>();

        /// <summary>
        /// get all category list
        /// </summary>
        /// <returns>itemcategory instance list</returns>
        public List<ItemCategoryInfo> GetAllCategoryList()//get all Category List
        {
            List<ItemCategoryInfo> CategoryList = new List<ItemCategoryInfo>();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"SELECT CategoryID,CategoryName 
                                FROM ItemCategory";
            _con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    ItemCategoryInfo itemCategoryInfo = new ItemCategoryInfo();
                    itemCategoryInfo.CategoryID = Convert.ToInt32(reader["CategoryID"]);
                    itemCategoryInfo.CategoryName = reader["CategoryName"].ToString();
                    ItemCategoryList.Add(itemCategoryInfo);
                }
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return ItemCategoryList;
            }
            else
            {
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return null;
            }
        }
        /// <summary>
        /// get category list by name
        /// </summary>
        /// <param name="CategoryName">categoryName</param>
        /// <returns>itemcategory instance list</returns>
        public List<ItemCategoryInfo> GetCategoryList(string CategoryName)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"SELECT * FROM ItemCategory
                                WHERE CategoryName LIKE '%'+@CategoryName+'%'
                                ORDER BY CategoryID";
            cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
            _con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    ItemCategoryInfo itemCategoryInfo = new ItemCategoryInfo();
                    itemCategoryInfo.CategoryID = Convert.ToInt32(reader["CategoryID"]);
                    itemCategoryInfo.CategoryName = reader["CategoryName"].ToString();
                    ItemCategoryList.Add(itemCategoryInfo);
                }
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return ItemCategoryList;
            }
            else
            {
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return null;
            }
        }
        /// <summary>
        /// insert into category table
        /// </summary>
        /// <param name="CategoryName">CategoryName</param>
        /// <returns>int i</returns>
        public int InsertCategory(string CategoryName)
        {
            ItemCategoryInfo itemCategoryInfo = new ItemCategoryInfo();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"INSERT INTO ItemCategory(CategoryName)
                                                  VALUES(@CategoryName)";
            cmd.Parameters.AddWithValue("@CategoryName", CategoryName);
            _con.Open();
            int i = cmd.ExecuteNonQuery();
            _con.Close();
            return i;
        }
        /// <summary>
        /// update table category
        /// </summary>
        /// <param name="NewName">categoryname</param>
        /// <param name="CategoryID">categoryID</param>
        /// <returns>int i</returns>
        public int UpdateCategory(string NewName,int CategoryID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"UPDATE ItemCategory
                                SET CategoryName=@NewName
                                WHERE CategoryID=@CategoryID";
            cmd.Parameters.AddWithValue("@NewName", NewName);
            //cmd.Parameters.AddWithValue("@OldName", OldName);
            cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
            _con.Open();
            int i = cmd.ExecuteNonQuery();
            _con.Close();
            return i;
        }
        /// <summary>
        /// delete a piece of category instance
        /// </summary>
        /// <param name="CategoryID">CategoryID</param>
        /// <returns>int i</returns>
        public int DeleteCategory(int CategoryID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"DELETE FROM ItemCategory
                                WHERE CategoryID=@CategoryID";
            cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
            _con.Open();
            int i = cmd.ExecuteNonQuery();
            _con.Close();
            return i;
        }
        /// <summary>
        /// get category list by id
        /// </summary>
        /// <param name="CategoryID">CategoryID</param>
        /// <returns>category list</returns>
        public List<ItemCategoryInfo> GetList (int  CategoryID)
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = _con;
            cmd.CommandText = @"SELECT * FROM ItemCategory
                                WHERE CategoryID =@CategoryID";
            cmd.Parameters.AddWithValue("@CategoryID", CategoryID);
            _con.Open();
            SqlDataReader reader = cmd.ExecuteReader();
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    ItemCategoryInfo itemCategoryInfo = new ItemCategoryInfo();
                    itemCategoryInfo.CategoryID = Convert.ToInt32(reader["CategoryID"]);
                    itemCategoryInfo.CategoryName = reader["CategoryName"].ToString();
                    ItemCategoryList.Add(itemCategoryInfo);
                }
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return ItemCategoryList;
            }
            else
            {
                if (reader != null)
                {
                    reader.Close();
                }
                _con.Close();
                return null;
            }
        }
    }
}