如何在.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 即可

 

剩下的就不用多說了。