一、界面
二、代码
1.数据库工具类
namespace SqlHelper
{
public static class SqlHelper
{
//定义一个连接字符串
//readonly修饰的变量,只能在初始化的时候赋值,以及在构造函数中赋值
//其他地方只能读取不能设置值。
private static readonly string constr = ConfigurationManager.ConnectionStrings["MySqlCon"].ConnectionString;
//1.执行增(insert)、删(delete)、改(update) 的方法
//ExecuteNonQuery()
public static int ExecuteNonQuery(string sql, CommandType cmdType, params MySqlParameter[] parms)
{
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
con.Open();
return cmd.ExecuteNonQuery();
}
}
}
//2.执行查询,返回单个值的方法
//ExecuteScalar()
public static object ExecuteScalar(string sql, CommandType cmdType, params MySqlParameter[] parms)
{
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
con.Open();
return cmd.ExecuteScalar();
}
}
}
//3.执行查询 ,返回多行,多列的方法
//ExecuteReader()
public static MySqlDataReader ExecuteReader(string sql, CommandType cmdType, params MySqlParameter[] parms)
{
MySqlConnection con = new MySqlConnection(constr);
using (MySqlCommand cmd = new MySqlCommand(sql, con))
{
cmd.CommandType = cmdType;
if (parms != null)
{
cmd.Parameters.AddRange(parms);
}
try
{
if (con.State == ConnectionState.Closed)
{
con.Open();
}
//system.Data.CommandBehavior.closeconnection这个枚举参数,表示将来使用完毕
//sqlDataReader后,在关闭reader的同时,在sqlDataReader内部会将关联的connection对象也关闭掉
return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
}
catch
{
con.Close();
con.Dispose();
throw;
}
}
}
//4.查询数据返回DataTable
public static DataTable ExecuteDataTable(string sql, CommandType cmdType, params MySqlParameter[] pms)
{
DataTable dt = new DataTable();
using (MySqlDataAdapter adapter = new MySqlDataAdapter(sql, constr))
{
adapter.SelectCommand.CommandType = cmdType;
if (pms != null)
{
adapter.SelectCommand.Parameters.AddRange(pms);
}
adapter.Fill(dt);
}
return dt;
}
}
}
2.实体类
namespace SqlHelper
{
class Hello
{
public int ID { get; set; }
public string Name { get; set; }
public string Score { get; set; }
public string Date { get; set; }
public string Shop { get; set; }
public override string ToString()
{
return this.Name;
}
}
}
class Category
{
public int TID { get; set; }
public string TName { get; set; }
public int TParentId { get; set; }
public string TNode { get; set; }
}
3.窗体代码
Form3 窗体
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
namespace SqlHelper
{
public partial class Form3 : Form
{
public Form3()
{
InitializeComponent();
}
//窗体加载事件
private void Form3_Load(object sender, EventArgs e)
{
UpdateTreeView();
}
private void UpdateTreeView()
{
//清空
treeView1.Nodes.Clear();
//读取Category数据递归加载到TreeView上
LoadDataToTree(0, treeView1.Nodes);
}
private void LoadDataToTree(int pid, TreeNodeCollection treeNodeCollection)
{
//现根据父ID查询下面的数据
List<Category> list = GetDataByParentId(pid);
//遍历list中的数据,把这些数据加载到treeNodeCollection节点集合中
foreach (Category item in list)
{
TreeNode node = treeNodeCollection.Add(item.TName);
node.Tag = item.TID;
LoadDataToTree(item.TID,node.Nodes);
}
}
//根据tParentID查询数据
private List<Category> GetDataByParentId(int pid)
{
List<Category> list = new List<Category>();
string sql = "select * from category where tParentId=@pid";
MySqlParameter id=new MySqlParameter("@pid", MySqlDbType.Int32){ Value = pid };
using (MySqlDataReader reader = SqlHelper.ExecuteReader(sql,CommandType.Text,id))
{
if(reader.HasRows)
{
while(reader.Read())
{
Category model = new Category();
model.TID = reader.GetInt32(0);
model.TName = reader.GetString(1);
list.Add(model);
}
}
}
return list;
}
//节点鼠标双击事件
private void treeView1_NodeMouseDoubleClick(object sender, TreeNodeMouseClickEventArgs e)
{
//1.获取用户点击的是那个节点
int categoryID=(int)e.Node.Tag;
//2.然后获取节点的ID
//3.查询指定ID下的所有的文章信息
LoadContentInfoByCategoryId(categoryID);
}
//根据文章类别,查询下面的所有的相关信息
private void LoadContentInfoByCategoryId(int categoryID)
{
listBox1.Items.Clear();
string sql = "select id,name from hello where id=@categoryID";
MySqlParameter parms = new MySqlParameter("@categoryID", MySqlDbType.Int32) {Value=categoryID };
using(MySqlDataReader reader=SqlHelper.ExecuteReader(sql,CommandType.Text,parms))
{
if (reader.HasRows)
{
while (reader.Read())
{
Hello model= new Hello();
model.ID = reader.GetInt32(0);
model.Name = reader.GetString(1);
//在listBox1中放入数据
listBox1.Items.Add(model);
}
}
}
}
//选中标题改变事件
private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
{
//1.获取当前选中行的ID
if (this.listBox1.SelectedItem != null)
{
//获取当前选中的文章的ID
int id = (this.listBox1.SelectedItem as Hello).ID;
//根据ID查询对应的信息,并显示到下面的文本框中
this.textBox1.Text = GetContentByID(id);
}
}
private string GetContentByID(int id)
{
string sql = "select shop from hello where id=@id";
MySqlParameter pid = new MySqlParameter("@id", MySqlDbType.Int32) { Value = id };
object objContent=SqlHelper.ExecuteScalar(sql,CommandType.Text, pid);
return objContent == null ? string.Empty : objContent.ToString();
}
//增加一级类别
private void ToolStripMenuItem_Click(object sender, EventArgs e)
{
AddForm fa = new AddForm(0,UpdateTreeView);
fa.Show();
}
//增加一个子类别
private void 编辑ToolStripMenuItem_Click(object sender, EventArgs e)
{
//判断当前TreeView 是否有选中类别
if (treeView1.SelectedNode != null)
{
//获取当前TreeView是选中类别的ID
int cateID=(int)treeView1.SelectedNode.Tag;
AddForm fa = new AddForm(cateID,UpdateTreeView);
fa.Show();
}
else
{
MessageBox.Show("请选择类别");
}
}
}
}
AddForm 窗体
namespace SqlHelper
{
public partial class AddForm : Form
{
private int parentId;
public AddForm()
{
InitializeComponent();
}
private Action _method;
public AddForm(int p,Action method):this()
{
// TODO: Complete member initialization
this.parentId = p;
this._method = method;
}
//增加类别
private void btnAdd_Click(object sender, EventArgs e)
{
//1.采集数据
string categoryName = txtCateName.Text.Trim();
string note = txtCateContent.Text.Trim();
//2.执行插入操作
string sql = "insert into category(tName,tParentId,tNote) values(@tName,@tParentId,@tNote)";
MySqlParameter[] parms = new MySqlParameter[]
{
new MySqlParameter("@tName",MySqlDbType.VarChar){Value=categoryName},
new MySqlParameter("@tNote",MySqlDbType.VarChar){Value=note},
new MySqlParameter("@tParentId",MySqlDbType.Int32){Value=this.parentId}
};
//3.执行sql
SqlHelper.ExecuteNonQuery(sql,CommandType.Text,parms);
if(this._method !=null)
{
//刷新父窗口中的TreeView
this._method();
}
//4.关闭当前窗体
this.Close();
}
}
}
4.数据库表设计