📄 dclass.cs
字号:
condition += " and Status_ID=@Status_ID_C";
cmd.Parameters.Add(new SqlParameter("@Status_ID_C",searcher.StatusID));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
cmd.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(searcher.ChargeIdIsValid)
{
condition += " and charge_id=@charge_id_C";
cmd.Parameters.Add(new SqlParameter("@charge_id_C",searcher.ChargeId));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Class 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,ClassSearcher searcher,ClassSearcher newValues)
{
string updateString = string.Empty;
string condition = string.Empty;
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
if(newValues.ClassIDIsValid)
{
updateString += ",Class_ID=@Class_ID_V";
cmd.Parameters.Add(new SqlParameter("@Class_ID_V",newValues.ClassID));
}
if(newValues.ClassNameIsValid)
{
updateString += ",Class_Name=@Class_Name_V";
cmd.Parameters.Add(new SqlParameter("@Class_Name_V",newValues.ClassName));
}
if(newValues.ClassColorIsValid)
{
updateString += ",Class_Color=@Class_Color_V";
cmd.Parameters.Add(new SqlParameter("@Class_Color_V",newValues.ClassColor));
}
if(newValues.StatusIDIsValid)
{
updateString += ",Status_ID=@Status_ID_V";
cmd.Parameters.Add(new SqlParameter("@Status_ID_V",newValues.StatusID));
}
if(newValues.TermIDIsValid)
{
updateString += ",Term_ID=@Term_ID_V";
cmd.Parameters.Add(new SqlParameter("@Term_ID_V",newValues.TermID));
}
if(newValues.ChargeIdIsValid)
{
updateString += ",charge_id=@charge_id_V";
cmd.Parameters.Add(new SqlParameter("@charge_id_V",newValues.ChargeId));
}
if(searcher.ClassIDIsValid)
{
condition += " and Class_ID=@Class_ID_C";
cmd.Parameters.Add(new SqlParameter("@Class_ID_C",searcher.ClassID));
}
if(searcher.ClassNameIsValid)
{
condition += " and Class_Name=@Class_Name_C";
cmd.Parameters.Add(new SqlParameter("@Class_Name_C",searcher.ClassName));
}
if(searcher.ClassColorIsValid)
{
condition += " and Class_Color=@Class_Color_C";
cmd.Parameters.Add(new SqlParameter("@Class_Color_C",searcher.ClassColor));
}
if(searcher.StatusIDIsValid)
{
condition += " and Status_ID=@Status_ID_C";
cmd.Parameters.Add(new SqlParameter("@Status_ID_C",searcher.StatusID));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
cmd.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(searcher.ChargeIdIsValid)
{
condition += " and charge_id=@charge_id_C";
cmd.Parameters.Add(new SqlParameter("@charge_id_C",searcher.ChargeId));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Class set "+updateString.Substring(1)+condition;
return cmd.ExecuteNonQuery();
}
#endregion
#region 查询实体集合
/// <summary>
/// 根据查询对象构建过滤条件查询
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(ClassSearcher 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, ClassSearcher searcher)
{
SqlCommand cmd=connection.Command as SqlCommand;
return Select(cmd,searcher);
}
public ArrayList Select(SqlCommand command,ClassSearcher searcher)
{
string condition=string.Empty;
command.Parameters.Clear();
ArrayList entities = new ArrayList();
if(searcher.ClassIDIsValid)
{
condition += " and Class_ID=@Class_ID_C";
command.Parameters.Add(new SqlParameter("@Class_ID_C",searcher.ClassID));
}
if(searcher.ClassNameIsValid)
{
condition += " and Class_Name=@Class_Name_C";
command.Parameters.Add(new SqlParameter("@Class_Name_C",searcher.ClassName));
}
if(searcher.ClassColorIsValid)
{
condition += " and Class_Color=@Class_Color_C";
command.Parameters.Add(new SqlParameter("@Class_Color_C",searcher.ClassColor));
}
if(searcher.StatusIDIsValid)
{
condition += " and Status_ID=@Status_ID_C";
command.Parameters.Add(new SqlParameter("@Status_ID_C",searcher.StatusID));
}
if(searcher.TermIDIsValid)
{
condition += " and Term_ID=@Term_ID_C";
command.Parameters.Add(new SqlParameter("@Term_ID_C",searcher.TermID));
}
if(searcher.ChargeIdIsValid)
{
condition += " and charge_id=@charge_id_C";
command.Parameters.Add(new SqlParameter("@charge_id_C",searcher.ChargeId));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
command.CommandText = "select * from Class"+condition+" order by term_id";
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())
{
Class entity = DataReaderToEntity(dr);
entities.Add(entity);
}
}
SetForeignKeyEntity(cmd, ref entities);
return entities;
}
private void SetForeignKeyEntity(SqlCommand command, ref ArrayList entities)
{
//由外键获取相关实体
DStatus dStatus=new DStatus();
DTerm dTerm=new DTerm();
DEmployee dEmployee=new DEmployee();
foreach(Class entity in entities)
{
entity.Status = dStatus.SelectSingle(command,entity.StatusID);
entity.Term = dTerm.SelectSingle(command,entity.TermID);
entity.Employee = dEmployee.SelectSingle(command,entity.ChargeId);
}
}
/// <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 Class order by term_id";
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 Class";
return ExcuteSelectCommand(cmd);
}
#endregion
#region 查询单个实体
/// <summary>
/// 按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public Class SelectSingle(object pkValue)
{
Class 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 Class SelectSingle(IConnection connection,object pkValue)
{
SqlCommand cmd=connection.Command as SqlCommand;
return SelectSingle(cmd,pkValue);
}
public Class SelectSingle(SqlCommand command,object pkValue)
{
Class entity=null;
command.Parameters.Clear();
command.CommandText = "select * from Class where Class_ID=@pk";
command.Parameters.Add(new SqlParameter("@pk",pkValue));
using (SqlDataReader dr = command.ExecuteReader())
{
if(dr.Read())
entity = DataReaderToEntity(dr);
}
//由外键获取相关实体
DStatus dStatus=new DStatus();
DTerm dTerm=new DTerm();
DEmployee dEmployee=new DEmployee();
entity.Status = dStatus.SelectSingle(command,entity.StatusID);
entity.Term = dTerm.SelectSingle(command,entity.TermID);
entity.Employee = dEmployee.SelectSingle(command,entity.ChargeId);
return entity;
}
#endregion
/// <summary>
/// 从DataReader中取出值生成实体对象
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>过滤条件字符串</returns>
private Class DataReaderToEntity(SqlDataReader dr)
{
Class entity = new Class ();
if(dr["Class_ID"]!=System.DBNull.Value)
{
entity.ClassID=Convert.ToInt32(dr["Class_ID"]);
}
if(dr["Class_Name"]!=System.DBNull.Value)
{
entity.ClassName=dr["Class_Name"].ToString();
}
if(dr["Class_Color"]!=System.DBNull.Value)
{
entity.ClassColor=dr["Class_Color"].ToString();
}
if(dr["Status_ID"]!=System.DBNull.Value)
{
entity.StatusID=Convert.ToInt32(dr["Status_ID"]);
}
if(dr["Term_ID"]!=System.DBNull.Value)
{
entity.TermID=Convert.ToInt32(dr["Term_ID"]);
}
if(dr["charge_id"]!=System.DBNull.Value)
{
entity.ChargeId=Convert.ToInt32(dr["charge_id"]);
}
return entity;
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -