impmenutype.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 ImpMenuType:DbCommandStore,MenuTypeDal<MenuType>
    {

        #region MenuTypeDal<MenuType> 成员
        DbCommand cmd = GetCommand();

        #region 增加一条菜单详细记录
        public string AddMenuType(MenuType menutype)
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_AddMenuType";
            SqlParameter[] p = new SqlParameter[5];
            p[0] = new SqlParameter("MN", menutype.MenuName);
            p[1] = new SqlParameter("T", menutype.Type);
            p[2] = new SqlParameter("ID", menutype.CreaterId);
            p[3] = new SqlParameter("D", menutype.Detail);
            p[4] = new SqlParameter("MenuId", menutype.MenuId);
            p[4].Direction = ParameterDirection.Output;
            p[4].DbType = DbType.Guid;

            cmd.Parameters.Add(p[0]);
            cmd.Parameters.Add(p[1]);
            cmd.Parameters.Add(p[2]);
            cmd.Parameters.Add(p[3]);
            cmd.Parameters.Add(p[4]);
            try
            {
                if(cmd.Connection.State==ConnectionState.Closed)
                   cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return cmd.Parameters["MenuId"].Value.ToString();
            }
            catch (Exception ex)
            {
                throw new Exception("增加菜单失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 删除一条菜单详细记录
        public int DeleteMenuType(MenuType menutype)
        {
            cmd.CommandText = "DELETE FROM UT_MenuType WHERE MenuId=@menutype";
            cmd.Parameters.Add(menutype.MenuId);
            DbTransaction transaction=null;
            try
            {
                cmd.Connection.Open();
                transaction= cmd.Transaction;
                cmd.Connection.BeginTransaction(IsolationLevel.Serializable);
                int i = cmd.ExecuteNonQuery();
                cmd.CommandText = "DELETE FROM UT_Menu WHERE MenuId=@menutype";
                cmd.Parameters.Add(menutype.MenuId);
                cmd.ExecuteNonQuery();
                transaction.Commit();
                return i;
            }
            catch (Exception ex)
            {
                transaction.Rollback();
                throw new Exception("删除菜单失败" + ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 编辑菜单详细记录
        public int EditMenuType(MenuType menutype)
        {
            cmd.CommandText = "UPDATE UT_MenuType SET MenuName=@MN,Detail=@d WHERE MenuId=@menutype";
            SqlParameter[] p = new SqlParameter[3];
            p[0] = new SqlParameter("MN", menutype.MenuName);
            p[1] = new SqlParameter("d", menutype.Detail);
            p[2] = new SqlParameter("menutype", DbType.Guid);
            p[2].Value = menutype.MenuId;

            cmd.Parameters.Add(p[0]);
            cmd.Parameters.Add(p[1]);
            cmd.Parameters.Add(p[2]);
            try
            {
                if(cmd.Connection.State==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 查询方式

        #region 获取所有的菜单记录
        public IList<MenuType> MenuTypes()
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType";
            try
            {
                cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据类型查询菜单
        public IList<MenuType> MenuTypes(string type)
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType WHERE Type=@t";
            SqlParameter p = new SqlParameter("t", type);
            cmd.Parameters.Add(p);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据类型和创建者查询菜单记录
        public IList<MenuType> MenuTypes(string type, string createrid)
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType WHERE Type=@t AND CreaterId=@cid";
            SqlParameter[] p = new SqlParameter[2];
            p[0] = new SqlParameter("t", type);
            p[1] = new SqlParameter("cid", createrid);
            cmd.Parameters.Add(p[0]);
            cmd.Parameters.Add(p[1]);
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据菜单类型,创建者ID,创建时间查询菜单
        public IList<MenuType> MenuTypes(string type, string createrid, DateTime time)
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType WHERE Type=@t AND CreaterId=@cid AND CreateTime=@time";
            cmd.Parameters.Add(type);
            cmd.Parameters.Add(createrid);
            cmd.Parameters.Add(time);
            try
            {
                cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据类型,创建者ID,菜单名获取菜单
        public IList<MenuType> MenuTypes(string type, string createrid, string menuname)
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType WHERE Type=@t AND CreaterId=@cid AND MenuName=@MN";
            cmd.Parameters.Add(type);
            cmd.Parameters.Add(createrid);
            cmd.Parameters.Add(menuname);
            try
            {
                cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据菜单类型,创建者ID,菜单名,创建时间查询菜单
        public IList<MenuType> MenuTypes(string type, string createrid, string menuname, DateTime time)
        {
            cmd.CommandText = "SELECT * FROM UT_MenuType WHERE Type=@t AND CreaterId=@cid AND MenuName=@MN AND CreateTime=@time";
            cmd.Parameters.Add(type);
            cmd.Parameters.Add(createrid);
            cmd.Parameters.Add(menuname);
            cmd.Parameters.Add(time);
            try
            {
                cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype=null;
                while (reader.Read())
                {
                    menutype=new MenuType(reader.GetValue(0).ToString(),reader.GetValue(1).ToString(),reader.GetValue(2).ToString(),reader.GetValue(3).ToString(),DateTime.Parse(reader.GetValue(4).ToString()));
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {
                
                throw new Exception("查询菜单失败"+ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }
        #endregion

        #region 根据ID获得菜单对象
        public MenuType GetOneMenu(string menuId)
        {
            cmd.CommandText = "SELECT * FROM UV_Menu WHERE MenuId=@id";
            SqlParameter p = new SqlParameter("id", DbType.Guid);
            p.Value=menuId;
            cmd.Parameters.Add(p);
            try
            {
                if(cmd.Connection.State==System.Data.ConnectionState.Closed)
                 cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                MenuType menutype = null;
                IList<Item> menuitems = new List<Item>();
                Item oneItem = null;
                string _menuid = String.Empty;
                string _menuname = String.Empty;
                string _menudetail = String.Empty;
                string _menucreateid = String.Empty;
                DateTime _menucreatetime = DateTime.Today;
                while (reader.Read())
                {
                    _menuid = reader.GetGuid(0).ToString();
                    _menuname= reader.GetString(1);
                    _menudetail= reader.GetString(3);
                    _menucreateid = reader.GetString(4);
                    _menucreatetime = reader.GetDateTime(5);
                    oneItem = new Item(long.Parse(reader.GetValue(11).ToString()),reader.GetString(6), float.Parse(reader.GetValue(7).ToString()), int.Parse(reader.GetValue(8).ToString()), reader.GetString(9), reader.GetString(10));
                    menuitems.Add(oneItem);
                }
                menutype = new MenuType(_menuid, _menuname, _menucreateid, _menudetail, _menucreatetime, menuitems);
                return menutype;
            }
            catch (Exception ex)
            {

                throw new Exception("查询菜单失败" + ex.Message);
            }
            finally
            {
                cmd.Parameters.Clear();
                cmd.Connection.Close();
            }
        }
        #endregion

        #endregion 

        #region 热门菜单


        public IList<MenuType> SelectHotMenu()
        {
            cmd.CommandText = "SELECT * FROM UV_HotMenu";
            try
            {
                if (cmd.Connection.State == System.Data.ConnectionState.Closed)
                    cmd.Connection.Open();
                DbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                List<MenuType> list = new List<MenuType>();
                MenuType menutype = null;
                while (reader.Read())
                {
                    menutype = new MenuType(reader.GetValue(0).ToString());
                    list.Add(menutype);
                }
                return list;
            }
            catch (Exception ex)
            {

                throw new Exception("查询菜单失败" + ex.Message);
            }
            finally
            {
                cmd.Connection.Close();
            }
        }

        #endregion 

        #endregion
    }
}