C#（.net） MySql資料庫連結工具類

標籤：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 [email protected] where [email protected]";                          int number = SqlHelper.ExecuteNoQuery(sql, new MySQLParameter[]                 {                    new MySQLParameter("@isActived", "YES"),                    new MySQLParameter("@id", 2)                });                               Console.WriteLine("受影響的行數:" + number);

C#（.net） MySql資料庫連結工具類