using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using Common;

namespace DA
{
   public  class DBDestines:IDisposable
   {
       string sql;
       SqlDataReader dr;
       SqlHelpers sqlh;
       public DBDestines()
       {
           sqlh = new SqlHelpers();
       }
       public DBDestines(SqlHelpers sh)
       {
           sqlh = sh;
       }
       public void Dispose()
           {
               sqlh.Dispose();
       }
       /// <summary>
       ///DB  查询所有预定信息的方法
       /// </summary>
       /// <returns></returns>
       public List<CDestines> DBDestinesQuery()
       {
           List<CDestines> dd = new List<CDestines>();
           sql = "select * from Destines";
           dr = sqlh.RQuery(sql ,null ,CommandType.Text  );
           while (dr.Read())
           {
               CDestines d = new CDestines();
               d.CdId1 = Convert.ToInt32(dr["did"]);
               d.CdCName1 = dr["dCName"].ToString();
               d.CdCLinkphone1 = dr["dCLinkphone"].ToString();
               d.CrtName1  = dr["rtName"].ToString();
               d.CtName1 = dr["tName"].ToString();
               d.CdStartTime1 = Convert.ToDateTime(dr["dStartTime"]);
               d.CdRemark1 = dr["dRemark"].ToString();
               dd.Add(d);
               
           }
           dr.Close();
           return dd;
       }
       /// <summary>
       /// DB  插入预订信息的方法
       /// </summary>
       /// <param name="cd"></param>
       public void DBDestinesInsert(CDestines cd)
       {
           List<SqlParameter > pars = new List<SqlParameter >();
           pars.Add(new SqlParameter("@dCName", SqlDbType.VarChar, 20));
           pars[0].Value = cd.CdCName1;
           pars.Add(new SqlParameter("@dCLinkphone", SqlDbType.VarChar, 30));
           pars[1].Value = cd.CdCLinkphone1;
           pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 20));
           pars[2].Value = cd.CrtName1;
           pars.Add(new SqlParameter("@tName", SqlDbType.VarChar, 20));
           pars[3].Value = cd.CtName1;
           pars.Add(new SqlParameter("@dStartTime", SqlDbType.DateTime ));
           pars[4].Value = cd.CdStartTime1;
           pars .Add (new SqlParameter ("@dRemark",SqlDbType .VarChar, 50));
           pars [5].Value =cd.CdRemark1 ;
           sql="insert into Destines values(@dCName,@dCLinkphone,@rtName,@tName,@dStartTime,@dRemark)";
           sqlh.NonQuery(sql ,pars, CommandType.Text  );
       }
       /// <summary>
       /// 删除预定信息的方法
       /// </summary>
       /// <param name="tName">餐台名称</param>
       public void DBDestinesDelete(string tName)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter ("@tName",SqlDbType .VarChar ,20));
           pars[0].Value = tName;
           sql = "delete from destines where tName=@tName";
           sqlh.ExcuteInsertUpdateDelete(sql, pars);
       
       }
       /// <summary>
       /// 修改预定信息的方法
       /// </summary>
       /// <param name="d"></param>
       /// <param name="did"></param>
       public void DBDestinesUpdate(CDestines d,int did)
       {
           List<SqlParameter> pars = new List<SqlParameter>();
          pars.Add(new SqlParameter("@dCName", SqlDbType.VarChar, 20));
           pars[0].Value = d.CdCName1;
           pars.Add(new SqlParameter("@dCLinkphone", SqlDbType.VarChar, 20));
           pars[1].Value = d.CdCLinkphone1;
           pars.Add(new SqlParameter("@rtName", SqlDbType.VarChar, 20));
           pars[2].Value = d.CrtName1;
           pars.Add(new SqlParameter("@tName", SqlDbType.VarChar, 20));
           pars[3].Value = d.CtName1;
           pars.Add(new SqlParameter("@dStartTime", SqlDbType.DateTime ));
           pars[4].Value = d.CdStartTime1;
           pars .Add (new SqlParameter ("@dRemark",SqlDbType .VarChar, 50));
           pars [5].Value =d.CdRemark1 ;
           pars.Add(new SqlParameter("@did", SqlDbType.Int));
           pars [6].Value =did;
           sql="update Destines set dcName=@dCName,dCLinkphone=@dCLinkphone,rtName=@rtName,tName=@tName,dStartTime=@dStartTime,dRemark=@dRemark where did=@did";
           sqlh.ExcuteInsertUpdateDelete (sql,pars );
       }
       /// <summary>
       /// 根据宾客姓名查询预定信息的方法
       /// </summary>
       /// <param name="cName">姓名</param>
       /// <returns></returns>
       public List<CDestines> DBDestinesNameQuery(string cName)
       {
           List<CDestines> dd = new List<CDestines>();
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@dcname", SqlDbType.VarChar, 20));
           pars[0].Value = cName;
           sql = "select * from Destines where dCName=@dcname";
           dr = sqlh.RQuery(sql,pars ,CommandType.Text);
           while (dr.Read())
           {
               CDestines d = new CDestines();
               d.CdId1  = Convert.ToInt32(dr["dId"]);
               d.CdCName1  = dr["dCName"].ToString();
               d.CdCLinkphone1  = dr["dCLinkphone"].ToString();
               d.CrtName1  = dr["rtName"].ToString();
               d.CtName1  = dr["tName"].ToString();
               d.CdStartTime1  = Convert.ToDateTime(dr["dStartTime"]);
               d.CdRemark1  = dr["dRemark"].ToString();
               dd.Add(d);
           }
           dr.Close();
           return dd;
       }
       /// <summary>
       /// 根据餐台编号查询预定信息的方法
       /// </summary>
       /// <param name="tName">餐台编号</param>
       /// <returns></returns>
       public List<CDestines> DBDestinesIDQuery(string tName)
       {
           List<CDestines> dd = new List<CDestines>();
           List<SqlParameter> pars = new List<SqlParameter>();
           pars.Add(new SqlParameter("@tname", SqlDbType.VarChar, 20));
           pars[0].Value = tName;
           sql = "select * from Destines where tName=@tname";
           dr = sqlh.RQuery(sql, pars, CommandType.Text);
           while (dr.Read())
           {
               CDestines d = new CDestines();
               d.CdId1 = Convert.ToInt32(dr["dId"]);
               d.CdCName1 = dr["dCName"].ToString();
               d.CdCLinkphone1 = dr["dCLinkphone"].ToString();
               d.CrtName1 = dr["rtName"].ToString();
               d.CtName1 = dr["tName"].ToString();
               d.CdStartTime1 = Convert.ToDateTime(dr["dStartTime"]);
               d.CdRemark1 = dr["dRemark"].ToString();
               dd.Add(d);
           }
           dr.Close();
           return dd;
       }
   }
}