impitemdal.cs

酒店在线订餐管理系统

using System;
using System.Collections.Generic;
using System.Text;
using IDDal;
using Model;
using System.Data.Common;
using System.Data;
using System.Data.SqlClient;
namespace ImpDDal
{
    public class ImpItemDal:DbCommandStore,ItemDal<Item>
    {
        #region ItemDal<Item> 成员

        DbCommand cmd = GetCommand();

        #region 增加一个菜

        /// <summary>
        /// 增加一个菜肴的方法
        /// </summary>
        /// <param name="item">菜肴对象</param>
        /// <returns>影响数据库行数</returns>
        public int AddItem(Item item)
        {
            cmd.CommandText = "INSERT INTO UT_Items(ItemsName,Price,Type,Pic,Detail) VALUES(@IN,@P,@T,@Pic,@d)";
            SqlParameter[] parameter=new SqlParameter[5];
            parameter[0] = new SqlParameter("IN", item.ItemName);
            parameter[1] = new SqlParameter("P", item.Price);
            parameter[2] = new SqlParameter("T", item.Type);
            parameter[3] = new SqlParameter("Pic", item.Pic);
            parameter[4] = new SqlParameter("d", item.Detail);

            cmd.Parameters.Add(parameter[0]);
            cmd.Parameters.Add(parameter[1]);
            cmd.Parameters.Add(parameter[2]);
            cmd.Parameters.Add(parameter[3]);
            cmd.Parameters.Add(parameter[4]);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }


        #endregion

        #region 删除一个菜肴
        public int DeleteItem(Item item)
        {
            cmd.CommandText = "DELETE FROM UT_Items WHERE SystemId=@id";
            SqlParameter parameter = new SqlParameter("id", item.SystemId);
            cmd.Parameters.Add(parameter);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 修改一个菜肴
        public int EditItem(Item item)
        {
            cmd.CommandText = "UPDATE UT_Items SET ItemsName=@IN,Price=@p,Type=@T,Pic=@pic,Detail=@d  WHERE SystemId=@id";
            SqlParameter[] parameter = new SqlParameter[6];
            parameter[0] = new SqlParameter("IN", item.ItemName);
            parameter[1] = new SqlParameter("P", item.Price);
            parameter[2] = new SqlParameter("T", item.Type);
            parameter[3] = new SqlParameter("Pic", item.Pic);
            parameter[4] = new SqlParameter("d", item.Detail);
            parameter[5] = new SqlParameter("id", item.SystemId);

            cmd.Parameters.Add(parameter[0]);
            cmd.Parameters.Add(parameter[1]);
            cmd.Parameters.Add(parameter[2]);
            cmd.Parameters.Add(parameter[3]);
            cmd.Parameters.Add(parameter[4]);
            cmd.Parameters.Add(parameter[5]);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 按查询条件返回菜肴
        public IList<Item> SelectItem(string itemName)
        {
            cmd.CommandText = "SELECT * FROM UT_Items WHERE ItemsName LIKE  @IN";
            SqlParameter p = new SqlParameter("IN", "%"+itemName+"%");
            cmd.Parameters.Add(p);

            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                List<Item> list = new List<Item>();
                Item item;
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    item = new Item(long.Parse(reader.GetValue(0).ToString()), reader.GetString(1), float.Parse(reader.GetValue(2).ToString()), int.Parse(reader.GetValue(3).ToString()), reader.GetString(4), reader.GetString(5));
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }

        public IList<Item> SelectItem(string itemName, float price, int type)
        {
            cmd.CommandText = "SELECT * FROM UT_Items WHERE ItemsName like '@IN' AND Price=@p AND Type=@type";
            cmd.Parameters.Add(itemName);
            cmd.Parameters.Add(price);
            cmd.Parameters.Add(type);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                List<Item> list = new List<Item>();
                Item item;
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    item = new Item(long.Parse(reader.GetValue(0).ToString()), reader.GetString(1), reader.GetFloat(2), int.Parse(reader.GetValue(3).ToString()), reader.GetString(4), reader.GetString(5));
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }

        public IList<Item> SelectItem(string itemName, float price)
        {
            cmd.CommandText = "SELECT * FROM UT_Items WHERE ItemsName like '@IN' AND Price=@p";
            cmd.Parameters.Add(itemName);
            cmd.Parameters.Add(price);

            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                List<Item> list = new List<Item>();
                Item item;
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                while (reader.Read())
                {
                    item = new Item(long.Parse(reader.GetValue(0).ToString()), reader.GetString(1), reader.GetFloat(2), int.Parse(reader.GetValue(3).ToString()), reader.GetString(4), reader.GetString(5));
                    list.Add(item);
                }
                return list;
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }

        public IList<Item> SelectItem(int type)
        {
            cmd.CommandText = "SELECT * FROM UT_Items WHERE Type=@p";
            SqlParameter p = new SqlParameter("p", type);
            cmd.Parameters.Add(p);
            try