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

. . . .
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 ADO.NET driver for MySQL
该组件为MYSQL为ADO.NET访问MYSQL数据库设计的.NET访问组件。
安装完成该组件后，引用命名空间MySql.Data.MySqlClient;
使用命令行编译时：csc /r:MySql.Data.dll test.cs
方法二：
通过ODBC访问MYSQL数据库
访问前要先下载两个组件：和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 System.Net;
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，地址：

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

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

using System;

using System.Collections.Generic;

using SystemponentModel;

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数据库：

参考：.microsoft./china/community/Column/63.mspx

1. 安装Microsoft ODBC.net：我安装的是mysql-connector-odbc-3.51.22-win32.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 SystemponentModel;

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

}

}

}

文章出处：.diybl./course/4_webprogram/

1、用MySQLDriverCS连接MySQL数据库

先下载和安装MySQLDriverCS，地址：

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

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

using System;

using System.Collections.Generic;

using SystemponentModel;

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数据库：

参考：.microsoft./china/community/Column/63.mspx

1. 安装Microsoft ODBC.net：我安装的是mysql-connector-odbc-3.51.22-win32.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 SystemponentModel;

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

}

}

}

文章出处：.diybl./course/4_webprogram/

C#连接mysql数据库

1.连接:
1.安装Microsoft ODBC.net。
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 ODBC.NET using Connector/ODBC
* author : Venu, venumysql.
*
* (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();
MyCommandmandText = "CREATE TABLE my_odbc_net(id int, name varchar(20), idb bigint)";
MyCommand.ExecuteNonQuery();

//Insert
MyCommandmandText = "INSERT INTO my_odbc_net VALUES(10,'venu', 300)";
Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());;

//Insert
MyCommandmandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',400)";
Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

//Insert
MyCommandmandText = "INSERT INTO my_odbc_net VALUES(20,'mysql',500)";
Console.WriteLine("INSERT, Total rows affected:" + MyCommand.ExecuteNonQuery());

//Up