第一次写的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(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; } } } } }
四、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>