一、界面：
二、数据库访问类：
public class DataClass
{
private readonly string connect = ConfigurationManager.AppSettings["connectString"];
public DataClass() { } /// <summary>
/// 执行查询语句，返回DataSet
/// </summary>
/// <param name="SQLString">查询语句</param>
/// <returns>DataSet</returns>
public DataSet Query(string SQLString)
{ using (MySqlConnection connection = new MySqlConnection(connect))
{
DataSet ds = new DataSet();
try
{
connection.Open();
MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
command.Fill(ds, "ds");
}
catch (MySqlException ex)
{
throw new Exception(ex.Message);
} return ds;
}
} /// <summary>
/// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
/// </summary>
/// <param name="strSQL">查询语句</param>
/// <returns>SqlDataReader</returns>
public MySqlDataReader ExecuteReader(string strSQL)
{
using (MySqlConnection connection = new MySqlConnection(connect))
{
MySqlCommand cmd = new MySqlCommand(strSQL, connection);
try
{
connection.Open();
MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return myReader;
}
catch (System.Data.SqlClient.SqlException e)
{
throw e;
}
}
} /// <summary>
/// 执行一条计算查询结果语句，返回查询结果（object）。
/// </summary>
/// <param name="SQLString">计算查询结果语句</param>
/// <returns>查询结果（object）</returns>
public object GetSingle(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connect))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
object obj = cmd.ExecuteScalar();
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
return null;
}
else
{
return obj;
}
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} public bool Exists(string strSql)
{
object obj = GetSingle(strSql);
int cmdresult;
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
{
cmdresult = ;
}
else
{
cmdresult = int.Parse(obj.ToString());
}
if (cmdresult == )
{
return false;
}
else
{
return true;
}
} /// <summary>
/// 执行SQL语句，返回影响的记录数
/// </summary>
/// <param name="SQLString">SQL语句</param>
/// <returns>影响的记录数</returns>
public int ExecuteSql(string SQLString)
{
using (MySqlConnection connection = new MySqlConnection(connect))
{
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
{
try
{
connection.Open();
int rows = cmd.ExecuteNonQuery();
return rows;
}
catch (System.Data.SqlClient.SqlException e)
{
connection.Close();
throw e;
}
}
}
} /// <summary>
/// 得到一个对象实体
/// </summary>
public ModelClass GetModelList(int MenuId)
{ StringBuilder strSql = new StringBuilder();
strSql.Append("select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu ");
strSql.Append(" where MenuId= " + MenuId); ModelClass model = new ModelClass();
DataSet ds = Query(strSql.ToString());
if (ds.Tables[].Rows.Count > )
{
if (ds.Tables[].Rows[]["MenuId"].ToString() != "")
{
model.MenuId = int.Parse(ds.Tables[].Rows[]["MenuId"].ToString());
}
model.MenuName = ds.Tables[].Rows[]["MenuName"].ToString();
model.MenuRemark = ds.Tables[].Rows[]["MenuRemark"].ToString();
model.MenuUrl = ds.Tables[].Rows[]["MenuUrl"].ToString();
if (ds.Tables[].Rows[]["ParentMenuId"].ToString() != "")
{
model.ParentMenuId = int.Parse(ds.Tables[].Rows[]["ParentMenuId"].ToString());
}
model.MenuIcon = ds.Tables[].Rows[]["MenuIcon"].ToString();
if (ds.Tables[].Rows[]["MenuSort"].ToString() != "")
{
model.MenuSort = int.Parse(ds.Tables[].Rows[]["MenuSort"].ToString());
}
return model;
}
else
{
return null;
}
} /// <summary>
/// 增加一条数据
/// </summary>
public int Add(ModelClass model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("insert into SYS_Menu(");
strSql.Append("MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort)");
strSql.Append(" values (");
strSql.Append("'" + model.MenuName + "'," );
strSql.Append("'" + model.MenuRemark + "',");
strSql.Append("'" + model.MenuUrl + "',");
strSql.Append("'" + model.ParentMenuId + "',");
strSql.Append("'" + model.MenuIcon + "',");
strSql.Append("'" + model.MenuSort + "')");
object obj = ExecuteSql(strSql.ToString());
if (obj == null)
{
return -;
}
else
{
return Convert.ToInt32(obj);
}
} /// <summary>
/// 删除一条数据
/// </summary>
public void Delete(int MenuId)
{
string sql = "delete from SYS_Menu where MenuId = " + MenuId;
ExecuteSql(sql);
} /// <summary>
/// 更新一条数据
/// </summary>
public void Update(ModelClass model)
{
StringBuilder strSql = new StringBuilder();
strSql.Append("update SYS_Menu set ");
strSql.Append("MenuName='" + model.MenuName + "',");
strSql.Append("MenuRemark='" + model.MenuRemark + "',");
strSql.Append("MenuUrl='" + model.MenuUrl + "',");
strSql.Append("ParentMenuId='" + model.ParentMenuId + "',");
strSql.Append("MenuIcon='" + model.MenuIcon + "',");
strSql.Append("MenuSort='" + model.MenuSort + "'");
strSql.Append(" where MenuId=" + model.MenuId); ExecuteSql(strSql.ToString());
}
}
三、窗体页面代码：
public partial class MenuManager : Form
{
DataClass help = new DataClass();
ModelClass model = new ModelClass();
public MenuManager()
{
InitializeComponent();
} /// <summary>
/// 窗体载入事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void MenuManager_Load(object sender, EventArgs e)
{
QueryTreeView();//树型菜单
ComBoxBindInit(); //上级菜单下拉列表初始化
} /// <summary>
/// 树型菜单查看事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntQuery_Click(object sender, EventArgs e)
{
QueryTreeView();//树型菜单
} /// <summary>
/// 树型菜单展示
/// </summary>
private void QueryTreeView()
{
BindListView(treeViewMenu);
//展开所有节点
treeViewMenu.ExpandAll();
this.treeViewMenu.Nodes[].EnsureVisible();//滚动打显示最上方
} #region 网站树形菜单
public void BindListView(TreeView treeView)
{
//清空树的所有节点
treeView.Nodes.Clear();
//创建根节点
TreeNode rootNode = new TreeNode();
rootNode.Text = "菜单列表";
rootNode.Name = ""; //展开所有节点
//添加根节点
treeView.Nodes.Add(rootNode);
// 获取所有节点信息
DataTable dt = help.Query(" select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu Where 1=1 order by ParentMenuId , MenuSort asc ").Tables[];
////创建其他节点
CreateChildNode(rootNode, dt);
} private void CreateChildNode(TreeNode parentNode, DataTable dataTable)
{
DataRow[] rowList = dataTable.Select("ParentMenuId='" + parentNode.Name + "'");
foreach (DataRow row in rowList)
{ //创建新节点
TreeNode node = new TreeNode();
//设置节点的属性
node.Text = row["MenuName"].ToString();
node.Name = row["MenuId"].ToString();
parentNode.Nodes.Add(node);
//递归调用，创建其他节点
CreateChildNode(node, dataTable);
}
}
#endregion /// <summary>
/// 上级菜单初始化
/// </summary>
private void ComBoxBindInit()
{
DataTable dt = help.Query("select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu ").Tables[]; DataRow dr = dt.NewRow();
dr["MenuName"] = "=============*菜单=============";
dr["MenuId"] = ;
dt.Rows.InsertAt(dr, ); comParentMenuId.DataSource = dt;
comParentMenuId.DisplayMember = "MenuName";
comParentMenuId.ValueMember = "MenuId"; } /// <summary>
/// 上级菜单重置（即查询所有菜单）
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntInit_Click(object sender, EventArgs e)
{
txtQueryMenu.Text = "";
ComBoxBindInit();
} /// <summary>
/// 模糊查询菜单信息绑定上级菜单下拉列表
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntSelect_Click(object sender, EventArgs e)
{
DataTable dt = help.Query("select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu where 1=1 and MenuName like '%" + txtQueryMenu.Text.Trim() + "%' ").Tables[]; DataRow dr = dt.NewRow();
dr["MenuName"] = "===无===";
dr["MenuId"] = ;
dt.Rows.InsertAt(dr, ); comParentMenuId.DataSource = dt;
comParentMenuId.DisplayMember = "MenuName";
comParentMenuId.ValueMember = "MenuId";
} /// <summary>
/// 选择节点取值
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void treeViewMenu_AfterSelect(object sender, TreeViewEventArgs e)
{
try
{
if (e.Node.Text != "菜单列表")
{
ComBoxBindInit();
model = help.GetModelList(Convert.ToInt32(e.Node.Name));
txtMenuId.Text = model.MenuId.ToString();
txtMenuName.Text = model.MenuName;
txtMenuUrl.Text = model.MenuUrl;
txtMenuSort.Text = model.MenuSort.ToString();
txtMenuIcon.Text = model.MenuIcon;
txtMenuRemark.Text = model.MenuRemark;
comParentMenuId.SelectedValue = model.ParentMenuId;
}
else
{
ClearText();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
} /// <summary>
/// 清空文本内容
/// </summary>
private void ClearText()
{
txtMenuId.Text = "";
txtMenuName.Text = "";
txtMenuUrl.Text = "";
txtMenuSort.Text = "";
txtMenuIcon.Text = "";
txtMenuRemark.Text = "";
} /// <summary>
/// 添加
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntAdd_Click(object sender, EventArgs e)
{
try
{
getModel();//为实体类赋值 #region 条件满足判断
//菜单名称不能为空
if (txtMenuName.Text == "")
{
MessageBox.Show("菜单名称不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//同级菜单中不能有相同菜单名称
if(help.Exists("select count(1) from SYS_Menu where MenuName='" + txtMenuName.Text + "' and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
{
MessageBox.Show("同级菜单中该名称已存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//验证该级菜单是否有相同排序号
if (help.Exists("select count(1) from SYS_Menu where MenuSort=" + Convert.ToInt32(txtMenuSort.Text.Trim()) + " and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
{
MessageBox.Show("该级菜单存在相同排序号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//排序号必填
if (txtMenuSort.Text == "")
{
MessageBox.Show("排序号不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion int result = help.Add(model);//添加
if (result >= )
{
MessageBox.Show("添加成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
ClearText();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
} /// <summary>
/// 修改
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntUpdate_Click(object sender, EventArgs e)
{
try
{
getModel();//为实体类赋值 #region 条件满足判断
//菜单名称不能为空
if (txtMenuName.Text == "")
{
MessageBox.Show("菜单名称不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//同级菜单中不能有相同菜单名称
if (help.Exists("select count(1) from SYS_Menu where MenuId <>" + model.MenuId + " and MenuName='" + txtMenuName.Text + "' and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
{
MessageBox.Show("同级菜单中该名称已存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//验证该级菜单是否有相同排序号
if (help.Exists("select count(1) from SYS_Menu where MenuId<>" + model.MenuId + " and MenuSort=" + Convert.ToInt32(txtMenuSort.Text.Trim()) + " and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
{
MessageBox.Show("该级菜单存在相同排序号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//排序号必填
if (txtMenuSort.Text == "")
{
MessageBox.Show("排序号不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
#endregion
if (txtMenuId.Text != "" && help.Exists("select count(1) from SYS_Menu where MenuId=" + model.MenuId))
{
help.Update(model);
MessageBox.Show("更新成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
else
{
MessageBox.Show("数据记录不存在！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
} }
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
} /// <summary>
/// 删除
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntDelete_Click(object sender, EventArgs e)
{
try
{
if (txtMenuId.Text == "")
{
MessageBox.Show("请选择所要删除的记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
//删除前先判断是否被其他菜单引用
if (help.Exists("select count(1) from SYS_Menu where ParentMenuId=" + Convert.ToInt32(txtMenuId.Text)))
{
MessageBox.Show("该菜单被子级菜单引用，请先删除引用！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
return;
}
if (MessageBox.Show("确定要删除该记录吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
{
help.Delete(Convert.ToInt32(txtMenuId.Text));
MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
ClearText();
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
} /// <summary>
/// 清空
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void bntClear_Click(object sender, EventArgs e)
{
ClearText();
} /// <summary>
/// 排序号只能输入数字类型
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtMenuSort_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar != && !Char.IsDigit(e.KeyChar))
{
e.Handled = true;
}
} /// <summary>
/// 为实体类赋值
/// </summary>
private void getModel()
{
if (txtMenuId.Text != "")
model.MenuId = Convert.ToInt32(txtMenuId.Text);
model.MenuName = txtMenuName.Text.Trim();
model.MenuUrl = txtMenuUrl.Text.Trim();
if (txtMenuSort.Text != "")
model.MenuSort = Convert.ToInt32(txtMenuSort.Text.Trim());
model.MenuIcon = txtMenuIcon.Text.Trim();
model.MenuRemark = txtMenuRemark.Text;
model.ParentMenuId = Convert.ToInt32(comParentMenuId.SelectedValue.ToString());
} /// <summary>
/// 光标处于上级菜单查询按下回车执行查询事件
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void txtQueryMenu_KeyPress(object sender, KeyPressEventArgs e)
{
if (e.KeyChar == )
{
bntSelect_Click(sender, e);
}
} }