如何在.Net下调用Mysql数据库

  最近在公司做项目，做一个系统，前台用C#开发，.net框架，后台用mysql数据库。之前做网站都是用sqlserver，所以对mysql不是很了解，所以在.net调用mysql也不明白。

 

上网查了点资料，得到大致方法如下：

1、下载：Connector/Net is a fully-managed ADO.NET driver for MySQL.

网站是：http://dev.mysql.com/downloads/connector/net/5.0.html

 

2、下载好后安装，把 C:\Program Files\MySQL\MySQL Connector Net 6.3.8\Assemblies下任意一个版本的 MySql.Data.dll 拷贝到工程目录下，添加引用

 

3、在代码头上加上

using MySql.Data.MySqlClient;
using MySql.Data.Types;

然后就可以用了

 

公司一个人做了一个CS文件接口，其他的只需要调用命名空间即可

MysqlHelper.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;


using MySql.Data.MySqlClient;
using MySql.Data.Types;

namespace MySQL
{
    public class  MysqlHelper
    {
        public static int Add(string sql, string connectString, int timeOut)
        {
            if (string.IsNullOrEmpty(sql))
                return -1;
            sql += "select @@identity";
            using (MySqlConnection connect = new MySqlConnection(connectString))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, connect))
                {
                    try
                    {
                        connect.Open();
                        cmd.CommandTimeout = timeOut;
                        MySqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                        int id = -1;
                        if (reader.Read())
                            id = reader.GetInt32("@@identity");
                        reader.Close();
                        if (id == 0)
                            id = -1;
                        return id;
                    }
                    catch
                    {
                        connect.Close();
                        return -1;
                    }
                }
            }
        }
        public static int Execute(string sql, string connectString, int timeOut)
        {
            if (string.IsNullOrEmpty(sql))
                return 0;
            using (MySqlConnection connect = new MySqlConnection(connectString))
            {
                using (MySqlCommand cmd = new MySqlCommand(sql, connect))
                {
                    try
                    {
                        connect.Open();
                        cmd.CommandTimeout = timeOut;
                        return cmd.ExecuteNonQuery();
                    }
                    catch
                    {
                        connect.Close();
                        return 0;
                    }
                }
            }
        }
        
        public static int Execute(string sql, string connectString, int timeOut, bool useTransaction)
        {
            if(string.IsNullOrEmpty(sql))
                return 0;
            if (!useTransaction)
                return Execute(sql, connectString, timeOut);
            else
            {
                using (MySqlConnection connect = new MySqlConnection(connectString))
                {
                    using (MySqlCommand cmd = new MySqlCommand(sql, connect))
                    {
                        MySqlTransaction transaction = null;
                        try
                        {
                            connect.Open();
                            transaction = connect.BeginTransaction(IsolationLevel.ReadCommitted);
                            cmd.CommandTimeout = timeOut;
                            cmd.Transaction = transaction;
                            int result = cmd.ExecuteNonQuery();
                            transaction.Commit();
                            return result;
                        }
                        catch
                        {
                            transaction.Rollback();
                            connect.Close();
                            return 0;
                        }
                    }
                }
            }
        }
        public static bool Execute(List<string> sqls, string connectString, int timeOut)
        {
            if (sqls.Count == 0)
                return false;
            using (MySqlConnection connect = new MySqlConnection(connectString))
            {
                MySqlTransaction transaction = null;
                try
                {
                    connect.Open();
                    transaction = connect.BeginTransaction(IsolationLevel.ReadCommitted);
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = connect;
                    cmd.Transaction = transaction;
                    cmd.CommandTimeout = timeOut;
                    foreach (string sql in sqls)
                    {                       
                        cmd.CommandText = sql;                       
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();
                }
                catch
                {
                        if(transaction != null)
                            transaction.Rollback();
                        connect.Close();
                        return false;
                }
            }
            return true;
        }
        public static MySqlDataReader Query(string sql, string connectString, int timeOut)
        {
            if (string.IsNullOrEmpty(connectString))
                return null;

            MySqlConnection connect = new MySqlConnection(connectString);
            MySqlCommand cmd = new MySqlCommand(sql, connect);

            try
            {
                connect.Open();
                cmd.CommandTimeout = timeOut;
                MySqlDataReader dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                return dataReader;
            }
            catch(Exception e)
            {
                System.Console.WriteLine(e.Message);
                connect.Close();
                return null;
            }
        }
    }
}

 

调用时，在其他文件头部加上

using MySQL 即可

 

剩下的就不用多说了。