第一次写的MySQLHelper

一、　第一次写MysqlHelper，用来管理城市的数据库

二、MySQLHelper源代码

using 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();

                        model1.cityid = reader.GetString();

                        model1.city = reader.GetString();

                        model1.provinceid = reader.GetString();

                        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();

                    model.provinceid = reader.GetString();

                    model.province = reader.GetString();

                    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();

                        model1.areaid = reader.GetString();

                        model1.area = reader.GetString();

                        model1.cityid = reader.GetString();

                        list.Add(model1);

                    }

                    comboBox3.ValueMember = "areaid";

                    comboBox3.DisplayMember = "area";

                    comboBox3.DataSource = list;

                }

            }

        }

    }

}

四、APP.config的配置

<?xml version="1.0" encoding="utf-8" ?>

<configuration>

    <startup>

        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.5.2" />

    </startup>

  <connectionStrings>

    <add name="sqlserver" connectionString="Data Source=localhost;Initial Catalog=zone;User ID=admin;Password=123456"/>

  </connectionStrings>

</configuration>

巴特西

第一次写的MySQLHelper

最新文章

热门文章