C#（.net） MySql数据库链接工具类

C#（.net） MySql数据库链接工具类

先下载和安装MySQLDriverCS 
http://sourceforge.net/projects/mysqldrivercs/

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

应用程序配置文件：

App.config:

<?xml version="1.0" encoding="utf-8" ?>
<configuration>


  <connectionStrings>

    <add name="server" connectionString="localhost"></add>
    <add name="database" connectionString="housing"></add>
    <add name="login" connectionString="root"></add>
    <add name="password" connectionString="root"></add>
  </connectionStrings>
</configuration>

SqlHelper.cs内容：

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

namespace Demo
{
    class SqlHelper
    {

        private static string server = ConfigurationManager.ConnectionStrings["server"].ConnectionString;
        private static string database = ConfigurationManager.ConnectionStrings["database"].ConnectionString;
        private static string login = ConfigurationManager.ConnectionStrings["login"].ConnectionString;
        private static string password = ConfigurationManager.ConnectionStrings["password"].ConnectionString;


        public static int ExecuteNoQuery(String sql,MySQLParameter[] parameters)
        {

            using (MySQLConnection conn = new MySQLConnection(new MySQLConnectionString(server, database, login, password).AsString))
            {

                conn.Open();

              
                //防止乱码
                MySQLCommand commn = new MySQLCommand("set names gb2312", conn);
                commn.ExecuteNonQuery();
                //连接语句和SQL
                MySQLCommand cmd = new MySQLCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange( parameters);
                //返回执行结果
                return cmd.ExecuteNonQuery();

            }
        
        }
        public static object ExecuteScalar(String sql, MySQLParameter[] parameters)
        {

            using (MySQLConnection conn = new MySQLConnection(new MySQLConnectionString(server, database, login, password).AsString))
            {

                conn.Open();
                //防止乱码
                MySQLCommand commn = new MySQLCommand("set names gb2312", conn);
                commn.ExecuteNonQuery();

                MySQLCommand cmd = new MySQLCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange(parameters);
                
                return cmd.ExecuteNonQuery();
            }
        
        }

        //较少的时候
        public static DataTable ExecuteReaderEx(String sql, MySQLParameter[] parameters)
        {

            using (MySQLConnection conn = new MySQLConnection(new MySQLConnectionString(server, database, login, password).AsString))
            {

                conn.Open();
                //防止乱码
                MySQLCommand commn = new MySQLCommand("set names gb2312", conn);
                commn.ExecuteNonQuery();

                MySQLCommand cmd = new MySQLCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange(parameters);

                MySQLDataAdapter mda = new MySQLDataAdapter(cmd);

                //查询出的数据是存在DataTable中的，DataTable可以理解成为一个虚拟的表，DataTable中的一行为一条记录，一列为一个数据库字段  


                DataTable dt = new DataTable();
                mda.Fill(dt);  

                return dt;
            }

        }
        public static DataSet ExecuteReaderEx2(String sql, MySQLParameter[] parameters)
        {


            using (MySQLConnection conn = new MySQLConnection(new MySQLConnectionString(server, database, login, password).AsString))
            {

                conn.Open();
                //防止乱码
                MySQLCommand commn = new MySQLCommand("set names gb2312", conn);
                commn.ExecuteNonQuery();

                MySQLCommand cmd = new MySQLCommand(sql, conn);
                //添加参数
                cmd.Parameters.AddRange(parameters);

                MySQLDataAdapter mda = new MySQLDataAdapter(cmd);

                //查询出的数据是存在DataTable中的，DataTable可以理解成为一个虚拟的表，DataTable中的一行为一条记录，一列为一个数据库字段  


                DataSet ds = new DataSet();
                mda.Fill(ds);
                return ds;
            }

        }




    }
}

使用示例：

  //sql语句
                string sql = "update tbl_sysuser set isActived=@isActived where id=@id";
          

                int number = SqlHelper.ExecuteNoQuery(sql, new MySQLParameter[] 
                {
                    new MySQLParameter("@isActived", "YES"),
                    new MySQLParameter("@id", 2)
                });
               

                Console.WriteLine("受影响的行数:" + number);