第一次写的MySQLHelper

MySql.Data.MySqlClient; using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.Linq; using System.Text; using System.Threading.Tasks; namespace Cater0718 { public static class MySqlHelper { //定义一个连接字符串 //readonly修饰的变量，只能在初始化的时候赋值，或者在构造函数中赋值 //其它地方只能读取，不能修改字符串 private static readonly string constr = ConfigurationManager.ConnectionStrings["sqlserver"].ConnectionString; //1、执行增(insert)、删(delete)、改(update)的方法 //cmd.ExecuteNonQuery() public static int ExecuteNonQuery(string sql, params SqlParameter[] pms) { using (MySqlConnection con = new MySqlConnection(constr)) { using (MySqlCommand cmd = new MySqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteNonQuery(); } } } //2、执行查询，返回单个结果的方法 //cmd.ExecuteSclar() public static Object ExecuteSclar(string sql, params SqlParameter[] pms) { using (MySqlConnection con = new MySqlConnection(constr)) { using (MySqlCommand cmd = new MySqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } con.Open(); return cmd.ExecuteScalar(); } } } //3、执行查询，返回多行多列结果的方法 //cmd.ExecuteReader() public static MySqlDataReader ExecuteReader(string sql, params MySqlParameter[] pms) { MySqlConnection con = new MySqlConnection(constr); using (MySqlCommand cmd = new MySqlCommand(sql, con)) { if (pms != null) { cmd.Parameters.AddRange(pms); } try { con.Open(); return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection); } catch { con.Close(); con.Dispose(); throw; } } } } }

三、定义的实例类

　　1、省份

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

namespace Cater0718
{
    public class zProvinces
    {
        public int id { get; set; }
        public string provinceid { get; set; }
        public string province { get; set; }
    }
}

　　2、城市

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

namespace Cater0718
{
    public class zCity
    {
        public int id { get; set; }
        public string cityid { get; set; }
        public string city { get; set; }
        public string provinceid { get; set; }
    }
}

　　3、地区

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

namespace Cater0718
{
    public class zArea
    {
        public int id { get; set; }
        public string areaid { get; set; }
        public string area { get; set; }
        public string cityid { get; set; }
    }
}

四、最后用WinForm写的窗体

using MySql.Data.MySqlClient;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace Cater0718
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox1.SelectedItem != null)
            {
                string provinceid = comboBox1.SelectedValue.ToString();

                List<zCity> list = new List<zCity>();
                string sql = "select * from cities where provinceid=@provinceid";
                MySqlParameter p1 = new MySqlParameter("@provinceid",MySqlDbType.String) {Value=provinceid };
                using (MySqlDataReader reader = MySqlHelper.ExecuteReader(sql,p1))
                {
                    while (reader.Read())
                    {
                        zCity model1 = new zCity();
                        model1.id = reader.GetInt16(0);
                        model1.cityid = reader.GetString(1);
                        model1.city = reader.GetString(2);
                        model1.provinceid = reader.GetString(3);

                        list.Add(model1);
                    }
                    comboBox2.ValueMember = "cityid";
                    comboBox2.DisplayMember = "city";
                    comboBox2.DataSource = list;
                }
            }
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            LoadProvince();
        }

        private void LoadProvince()
        {
            List<zProvinces> list = new List<zProvinces>();
            string sql = "select * from provinces";
            using (MySqlDataReader reader = MySqlHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    zProvinces model = new zProvinces();
                    model.id = reader.GetInt16(0);
                    model.provinceid = reader.GetString(1);
                    model.province = reader.GetString(2);

                    list.Add(model);
                }
                comboBox1.ValueMember = "provinceid";
                comboBox1.DisplayMember = "province";
                comboBox1.DataSource = list;
            }
        }

        private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (comboBox2.SelectedItem != null)
            {
                string cityid = comboBox2.SelectedValue.ToString();

                List<zArea> list = new List<zArea>();
                string sql = "select * from areas where cityid=@cityid";
                MySqlParameter p1 = new MySqlParameter("@cityid", MySqlDbType.String) { Value = cityid };
                using (MySqlDataReader reader = MySqlHelper.ExecuteReader(sql, p1))
                {
                    while (reader.Read())
                    {
                        zArea model1 = new zArea();
                        model1.id = reader.GetInt16(0);
                        model1.areaid = reader.GetString(1);
                        model1.area = reader.GetString(2);
                        model1.cityid = reader.GetString(3);

                        list.Add(model1);
                    }
                    comboBox3.ValueMember = "areaid";
                    comboBox3.DisplayMember = "area";
                    comboBox3.DataSource = list;
                }
            }
        }
    }
}

 

第一次写的MySQLHelper

标签：gen   row   window   comm   初始化   pos   form   eai   manage