📄 dmenu.cs
字号:
cmd.Parameters.Add(new SqlParameter("@Menu_Type_ID_C",searcher.MenuTypeID));
}
if(searcher.MenuUrlIsValid)
{
condition += " and Menu_Url=@Menu_Url_C";
cmd.Parameters.Add(new SqlParameter("@Menu_Url_C",searcher.MenuUrl));
}
if(searcher.ParentIDIsValid)
{
condition += " and Parent_ID=@Parent_ID_C";
cmd.Parameters.Add(new SqlParameter("@Parent_ID_C",searcher.ParentID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Menu 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,MenuSearcher searcher,MenuSearcher newValues)
{
string updateString = string.Empty;
string condition = string.Empty;
SqlCommand cmd=connection.Command as SqlCommand;
cmd.Parameters.Clear();
if(newValues.MenuIDIsValid)
{
updateString += ",Menu_ID=@Menu_ID_V";
cmd.Parameters.Add(new SqlParameter("@Menu_ID_V",newValues.MenuID));
}
if(newValues.MenuNameIsValid)
{
updateString += ",Menu_Name=@Menu_Name_V";
cmd.Parameters.Add(new SqlParameter("@Menu_Name_V",newValues.MenuName));
}
if(newValues.MenuTypeIDIsValid)
{
updateString += ",Menu_Type_ID=@Menu_Type_ID_V";
cmd.Parameters.Add(new SqlParameter("@Menu_Type_ID_V",newValues.MenuTypeID));
}
if(newValues.MenuUrlIsValid)
{
updateString += ",Menu_Url=@Menu_Url_V";
cmd.Parameters.Add(new SqlParameter("@Menu_Url_V",newValues.MenuUrl));
}
if(newValues.ParentIDIsValid)
{
updateString += ",Parent_ID=@Parent_ID_V";
cmd.Parameters.Add(new SqlParameter("@Parent_ID_V",newValues.ParentID));
}
if(searcher.MenuIDIsValid)
{
condition += " and Menu_ID=@Menu_ID_C";
cmd.Parameters.Add(new SqlParameter("@Menu_ID_C",searcher.MenuID));
}
if(searcher.MenuNameIsValid)
{
condition += " and Menu_Name=@Menu_Name_C";
cmd.Parameters.Add(new SqlParameter("@Menu_Name_C",searcher.MenuName));
}
if(searcher.MenuTypeIDIsValid)
{
condition += " and Menu_Type_ID=@Menu_Type_ID_C";
cmd.Parameters.Add(new SqlParameter("@Menu_Type_ID_C",searcher.MenuTypeID));
}
if(searcher.MenuUrlIsValid)
{
condition += " and Menu_Url=@Menu_Url_C";
cmd.Parameters.Add(new SqlParameter("@Menu_Url_C",searcher.MenuUrl));
}
if(searcher.ParentIDIsValid)
{
condition += " and Parent_ID=@Parent_ID_C";
cmd.Parameters.Add(new SqlParameter("@Parent_ID_C",searcher.ParentID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
cmd.CommandText="update Menu set "+updateString.Substring(1)+condition;
return cmd.ExecuteNonQuery();
}
#endregion
#region 查询实体集合
/// <summary>
/// 根据查询对象构建过滤条件查询
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>实体类对象列表</returns>
public ArrayList Select(MenuSearcher 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, MenuSearcher searcher)
{
SqlCommand cmd=connection.Command as SqlCommand;
return Select(cmd,searcher);
}
public ArrayList Select(SqlCommand command,MenuSearcher searcher)
{
string condition=string.Empty;
command.Parameters.Clear();
ArrayList entities = new ArrayList();
if(searcher.MenuIDIsValid)
{
condition += " and Menu_ID=@Menu_ID_C";
command.Parameters.Add(new SqlParameter("@Menu_ID_C",searcher.MenuID));
}
if(searcher.MenuNameIsValid)
{
condition += " and Menu_Name=@Menu_Name_C";
command.Parameters.Add(new SqlParameter("@Menu_Name_C",searcher.MenuName));
}
if(searcher.MenuTypeIDIsValid)
{
condition += " and Menu_Type_ID=@Menu_Type_ID_C";
command.Parameters.Add(new SqlParameter("@Menu_Type_ID_C",searcher.MenuTypeID));
}
if(searcher.MenuUrlIsValid)
{
condition += " and Menu_Url=@Menu_Url_C";
command.Parameters.Add(new SqlParameter("@Menu_Url_C",searcher.MenuUrl));
}
if(searcher.ParentIDIsValid)
{
condition += " and Parent_ID=@Parent_ID_C";
command.Parameters.Add(new SqlParameter("@Parent_ID_C",searcher.ParentID));
}
if(condition != string.Empty)
{
condition=" where"+condition.Substring(4);
}
command.CommandText = "select * from Menu"+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())
{
Menu entity = DataReaderToEntity(dr);
entities.Add(entity);
}
}
SetForeignKeyEntity(cmd, ref entities);
return entities;
}
private void SetForeignKeyEntity(SqlCommand command, ref ArrayList entities)
{
//由外键获取相关实体
DMenuType dMenuType=new DMenuType();
MenuSearcher searcher=new MenuSearcher();
searcher.ParentIDIsValid=true;
foreach(Menu entity in entities)
{
entity.MenuType = dMenuType.SelectSingle(command,entity.MenuTypeID);
searcher.ParentID=entity.MenuID;
entity.MenuList=Select(command,searcher);
}
}
/// <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 Menu";
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 Menu";
return ExcuteSelectCommand(cmd);
}
#endregion
#region 查询单个实体
/// <summary>
/// 按主键字段查询特定实体
/// </summary>
/// <param name="pkValue">主键值</param>
/// <returns>实体类对象</returns>
public Menu SelectSingle(object pkValue)
{
Menu 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 Menu SelectSingle(IConnection connection,object pkValue)
{
SqlCommand cmd=connection.Command as SqlCommand;
return SelectSingle(cmd,pkValue);
}
public Menu SelectSingle(SqlCommand command,object pkValue)
{
Menu entity=null;
command.Parameters.Clear();
command.CommandText = "select * from Menu where Menu_ID=@pk";
command.Parameters.Add(new SqlParameter("@pk",pkValue));
using (SqlDataReader dr = command.ExecuteReader())
{
if(dr.Read())
entity = DataReaderToEntity(dr);
}
//由外键获取相关实体
DMenuType dMenuType=new DMenuType();
MenuSearcher searcher=new MenuSearcher();
searcher.ParentIDIsValid=true;
entity.MenuType = dMenuType.SelectSingle(command,entity.MenuTypeID);
searcher.ParentID=entity.MenuID;
entity.MenuList=Select(command,searcher);
return entity;
}
#endregion
/// <summary>
/// 从DataReader中取出值生成实体对象
/// </summary>
/// <param name="searcher">查询对象</param>
/// <returns>过滤条件字符串</returns>
private Menu DataReaderToEntity(SqlDataReader dr)
{
Menu entity = new Menu ();
if(dr["Menu_ID"]!=System.DBNull.Value)
{
entity.MenuID=Convert.ToInt32(dr["Menu_ID"]);
}
if(dr["Menu_Name"]!=System.DBNull.Value)
{
entity.MenuName=dr["Menu_Name"].ToString();
}
if(dr["Menu_Type_ID"]!=System.DBNull.Value)
{
entity.MenuTypeID=Convert.ToInt32(dr["Menu_Type_ID"]);
}
if(dr["Menu_Url"]!=System.DBNull.Value)
{
entity.MenuUrl=dr["Menu_Url"].ToString();
}
if(dr["Parent_ID"]!=System.DBNull.Value)
{
entity.ParentID=Convert.ToInt32(dr["Parent_ID"]);
}
return entity;
}
}
}
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -