C#-连接MYSQL数据库的3种方法及示例

C# 连接MYSQL数据库的方法及示例

连接MYSQL数据库的方法及示例

方法一：

using MySql.Data

using MySql.Data.MySqlClient;

其他操作跟SQL是差不多,无非就是前缀变成MySql了.

补充:

下面是连接字符串,供参考.

MySqlConnection con = new

MySql.Data.MySqlClient.MySqlConnection("Database='testdb';Data

Source='localhost';User Id='db';Password='apple';charset='utf8'");

con.Open();

MySqlCommand cmd = new MySqlCommand();

cmd.Connection = con;

使用MYSQL推出的MySQL Connector/Net is an https://www.360docs.net/doc/0b5681637.html, driver for MySQL

该组件为MYSQL为https://www.360docs.net/doc/0b5681637.html,访问MYSQL数据库设计的.NET访问组件。

安装完成该组件后，引用命名空间MySql.Data.MySqlClient;

使用命令行编译时：csc /r:MySql.Data.dll test.cs

方法二：

通过ODBC访问MYSQL数据库

访问前要先下载两个组件：https://www.360docs.net/doc/0b5681637.html,和MYSQL的ODBC驱动(MySQL Connector/ODBC (MyODBC) driver)目前为3.51版

安装完成后，即可通过ODBC访问MYSQL数据库

方法三：

使用CoreLab推出的MYSQL访问组件，面向.NET

安装完成后，引用命名空间：CoreLab.MySql;

使用命令编译时：csc /r:CoreLab.MySql.dll test.cs

以下为访问MYSQL数据库实例

编译指令：csc /r:CoreLab.MySql.dll /r:MySql.Data.dll test.cs

using System;

using https://www.360docs.net/doc/0b5681637.html,;

using System.Text;

using CoreLab.MySql;

using System.Data.Odbc;

using MySql.Data.MySqlClient;

class ConnectMySql

{

public void Connect_CoreLab()

{

string constr = "User Id=root;Host=localhost;Database=qing;password=qing"; MySqlConnection mycn = new MySqlConnection(constr);

mycn.Open();

MySqlCommand mycm = new MySqlCommand("select * from shop",mycn); MySqlDataReader msdr = mycm.ExecuteReader();

while(msdr.Read())

{

if (msdr.HasRows)

{

Console.WriteLine(msdr.GetString(0));

}

}

msdr.Close();

mycn.Close();

}

public void Connect_Odbc()

{

//string MyConString ="DSN=MySQL;UID=root;PWD=qing";

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=test;" +

"UID=root;" +

"PASSWORD=qing;" +

"OPTION=3";

OdbcConnection MyConn = new OdbcConnection(MyConString);

MyConn.Open();

OdbcCommand mycm = new OdbcCommand("select * from hello",MyConn); OdbcDataReader msdr = mycm.ExecuteReader();

while(msdr.Read())

{

if (msdr.HasRows)

{

Console.WriteLine(msdr.GetString(0));

}

}

msdr.Close();

MyConn.Close();

}

public void Connect_Net()

{

string myConnectionString = "Database=test;Data Source=localhost;User Id=root;Password=qing";

MySqlConnection mycn = new MySqlConnection(myConnectionString); mycn.Open();

MySqlCommand mycm = new MySqlCommand("select * from hello",mycn); MySqlDataReader msdr = mycm.ExecuteReader();

while(msdr.Read())

{

if (msdr.HasRows)

{

Console.WriteLine(msdr.GetString(0));

}

}

msdr.Close();

mycn.Close();

}

public static void Main()

{

ConnectMySql ms = new ConnectMySql();

ms.Connect_CoreLab();

ms.Connect_Odbc();

Connect_Net();

}

}

1、用MySQLDriverCS连接MySQL数据库

先下载和安装MySQLDriverCS，地址：

https://www.360docs.net/doc/0b5681637.html,/projects/mysqldrivercs/

在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中注：我下载的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exe

using System;

using System.Collections.Generic;

using https://www.360docs.net/doc/0b5681637.html,ponentModel;

using System.Data;

using System.Data.Odbc;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using MySQLDriverCS;

namespace mysql

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

MySQLConnection conn = null;

conn = new MySQLConnection(new MySQLConnectionString("localhost", "inv", "root", "831025").AsString);

conn.Open();

MySQLCommand commn = new MySQLCommand("set names gb2312", conn);

commn.ExecuteNonQuery();

string sql = "select * from exchange ";

MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);

DataSet ds = new DataSet();

mda.Fill(ds, "table1");

this.dataGrid1.DataSource = ds.Tables["table1"];

conn.Close();

}

}

}

2、通过ODBC访问mysql数据库：

参考：https://www.360docs.net/doc/0b5681637.html,/china/community/Column/63.mspx

1. 安装Microsoft https://www.360docs.net/doc/0b5681637.html,：我安装的是mysql-connector-odbc-3.51.22-win3

2.msi

2. 安装MDAC 2.7或者更高版本：我安装的是mdac_typ.exe 2.7简体中文版

3. 安装MySQL的ODBC驱动程序：我安装的是odbc_net.msi

4. 管理工具-> 数据源ODBC –>配置DSN…

5. 解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)

6. 代码中增加引用using Microsoft.Data.Odbc;

using System;

using System.Collections.Generic;

using https://www.360docs.net/doc/0b5681637.html,ponentModel;

using System.Drawing;

using System.Linq; //vs2005好像没有这个命名空间，在c#2008下测试自动生成的using System.Text;

using System.Windows.Forms;

using Microsoft.Data.Odbc;

namespace mysql

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=inv;" +

"UID=root;" +

"PASSWORD=831025;" +

"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString);

MyConnection.Open();

Console.WriteLine("\n success, connected successfully !\n");

string query = "insert into test values( ''hello'', ''lucas'', ''liu'')";

OdbcCommand cmd = new OdbcCommand(query, MyConnection);

//处理异常：插入重复记录有异常

try{

cmd.ExecuteNonQuery();

}

catch(Exception ex){

Console.WriteLine("record duplicate.");

}finally{

cmd.Dispose();

}

//***********************用read方法读数据到textbox********************** string tmp1 = null;

string tmp2 = null;

string tmp3 = null;

query = "select * from test ";

OdbcCommand cmd2 = new OdbcCommand(query, MyConnection);

OdbcDataReader reader = cmd2.ExecuteReader();

while (reader.Read())

{

tmp1 = reader[0].ToString();

tmp2 = reader[1].ToString();

tmp3 = reader[2].ToString();

}

this.textBox1.Text = tmp1 + " " + tmp2 + " " + tmp3;

*/

//************************用datagridview控件显示数据表************************** string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=inv;" +

"UID=root;" +

"PASSWORD=831025;" +

"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString); OdbcDataAdapter oda = new OdbcDataAdapter("select * from customer ", MyConnection); DataSet ds = new DataSet();

oda.Fill(ds, "employee");

this.dataGridView1.DataSource = ds.Tables["employee"];

*/

MyConnection.Close();

}

}

}

文章出处：https://www.360docs.net/doc/0b5681637.html,/course/4_webprogram/https://www.360docs.net/doc/0b5681637.html,/asp_netshl/2008429/112011.html 1、用MySQLDriverCS连接MySQL数据库

先下载和安装MySQLDriverCS，地址：

https://www.360docs.net/doc/0b5681637.html,/projects/mysqldrivercs/

在安装文件夹下面找到MySQLDriver.dll，然后将MySQLDriver.dll添加引用到项目中

注：我下载的是版本是MySQLDriverCS-n-EasyQueryTools-4.0.1-DotNet2.0.exe

using System;

using System.Collections.Generic;

using https://www.360docs.net/doc/0b5681637.html,ponentModel;

using System.Data;

using System.Data.Odbc;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using MySQLDriverCS;

namespace mysql

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

MySQLConnection conn = null;

conn = new MySQLConnection(new MySQLConnectionString("localhost", "inv", "root", "831025").AsString);

conn.Open();

MySQLCommand commn = new MySQLCommand("set names gb2312", conn);

commn.ExecuteNonQuery();

string sql = "select * from exchange ";

MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);

DataSet ds = new DataSet();

mda.Fill(ds, "table1");

this.dataGrid1.DataSource = ds.Tables["table1"];

conn.Close();

}

}

}

2、通过ODBC访问mysql数据库：

参考：https://www.360docs.net/doc/0b5681637.html,/china/community/Column/63.mspx

1. 安装Microsoft https://www.360docs.net/doc/0b5681637.html,：我安装的是mysql-connector-odbc-3.51.22-win3

2.msi

2. 安装MDAC 2.7或者更高版本：我安装的是mdac_typ.exe 2.7简体中文版

3. 安装MySQL的ODBC驱动程序：我安装的是odbc_net.msi

4. 管理工具-> 数据源ODBC –>配置DSN…

5. 解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)

6. 代码中增加引用using Microsoft.Data.Odbc;

using System;

using System.Collections.Generic;

using https://www.360docs.net/doc/0b5681637.html,ponentModel;

using System.Drawing;

using System.Linq; //vs2005好像没有这个命名空间，在c#2008下测试自动生成的using System.Text;

using System.Windows.Forms;

using Microsoft.Data.Odbc;

namespace mysql

{

public partial class Form1 : Form

{

public Form1()

{

InitializeComponent();

}

private void Form1_Load(object sender, EventArgs e)

{

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=inv;" +

"UID=root;" +

"PASSWORD=831025;" +

"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString);

MyConnection.Open();

Console.WriteLine("\n success, connected successfully !\n");

string query = "insert into test values( ''hello'', ''lucas'', ''liu'')";

OdbcCommand cmd = new OdbcCommand(query, MyConnection);

//处理异常：插入重复记录有异常

try{

cmd.ExecuteNonQuery();

}

catch(Exception ex){

Console.WriteLine("record duplicate.");

}finally{

cmd.Dispose();

}

//***********************用read方法读数据到textbox**********************

string tmp1 = null;

string tmp2 = null;

string tmp3 = null;

query = "select * from test ";

OdbcCommand cmd2 = new OdbcCommand(query, MyConnection);

OdbcDataReader reader = cmd2.ExecuteReader();

while (reader.Read())

{

tmp1 = reader[0].ToString();

tmp2 = reader[1].ToString();

tmp3 = reader[2].ToString();

}

this.textBox1.Text = tmp1 + " " + tmp2 + " " + tmp3;

*/

//************************用datagridview控件显示数据表************************** string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=inv;" +

"UID=root;" +

"PASSWORD=831025;" +

"OPTION=3";

OdbcConnection MyConnection = new OdbcConnection(MyConString); OdbcDataAdapter oda = new OdbcDataAdapter("select * from customer ", MyConnection); DataSet ds = new DataSet();

oda.Fill(ds, "employee");

this.dataGridView1.DataSource = ds.Tables["employee"];

*/

MyConnection.Close();

}

}

}

文章出处：https://www.360docs.net/doc/0b5681637.html,/course/4_webprogram/https://www.360docs.net/doc/0b5681637.html,/asp_netshl/2008429/112011.html

C#连接mysql数据库

1.连接:

1.安装Microsoft https://www.360docs.net/doc/0b5681637.html,。

2.安装MySQL的ODBC驱动程序。

2.解决方案管理中添加引用Microsoft.Data.Odbc.dll(1.0.3300)

3.代码中增加引用

using Microsoft.Data.Odbc;

4.编写代码

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" +

"SERVER=localhost;" +

"DATABASE=samp_db;" +

"UID=root;" +

"PASSWORD=;" +

"OPTION=3";

//Connect to MySQL using Connector/ODBC

OdbcConnection MyConnection = new OdbcConnection(MyConString);

MyConnection.Open();

Console.WriteLine("\n !!! success, connected successfully !!!\n"); MyConnection.Close();

2.全部例程:

/**

* @sample : mycon.cs

* @purpose : Demo sample for https://www.360docs.net/doc/0b5681637.html, using Connector/ODBC * @author : Venu, venu@https://www.360docs.net/doc/0b5681637.html,

*

* (C) Copyright MySQL AB, 1995-2003

*

**/

/* build command

*

* csc /t:exe

* /out:mycon.exe mycon.cs

* /r:Microsoft.Data.Odbc.dll

*/

using Console = System.Console;

using Microsoft.Data.Odbc;

namespace myodbc3

{

class mycon

{

static void Main(string[] args)

{

try

{

//Connection string for Connector/ODBC 2.50

/*string MyConString = "DRIVER={MySQL};" +

"SERVER=localhost;" +

"DATABASE=test;" +

"UID=venu;" +

"PASSWORD=venu;" +

"OPTION=3";

*/

//Connection string for Connector/ODBC 3.51

string MyConString = "DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=localhost;" +

"DATABASE=test;" +

"UID=venu;" +

"PASSWORD=venu;" +

"OPTION=3";

//Connect to MySQL using Connector/ODBC

OdbcConnection MyConnection = new OdbcConnection(MyConString); MyConnection.Open();

Console.WriteLine("\n !!! success, connected successfully !!!\n");

//Display connection information

Console.WriteLine("Connection Information:");

Console.WriteLine("\tConnection String:" + MyConnection.ConnectionString); Console.WriteLine("\tConnection Timeout:" + MyConnection.ConnectionTimeout); Console.WriteLine("\tDatabase:" + MyConnection.Database);

Console.WriteLine("\tDataSource:" + MyConnection.DataSource);

Console.WriteLine("\tDriver:" + MyConnection.Driver);

Console.WriteLine("\tServerVersion:" + MyConnection.ServerVersion);

//Create a sample table

OdbcCommand MyCommand = new OdbcCommand("DROP TABLE IF EXISTS my_odbc_ net",MyConnection);

MyCommand.ExecuteNonQuery();

https://www.360docs.net/doc/0b5681637.html,mandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), i db bigint)";

MyCommand.ExecuteNonQuery();

//Insert

https://www.360docs.net/doc/0b5681637.html,mandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());;

//Insert

https://www.360docs.net/doc/0b5681637.html,mandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

//Insert

https://www.360docs.net/doc/0b5681637.html,mandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)"; Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

//Update

https://www.360docs.net/doc/0b5681637.html,mandText = "UPDATE my_odbc_net SET id=999 WHERE id=20"; Console.WriteLine("Update, Total rows affected:" + MyCommand.ExecuteNonQuery());

//COUNT(*)

https://www.360docs.net/doc/0b5681637.html,mandText = "SELECT COUNT(*) as TRows FROM my_odbc_net"; Console.WriteLine("Total Rows:" + MyCommand.ExecuteScalar());

//Fetch

https://www.360docs.net/doc/0b5681637.html,mandText = "SELECT * FROM my_odbc_net";

OdbcDataReader MyDataReader;

MyDataReader = MyCommand.ExecuteReader();

while (MyDataReader.Read())

{

if(https://www.360docs.net/doc/0b5681637.html,pare(MyConnection.Driver,"myodbc3.dll") == 0) {

Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +

MyDataReader.GetString(1) + " " +

MyDataReader.GetInt64(2)); //Supported only by Connector/ODBC 3.51

}

else {

Console.WriteLine("Data:" + MyDataReader.GetInt32(0) + " " +

MyDataReader.GetString(1) + " " +

MyDataReader.GetInt32(2)); //BIGINTs not supported by Connector/ODBC

}

}

//Close all resources

MyDataReader.Close();

MyConnection.Close();

}

catch (OdbcException MyOdbcException)//Catch any ODBC exception ..

{

for (int i=0; i < MyOdbcException.Errors.Count; i++)

{

Console.Write("ERROR #" + i + "\n" +

"Message: " + MyOdbcException.Errors[i].Message + "\n" +

"Native: " + MyOdbcException.Errors[i].NativeError.ToString() + "\n" +

"Source: " + MyOdbcException.Errors[i].Source + "\n" +

"SQL: " + MyOdbcException.Errors[i].SQLState + "\n");

}

}

}

}

}

3.使用dataset填充dataGrid:

OdbcConnection con = new OdbcConnection("DRIVER={MySQL ODBC 3.51 Driver};" + "SERVER=glf;" +

"DATABASE=qxk_db;" +

"UID=root;" +

"PASSWORD=;" +

"OPTION=3");

da=new OdbcDataAdapter("select * from achi_eval",con);

ds=new DataSet();

da.Fill(ds,"customers");

dtSource = ds.Tables["customers"];

pageSize=20;

maxRec = dtSource.Rows.Count;

PageCount = maxRec / pageSize;

if ((maxRec % pageSize) > 0)

{

PageCount += 1;

}

currentPage = 1;

recNo = 0;

LoadPage();

这是前一段需要用到,精选了一些资料,希望对大家有帮助.

using System;

using System.Configuration;

using MySql.Data.MySqlClient;

///

/// TestDatebase 的摘要说明

///

public class TestDatebase

{

public TestDatebase()

{

//

// TODO: 在此处添加构造函数逻辑

//

}

public static void Main ( String[] args )

{