.NET实现MySQL访问接口

最近在项目过程中，需要用到MySQL数据库，下面就将自己的一些使用心得分享一下。

安装MySQL数据库的.NET组件

1、可以到http://dev.mysql.com/downloads去下载最新的组件版本；

2、安装完毕后，在自己的项目中添加对应版本的MySql,Data.dll。

经过上面两个步骤，我们就可以进行MySQL访问通用类的编写了。

MySQL数据库访问通用类

 /* 名称：MySQL数据库操作类

 * 内容：MySQL数据库操作类
 * 作者：kikaylee
 * 时间：2014.1.7
 */
using System;
using System.Data;
using System.Diagnostics;
using System.Collections.Generic;
using MySql.Data.MySqlClient;

namespace DAL
{
    /// <summary>
    /// MySQL数据库操作类
    /// </summary>
    public class MySQLHelper
    {
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private static String connectionString = GetDbConnectionString.GetConStr();
        private MySQLHelper()
        {
        }

        /// <summary>
        /// 执行单条插入语句，并返回id，不需要返回id的用ExceuteNonQuery执行
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数集合</param>
        /// <returns>返回插入数据的id</returns>
        public static int ExecuteInsert(string sql, MySqlParameter[] parameters)
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    if (parameters != null) cmd.Parameters.AddRange(parameters);
                    cmd.ExecuteNonQuery();
                    cmd.CommandText = @"select LAST_INSERT_ID()";
                    int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                    return value;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        public static int ExecuteInsert(string sql)
        {
            return ExecuteInsert(sql, null);
        }

        /// <summary>
        /// 执行带参数的sql语句,返回影响的记录数（insert,update,delete)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回影响的记录数</returns>
        public static int ExecuteNonQuery(string sql, MySqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    if (parameters != null) cmd.Parameters.AddRange(parameters);
                    int rows = cmd.ExecuteNonQuery();
                    return rows;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        /// 执行不带参数的sql语句，返回影响的记录数
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>返回影响的记录数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }

        /// <summary>
        /// 执行单条语句返回第一行第一列,可以用来返回count(*)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="parameters">参数</param>
        /// <returns>返回总数</returns>
        public static int ExecuteScalar(string sql, MySqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand(sql, connection);
                try
                {
                    connection.Open();
                    if (parameters != null) cmd.Parameters.AddRange(parameters);
                    int value = Int32.Parse(cmd.ExecuteScalar().ToString());
                    return value;
                }
                catch (Exception e)
                {
                    throw e;
                }
            }
        }
        /// <summary>
        ///  执行单条语句返回第一行第一列,可以用来返回count(*)
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>   返回总数</returns>
        public static int ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }

        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sqlList"></param>
        /// <param name="paraList"></param>
        public static void ExecuteTrans(List<string> sqlList, List<MySqlParameter[]> paraList)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                MySqlCommand cmd = new MySqlCommand();
                MySqlTransaction transaction = null;
                cmd.Connection = connection;
                try
                {
                    connection.Open();
                    transaction = connection.BeginTransaction();
                    cmd.Transaction = transaction;

                    for (int i = 0; i < sqlList.Count; i++)
                    {
                        cmd.CommandText = sqlList[i];
                        if (paraList != null && paraList[i] != null)
                        {
                            cmd.Parameters.Clear();
                            cmd.Parameters.AddRange(paraList[i]);
                        }
                        cmd.ExecuteNonQuery();
                    }
                    transaction.Commit();

                }
                catch (Exception e)
                {
                    try
                    {
                        transaction.Rollback();
                    }
                    catch
                    {

                    }
                    throw e;
                }

            }
        }
        /// <summary>
        /// 执行事务
        /// </summary>
        /// <param name="sqlList"></param>
        public static void ExecuteTrans(List<string> sqlList)
        {
            ExecuteTrans(sqlList, null);
        }

        /// <summary>
        /// 执行查询语句，返回dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataSet ExecuteQuery(string sql, MySqlParameter[] parameters)
        {
            //Debug.WriteLine(sql);
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                DataSet ds = new DataSet();
                try
                {
                    connection.Open();

                    MySqlDataAdapter da = new MySqlDataAdapter(sql, connection);
                    if (parameters != null) da.SelectCommand.Parameters.AddRange(parameters);
                    da.Fill(ds, "ds");
                }
                catch (Exception ex)
                {
                    throw ex;
                }
                return ds;
            }
        }
        /// <summary>
        /// 执行查询语句，返回dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet ExecuteQuery(string sql)
        {
            return ExecuteQuery(sql, null);
        }
    }
}

当然了，也可以利用服务器管理器，字节进行强类型的数据连接，详情可以查看我以前转载的一片博客：http://blog.youkuaiyun.com/kikaylee/article/details/17991063。