C#串連mysql資料庫

標籤：c#   mysql   

1.下載安裝

 http://sourceforge.net/projects/mysqldrivercs/下載MySQlDriverCS 並安裝.

或者到本人的網盤下載:      http://pan.baidu.com/s/1pJqTXRP

2.添加引用 單擊右鍵添加引用

在安裝目錄下找到mysqlDrivercs.dll

在解決方案中找到引用,按右鍵添加引用.

 

 

 

下面是操作資料庫的代碼:

using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Data.Odbc;

using System.Drawing;

using System.Linq;

using System.Text;

using System.Windows.Forms;

using MySQLDriverCS;

 

namespace mysql1

{

    public partial class Form1 : Form

    {

        publicForm1()

        {

            InitializeComponent();

        }

        privatevoid Form1_Load(objectsender,EventArgs e)

        {

            MySQLConnectionconn =null;

            conn = newMySQLConnection(newMySQLConnectionString("localhost","test","root","123456").AsString);

            conn.Open();

            //MySQLCommandcommn = new MySQLCommand("set names gb2312", conn);

            //commn.ExecuteNonQuery();

            stringsql = "select * from gw_test ";

             MySQLDataAdaptermda =new MySQLDataAdapter(sql,conn);

             DataSetds = new DataSet();

             mda.Fill(ds, "table1");

            this.dataGrid1.DataSource= ds.Tables["table1"];

            conn.Close();

        }

    }

}

下面簡紹的本人寫好的操作mysql的類.裡面對資料庫的串連, 資料的增刪改查做了封裝.

using System;using System.Collections.Generic;using System.Linq;using System.Text;using MySQLDriverCS;using System.ComponentModel;using System.Data;using System.Windows.Forms;using System.Collections;/** * 下面的DbServer類對 C#操作mysql資料庫做了一個簡化   封裝了 操作資料庫最常用的增刪改查操作    訂閱號 next_space  關注會有更多的資源 */namespace shiyan4{    class DbServer    {        private string dbname;        private string dbhost;        private string dbuser;        private string dbpwd;        private string dbtype="mysql";        MySQLConnection conn = null;        MySQLCommand command;        private bool isConnect;        public DbServer()        {                  }        //建立資料庫驅動類  dbhost 主機地址 dbname 資料庫名  dbuser 使用者名稱  dbpwd密碼        public DbServer(string dbhost, string dbname, string dbuser, string dbpwd)        {            this.dbhost = dbhost; this.dbname = dbname;            this.dbpwd = dbpwd;   this.dbuser = dbuser;            this.isConnect = false;        }        ~DbServer()        {            conn.Close();                }        //串連資料庫        public bool connect()        {            conn = new MySQLConnection(new MySQLConnectionString(dbhost, dbname,dbuser, dbpwd).AsString);            try            { conn.Open();}            catch (Exception ex)            {                MessageBox.Show("資料庫連接失敗");//MessageBox.Show(ex.Message);                return false;            }                       return true;        }        //從資料庫中讀取記錄  sql 要執行的語句        public DataTable getDataTable(string tableName, string con, string fields = "")        {            if (fields == "")                fields = "*";            string sql = string.Format("select {0} from  {1} where {2};", fields, tableName, con);            MySQLDataAdapter mda = new MySQLDataAdapter(sql, conn);           // DataSet ds = new DataSet(); mda.Fill(ds, "table1");            DataTable dt = new DataTable();            mda.Fill(dt);                       return dt;        }        //刪除資料 table 表名 condition 條件        public bool delDate(string table,string condition)        {            string str = string.Format("delete from {0} where {1}", table, condition);            int res = exceSql(str);            //MessageBox.Show(res + "");            if (res == -1)                return false;            return true;        }        //添加資料 table表名  r 要添加的資料          public bool addData(string tableName,Row r)        {            ArrayList list=r.getList();            IEnumerator enumerator = list.GetEnumerator();            StringBuilder fields = new StringBuilder("(");            StringBuilder data = new StringBuilder("(");            while (enumerator.MoveNext())            {               RowItem it=(RowItem) enumerator.Current;               string filedname = it.getFieldName();               string value = it.getValue();               //fields += "'" + filedname + "'" + ",";               fields.AppendFormat("`{0}`,", filedname);               data.AppendFormat("'{0}',", value);            }            fields.Replace(',', ')', fields.Length - 1,1);            data.Replace(',', ')', data.Length - 1, 1);            //MessageBox.Show(fields.ToString()+" "+data.ToString());            string sqlstr = string.Format("INSERT INTO {0} {1}  VALUES{2}", tableName,fields.ToString(), data.ToString());            int res = exceSql(sqlstr);            if (res == -1)                return false;            return true;        }        //根據條件尋找資料  table 表名  con 條件 fields 待查詢的欄位        public ArrayList findData(string tableName,string con,string fields="")        {            if (fields == "")                fields = "*";                       string sql = string.Format("select {0} from  {1} where {2};",fields, tableName, con);           //  MessageBox.Show(sql);            MySQLCommand cmd = new MySQLCommand(sql, conn);            command = new MySQLCommand("", conn);            command.CommandText = sql;            MySQLDataReader reader = command.ExecuteReaderEx();            string str = "0";            int length = 0;            int fieldNum = reader.FieldCount;            ArrayList rows = new ArrayList();            while (reader.Read())            {                ArrayList row = new ArrayList();                for (int i = 0; i < fieldNum;i++ )                {                    row.Add(reader.GetString(i));                }                rows.Add(row);                              length++;            }            reader.Close();            cmd.Dispose();            return rows;        }        //更新資料  table 表名  r 新的資料  con 條件        public bool updateData(string tableName,Row r,string con)        {            ArrayList list = r.getList();            IEnumerator enumerator = list.GetEnumerator();            StringBuilder fields = new StringBuilder();                            while (enumerator.MoveNext())            {                RowItem it = (RowItem)enumerator.Current;                string filedname = it.getFieldName();                string value = it.getValue();                fields.AppendFormat("{0}='{1}',", filedname,value);            }            fields.Replace(',', ' ', fields.Length - 1, 1);            string sql = string.Format("update {0} set {1} where {2};", tableName,fields.ToString(),con);           // MessageBox.Show(sql);            int res = exceSql(sql);            if (res == -1)                return false;            return true;        }        //直接執行sql命令 返回受影響的行數        public int exceSql(string sql)        {            command = new MySQLCommand("", conn);            command.CommandText = sql;            int res;            try            {                 res = command.ExecuteNonQuery();                //返回結果為受影響行數               // MessageBox.Show(res + "");            }            catch (System.Exception ex)            {                MessageBox.Show("執行命令失敗:" + ex.Message);                return -1;            }            finally            {                command.Dispose();            }            return res;        }        //建立表    }    //單個欄位    class RowItem    {        string fieldName;        string value;        public RowItem(string fieldName, string value)        {            this.fieldName = fieldName;            this.value = value;        }        public string getFieldName()        {            return fieldName;        }        public string getValue()        {            return value;        }    }    //一行資料    class Row    {        ArrayList list;        public Row()        {            list = new ArrayList();        }        //添加一個 索引值對        public void addRowItem(string fieldName,string value)        {            RowItem it = new RowItem(fieldName, value);            list.Add(it);        }        public ArrayList getList()        {            return list;        }    }}/**  db = new DbServer("localhost", "test", "root", "123456");            db.connect();                   string sql = "select * from gw_test ";            DataTable dt = db.getDataTable(sql);            this.dataGrid1.DataSource = dt;            Row r = new Row();            r.addRowItem(new RowItem("gw1", "tes1t"));            r.addRowItem(new RowItem("gw2", "1111"));            //if (db.addData("gw_test", r))            {            //    MessageBox.Show("添加成功");            }            Row newdata=new Row();            newdata.addRowItem(new RowItem("gw2","55555555"));            if (db.updateData("gw_test", newdata, "gw2='222'"))            {                MessageBox.Show("更新成功");            };            if(db.delDate("gw_test","gw1= 'tes1t'"))            {                MessageBox.Show("刪除成功");            } */

測試類別:

using System;using System.Collections.Generic;using System.ComponentModel;using System.Data;using System.Drawing;using System.Linq;using System.Text;using System.Windows.Forms;using MySQLDriverCS;using System.Collections;//DbSercer樣本程式namespace shiyan4{    public partial class Form1 : Form    {        DbServer db;        public Form1()        {            InitializeComponent();        }        private void Form1_Load(object sender, EventArgs e)        {            db = new DbServer("localhost", "test", "root", "123456");            db.connect();            DataTable dt=db.getDataTable("student", "1=1");            this.dataGrid1.DataSource = dt;        }        private void btn_add_Click(object sender, EventArgs e)        {            Row r = new Row();            r.addRowItem("sno", "122055905");            r.addRowItem("name", "1111");            r.addRowItem("cid", "1220551");            r.addRowItem("enteryear", "2014");                       if (db.addData("student", r))            {                MessageBox.Show("添加成功");            }                    }        private void btn_del_Click(object sender, EventArgs e)        {            if (db.delDate("student", "1=1"))            {                MessageBox.Show("刪除成功");            }        }        private void btn_find_Click(object sender, EventArgs e)        {            ArrayList datas=db.findData("student", "1=1");            string result = "";            foreach (ArrayList o in datas)            {                foreach (string oo in o)                {                    result += oo+" ";                }                result += "\n";            }            MessageBox.Show(result);        }        //        private void btn_update_Click(object sender, EventArgs e)        {            Row newdata = new Row();            newdata.addRowItem("name", "gw");            if (db.updateData("student", newdata, "sno='122055905'"))            {                MessageBox.Show("更新成功");            };        }        private void textBox1_TextChanged(object sender, EventArgs e)        {        }        private void label1_Click(object sender, EventArgs e)        {        }        private void add_Click(object sender, EventArgs e)        {            string id = this.tb_no.Text;            string name = this.tb_name.Text;            string cid = this.tb_cid.Text;            MessageBox.Show(id + name + cid);;           Row r = new Row();            r.addRowItem("sno", id);            r.addRowItem("name", name);            r.addRowItem("cid", cid);            r.addRowItem("enteryear", "2014");            if (db.addData("student", r))            {                MessageBox.Show("添加成功");            }            DataTable dt = db.getDataTable("student", "1=1");            this.dataGrid1.DataSource = dt;        }      }}/**  db = new DbServer("localhost", "test", "root", "123456");            db.connect();                   string sql = "select * from gw_test ";            DataTable dt = db.getDataTable(sql);            this.dataGrid1.DataSource = dt;            Row r = new Row();            r.addRowItem(new RowItem("gw1", "tes1t"));            r.addRowItem(new RowItem("gw2", "1111"));            //if (db.addData("gw_test", r))            {            //    MessageBox.Show("添加成功");            }            Row newdata=new Row();            newdata.addRowItem(new RowItem("gw2","55555555"));            if (db.updateData("gw_test", newdata, "gw2='222'"))            {                MessageBox.Show("更新成功");            };            if(db.delDate("gw_test","gw1= 'tes1t'"))            {                MessageBox.Show("刪除成功");            } */

實驗:

 

 

C#串連mysql資料庫