首先去mysql网站下载一个mysql非安装版本
http://dev.mysql.com/downloads/mysql/5.0.html
如果你感觉使用dos命令行不方便的话，可以顺便也下载一个GUI客户端：
http://dev.mysql.com/downloads/gui-tools/5.0.html
然后打开vs..这个版本必须是2003以上。
然后呢，建立一个windowsApplication工程，然后在里面的app.config
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="Restaurant" value="server=localhost;user id=root;password=;database=test;allow zero datetime=true;"></add>
</appSettings>
</configuration>
如果你想把连接字符串写在程序里的话：将下面的代码改为string strConn = “server=localhost;user id=root;password=;database=test;allow zero datetime=true;”
这里与sqlserver连接字符串相区别的是：
user->user id,
当然估计没有下载MySql.Data.dll所以在
http://dev.mysql.com/downloads/connector/net/5.2.html下载
然后在net工程里添加引用，找到刚才你下载MySql.Data.dll的压缩包，解压后，在bin目录下有MySql.Data.dll.
添加成功后，在你的代码里添加一个
using MySql.Data.MySqlClient;
然后下面的和SqlClient的效果是一样的。只不过前面多了"My"
下面看具体的代码：
- using System;
- using System.Collections.Generic;
- using System.Text;
- using System.Data;
- using MySql.Data.MySqlClient;
- namespace SqlConnect
- {
- /// <summary>
- /// author: chico chen
- /// date: 2008-11-02
- /// </summary>
- public class SqlAccess
- {
- private MySqlConnection conn = null;
- private MySqlCommand cmd = null;
- /// <summary>
- /// 建立与数据库的连接
- /// </summary>
- /// <returns></returns>
- private MySqlConnection CreateConn()
- {
- string strConn = System.Configuration.ConfigurationSettings.AppSettings["Restaurant"].ToString();
- // SqlConnection 对象
- conn = new MySqlConnection(strConn);
- try
- {
- conn.Open();
- return conn;
- }
- catch (Exception e)
- {
- Console.WriteLine("sql 连接未打开");
- return null;
- }
- }
- /// <summary>
- /// 关闭与数据库的连接
- /// </summary>
- private void CloseConn()
- {
- try
- {
- conn.Close();
- }
- catch (Exception e)
- {
- Console.WriteLine("sql 连接未关闭");
- }
- }
- /// <summary>
- /// 查询数据库
- /// </summary>
- /// <param name="sql">类似于SELECT * FROM [User];</param>
- /// <returns></returns>
- public DataSet SelectDataSet(string sql)
- {
- CreateConn();
- MySqlDataAdapter sda = new MySqlDataAdapter(sql, conn);
- DataSet ds = new DataSet();
- sda.Fill(ds);
- CloseConn();
- return ds;
- }
- /// <summary>
- /// 执行无返回值的sql语句，如果成功返回true，失败返回false；
- /// </summary>
- /// <param name="sqlcmd">类似于UPDATE [USER] SET userID=@userIDC WHERE userID=@userID
- /// 或delete from [user] where userID=@userID;
- /// 或者insert into [user] (userID, password, name) values (@userID,@password,@Name) ;</param>
- /// <param name="sqlPara">
- /// SqlParameter[] sp = new SqlParameter[3];
- /// sp[0] = new SqlParameter(user, SqlDbType.VarChar, 50);
- /// sp[0].Value = "www";
- /// </param>
- /// <returns></returns>
- public bool EXESql(string sqlcmd, MySqlParameter[] sqlPara)
- {
- try
- {
- CreateConn();
- cmd = new MySqlCommand();
- cmd.Connection = conn;
- cmd.CommandType = CommandType.Text;
- cmd.CommandText = sqlcmd;
- foreach (MySqlParameter sp in sqlPara)
- {
- cmd.Parameters.Add(sp);
- }
- cmd.ExecuteNonQuery();
- CloseConn();
- return true;
- }
- catch (Exception e)
- {
- return false;
- }
- }
- }
- }
这里调用的代码这样写：
DataSet ds = sqlAccess.SelectDataSet("select * from user");
当然如果你使用DataSet还要加using System.Data;
连接字符串中为什么要加Allow Zero datetime = true是因为在添加数据库数据时，
添加了错误的dateTime数据，所以mysql自动将其转为0000-00-00 00:00:00,所以在
DataSet ds = new DataSet();
sda.Fill(ds);
这里就会报异常，原因是不支持这种Date/Time。
要使用刚才的接口的话：
- {
- DataSet ds = sqlAccess.SelectDataSet("select * from user");
- Array alist = ds.Tables[0].Rows[0].ItemArray;
- foreach(object o in alist)
- {
- label1.Text += o.ToString();
- }
- UpdateName();
- }
- private const string sql ="UPDATE USER SET userName=@userName WHERE userID=@userID";
- private const string userName = "@userName";
- private const string userID = "@userID";
- private void UpdateName()
- {
- int i = 1;
- string name = "xxxx";
- MySqlParameter[] my =
- {
- new MySqlParameter(userName,MySqlDbType.String),
- new MySqlParameter(userID,MySqlDbType.Int32)
- };
- my[0].Value = name;
- my[1].Value = i;
- label2.Text = sqlAccess.EXESql(sql, my).ToString();
- }