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("删除成功");
            }
 */