递归treeview的使用（将省市信息显示到treeview上）

最新推荐文章于 2022-07-20 08:05:54 发布

卿月晟

最新推荐文章于 2022-07-20 08:05:54 发布

阅读量461

点赞数

CC 4.0 BY-SA版权

文章标签： WinForm

本文链接：https://blog.youkuaiyun.com/gao3705512/article/details/8847178

本文介绍了一个使用C#实现的SQL帮助类，用于执行SQL操作并从数据库中获取省市信息。通过实例化Area类来存储数据，并展示了如何将这些数据绑定到TreeView控件上以实现层次化的显示。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一个sqlhelper类：

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace 将省市信息先是到treeview上
{
   public static  class SqlHelper
    {

        private static readonly string constr = ConfigurationManager.ConnectionStrings["sql"].ConnectionString;

        /// <summary>
        /// 执行insert，delete，update的方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>

        public static int ExecuteNonQuery(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //如果pms为null则直接调用cmd.Parameters.AddRange(pms);会报错
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteNonQuery();

                }

            }
        }
        /// <summary>
        /// 执行sql语句，返回单个值
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, params SqlParameter[] pms)
        {
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    //如果pms为null则直接调用cmd.Parameters.AddRange(pms);会报错
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    return cmd.ExecuteScalar();

                }

            }

        }
        public static SqlDataReader ExecuteReader(string sql, params SqlParameter[] pms)
        {
            SqlConnection con = new SqlConnection(constr);
            try
            {



                using (SqlCommand cmd = new SqlCommand(sql, con))
                {
                    if (pms != null)
                    {
                        cmd.Parameters.AddRange(pms);
                    }
                    con.Open();
                    //当调用ExecuteReader（）方法的时候。如果传递一个CommandBehavior.CloseConnection参数，则表示将来当用户关闭reader的时候。系统会自动关闭连接
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    return reader;

                }
            }
            catch
            {
                if (con != null)
                {
                    con.Close();
                    con.Dispose();
                }
                throw;
            }

        }
        /// <summary>
        /// 封装一个返回的datatable方法
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="pms"></param>
        /// <returns></returns>
        public static DataTable ExecuteDataTable(string sql, params SqlParameter[] pms)
        {
            SqlDataAdapter sqlAdapter = new SqlDataAdapter(sql, constr);
            if (pms != null)
            {
                sqlAdapter.SelectCommand.Parameters.AddRange(pms);
            }
            DataTable dt = new DataTable();
            sqlAdapter.Fill(dt);
            return dt;
        }


    }
}

然后是area类（用来存放数据的）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace 将省市信息先是到treeview上
{
    public class Area
    {
        public int AreaId
        {
            get;
            set;

        }
        public string AreaName
        {
            get;
            set;
        }
        public int AreaPId
        {
            get;
            set;
        }
    }
}

然后是form界面：

紧接着是form代码：

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace 将省市信息先是到treeview上
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            List<Area> list = GetDataByParentId(0);
            //将list中的数据绑定到treeview上
            LoadDateToTree(treeView1.Nodes, list);

        }

        private void LoadDateToTree(TreeNodeCollection treeNodeCollection, List<Area> list)
        {
            foreach (Area item in list)
            {
                TreeNode tnode = treeNodeCollection.Add(item.AreaName);
                //记录当前区域的ID

                tnode.Tag = item.AreaId;
                //获取当前ID对应的所有子城市
                List<Area> listSub = GetDataByParentId(item.AreaId);
                LoadDateToTree(tnode.Nodes, listSub);
            }
        }
        /// <summary>
        /// 根据父ID获取该区域下面的所有子区域
        /// </summary>
        /// <param name="p"></param>
        /// <returns></returns>
        private List<Area> GetDataByParentId(int pid)
        {
            List<Area> list = new List<Area>();
            string sql = "select * from  province where proid=@pid";
            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@pid", pid)))
            {
                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Area model = new Area();
                        model.AreaId = reader.GetInt32(0);
                        model.AreaName = reader.GetString(1);
                        model.AreaPId = reader.GetInt32(2);
                        list.Add(model);

                    }
                }
                return list;
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {

        }
    }
}