dtimeplan.cs

这是一个自动排课软件(包含源码,需求分析,详细设计).希望对你有所帮助.

using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections;
using Entities;
using Search;

namespace DAL.SqlServer
{
    public class DTimePlan:IDTimePlan
	{
	    #region 插入实体操作部份
	    /// <summary>
        /// 插入
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int Insert(SqlCommand cmd, TimePlan timePlan)
		{
		    cmd.Parameters.Clear();
			cmd.CommandText = "insert into TimePlan (TimePlan_Name,Sunday_Am,Sunday_Pm,Monday_Am,Monday_Pm,Tuesday_Am,Tuesday_Pm,Wednesday_Am,Wednesday_Pm,Thursday_Am,Thursday_Pm,Friday_Am,Friday_Pm,Saturday_Am,Saturday_Pm) values (@TimePlan_Name,@Sunday_Am,@Sunday_Pm,@Monday_Am,@Monday_Pm,@Tuesday_Am,@Tuesday_Pm,@Wednesday_Am,@Wednesday_Pm,@Thursday_Am,@Thursday_Pm,@Friday_Am,@Friday_Pm,@Saturday_Am,@Saturday_Pm)";
			//从实体中取出值放入Command的参数列表
            cmd.Parameters.Add(new SqlParameter("@TimePlan_Name",timePlan.TimePlanName));
            cmd.Parameters.Add(new SqlParameter("@Sunday_Am",timePlan.SundayAm));
            cmd.Parameters.Add(new SqlParameter("@Sunday_Pm",timePlan.SundayPm));
            cmd.Parameters.Add(new SqlParameter("@Monday_Am",timePlan.MondayAm));
            cmd.Parameters.Add(new SqlParameter("@Monday_Pm",timePlan.MondayPm));
            cmd.Parameters.Add(new SqlParameter("@Tuesday_Am",timePlan.TuesdayAm));
            cmd.Parameters.Add(new SqlParameter("@Tuesday_Pm",timePlan.TuesdayPm));
            cmd.Parameters.Add(new SqlParameter("@Wednesday_Am",timePlan.WednesdayAm));
            cmd.Parameters.Add(new SqlParameter("@Wednesday_Pm",timePlan.WednesdayPm));
            cmd.Parameters.Add(new SqlParameter("@Thursday_Am",timePlan.ThursdayAm));
            cmd.Parameters.Add(new SqlParameter("@Thursday_Pm",timePlan.ThursdayPm));
            cmd.Parameters.Add(new SqlParameter("@Friday_Am",timePlan.FridayAm));
            cmd.Parameters.Add(new SqlParameter("@Friday_Pm",timePlan.FridayPm));
            cmd.Parameters.Add(new SqlParameter("@Saturday_Am",timePlan.SaturdayAm));
            cmd.Parameters.Add(new SqlParameter("@Saturday_Pm",timePlan.SaturdayPm));
            return cmd.ExecuteNonQuery();
		}
	    /// <summary>
        /// 不使用事务的插入方法
        /// </summary>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
	    public int Insert(TimePlan timePlan)
		{
		    int result=0;
			string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
		    using(SqlConnection conn=new SqlConnection(connectionString))
			{
				conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
                    result=Insert(cmd, timePlan);
                }
			}
			return result;
		}
		
		/// <summary>
        /// 使用事务的插入方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Insert(IConnection connection,TimePlan timePlan)
        {
			SqlCommand cmd=connection.Command as SqlCommand;
            return Insert(cmd, timePlan);
        }
		#endregion
		
		#region 删除实体操作
		
		/// <summary>
        /// 删除
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int ExcuteDeleteCommand(SqlCommand cmd, TimePlan timePlan)
        {
			cmd.Parameters.Clear();
            cmd.CommandText = "delete TimePlan where TimePlan_ID=@TimePlan_ID";
            //从实体中取出值放入Command的参数列表
		    cmd.Parameters.Add(new SqlParameter("@TimePlan_ID", timePlan.TimePlanID));
            return cmd.ExecuteNonQuery();
			
        }
		/// <summary>
        /// 不使用事务的删除方法
        /// </summary>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(TimePlan timePlan)
        {
            int result = 0;
			string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
				
                    result=ExcuteDeleteCommand(cmd, timePlan);
                }
            }
            return result;
        }
		
		/// <summary>
        /// 使用事务的删除方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(IConnection connection,TimePlan timePlan)
        {
            int result = 0;
			SqlCommand cmd=connection.Command as SqlCommand;
            result =  ExcuteDeleteCommand(cmd, timePlan);
            return result;
        }
		
		/// <summary>
        /// 根据查询对象构建过滤条件并使用事务的删除方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(IConnection connection,TimePlanSearcher searcher)
        {
            int result = 0;
			string condition=string.Empty;
			SqlCommand cmd=connection.Command as SqlCommand;
			cmd.Parameters.Clear();
		    if(searcher.TimePlanIDIsValid)
			{
			    condition += " and TimePlan_ID=@TimePlan_ID";
		        cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",searcher.TimePlanID));
			}
		    if(searcher.TimePlanNameIsValid)
			{
			    condition += " and TimePlan_Name=@TimePlan_Name";
		        cmd.Parameters.Add(new SqlParameter("@TimePlan_Name",searcher.TimePlanName));
			}
		    if(searcher.SundayAmIsValid)
			{
			    condition += " and Sunday_Am=@Sunday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Sunday_Am",searcher.SundayAm));
			}
		    if(searcher.SundayPmIsValid)
			{
			    condition += " and Sunday_Pm=@Sunday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Sunday_Pm",searcher.SundayPm));
			}
		    if(searcher.MondayAmIsValid)
			{
			    condition += " and Monday_Am=@Monday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Monday_Am",searcher.MondayAm));
			}
		    if(searcher.MondayPmIsValid)
			{
			    condition += " and Monday_Pm=@Monday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Monday_Pm",searcher.MondayPm));
			}
		    if(searcher.TuesdayAmIsValid)
			{
			    condition += " and Tuesday_Am=@Tuesday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Tuesday_Am",searcher.TuesdayAm));
			}
		    if(searcher.TuesdayPmIsValid)
			{
			    condition += " and Tuesday_Pm=@Tuesday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Tuesday_Pm",searcher.TuesdayPm));
			}
		    if(searcher.WednesdayAmIsValid)
			{
			    condition += " and Wednesday_Am=@Wednesday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Wednesday_Am",searcher.WednesdayAm));
			}
		    if(searcher.WednesdayPmIsValid)
			{
			    condition += " and Wednesday_Pm=@Wednesday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Wednesday_Pm",searcher.WednesdayPm));
			}
		    if(searcher.ThursdayAmIsValid)
			{
			    condition += " and Thursday_Am=@Thursday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Thursday_Am",searcher.ThursdayAm));
			}
		    if(searcher.ThursdayPmIsValid)
			{
			    condition += " and Thursday_Pm=@Thursday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Thursday_Pm",searcher.ThursdayPm));
			}
		    if(searcher.FridayAmIsValid)
			{
			    condition += " and Friday_Am=@Friday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Friday_Am",searcher.FridayAm));
			}
		    if(searcher.FridayPmIsValid)
			{
			    condition += " and Friday_Pm=@Friday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Friday_Pm",searcher.FridayPm));
			}
		    if(searcher.SaturdayAmIsValid)
			{
			    condition += " and Saturday_Am=@Saturday_Am";
		        cmd.Parameters.Add(new SqlParameter("@Saturday_Am",searcher.SaturdayAm));
			}
		    if(searcher.SaturdayPmIsValid)
			{
			    condition += " and Saturday_Pm=@Saturday_Pm";
		        cmd.Parameters.Add(new SqlParameter("@Saturday_Pm",searcher.SaturdayPm));
			}
			if(condition!=string.Empty)
			{
				condition=" where "+condition;
			}
			cmd.CommandText="delete from  TimePlan "+condition;
            result =  cmd.ExecuteNonQuery();
			
            return result;
        }
		/// <summary>
        /// 根据查询对象构建过滤条件并不使用事务的删除方法
        /// </summary>
        /// <param name="searcher">查询对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(TimePlanSearcher searcher)
        {
            int result = 0;
			string condition=string.Empty;
			string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
		            if(searcher.TimePlanIDIsValid)
			        {
			            condition += " and TimePlan_ID=@TimePlan_ID";
		                cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",searcher.TimePlanID));
			        }
		            if(searcher.TimePlanNameIsValid)
			        {
			            condition += " and TimePlan_Name=@TimePlan_Name";
		                cmd.Parameters.Add(new SqlParameter("@TimePlan_Name",searcher.TimePlanName));
			        }
		            if(searcher.SundayAmIsValid)
			        {
			            condition += " and Sunday_Am=@Sunday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Sunday_Am",searcher.SundayAm));
			        }
		            if(searcher.SundayPmIsValid)
			        {
			            condition += " and Sunday_Pm=@Sunday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Sunday_Pm",searcher.SundayPm));
			        }
		            if(searcher.MondayAmIsValid)
			        {
			            condition += " and Monday_Am=@Monday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Monday_Am",searcher.MondayAm));
			        }
		            if(searcher.MondayPmIsValid)
			        {
			            condition += " and Monday_Pm=@Monday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Monday_Pm",searcher.MondayPm));
			        }
		            if(searcher.TuesdayAmIsValid)
			        {
			            condition += " and Tuesday_Am=@Tuesday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Tuesday_Am",searcher.TuesdayAm));
			        }
		            if(searcher.TuesdayPmIsValid)
			        {
			            condition += " and Tuesday_Pm=@Tuesday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Tuesday_Pm",searcher.TuesdayPm));
			        }
		            if(searcher.WednesdayAmIsValid)
			        {
			            condition += " and Wednesday_Am=@Wednesday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Wednesday_Am",searcher.WednesdayAm));
			        }
		            if(searcher.WednesdayPmIsValid)
			        {
			            condition += " and Wednesday_Pm=@Wednesday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Wednesday_Pm",searcher.WednesdayPm));
			        }
		            if(searcher.ThursdayAmIsValid)
			        {
			            condition += " and Thursday_Am=@Thursday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Thursday_Am",searcher.ThursdayAm));
			        }
		            if(searcher.ThursdayPmIsValid)
			        {
			            condition += " and Thursday_Pm=@Thursday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Thursday_Pm",searcher.ThursdayPm));
			        }
		            if(searcher.FridayAmIsValid)
			        {
			            condition += " and Friday_Am=@Friday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Friday_Am",searcher.FridayAm));
			        }
		            if(searcher.FridayPmIsValid)
			        {
			            condition += " and Friday_Pm=@Friday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Friday_Pm",searcher.FridayPm));
			        }
		            if(searcher.SaturdayAmIsValid)
			        {
			            condition += " and Saturday_Am=@Saturday_Am";
		                cmd.Parameters.Add(new SqlParameter("@Saturday_Am",searcher.SaturdayAm));
			        }
		            if(searcher.SaturdayPmIsValid)
			        {
			            condition += " and Saturday_Pm=@Saturday_Pm";
		                cmd.Parameters.Add(new SqlParameter("@Saturday_Pm",searcher.SaturdayPm));
			        }
			        if(condition!=string.Empty)
			        {
                        condition = condition.Substring(5);
				        condition=" where "+condition;
			        }
                    cmd.CommandText="delete from  TimePlan "+condition;
                    result =  cmd.ExecuteNonQuery();
                }
            }
            return result;
        }
		#endregion
		
		#region 更新实体操作
		
		/// <summary>
        /// 更新
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="timePlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int ExcuteUpdateCommand(SqlCommand cmd, TimePlan timePlan)
		{
		    cmd.CommandText = "update TimePlan set TimePlan_Name=@TimePlan_Name,Sunday_Am=@Sunday_Am,Sunday_Pm=@Sunday_Pm,Monday_Am=@Monday_Am,Monday_Pm=@Monday_Pm,Tuesday_Am=@Tuesday_Am,Tuesday_Pm=@Tuesday_Pm,Wednesday_Am=@Wednesday_Am,Wednesday_Pm=@Wednesday_Pm,Thursday_Am=@Thursday_Am,Thursday_Pm=@Thursday_Pm,Friday_Am=@Friday_Am,Friday_Pm=@Friday_Pm,Saturday_Am=@Saturday_Am,Saturday_Pm=@Saturday_Pm where TimePlan_ID=@TimePlan_ID";
			//从实体中取出值放入Command的参数列表
            cmd.Parameters.Add(new SqlParameter("@TimePlan_Name",timePlan.TimePlanName));
            cmd.Parameters.Add(new SqlParameter("@Sunday_Am",timePlan.SundayAm));
            cmd.Parameters.Add(new SqlParameter("@Sunday_Pm",timePlan.SundayPm));
            cmd.Parameters.Add(new SqlParameter("@Monday_Am",timePlan.MondayAm));
            cmd.Parameters.Add(new SqlParameter("@Monday_Pm",timePlan.MondayPm));
            cmd.Parameters.Add(new SqlParameter("@Tuesday_Am",timePlan.TuesdayAm));
            cmd.Parameters.Add(new SqlParameter("@Tuesday_Pm",timePlan.TuesdayPm));
            cmd.Parameters.Add(new SqlParameter("@Wednesday_Am",timePlan.WednesdayAm));
            cmd.Parameters.Add(new SqlParameter("@Wednesday_Pm",timePlan.WednesdayPm));
            cmd.Parameters.Add(new SqlParameter("@Thursday_Am",timePlan.ThursdayAm));
            cmd.Parameters.Add(new SqlParameter("@Thursday_Pm",timePlan.ThursdayPm));
            cmd.Parameters.Add(new SqlParameter("@Friday_Am",timePlan.FridayAm));
            cmd.Parameters.Add(new SqlParameter("@Friday_Pm",timePlan.FridayPm));
            cmd.Parameters.Add(new SqlParameter("@Saturday_Am",timePlan.SaturdayAm));