📄 dcourse.cs
字号:
}
if(searcher.CourseColorIsValid)
{
condition += " and Course_Color=@Course_Color_C";
cmd.Parameters.Add(new SqlParameter("@Course_Color_C",searcher.CourseColor));
}
if(searcher.TimeSpanIsValid)
{
condition += " and Time_Span=@Time_Span_C";
cmd.Parameters.Add(new SqlParameter("@Time_Span_C",searcher.TimeSpan));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
cmd.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Course 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,CourseSearcher searcher,CourseSearcher newValues)
{
string updateString = string.Empty;
string condition = string.Empty;
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
if(newValues.CourseIDIsValid)
{
updateString += ",course_id=@course_id_V";
cmd.Parameters.Add(new SqlParameter("@course_id_V", newValues.CourseID));
}
if(newValues.CourseNameIsValid)
{
updateString += ",Course_Name=@Course_Name_V";
cmd.Parameters.Add(new SqlParameter("@Course_Name_V",newValues.CourseName));
}
if(newValues.CourseColorIsValid)
{
updateString += ",Course_Color=@Course_Color_V";
cmd.Parameters.Add(new SqlParameter("@Course_Color_V",newValues.CourseColor));
}
if(newValues.TimeSpanIsValid)
{
updateString += ",Time_Span=@Time_Span_V";
cmd.Parameters.Add(new SqlParameter("@Time_Span_V",newValues.TimeSpan));
}
if(newValues.TermIDIsValid)
{
updateString += ",Term_ID=@Term_ID_V";
cmd.Parameters.Add(new SqlParameter("@Term_ID_V",newValues.TermID));
}
if(searcher.CourseIDIsValid)
{
condition += " and course_id=@course_id_C";
cmd.Parameters.Add(new SqlParameter("@course_id_C", searcher.CourseID));
}
if(searcher.CourseNameIsValid)
{
condition += " and Course_Name=@Course_Name_C";
cmd.Parameters.Add(new SqlParameter("@Course_Name_C",searcher.CourseName));
}
if(searcher.CourseColorIsValid)
{
condition += " and Course_Color=@Course_Color_C";
cmd.Parameters.Add(new SqlParameter("@Course_Color_C",searcher.CourseColor));
}
if(searcher.TimeSpanIsValid)
{
condition += " and Time_Span=@Time_Span_C";
cmd.Parameters.Add(new SqlParameter("@Time_Span_C",searcher.TimeSpan));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
cmd.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Course set "+updateString.Substring(1)+condition;
return cmd.ExecuteNonQuery();
}
#endregion
#region 查询实体集合
/// <summary>
/// 根据查询对象构建过滤条件查询
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(CourseSearcher 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, CourseSearcher searcher)
{
SqlCommand cmd=connection.Command as SqlCommand;
return Select(cmd,searcher);
}
public ArrayList Select(SqlCommand command,CourseSearcher searcher)
{
string condition=string.Empty;
command.Parameters.Clear();
ArrayList entities = new ArrayList();
if(searcher.CourseIDIsValid)
{
condition += " and course_id=@course_id_C";
command.Parameters.Add(new SqlParameter("@course_id_C", searcher.CourseID));
}
if(searcher.CourseNameIsValid)
{
condition += " and Course_Name=@Course_Name_C";
command.Parameters.Add(new SqlParameter("@Course_Name_C",searcher.CourseName));
}
if(searcher.CourseColorIsValid)
{
condition += " and Course_Color=@Course_Color_C";
command.Parameters.Add(new SqlParameter("@Course_Color_C",searcher.CourseColor));
}
if(searcher.TimeSpanIsValid)
{
condition += " and Time_Span=@Time_Span_C";
command.Parameters.Add(new SqlParameter("@Time_Span_C",searcher.TimeSpan));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
command.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
command.CommandText = "select * from Course"+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())
{
Course entity = DataReaderToEntity(dr);
entities.Add(entity);
}
}
SetForeignKeyEntity(cmd, ref entities);
return entities;
}
private void SetForeignKeyEntity(SqlCommand command, ref ArrayList entities)
{
//由外键获取相关实体
DTerm dTerm=new DTerm();
foreach(Course entity in entities)
{
entity.Term = dTerm.SelectSingle(command,entity.TermID);
}
}
/// <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 Course";
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 Course";
return ExcuteSelectCommand(cmd);
}
#endregion
#region 查询单个实体
/// <summary>
/// 按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public Course SelectSingle(object pkValue)
{
Course 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 Course SelectSingle(IConnection connection,object pkValue)
{
SqlCommand cmd=connection.Command as SqlCommand;
return SelectSingle(cmd,pkValue);
}
public Course SelectSingle(SqlCommand command,object pkValue)
{
Course entity=null;
command.Parameters.Clear();
command.CommandText = "select * from Course where course_id=@pk";
command.Parameters.Add(new SqlParameter("@pk",pkValue));
using (SqlDataReader dr = command.ExecuteReader())
{
if(dr.Read())
entity = DataReaderToEntity(dr);
}
//由外键获取相关实体
DTerm dTerm=new DTerm();
entity.Term = dTerm.SelectSingle(command,entity.TermID);
return entity;
}
#endregion
/// <summary>
/// 从DataReader中取出值生成实体对象
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>过滤条件字符串</returns>
private Course DataReaderToEntity(SqlDataReader dr)
{
Course entity = new Course ();
if(dr["course_id"]!=System.DBNull.Value)
{
entity.CourseID = Convert.ToInt32(dr["course_id"]);
}
if(dr["Course_Name"]!=System.DBNull.Value)
{
entity.CourseName=dr["Course_Name"].ToString();
}
if(dr["Course_Color"]!=System.DBNull.Value)
{
entity.CourseColor=dr["Course_Color"].ToString();
}
if(dr["Time_Span"]!=System.DBNull.Value)
{
entity.TimeSpan=Convert.ToInt32(dr["Time_Span"]);
}
if(dr["Term_ID"]!=System.DBNull.Value)
{
entity.TermID=Convert.ToInt32(dr["Term_ID"]);
}
return entity;
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -