📄 dteachplan.cs
字号:
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update TeachPlan set "+updateString.Substring(1)+condition;
result=cmd.ExecuteNonQuery();
}
}
return result;
}
/// <summary>
/// 根据查询对象构建过滤条件并使用事务的更新方法
/// </summary>
/// <param name="connection">实现共享Command的对象</param>
/// <param name="searcher">查询对象</param>
/// <param name="newValues">要更新的新值</param>
/// <returns>影响的记录行数</returns>
public int Update(IConnection connection,TeachPlanSearcher searcher,TeachPlanSearcher newValues)
{
string updateString = string.Empty;
string condition = string.Empty;
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
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));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update TeachPlan set "+updateString.Substring(1)+condition;
return cmd.ExecuteNonQuery();
}
#endregion
#region 查询实体集合
/// <summary>
/// 根据查询对象构建过滤条件查询
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(TeachPlanSearcher searcher)
{
ArrayList entities = new ArrayList();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
entities = Select(cmd,searcher);
}
}
return entities;
}
/// <summary>
/// 使用事务并且依据查询对象构建过滤条件查询
/// </summary>
/// <param name="connection">实现共享Command的对象</param>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(IConnection connection, TeachPlanSearcher searcher)
{
SqlCommand cmd=connection.Command as SqlCommand;
return Select(cmd,searcher);
}
public ArrayList Select(SqlCommand command,TeachPlanSearcher searcher)
{
string condition=string.Empty;
command.Parameters.Clear();
ArrayList entities = new ArrayList();
if(searcher.TeachPlanIDIsValid)
{
condition += " and TeachPlan_ID=@TeachPlan_ID_C";
command.Parameters.Add(new SqlParameter("@TeachPlan_ID_C",searcher.TeachPlanID));
}
if(searcher.ClassIDIsValid)
{
condition += " and Class_ID=@Class_ID_C";
command.Parameters.Add(new SqlParameter("@Class_ID_C",searcher.ClassID));
}
if(searcher.CourseIDIsValid)
{
condition += " and Course_ID=@Course_ID_C";
command.Parameters.Add(new SqlParameter("@Course_ID_C",searcher.CourseID));
}
if(searcher.TeacherIDIsValid)
{
condition += " and Teacher_ID=@Teacher_ID_C";
command.Parameters.Add(new SqlParameter("@Teacher_ID_C",searcher.TeacherID));
}
if(searcher.BeginDateIsValid)
{
condition += " and Begin_Date=@Begin_Date_C";
command.Parameters.Add(new SqlParameter("@Begin_Date_C",searcher.BeginDate));
}
if(searcher.OverDateIsValid)
{
condition += " and Over_Date=@Over_Date_C";
command.Parameters.Add(new SqlParameter("@Over_Date_C",searcher.OverDate));
}
if(searcher.TimePlanIDIsValid)
{
condition += " and TimePlan_ID=@TimePlan_ID_C";
command.Parameters.Add(new SqlParameter("@TimePlan_ID_C",searcher.TimePlanID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
command.CommandText = "select * from TeachPlan"+condition;
return ExcuteSelectCommand(command);
}
/// <summary>
/// 执行Command获取对象列表
/// </summary>
/// <param name="cmd">Command对象</param>
/// <returns>实体类对象列表</returns>
private ArrayList ExcuteSelectCommand(SqlCommand cmd)
{
ArrayList entities = new ArrayList();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
TeachPlan entity = DataReaderToEntity(dr);
entities.Add(entity);
}
}
SetForeignKeyEntity(cmd, ref entities);
return entities;
}
private void SetForeignKeyEntity(SqlCommand command, ref ArrayList entities)
{
//由外键获取相关实体
DCourse dCourse=new DCourse();
DClass dClass=new DClass();
DTimePlan dTimePlan=new DTimePlan();
DEmployee dEmployee=new DEmployee();
foreach(TeachPlan entity in entities)
{
entity.Course = dCourse.SelectSingle(command,entity.CourseID);
entity.Class = dClass.SelectSingle(command,entity.ClassID);
entity.TimePlan = dTimePlan.SelectSingle(command,entity.TimePlanID);
entity.Employee = dEmployee.SelectSingle(command,entity.TeacherID);
}
}
/// <summary>
/// 查询所有实体
/// </summary>
/// <returns>实体类对象列表</returns>
public ArrayList Select()
{
ArrayList entities = new ArrayList();
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = "select * from TeachPlan";
entities=ExcuteSelectCommand(cmd);
}
return entities;
}
}
/// <summary>
/// 使用事务查询查询所有实体
/// </summary>
/// <param name="connection">实现共享Command的对象</param>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(IConnection connection)
{
ArrayList entities = new ArrayList();
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
cmd.CommandText = "select * from TeachPlan";
return ExcuteSelectCommand(cmd);
}
#endregion
#region 查询单个实体
/// <summary>
/// 按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public TeachPlan SelectSingle(object pkValue)
{
TeachPlan entity=null;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using(SqlConnection conn=new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
entity=SelectSingle(cmd,pkValue);
}
}
return entity;
}
/// <summary>
/// 使用事务并按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public TeachPlan SelectSingle(IConnection connection,object pkValue)
{
SqlCommand cmd=connection.Command as SqlCommand;
return SelectSingle(cmd,pkValue);
}
public TeachPlan SelectSingle(SqlCommand command,object pkValue)
{
TeachPlan entity=null;
command.Parameters.Clear();
command.CommandText = "select * from TeachPlan where TeachPlan_ID=@pk";
command.Parameters.Add(new SqlParameter("@pk",pkValue));
using (SqlDataReader dr = command.ExecuteReader())
{
if(dr.Read())
entity = DataReaderToEntity(dr);
}
//由外键获取相关实体
DCourse dCourse=new DCourse();
DClass dClass=new DClass();
DTimePlan dTimePlan=new DTimePlan();
DEmployee dEmployee=new DEmployee();
entity.Course = dCourse.SelectSingle(command,entity.CourseID);
entity.Class = dClass.SelectSingle(command,entity.ClassID);
entity.TimePlan = dTimePlan.SelectSingle(command,entity.TimePlanID);
entity.Employee = dEmployee.SelectSingle(command,entity.TeacherID);
return entity;
}
#endregion
/// <summary>
/// 从DataReader中取出值生成实体对象
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>过滤条件字符串</returns>
private TeachPlan DataReaderToEntity(SqlDataReader dr)
{
TeachPlan entity = new TeachPlan ();
if(dr["TeachPlan_ID"]!=System.DBNull.Value)
{
entity.TeachPlanID=Convert.ToInt32(dr["TeachPlan_ID"]);
}
if(dr["Class_ID"]!=System.DBNull.Value)
{
entity.ClassID=Convert.ToInt32(dr["Class_ID"]);
}
if(dr["Course_ID"]!=System.DBNull.Value)
{
entity.CourseID=Convert.ToInt32(dr["Course_ID"]);
}
if(dr["Teacher_ID"]!=System.DBNull.Value)
{
entity.TeacherID=Convert.ToInt32(dr["Teacher_ID"]);
}
if(dr["Begin_Date"]!=System.DBNull.Value)
{
entity.BeginDate=Convert.ToDateTime(dr["Begin_Date"]);
}
if(dr["Over_Date"]!=System.DBNull.Value)
{
entity.OverDate=Convert.ToDateTime(dr["Over_Date"]);
}
if(dr["TimePlan_ID"]!=System.DBNull.Value)
{
entity.TimePlanID=Convert.ToInt32(dr["TimePlan_ID"]);
}
return entity;
}
public ArrayList GetClassTechPlan(int clsID, int year, int month)
{
DateTime currentDate = DateTime.Parse(year + "-" + month + "-1");
string strCurrent = currentDate.ToShortDateString();
string strNext = currentDate.AddMonths(1).ToShortDateString();
string sql = "select * from teachplan where class_id=" + clsID + " and (" +
"(begin_date<'" + strCurrent + "' and over_date>='" + strCurrent + "') or " +
"(begin_date<'" + strNext + "' and over_date>='" + strNext + "') or " +
"(begin_date>='" + strCurrent + "' and over_date<'" + strNext + "')) " +
"order by begin_date";
ArrayList entities = null;
string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand cmd = new SqlCommand(sql, conn))
{
entities = ExcuteSelectCommand(cmd);
}
}
return entities;
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -