第20课-数据库开发及ado.net 可空值类型,资料管理器,多条件查询,Case

第20课-数据库开发及ado.net

可空值类型,资料管理器,多条件查询,Case

SqlHelper

using System;

using System.Collections.Generic;

using System.Configuration;

using System.Data;

using System.Data.SqlClient;

using System.Text;

namespace _02省市联动

{

   public static  class SqlHelper

    {

       //常量 无法修改

       private const string A = "aaa";

       //readonly 是一个只读的变量,只能在声明或构造函数中修改

       //只读变量只能在构造函数或者是一开始声明变量的时候赋值，不允许在其它地方为变量赋值。

       //连接字符串

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

    

     

       /// <summary>

       /// 封装一个ExecuteNonQuery方法,执行insert、delete、updata

       /// </summary>

       /// <param name="sql"></param>

       /// <param name="pms"></param>

       /// <returns></returns>

       public static int ExecuteQuery(string sql,params SqlParameter[] pms)

       {

           using (SqlConnection con=new SqlConnection (conStr))

           {

               using (SqlCommand com=new SqlCommand (sql,con))

               {

                   if (pms!=null)

                   {

                       com.Parameters.AddRange(pms);

                   }

                   con.Open();

                   return com.ExecuteNonQuery();

               }

           }

       }

       /// <summary>

       /// 封装返回一个单个值的方法

       /// </summary>

       /// <returns></returns>

       public static object ExecuteScalar(string sql,params SqlParameter[] pms)

       {

           using (SqlConnection con = new SqlConnection(conStr))

           {

               using (SqlCommand com=new SqlCommand (sql,con))

               {

                   if (pms!=null)

                   {

                       com.Parameters.AddRange(pms);

                   }

                   con.Open();

                   return com.ExecuteScalar();

               }

           }

       }

       /// <summary>

       /// 封装一个返回SqlDataReader的方法

       /// </summary>

       /// <returns></returns>

       public static SqlDataReader  ExecuteReader(string sql,params SqlParameter[] pms)

       {

              SqlConnection con=new SqlConnection (conStr);

               using (SqlCommand com=new SqlCommand (sql,con))

               {

                   if (pms!=null)

                   {

                       com.Parameters.AddRange(pms);

                   }

                   try

                   {

                       con.Open();

                       return com.ExecuteReader(System.Data.CommandBehavior.CloseConnection);   

                   }

                   catch (Exception)

                   {

                       con.Close();

                       con.Dispose();

                       throw;

                   }

                   

                }

       }

       public static DataTable ExecuteDataTable(string sql,params SqlParameter[] pms)

       {

           DataTable dt = new DataTable();

           using (SqlDataAdapter adapter=new SqlDataAdapter (sql,conStr ))

           {

               //如何添加参数

               adapter.Fill(dt);

           }

           return dt;

       }

    }

}

省市联动

using System;

using System.Collections.Generic;

using System.Text;

 

namespace _02省市联动

{

    public  class ProvinceItem

    {

        public int AreaId {get ;set;}

        public string AreaName{get ;set;}

        public int AreaPid{get ;set;}

        public override string ToString()

        {

            return this.AreaName;

        }

    }

}

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

 

namespace _02省市联动

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

        private void Form1_Load(object sender, EventArgs e)

        {

           //加载省份信息到第一个ComboBox

            LoadProvince();

            //设置俩个下拉菜单的默认值为“请选择”

            comboBox1.SelectedIndex = 0;

            comboBox2.SelectedIndex = 0;

       

        }

        private void LoadProvince()

        {

            string sql = "select * from TblArea where AreaPId  =0";

            using (SqlDataReader reader=SqlHelper .ExecuteReader(sql))

            {

                {

                if (reader .HasRows)

                    while(reader .Read())

                    {

                        ProvinceItem item = new ProvinceItem();

                        item.AreaId = reader.GetInt32(0);

                        item.AreaName = reader.GetString(1);

                        item.AreaPid = reader.GetInt32(2);

                        comboBox1.Items.Add(item);

                    }

                }

            }

            //为ComboBox 增加一个“请选择”

            ProvinceItem itemDefault = new ProvinceItem();

            itemDefault.AreaId = -1;

            itemDefault.AreaName = "请选择";

            comboBox1.Items.Insert(0, itemDefault);

        }

        /// <summary>

        /// 下拉菜单的选择项改变事件

        /// </summary>

        /// <param name="sender"></param>

        /// <param name="e"></param>

        private void comboBox1_SelectedValueChanged(object sender, EventArgs e)

        {

            //获取当前用户选择的项

            if (comboBox1.SelectedIndex > 0)

            {

                //加载第二个下来菜单，数据来源：根据第一个下拉菜单用户选项的AeraId来查询该项的所有子项

                //获取当前选中项的ID

                ProvinceItem item=comboBox1.SelectedItem as ProvinceItem;

                int areaId = item.AreaId;

                LoadCity(areaId);

            }

        }

        private void LoadCity(int areaId)

        {

            comboBox2.Items.Clear();

            string sql = "select * from TblArea where AreaPId=@aid";

            using (SqlDataReader reader = SqlHelper.ExecuteReader(sql, new SqlParameter("@aid", areaId)))

            {

                if (reader .HasRows )

                {

                    while (reader.Read())

                    {

                         ProvinceItem item = new ProvinceItem();

                        item.AreaId = reader.GetInt32(0);

                        item.AreaName = reader.GetString(1);

                        item.AreaPid = reader.GetInt32(2);

                        comboBox2.Items.Add(item);

                    }

                }

            }

            //也添加一个“请选择”

            ProvinceItem itemDefault = new ProvinceItem();

            itemDefault.AreaId = -1;

            itemDefault.AreaName = "请选择";

            comboBox2.Items.Insert(0, itemDefault);

            comboBox2.SelectedIndex = 0;

        }

    }

}

省市数据递归加载到TreeView

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.SqlClient;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

 

namespace _03省市数据递归加载到TreeView

{

    public partial class Form1 : Form

    {

        public Form1()

        {

            InitializeComponent();

        }

 

        private void button1_Click(object sender, EventArgs e)

        {

            ////1.把所有的省份及直辖市加载到TreeView的根节点上

            //List<Area> listProvince = GetSubItemByParentId(0);

            //foreach (Area  item in listProvince)

            //{

            //    treeView1.Nodes.Add(item .AreaName );

            //}

            //递归将省市加载到TreeView中

            LoadDataToTree(treeView1.Nodes,0);

 

 

        }

 

        private void LoadDataToTree(TreeNodeCollection treeNodeCollection, int pid)

        {

            //1.根据指定的Pid获取该城市下的子城市

            List<Area> listCity = GetSubItemByParentId(pid);

            //2.遍历将获取到的数据绑定到TreeNodeCollection节点集合中

            foreach (var item in listCity )

            {

                //返回刚刚添加的这个节点

                TreeNode node = treeNodeCollection.Add(item.AreaName);

                LoadDataToTree(node.Nodes ,item.Areaid);

            }

        }

 

        //封装一个方法，根据父ID查询下面的所有子项

        private List<Area> GetSubItemByParentId(int pid)

        {

            List<Area> list = new List<Area>();

            string sql = "select * from TblArea where areaPid=@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;

        }

    }

}

using System;

using System.Collections.Generic;

using System.Text;

 

namespace _03省市数据递归加载到TreeView

{

   public  class Area

    {

       public int Areaid { get; set; }

       public string AreaName { get; set; }

       public int AreaPid { get; set; }

    }

}

 

转载于:https://www.cnblogs.com/Time_1990/p/5862305.html