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

namespace DAL
{
    public class VisaCustomerSchoolService
    {
        List<VisaCustomerSchool> excuteSql(string sql, params SqlParameter[] param)
        {
            return ServiceBase<VisaCustomerSchool>.excuteSql(new VisaCustomerSchool(), "VisaCustomerSchool", sql, CommandType.Text, param);
        }
        VisaCustomerSchool excuteType(string sql, params SqlParameter[] param)
        {
            //查询结果放入对象集合
            List<VisaCustomerSchool> hdList = excuteSql(sql, param);
            //判断集合是否为空
            if (hdList == null || hdList.Count == 0)
                //返回null
                return null;
            //返回单个对象
            return hdList[0];
        }

        //增Add
        public bool Add(VisaCustomerSchool Dov)
        {
            string sql = "insert into VisaCustomerSchool values(@DCid,@School,@Address,@Teacher,@Subject,@StudyStart,@StudyEnd);SELECT @@IDENTITY";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCid",Dov.DCid),
                new SqlParameter("@School",Dov.School),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@Teacher",Dov.Teacher),
                new SqlParameter("@Subject",Dov.Subject),
                new SqlParameter("@StudyStart",Dov.StudyStart),
                new SqlParameter("@StudyEnd",Dov.StudyEnd)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }
        //改Update
        public bool Edit(VisaCustomerSchool Dov)
        {
            string sql = "update VisaCustomerSchool set DCid=@DCid,School=@School,Address=@Address,Teacher=@Teacher,Subject=@Subject,StudyStart=@StudyStart,StudyEnd=@StudyEnd where Id = @Id";

            SqlParameter[] parameter = new SqlParameter[]{
                new SqlParameter("@DCid",Dov.DCid),
                new SqlParameter("@School",Dov.School),
                new SqlParameter("@Address",Dov.Address),
                new SqlParameter("@Teacher",Dov.Teacher),
                new SqlParameter("@Subject",Dov.Subject),
                new SqlParameter("@StudyStart",Dov.StudyStart),
                new SqlParameter("@StudyEnd",Dov.StudyEnd),
                new SqlParameter("@Id",Dov.Id)
            };

            if (SqlHelper.ExecuteNonQuery(sql, CommandType.Text, parameter) > 0)
                return true;
            return false;
        }


        public bool del(int DCID)
        {
            if (SqlHelper.ExecuteNonQuery("delete from VisaCustomerSchool where DCid=@DCId", CommandType.Text, new SqlParameter("@DCId", DCID)) > 0)
                return true;
            return false;

        }

        public List<VisaCustomerSchool> GetAllByDCID(int DCID)
        {
            return excuteSql("select * from VisaCustomerSchool where DCId=@DCId order by Id", new SqlParameter("@DCId", DCID));
        }

        public VisaCustomerSchool GetByID(int id)
        {
            return excuteType("select * from VisaCustomerSchool where Id=@Id", new SqlParameter("@Id", id));
        }
    }
}