C#连接MySQL

一、环境搭配

         安装MySQL，引用MySql.Data.DLL文件，这个MySql.Data.DLL文件在你安装Mysql的时候已经下载好给你的了。

        把它复制到项目的Debug目录下，然后引用即可。

二、连接使用步骤

        声明对象，有连接对象，语句执行对象，结果读取对象，在这我们要先引用一下MysqlClient。

1.  
   //连接对象
2.  
   MySqlConnection conn=null;
3.  
    
4.  
   //语句执行对象
5.  
   MySqlCommand comm=null;
6.  
   //语句执行结果数据对象
7.  
   MySqlDataReader dr = null;

        连接数据库

 conn = new MySqlConnection("Database = stu;Server = localhost;Port = 3306;Password = 123456;UserID = root;charset = utf8mb4");

        sql语句命令对象

comm = new MySqlCommand("select * from user",conn);

        执行语句获取数据

1.  
   dr = comm.ExecuteReader(); /*查询*/
2.  
   //dr = comm.ExecuteNonQuery(); /*增删改*/
3.  
   while (dr.Read())
4.  
   {
5.  
   tbText.Text = dr.GetString("对应表字段名称") "----" dr.GetString("password");
6.  
   tbText.Text = "\r";
7.  
   }
8.  
   dr.Close();
9.  
   conn.Close();

        注意使用完，我们要关闭掉连接资源。如果连接失败，可能是版本不对，活动平台要修改成x86的平台。

三、功能代码实现

         首先创建一个数据库，随便写入几条数据。

1.  
   CREATE TABLE `user` (
2.  
   `username` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
3.  
   `password` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL
4.  
   ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
5.  
    
6.  
   insert into user values('jack','sss');
7.  
   insert into user values('123','123');

        布局 有一个CheckedListBox控件，方便勾选删除。

        代码

1.  
   using System;
2.  
   using System.Collections.Generic;
3.  
   using System.ComponentModel;
4.  
   using System.Data;
5.  
   using System.Drawing;
6.  
   using System.Linq;
7.  
   using System.Text;
8.  
   using System.Threading.Tasks;
9.  
   using System.Windows.Forms;
10.  
    using MySql.Data.MySqlClient;
11.  
    using MySql.Data;
12.  
    namespace MysqlText
13.  
    {
14.  
    public partial class frm_main : Form
15.  
    {
16.  
    //连接对象
17.  
    MySqlConnection conn=null;
18.  
    //语句执行对象
19.  
    MySqlCommand comm=null;
20.  
    //语句执行结果数据对象
21.  
    MySqlDataReader dr = null;
22.  
    string strConn = "";
23.  
    public frm_main()
24.  
    {
25.  
    InitializeComponent();
26.  
    strConn = "Database = stu;Server = localhost;Port = 3306;Password = 123456;UserID = root;charset = utf8mb4";
27.  
    conn = new MySqlConnection(strConn);
28.  
    }
29.  
     
30.  
    /// <summary>
31.  
    /// 连接
32.  
    /// </summary>
33.  
    /// <param name="sender"></param>
34.  
    /// <param name="e"></param>
35.  
    private void btnConn_Click(object sender, EventArgs e)
36.  
    {
37.  
    //判断连接状态
38.  
    if (conn.State != ConnectionState.Open)
39.  
    {
40.  
    conn.Open();
41.  
    tbText.Text = strConn;
42.  
    label4.Text = "";
43.  
    label4.Text = "连接成功";
44.  
    }
45.  
    }
46.  
    /// <summary>
47.  
    /// 查询
48.  
    /// </summary>
49.  
    /// <param name="sender"></param>
50.  
    /// <param name="e"></param>
51.  
    private void btnSel_Click(object sender, EventArgs e)
52.  
    {
53.  
    //判断连接状态
54.  
    if (!CkeckConn())
55.  
    {
56.  
    MessageBox.Show("请连接数据库");
57.  
    return;
58.  
    }
59.  
    comm = new MySqlCommand("select * from user", conn);
60.  
    tbText.Text = "";
61.  
    dr = comm.ExecuteReader(); /*查询*/
62.  
    while (dr.Read())
63.  
    {
64.  
    tbText.Text = dr.GetString("username") "----" dr.GetString("password");
65.  
    tbText.Text = "\r\n";
66.  
    }
67.  
    dr.Close();
68.  
    ckLBoxsRefresh();
69.  
    }
70.  
     
71.  
    /// <summary>
72.  
    /// 添加
73.  
    /// </summary>
74.  
    /// <param name="sender"></param>
75.  
    /// <param name="e"></param>
76.  
    private void btnAdd_Click(object sender, EventArgs e)
77.  
    {
78.  
    //判断连接状态
79.  
    if (!CkeckConn())
80.  
    {
81.  
    MessageBox.Show("请连接数据库");
82.  
    return;
83.  
    }
84.  
    label4.Text = "";
85.  
    //先判断用户是否已注册
86.  
    if (tbUser.Text =="" || tbPW.Text=="")
87.  
    {
88.  
    label4.Text = "请完善信息";
89.  
    return;
90.  
    }
91.  
    comm = new MySqlCommand("select * from user where username = '" tbUser.Text "'", conn);
92.  
    dr = comm.ExecuteReader();
93.  
    if (dr.Read())
94.  
    {
95.  
    label4.Text = "已存在用户" tbUser.Text;
96.  
    }
97.  
    else
98.  
    {
99.  
    dr.Close();
100.  
     int num = 0;
101.  
     comm = new MySqlCommand("insert into user values('" tbUser.Text "','" tbPW.Text "')", conn);
102.  
     num = comm.ExecuteNonQuery();
103.  
     if (num > 0)
104.  
     {
105.  
     label4.Text = "已添加用户" tbUser.Text;
106.  
     ckLBoxsRefresh();
107.  
     tbText.Text = "";
108.  
      
109.  
     }
110.  
     else
111.  
     {
112.  
     label4.Text = "添加失败";
113.  
     }
114.  
     }
115.  
     dr.Close();
116.  
     }
117.  
     /// <summary>
118.  
     /// 修改
119.  
     /// </summary>
120.  
     /// <param name="sender"></param>
121.  
     /// <param name="e"></param>
122.  
     private void btnUp_Click(object sender, EventArgs e)
123.  
     {
124.  
     //判断连接状态
125.  
     if (!CkeckConn())
126.  
     {
127.  
     MessageBox.Show("请连接数据库");
128.  
     return;
129.  
     }
130.  
     label4.Text = "";
131.  
     //先判断用户是否已注册
132.  
     comm = new MySqlCommand("select * from user where username = '" tbUpUser.Text "'", conn);
133.  
     dr = comm.ExecuteReader();
134.  
     if (dr.Read())
135.  
     {
136.  
     dr.Close();
137.  
     int num = 0;
138.  
     comm = new MySqlCommand("update user set password = '" tbUpPW.Text "'where username = '" tbUpUser.Text "'", conn);
139.  
     num = comm.ExecuteNonQuery();
140.  
     if (num > 0)
141.  
     {
142.  
     label4.Text = "已修改用户" tbUpUser.Text "密码";
143.  
     tbText.Text = "";
144.  
     }
145.  
     else
146.  
     {
147.  
     label4.Text = "修改失败";
148.  
     }
149.  
     }
150.  
     else
151.  
     {
152.  
     label4.Text = "用户不存在";
153.  
     }
154.  
     dr.Close();
155.  
     }
156.  
      
157.  
     /// <summary>
158.  
     /// 删除
159.  
     /// </summary>
160.  
     /// <param name="sender"></param>
161.  
     /// <param name="e"></param>
162.  
     private void btnDel_Click(object sender, EventArgs e)
163.  
     {
164.  
     //判断连接状态
165.  
     if (!CkeckConn())
166.  
     {
167.  
     MessageBox.Show("请连接数据库");
168.  
     return;
169.  
     }
170.  
     label4.Text = "";
171.  
     //查找选中
172.  
     for (int i = 0; i < ckLBoxs.Items.Count; i )
173.  
     {
174.  
     if (ckLBoxs.GetItemChecked(i))
175.  
     {
176.  
     int num = 0;
177.  
     comm = new MySqlCommand("delete from user where username = '" ckLBoxs.Items[i].ToString() "'", conn);
178.  
      
179.  
     num = comm.ExecuteNonQuery();
180.  
     if (num > 0)
181.  
     {
182.  
     label4.Text = "已删除用户" ckLBoxs.Items[i].ToString() "\t";
183.  
     ckLBoxsRefresh();
184.  
     tbText.Text = "";
185.  
     }
186.  
     else
187.  
     {
188.  
     label4.Text = "用户不存在";
189.  
     }
190.  
     }
191.  
     }
192.  
     }
193.  
     /// <summary>
194.  
     /// 判断连接
195.  
     /// </summary>
196.  
     /// <returns></returns>
197.  
     private bool CkeckConn()
198.  
     {
199.  
     if (conn.State ==ConnectionState.Open)
200.  
     {
201.  
     return true;
202.  
     }
203.  
     else
204.  
     {
205.  
     return false;
206.  
     }
207.  
     }
208.  
     /// <summary>
209.  
     /// 列表更新
210.  
     /// </summary>
211.  
     private void ckLBoxsRefresh()
212.  
     {
213.  
     //判断连接状态
214.  
     if (!CkeckConn())
215.  
     {
216.  
     MessageBox.Show("请连接数据库");
217.  
     return;
218.  
     }
219.  
     comm = new MySqlCommand("select * from user", conn);
220.  
     dr = comm.ExecuteReader();
221.  
     ckLBoxs.Items.Clear();
222.  
     int num = 0;
223.  
     while (dr.Read())
224.  
     {
225.  
     ckLBoxs.Items.Add(dr.GetString(0));
226.  
     num ;
227.  
     }
228.  
     label4.Text = "";
229.  
     label4.Text = "已更新数据" num.ToString() "条。";
230.  
     dr.Close();
231.  
     }
232.  
     /// <summary>
233.  
     /// 退出
234.  
     /// </summary>
235.  
     /// <param name="sender"></param>
236.  
     /// <param name="e"></param>
237.  
     private void btn_Exit_Click(object sender, EventArgs e)
238.  
     {
239.  
     this.Close();
240.  
     }
241.  
     /// <summary>
242.  
     /// 关闭
243.  
     /// </summary>
244.  
     /// <param name="sender"></param>
245.  
     /// <param name="e"></param>
246.  
     private void frm_main_FormClosing(object sender, FormClosingEventArgs e)
247.  
     {
248.  
     if (conn !=null )
249.  
     {
250.  
     if (conn.State != ConnectionState.Closed)
251.  
     {
252.  
     conn.Close();
253.  
     }
254.  
     }
255.  
     if (dr !=null)
256.  
     {
257.  
     dr.Close();
258.  
     }
259.  
     }
260.  
     }
261.  
     }

 四、效果

        连接

        查询

        添加

        修改

        删除

C#连接mysql以及CRUD的实现就这样，如有什么问题或者交流可以留言或私信me。

这篇好文章是转载于：学新通技术网