dteachplan.cs

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

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

namespace DAL.SqlServer
{
    public class DTeachPlan:IDTeachPlan
	{
	    #region 插入实体操作部份
	    /// <summary>
        /// 插入
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int Insert(SqlCommand cmd, TeachPlan teachPlan)
		{
		    cmd.Parameters.Clear();
			cmd.CommandText = "insert into TeachPlan (Class_ID,Course_ID,Teacher_ID,Begin_Date,Over_Date,TimePlan_ID) values (@Class_ID,@Course_ID,@Teacher_ID,@Begin_Date,@Over_Date,@TimePlan_ID)";
			//从实体中取出值放入Command的参数列表
            cmd.Parameters.Add(new SqlParameter("@Class_ID",teachPlan.ClassID));
            cmd.Parameters.Add(new SqlParameter("@Course_ID",teachPlan.CourseID));
            cmd.Parameters.Add(new SqlParameter("@Teacher_ID",teachPlan.TeacherID));
            cmd.Parameters.Add(new SqlParameter("@Begin_Date",teachPlan.BeginDate));
            cmd.Parameters.Add(new SqlParameter("@Over_Date",teachPlan.OverDate));
            cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",teachPlan.TimePlanID));
            return cmd.ExecuteNonQuery();
		}
	    /// <summary>
        /// 不使用事务的插入方法
        /// </summary>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
	    public int Insert(TeachPlan teachPlan)
		{
		    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, teachPlan);
                }
			}
			return result;
		}
		
		/// <summary>
        /// 使用事务的插入方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Insert(IConnection connection,TeachPlan teachPlan)
        {
			SqlCommand cmd=connection.Command as SqlCommand;
            return Insert(cmd, teachPlan);
        }
		#endregion
		
		#region 删除实体操作
		
		/// <summary>
        /// 删除
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int ExcuteDeleteCommand(SqlCommand cmd, TeachPlan teachPlan)
        {
			cmd.Parameters.Clear();
            cmd.CommandText = "delete TeachPlan where TeachPlan_ID=@TeachPlan_ID";
            //从实体中取出值放入Command的参数列表
		    cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID", teachPlan.TeachPlanID));
            return cmd.ExecuteNonQuery();
			
        }
		/// <summary>
        /// 不使用事务的删除方法
        /// </summary>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(TeachPlan teachPlan)
        {
            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, teachPlan);
                }
            }
            return result;
        }
		
		/// <summary>
        /// 使用事务的删除方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(IConnection connection,TeachPlan teachPlan)
        {
            int result = 0;
			SqlCommand cmd=connection.Command as SqlCommand;
            result =  ExcuteDeleteCommand(cmd, teachPlan);
            return result;
        }
		
		/// <summary>
        /// 根据查询对象构建过滤条件并使用事务的删除方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(IConnection connection,TeachPlanSearcher searcher)
        {
            int result = 0;
			string condition=string.Empty;
			SqlCommand cmd=connection.Command as SqlCommand;
			cmd.Parameters.Clear();
		    if(searcher.TeachPlanIDIsValid)
			{
			    condition += " and TeachPlan_ID=@TeachPlan_ID";
		        cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID",searcher.TeachPlanID));
			}
		    if(searcher.ClassIDIsValid)
			{
			    condition += " and Class_ID=@Class_ID";
		        cmd.Parameters.Add(new SqlParameter("@Class_ID",searcher.ClassID));
			}
		    if(searcher.CourseIDIsValid)
			{
			    condition += " and Course_ID=@Course_ID";
		        cmd.Parameters.Add(new SqlParameter("@Course_ID",searcher.CourseID));
			}
		    if(searcher.TeacherIDIsValid)
			{
			    condition += " and Teacher_ID=@Teacher_ID";
		        cmd.Parameters.Add(new SqlParameter("@Teacher_ID",searcher.TeacherID));
			}
		    if(searcher.BeginDateIsValid)
			{
			    condition += " and Begin_Date=@Begin_Date";
		        cmd.Parameters.Add(new SqlParameter("@Begin_Date",searcher.BeginDate));
			}
		    if(searcher.OverDateIsValid)
			{
			    condition += " and Over_Date=@Over_Date";
		        cmd.Parameters.Add(new SqlParameter("@Over_Date",searcher.OverDate));
			}
		    if(searcher.TimePlanIDIsValid)
			{
			    condition += " and TimePlan_ID=@TimePlan_ID";
		        cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",searcher.TimePlanID));
			}
			if(condition!=string.Empty)
			{
				condition=" where "+condition;
			}
			cmd.CommandText="delete from  TeachPlan "+condition;
            result =  cmd.ExecuteNonQuery();
			
            return result;
        }
		/// <summary>
        /// 根据查询对象构建过滤条件并不使用事务的删除方法
        /// </summary>
        /// <param name="searcher">查询对象</param>
        /// <returns>影响的记录行数</returns>
        public int Delete(TeachPlanSearcher 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.TeachPlanIDIsValid)
			        {
			            condition += " and TeachPlan_ID=@TeachPlan_ID";
		                cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID",searcher.TeachPlanID));
			        }
		            if(searcher.ClassIDIsValid)
			        {
			            condition += " and Class_ID=@Class_ID";
		                cmd.Parameters.Add(new SqlParameter("@Class_ID",searcher.ClassID));
			        }
		            if(searcher.CourseIDIsValid)
			        {
			            condition += " and Course_ID=@Course_ID";
		                cmd.Parameters.Add(new SqlParameter("@Course_ID",searcher.CourseID));
			        }
		            if(searcher.TeacherIDIsValid)
			        {
			            condition += " and Teacher_ID=@Teacher_ID";
		                cmd.Parameters.Add(new SqlParameter("@Teacher_ID",searcher.TeacherID));
			        }
		            if(searcher.BeginDateIsValid)
			        {
			            condition += " and Begin_Date=@Begin_Date";
		                cmd.Parameters.Add(new SqlParameter("@Begin_Date",searcher.BeginDate));
			        }
		            if(searcher.OverDateIsValid)
			        {
			            condition += " and Over_Date=@Over_Date";
		                cmd.Parameters.Add(new SqlParameter("@Over_Date",searcher.OverDate));
			        }
		            if(searcher.TimePlanIDIsValid)
			        {
			            condition += " and TimePlan_ID=@TimePlan_ID";
		                cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",searcher.TimePlanID));
			        }
			        if(condition!=string.Empty)
			        {
				        condition=" where "+condition;
			        }
                    cmd.CommandText="delete from  TeachPlan "+condition;
                    result =  cmd.ExecuteNonQuery();
                }
            }
            return result;
        }
		#endregion
		
		#region 更新实体操作
		
		/// <summary>
        /// 更新
        /// </summary>
		/// <param name="cmd">Command对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
		private int ExcuteUpdateCommand(SqlCommand cmd, TeachPlan teachPlan)
		{
		    cmd.CommandText = "update TeachPlan set Class_ID=@Class_ID,Course_ID=@Course_ID,Teacher_ID=@Teacher_ID,Begin_Date=@Begin_Date,Over_Date=@Over_Date,TimePlan_ID=@TimePlan_ID where TeachPlan_ID=@TeachPlan_ID";
			//从实体中取出值放入Command的参数列表
            cmd.Parameters.Add(new SqlParameter("@Class_ID",teachPlan.ClassID));
            cmd.Parameters.Add(new SqlParameter("@Course_ID",teachPlan.CourseID));
            cmd.Parameters.Add(new SqlParameter("@Teacher_ID",teachPlan.TeacherID));
            cmd.Parameters.Add(new SqlParameter("@Begin_Date",teachPlan.BeginDate));
            cmd.Parameters.Add(new SqlParameter("@Over_Date",teachPlan.OverDate));
            cmd.Parameters.Add(new SqlParameter("@TimePlan_ID",teachPlan.TimePlanID));
			cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID", teachPlan.TeachPlanID));
            return cmd.ExecuteNonQuery();
		}
		
		/// <summary>
        /// 不使用事务的更新方法
        /// </summary>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
	    public int Update(TeachPlan teachPlan)
		{
		    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=ExcuteUpdateCommand(cmd, teachPlan);
                }
			}
			return result;
		}
		/// <summary>
        /// 使用事务的更新方法
        /// </summary>
        /// <param name="connection">实现共享Command的对象</param>
        /// <param name="teachPlan">实体类对象</param>
        /// <returns>影响的记录行数</returns>
        public int Update(IConnection connection,TeachPlan teachPlan)
        {
            int result = 0;
			SqlCommand cmd=connection.Command as SqlCommand;
            result =  ExcuteUpdateCommand(cmd, teachPlan);
            return result;
        }
		/// <summary>
        /// 根据查询对象构建过滤条件并不使用事务的更新方法
        /// </summary>
        /// <param name="searcher">查询对象</param>
		/// <param name="newValues">要更新的新值</param>
        /// <returns>影响的记录行数</returns>
	    public int Update(TeachPlanSearcher searcher,TeachPlanSearcher newValues)
		{
		    int result=0;
			string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
			using(SqlConnection conn=new SqlConnection(connectionString))
			{
				conn.Open();
                using (SqlCommand cmd = conn.CreateCommand())
                {
					//string updateString=GetUpdateString(cmd, newValues)+condition;
					string updateString = string.Empty;
					string condition = string.Empty;
		            if(newValues.TeachPlanIDIsValid)
			        {
			            updateString += ",TeachPlan_ID=@TeachPlan_ID_V";
			            cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID_V",newValues.TeachPlanID));
                    }
		            if(newValues.ClassIDIsValid)
			        {
			            updateString += ",Class_ID=@Class_ID_V";
			            cmd.Parameters.Add(new SqlParameter("@Class_ID_V",newValues.ClassID));
                    }
		            if(newValues.CourseIDIsValid)
			        {
			            updateString += ",Course_ID=@Course_ID_V";
			            cmd.Parameters.Add(new SqlParameter("@Course_ID_V",newValues.CourseID));
                    }
		            if(newValues.TeacherIDIsValid)
			        {
			            updateString += ",Teacher_ID=@Teacher_ID_V";
			            cmd.Parameters.Add(new SqlParameter("@Teacher_ID_V",newValues.TeacherID));
                    }
		            if(newValues.BeginDateIsValid)
			        {
			            updateString += ",Begin_Date=@Begin_Date_V";
			            cmd.Parameters.Add(new SqlParameter("@Begin_Date_V",newValues.BeginDate));
                    }
		            if(newValues.OverDateIsValid)
			        {
			            updateString += ",Over_Date=@Over_Date_V";
			            cmd.Parameters.Add(new SqlParameter("@Over_Date_V",newValues.OverDate));
                    }
		            if(newValues.TimePlanIDIsValid)
			        {
			            updateString += ",TimePlan_ID=@TimePlan_ID_V";
			            cmd.Parameters.Add(new SqlParameter("@TimePlan_ID_V",newValues.TimePlanID));
                    }
		            if(searcher.TeachPlanIDIsValid)
			        {
			            condition += " and TeachPlan_ID=@TeachPlan_ID_C";
			            cmd.Parameters.Add(new SqlParameter("@TeachPlan_ID_C",searcher.TeachPlanID));
					}
		            if(searcher.ClassIDIsValid)
			        {
			            condition += " and Class_ID=@Class_ID_C";
			            cmd.Parameters.Add(new SqlParameter("@Class_ID_C",searcher.ClassID));
					}
		            if(searcher.CourseIDIsValid)
			        {
			            condition += " and Course_ID=@Course_ID_C";
			            cmd.Parameters.Add(new SqlParameter("@Course_ID_C",searcher.CourseID));
					}
		            if(searcher.TeacherIDIsValid)
			        {
			            condition += " and Teacher_ID=@Teacher_ID_C";
			            cmd.Parameters.Add(new SqlParameter("@Teacher_ID_C",searcher.TeacherID));
					}
		            if(searcher.BeginDateIsValid)
			        {
			            condition += " and Begin_Date=@Begin_Date_C";
			            cmd.Parameters.Add(new SqlParameter("@Begin_Date_C",searcher.BeginDate));
					}
		            if(searcher.OverDateIsValid)
			        {
			            condition += " and Over_Date=@Over_Date_C";
			            cmd.Parameters.Add(new SqlParameter("@Over_Date_C",searcher.OverDate));
					}
		            if(searcher.TimePlanIDIsValid)
			        {
			            condition += " and TimePlan_ID=@TimePlan_ID_C";
			            cmd.Parameters.Add(new SqlParameter("@TimePlan_ID_C",searcher.TimePlanID));