C#连接mysql数据库
一、说明
C#连接mysql数据库有多种方法,这里详细说明采用Mysql.Data.dll组件方法。
Mysql.Data.dll下载地址http://pan.baidu.com/s/1nt0n0RB
将该程序集添加至程序引用中。
二、程序
集成在一个类中:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
using System.Data;
using System.Windows.Forms;
namespace ShoppingServer
{
public class DBHelper
{
/// <summary>
/// 得到连接对象
/// </summary>
/// <returns>MySqlConnection</returns>
public MySqlConnection GetConn()
{
String constr = "Database='shoppingsystem';UserId='root';DataSource='localhost';Password='1152734'";
MySqlConnection mysqlconn = new MySqlConnection(constr);
return mysqlconn;
}
}
public class SQLHelper : DBHelper
{
/// <summary>
/// 查询操作
/// </summary>
/// <param name="sql"></param>
/// <returns>DataTable</returns>
public DataTable Selectinfo(string sql)
{
MySqlConnection mysqlconn = null;
MySqlDataAdapter sda = null;
DataTable dt = null;
try
{
mysqlconn = base.GetConn();
sda = new MySqlDataAdapter(sql, mysqlconn);
dt = new DataTable();
sda.Fill(dt);
return dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return dt;
}
}
/// <summary>
/// 增删改操作
/// </summary>
/// <param name="sql">sql语句</param>
/// <returns>执行后的条数</returns>
public int AddDelUpdate(string sql)
{
MySqlConnection conn = null;
MySqlCommand cmd = null;
try
{
conn = base.GetConn();
conn.Open();
cmd = new MySqlCommand(sql, conn);
int i = cmd.ExecuteNonQuery();
conn.Close();
return i;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return -1;
}
}
}
}
主程序调用:
SQLHelper helper = new SQLHelper();
private void MyForm_Load(object sender, EventArgs e)
{
string name = "";
string sql = string.Format("select * from shoppingsystem.goods");
DataTable dt = helper.Selectinfo(sql);
for (int i = 0; i < dt.Rows.Count; i++)
{
//从数据库得到stuname 字段的值
name += (string)dt.Rows[i]["name"] + "\r\n";
}
//把得到的值放到文本框中
this.richTextBox1.Text = name;
}
private void btnAdd_Click(object sender, EventArgs e)
{
string sql = string.Format("insert into userinfo values('{0}','{1}')", this.txtname.Text, txtpwd.Text);
int i = helper.AddDelUpdate(sql); if (i > 0)
{
MessageBox.Show("增加成功");
}
}