WinForm实现对权限菜单进行管理

一、界面：

二、数据库访问类：

  1  public class DataClass
  2     {
  3         private readonly string connect = ConfigurationManager.AppSettings["connectString"];
  4         public DataClass() { }
  5 
  6         /// <summary>
  7         /// 执行查询语句，返回DataSet
  8         /// </summary>
  9         /// <param name="SQLString">查询语句</param>
 10         /// <returns>DataSet</returns>
 11         public DataSet Query(string SQLString)
 12         {
 13 
 14             using (MySqlConnection connection = new MySqlConnection(connect))
 15             {
 16                 DataSet ds = new DataSet();
 17                 try
 18                 {
 19                     connection.Open();
 20                     MySqlDataAdapter command = new MySqlDataAdapter(SQLString, connection);
 21                     command.Fill(ds, "ds");
 22                 }
 23                 catch (MySqlException ex)
 24                 {
 25                     throw new Exception(ex.Message);
 26                 }
 27 
 28                 return ds;
 29             }
 30         }
 31 
 32         /// <summary>
 33         /// 执行查询语句，返回SqlDataReader ( 注意：调用该方法后，一定要对SqlDataReader进行Close )
 34         /// </summary>
 35         /// <param name="strSQL">查询语句</param>
 36         /// <returns>SqlDataReader</returns>
 37         public MySqlDataReader ExecuteReader(string strSQL)
 38         {
 39             using (MySqlConnection connection = new MySqlConnection(connect))
 40             {
 41                 MySqlCommand cmd = new MySqlCommand(strSQL, connection);
 42                 try
 43                 {
 44                     connection.Open();
 45                     MySqlDataReader myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
 46                     return myReader;
 47                 }
 48                 catch (System.Data.SqlClient.SqlException e)
 49                 {
 50                     throw e;
 51                 }
 52             }
 53         }
 54 
 55         /// <summary>
 56         /// 执行一条计算查询结果语句，返回查询结果（object）。
 57         /// </summary>
 58         /// <param name="SQLString">计算查询结果语句</param>
 59         /// <returns>查询结果（object）</returns>
 60         public object GetSingle(string SQLString)
 61         {
 62             using (MySqlConnection connection = new MySqlConnection(connect))
 63             {
 64                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
 65                 {
 66                     try
 67                     {
 68                         connection.Open();
 69                         object obj = cmd.ExecuteScalar();
 70                         if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 71                         {
 72                             return null;
 73                         }
 74                         else
 75                         {
 76                             return obj;
 77                         }
 78                     }
 79                     catch (System.Data.SqlClient.SqlException e)
 80                     {
 81                         connection.Close();
 82                         throw e;
 83                     }
 84                 }
 85             }
 86         }
 87 
 88         public bool Exists(string strSql)
 89         {
 90             object obj = GetSingle(strSql);
 91             int cmdresult;
 92             if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
 93             {
 94                 cmdresult = 0;
 95             }
 96             else
 97             {
 98                 cmdresult = int.Parse(obj.ToString());
 99             }
100             if (cmdresult == 0)
101             {
102                 return false;
103             }
104             else
105             {
106                 return true;
107             }
108         }
109 
110         /// <summary>
111         /// 执行SQL语句，返回影响的记录数
112         /// </summary>
113         /// <param name="SQLString">SQL语句</param>
114         /// <returns>影响的记录数</returns>
115         public int ExecuteSql(string SQLString)
116         {
117             using (MySqlConnection connection = new MySqlConnection(connect))
118             {
119                 using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
120                 {
121                     try
122                     {
123                         connection.Open();
124                         int rows = cmd.ExecuteNonQuery();
125                         return rows;
126                     }
127                     catch (System.Data.SqlClient.SqlException e)
128                     {
129                         connection.Close();
130                         throw e;
131                     }
132                 }
133             }
134         }
135 
136         /// <summary>
137         /// 得到一个对象实体
138         /// </summary>
139         public ModelClass GetModelList(int MenuId)
140         {
141 
142             StringBuilder strSql = new StringBuilder();
143             strSql.Append("select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu ");
144             strSql.Append(" where  MenuId= " + MenuId);
145 
146             ModelClass model = new ModelClass();
147             DataSet ds = Query(strSql.ToString());
148             if (ds.Tables[0].Rows.Count > 0)
149             {
150                 if (ds.Tables[0].Rows[0]["MenuId"].ToString() != "")
151                 {
152                     model.MenuId = int.Parse(ds.Tables[0].Rows[0]["MenuId"].ToString());
153                 }
154                 model.MenuName = ds.Tables[0].Rows[0]["MenuName"].ToString();
155                 model.MenuRemark = ds.Tables[0].Rows[0]["MenuRemark"].ToString();
156                 model.MenuUrl = ds.Tables[0].Rows[0]["MenuUrl"].ToString();
157                 if (ds.Tables[0].Rows[0]["ParentMenuId"].ToString() != "")
158                 {
159                     model.ParentMenuId = int.Parse(ds.Tables[0].Rows[0]["ParentMenuId"].ToString());
160                 }
161                 model.MenuIcon = ds.Tables[0].Rows[0]["MenuIcon"].ToString();
162                 if (ds.Tables[0].Rows[0]["MenuSort"].ToString() != "")
163                 {
164                     model.MenuSort = int.Parse(ds.Tables[0].Rows[0]["MenuSort"].ToString());
165                 }
166                 return model;
167             }
168             else
169             {
170                 return null;
171             }
172         }
173 
174         /// <summary>
175         /// 增加一条数据
176         /// </summary>
177         public int Add(ModelClass model)
178         {
179             StringBuilder strSql = new StringBuilder();
180             strSql.Append("insert into SYS_Menu(");
181             strSql.Append("MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort)");
182             strSql.Append(" values (");
183             strSql.Append("'" + model.MenuName + "'," );
184             strSql.Append("'" + model.MenuRemark + "',");
185             strSql.Append("'" + model.MenuUrl + "',");
186             strSql.Append("'" + model.ParentMenuId + "',");
187             strSql.Append("'" + model.MenuIcon + "',");
188             strSql.Append("'" + model.MenuSort + "')");
189             object obj = ExecuteSql(strSql.ToString());
190             if (obj == null)
191             {
192                 return -1;
193             }
194             else
195             {
196                 return Convert.ToInt32(obj);
197             }
198         }
199 
200         /// <summary>
201         /// 删除一条数据
202         /// </summary>
203         public void Delete(int MenuId)
204         {
205             string sql = "delete from SYS_Menu where MenuId = " + MenuId;
206             ExecuteSql(sql);
207         }
208 
209         /// <summary>
210         /// 更新一条数据
211         /// </summary>
212         public void Update(ModelClass model)
213         {
214             StringBuilder strSql = new StringBuilder();
215             strSql.Append("update SYS_Menu set ");
216             strSql.Append("MenuName='" + model.MenuName + "',");
217             strSql.Append("MenuRemark='" + model.MenuRemark + "',");
218             strSql.Append("MenuUrl='" + model.MenuUrl + "',");
219             strSql.Append("ParentMenuId='" + model.ParentMenuId + "',");
220             strSql.Append("MenuIcon='" + model.MenuIcon + "',");
221             strSql.Append("MenuSort='" + model.MenuSort + "'");
222             strSql.Append(" where MenuId=" + model.MenuId);
223 
224             ExecuteSql(strSql.ToString());
225         }
226     }

三、窗体页面代码：

  1  public partial class MenuManager : Form
  2     {
  3         DataClass help = new DataClass();
  4         ModelClass model = new ModelClass();
  5         public MenuManager()
  6         {
  7             InitializeComponent();
  8         }
  9 
 10         /// <summary>
 11         /// 窗体载入事件
 12         /// </summary>
 13         /// <param name="sender"></param>
 14         /// <param name="e"></param>
 15         private void MenuManager_Load(object sender, EventArgs e)
 16         {
 17             QueryTreeView();//树型菜单
 18             ComBoxBindInit(); //上级菜单下拉列表初始化
 19         }
 20 
 21         /// <summary>
 22         /// 树型菜单查看事件
 23         /// </summary>
 24         /// <param name="sender"></param>
 25         /// <param name="e"></param>
 26         private void bntQuery_Click(object sender, EventArgs e)
 27         {
 28             QueryTreeView();//树型菜单
 29         }
 30 
 31         /// <summary>
 32         /// 树型菜单展示
 33         /// </summary>
 34         private void QueryTreeView()
 35         {
 36             BindListView(treeViewMenu);
 37             //展开所有节点
 38             treeViewMenu.ExpandAll();
 39             this.treeViewMenu.Nodes[0].EnsureVisible();//滚动打显示最上方
 40         }
 41 
 42         #region 网站树形菜单
 43         public void BindListView(TreeView treeView)
 44         {
 45             //清空树的所有节点
 46             treeView.Nodes.Clear();
 47             //创建根节点
 48             TreeNode rootNode = new TreeNode();
 49             rootNode.Text = "菜单列表";
 50             rootNode.Name = "0";
 51 
 52             //展开所有节点
 53             //添加根节点
 54             treeView.Nodes.Add(rootNode);
 55             // 获取所有节点信息
 56             DataTable dt = help.Query(" select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu Where 1=1 order by ParentMenuId , MenuSort asc  ").Tables[0];
 57             ////创建其他节点
 58             CreateChildNode(rootNode, dt);
 59         }
 60 
 61         private void CreateChildNode(TreeNode parentNode, DataTable dataTable)
 62         {
 63             DataRow[] rowList = dataTable.Select("ParentMenuId='" + parentNode.Name + "'");
 64             foreach (DataRow row in rowList)
 65             {   //创建新节点
 66                 TreeNode node = new TreeNode();
 67                 //设置节点的属性
 68                 node.Text = row["MenuName"].ToString();
 69                 node.Name = row["MenuId"].ToString();
 70                 parentNode.Nodes.Add(node);
 71                 //递归调用，创建其他节点
 72                 CreateChildNode(node, dataTable);
 73             }
 74         }
 75         #endregion
 76 
 77         /// <summary>
 78         /// 上级菜单初始化
 79         /// </summary>
 80         private void ComBoxBindInit()
 81         {
 82             DataTable dt = help.Query("select MenuId,MenuName,MenuRemark,MenuUrl,ParentMenuId,MenuIcon,MenuSort FROM SYS_Menu ").Tables[0];
 83 
 84             DataRow dr = dt.NewRow();
 85             dr["MenuName"] = "=============顶级菜单=============";
 86             dr["MenuId"] = 0;
 87             dt.Rows.InsertAt(dr, 0);
 88 
 89             comParentMenuId.DataSource = dt;
 90             comParentMenuId.DisplayMember = "MenuName";
 91             comParentMenuId.ValueMember = "MenuId";
 92            
 93         }
 94 
 95         /// <summary>
 96         /// 上级菜单重置（即查询所有菜单）
 97         /// </summary>
 98         /// <param name="sender"></param>
 99         /// <param name="e"></param>
100         private void bntInit_Click(object sender, EventArgs e)
101         {
102             txtQueryMenu.Text = "";
103             ComBoxBindInit();
104         }
105 
106         /// <summary>
107         /// 模糊查询菜单信息绑定上级菜单下拉列表
108         /// </summary>
109         /// <param name="sender"></param>
110         /// <param name="e"></param>
111         private void bntSelect_Click(object sender, EventArgs e)
112         {
113             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[0];
114 
115             DataRow dr = dt.NewRow();
116             dr["MenuName"] = "===无===";
117             dr["MenuId"] = 0;
118             dt.Rows.InsertAt(dr, 0);
119 
120             comParentMenuId.DataSource = dt;
121             comParentMenuId.DisplayMember = "MenuName";
122             comParentMenuId.ValueMember = "MenuId";
123         }
124 
125         /// <summary>
126         /// 选择节点取值
127         /// </summary>
128         /// <param name="sender"></param>
129         /// <param name="e"></param>
130         private void treeViewMenu_AfterSelect(object sender, TreeViewEventArgs e)
131         {
132             try
133             {
134                 if (e.Node.Text != "菜单列表")
135                 {
136                     ComBoxBindInit();
137                     model = help.GetModelList(Convert.ToInt32(e.Node.Name));
138                     txtMenuId.Text = model.MenuId.ToString();
139                     txtMenuName.Text = model.MenuName;
140                     txtMenuUrl.Text = model.MenuUrl;
141                     txtMenuSort.Text = model.MenuSort.ToString();
142                     txtMenuIcon.Text = model.MenuIcon;
143                     txtMenuRemark.Text = model.MenuRemark;
144                     comParentMenuId.SelectedValue = model.ParentMenuId;
145                 }
146                 else
147                 {
148                     ClearText();
149                 }
150             }
151             catch (Exception ex)
152             {
153                 MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
154             }
155         }
156 
157         /// <summary>
158         /// 清空文本内容
159         /// </summary>
160         private void ClearText()
161         {
162             txtMenuId.Text = "";
163             txtMenuName.Text = "";
164             txtMenuUrl.Text = "";
165             txtMenuSort.Text = "";
166             txtMenuIcon.Text = "";
167             txtMenuRemark.Text = "";
168         }
169 
170         /// <summary>
171         /// 添加
172         /// </summary>
173         /// <param name="sender"></param>
174         /// <param name="e"></param>
175         private void bntAdd_Click(object sender, EventArgs e)
176         {
177             try
178             {
179                 getModel();//为实体类赋值
180 
181                 #region 条件满足判断
182                 //菜单名称不能为空
183                 if (txtMenuName.Text == "")
184                 {
185                     MessageBox.Show("菜单名称不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
186                     return;
187                 }
188                 //同级菜单中不能有相同菜单名称
189                 if(help.Exists("select count(1) from SYS_Menu where MenuName='" + txtMenuName.Text + "' and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
190                 {
191                     MessageBox.Show("同级菜单中该名称已存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
192                     return;
193                 }
194                 //验证该级菜单是否有相同排序号
195                 if (help.Exists("select count(1) from SYS_Menu where MenuSort=" + Convert.ToInt32(txtMenuSort.Text.Trim()) + " and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
196                 {
197                     MessageBox.Show("该级菜单存在相同排序号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
198                     return;
199                 }
200                 //排序号必填
201                 if (txtMenuSort.Text == "")
202                 {
203                     MessageBox.Show("排序号不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
204                     return;
205                 }
206                 #endregion
207 
208                 int result = help.Add(model);//添加
209                 if (result >= 0)
210                 {
211                     MessageBox.Show("添加成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
212                     ClearText();
213                 }
214             }
215             catch (Exception ex)
216             {
217                 MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
218             }
219         }
220 
221         /// <summary>
222         /// 修改
223         /// </summary>
224         /// <param name="sender"></param>
225         /// <param name="e"></param>
226         private void bntUpdate_Click(object sender, EventArgs e)
227         {
228             try
229             {
230                 getModel();//为实体类赋值
231 
232                 #region 条件满足判断
233                 //菜单名称不能为空
234                 if (txtMenuName.Text == "")
235                 {
236                     MessageBox.Show("菜单名称不能为空", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
237                     return;
238                 }
239                 //同级菜单中不能有相同菜单名称
240                 if (help.Exists("select count(1) from SYS_Menu where MenuId <>" + model.MenuId + " and MenuName='" + txtMenuName.Text + "' and ParentMenuId=" + Convert.ToInt32(comParentMenuId.SelectedValue.ToString())))
241                 {
242                     MessageBox.Show("同级菜单中该名称已存在", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
243                     return;
244                 }
245                 //验证该级菜单是否有相同排序号
246                 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())))
247                 {
248                     MessageBox.Show("该级菜单存在相同排序号！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
249                     return;
250                 }
251                 //排序号必填
252                 if (txtMenuSort.Text == "")
253                 {
254                     MessageBox.Show("排序号不能为空！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
255                     return;
256                 }
257                 #endregion
258                 if (txtMenuId.Text != "" && help.Exists("select count(1) from SYS_Menu where MenuId=" + model.MenuId))
259                 {
260                     help.Update(model);
261                     MessageBox.Show("更新成功", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
262                 }
263                 else
264                 {
265                     MessageBox.Show("数据记录不存在！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
266                 }
267                
268             }
269             catch (Exception ex)
270             {
271                 MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
272             }
273         }
274 
275         /// <summary>
276         /// 删除
277         /// </summary>
278         /// <param name="sender"></param>
279         /// <param name="e"></param>
280         private void bntDelete_Click(object sender, EventArgs e)
281         {
282             try
283             {
284                 if (txtMenuId.Text == "")
285                 {
286                     MessageBox.Show("请选择所要删除的记录", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
287                     return;
288                 }
289                 //删除前先判断是否被其他菜单引用
290                 if (help.Exists("select count(1) from SYS_Menu where ParentMenuId=" + Convert.ToInt32(txtMenuId.Text)))
291                 {
292                     MessageBox.Show("该菜单被子级菜单引用，请先删除引用！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
293                     return;
294                 }
295                 if (MessageBox.Show("确定要删除该记录吗？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Information) == DialogResult.Yes)
296                 {
297                     help.Delete(Convert.ToInt32(txtMenuId.Text));
298                     MessageBox.Show("删除成功！", "提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
299                     ClearText();
300                 }
301             }
302             catch (Exception ex)
303             {
304                 MessageBox.Show(ex.Message, "提示", MessageBoxButtons.OK, MessageBoxIcon.Warning);
305             }
306         }
307 
308         /// <summary>
309         /// 清空
310         /// </summary>
311         /// <param name="sender"></param>
312         /// <param name="e"></param>
313         private void bntClear_Click(object sender, EventArgs e)
314         {
315             ClearText();
316         }
317 
318         /// <summary>
319         /// 排序号只能输入数字类型
320         /// </summary>
321         /// <param name="sender"></param>
322         /// <param name="e"></param>
323         private void txtMenuSort_KeyPress(object sender, KeyPressEventArgs e)
324         {
325             if (e.KeyChar != 8 && !Char.IsDigit(e.KeyChar))
326             {
327                 e.Handled = true;
328             }  
329         }
330 
331         /// <summary>
332         /// 为实体类赋值
333         /// </summary>
334         private void getModel()
335         {
336             if (txtMenuId.Text != "")
337                 model.MenuId = Convert.ToInt32(txtMenuId.Text);
338             model.MenuName = txtMenuName.Text.Trim();
339             model.MenuUrl = txtMenuUrl.Text.Trim();
340             if (txtMenuSort.Text != "")
341                 model.MenuSort = Convert.ToInt32(txtMenuSort.Text.Trim());
342             model.MenuIcon = txtMenuIcon.Text.Trim();
343             model.MenuRemark = txtMenuRemark.Text;
344             model.ParentMenuId = Convert.ToInt32(comParentMenuId.SelectedValue.ToString());
345         }
346         
347         /// <summary>
348         /// 光标处于上级菜单查询按下回车执行查询事件
349         /// </summary>
350         /// <param name="sender"></param>
351         /// <param name="e"></param>
352         private void txtQueryMenu_KeyPress(object sender, KeyPressEventArgs e)
353         {
354             if (e.KeyChar == 13)
355             {
356                 bntSelect_Click(sender, e);
357             }
358         }
359 
360        
361     }