📄 dcorpuser.cs
字号:
{
condition += " and Password=@Password_C";
cmd.Parameters.Add(new SqlParameter("@Password_C",searcher.Password));
}
if(searcher.EmployeeIDIsValid)
{
condition += " and Employee_ID=@Employee_ID_C";
cmd.Parameters.Add(new SqlParameter("@Employee_ID_C",searcher.EmployeeID));
}
if(searcher.RoleIDIsValid)
{
condition += " and Role_ID=@Role_ID_C";
cmd.Parameters.Add(new SqlParameter("@Role_ID_C",searcher.RoleID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update CorpUser 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,CorpUserSearcher searcher,CorpUserSearcher newValues)
{
string updateString = string.Empty;
string condition = string.Empty;
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
if(newValues.UserIDIsValid)
{
updateString += ",User_ID=@User_ID_V";
cmd.Parameters.Add(new SqlParameter("@User_ID_V",newValues.UserID));
}
if(newValues.LoginNameIsValid)
{
updateString += ",Login_Name=@Login_Name_V";
cmd.Parameters.Add(new SqlParameter("@Login_Name_V",newValues.LoginName));
}
if(newValues.PasswordIsValid)
{
updateString += ",Password=@Password_V";
cmd.Parameters.Add(new SqlParameter("@Password_V",newValues.Password));
}
if(newValues.EmployeeIDIsValid)
{
updateString += ",Employee_ID=@Employee_ID_V";
cmd.Parameters.Add(new SqlParameter("@Employee_ID_V",newValues.EmployeeID));
}
if(newValues.RoleIDIsValid)
{
updateString += ",Role_ID=@Role_ID_V";
cmd.Parameters.Add(new SqlParameter("@Role_ID_V",newValues.RoleID));
}
if(searcher.UserIDIsValid)
{
condition += " and User_ID=@User_ID_C";
cmd.Parameters.Add(new SqlParameter("@User_ID_C",searcher.UserID));
}
if(searcher.LoginNameIsValid)
{
condition += " and Login_Name=@Login_Name_C";
cmd.Parameters.Add(new SqlParameter("@Login_Name_C",searcher.LoginName));
}
if(searcher.PasswordIsValid)
{
condition += " and Password=@Password_C";
cmd.Parameters.Add(new SqlParameter("@Password_C",searcher.Password));
}
if(searcher.EmployeeIDIsValid)
{
condition += " and Employee_ID=@Employee_ID_C";
cmd.Parameters.Add(new SqlParameter("@Employee_ID_C",searcher.EmployeeID));
}
if(searcher.RoleIDIsValid)
{
condition += " and Role_ID=@Role_ID_C";
cmd.Parameters.Add(new SqlParameter("@Role_ID_C",searcher.RoleID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update CorpUser set "+updateString.Substring(1)+condition;
return cmd.ExecuteNonQuery();
}
#endregion
#region 查询实体集合
/// <summary>
/// 根据查询对象构建过滤条件查询
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(CorpUserSearcher 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, CorpUserSearcher searcher)
{
SqlCommand cmd=connection.Command as SqlCommand;
return Select(cmd,searcher);
}
public ArrayList Select(SqlCommand command,CorpUserSearcher searcher)
{
string condition=string.Empty;
command.Parameters.Clear();
ArrayList entities = new ArrayList();
if(searcher.UserIDIsValid)
{
condition += " and User_ID=@User_ID_C";
command.Parameters.Add(new SqlParameter("@User_ID_C",searcher.UserID));
}
if(searcher.LoginNameIsValid)
{
condition += " and Login_Name=@Login_Name_C";
command.Parameters.Add(new SqlParameter("@Login_Name_C",searcher.LoginName));
}
if(searcher.PasswordIsValid)
{
condition += " and Password=@Password_C";
command.Parameters.Add(new SqlParameter("@Password_C",searcher.Password));
}
if(searcher.EmployeeIDIsValid)
{
condition += " and Employee_ID=@Employee_ID_C";
command.Parameters.Add(new SqlParameter("@Employee_ID_C",searcher.EmployeeID));
}
if(searcher.RoleIDIsValid)
{
condition += " and Role_ID=@Role_ID_C";
command.Parameters.Add(new SqlParameter("@Role_ID_C",searcher.RoleID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
command.CommandText = "select * from CorpUser"+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())
{
CorpUser entity = DataReaderToEntity(dr);
entities.Add(entity);
}
}
SetForeignKeyEntity(cmd, ref entities);
return entities;
}
private void SetForeignKeyEntity(SqlCommand command, ref ArrayList entities)
{
//由外键获取相关实体
DCorpRole dCorpRole=new DCorpRole();
DEmployee dEmployee=new DEmployee();
foreach(CorpUser entity in entities)
{
entity.CorpRole = dCorpRole.SelectSingle(command,entity.RoleID);
entity.Employee = dEmployee.SelectSingle(command,entity.EmployeeID);
}
}
/// <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 CorpUser";
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 CorpUser";
return ExcuteSelectCommand(cmd);
}
#endregion
#region 查询单个实体
/// <summary>
/// 按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public CorpUser SelectSingle(object pkValue)
{
CorpUser 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 CorpUser SelectSingle(IConnection connection,object pkValue)
{
SqlCommand cmd=connection.Command as SqlCommand;
return SelectSingle(cmd,pkValue);
}
public CorpUser SelectSingle(SqlCommand command,object pkValue)
{
CorpUser entity=null;
command.Parameters.Clear();
command.CommandText = "select * from CorpUser where User_ID=@pk";
command.Parameters.Add(new SqlParameter("@pk",pkValue));
using (SqlDataReader dr = command.ExecuteReader())
{
if(dr.Read())
entity = DataReaderToEntity(dr);
}
//由外键获取相关实体
DCorpRole dCorpRole=new DCorpRole();
DEmployee dEmployee=new DEmployee();
entity.CorpRole = dCorpRole.SelectSingle(command,entity.RoleID);
entity.Employee = dEmployee.SelectSingle(command,entity.EmployeeID);
return entity;
}
#endregion
/// <summary>
/// 从DataReader中取出值生成实体对象
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>过滤条件字符串</returns>
private CorpUser DataReaderToEntity(SqlDataReader dr)
{
CorpUser entity = new CorpUser ();
if(dr["User_ID"]!=System.DBNull.Value)
{
entity.UserID=Convert.ToInt32(dr["User_ID"]);
}
if(dr["Login_Name"]!=System.DBNull.Value)
{
entity.LoginName=dr["Login_Name"].ToString();
}
if(dr["Password"]!=System.DBNull.Value)
{
entity.Password=dr["Password"].ToString();
}
if(dr["Employee_ID"]!=System.DBNull.Value)
{
entity.EmployeeID=Convert.ToInt32(dr["Employee_ID"]);
}
if(dr["Role_ID"]!=System.DBNull.Value)
{
entity.RoleID=Convert.ToInt32(dr["Role_ID"]);
}
return entity;
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -