C # connect to MySQL Database

1. Download and install

http://sourceforge.net/projects/mysqldrivercs/Download the Mysqldrivercs and install it.

or to My network disk download: Http://pan.baidu.com/s/1pJqTXRP

2. Add Reference Right-click Add Reference

Locate MysqlDrivercs.dll under the installation directory

Locate the reference in the solution, and right-click Add Reference.

Here's the code to manipulate the database:

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

{

Public Form1 ()

{

InitializeComponent ();

}

Private void 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 ();

}

}

}

Below company I write good operation MySQL class. In the face of the database connection, data additions and deletions to do the package.

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;/** * Below the DBServer class for C # Operation of MySQL database made a simplified encapsulation of the operation of the database most commonly used additions and deletions of the operation subscription number Next_space attention will have more resources */namespace shiyan4{class DBServer {pri        Vate 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 () {}//CREATE DATABASE driver class Dbhost host address dbname database name dbuser user name dbpwd password publi C DBServer (String dbhost, String dbname, String dbuser, String dbpwd) {this.dbhost = Dbhost; This.dbnam            e = dbname;   This.dbpwd = dbpwd;            This.dbuser = Dbuser;        This.isconnect = false; } ~dbserver () {conn. Close (); }//Connect database public bool Connect () {conn = new mysqlconnection (New Mysqlconnectionstring (DBH OST, Dbname,dbuser, DBPWD).            asstring); try {Conn.            Open ();} catch (Exception ex) {MessageBox.Show ("database connection Failed");//messagebox.show (ex.                Message);            return false;        } return true;        }//Read records from database SQL to execute statement 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; }//Delete data table table name condition 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; }//Add data table table name r to add data 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; }//Find data by criteria table name con condition fields to be queried public ArrayList findData (string tablename,string con,string fi                       Elds= "") {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;            }//Update data table name r new data con condition 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; }//Direct Execute SQL command returns the number of rows affected public int excesql (String sql) {command = new Mysqlcommand ("", con            n);            Command.commandtext = SQL;            int res; try {res = command.                ExecuteNonQuery ();            The returned result is the number of rows affected//MessageBox.Show (res + ""); } catch (System.Exception ex) {MessageBox.Show ("Execute command failed:" + ex.)                Message);            return-1; } finally {command.            Dispose ();      } return res;  }//CREATE TABLE}//Single field 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;        }}//row of data class row {ArrayList list;        Public Row () {list = new ArrayList (); }//Add a key value to public void Addrowitem (string fieldname,string value) {Rowitem it = new Rowi            TEM (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 ("add success");            } row Newdata=new row ();            Newdata.addrowitem (New Rowitem ("Gw2", "55555555"));            if (Db.updatedata ("Gw_test", NewData, "gw2= ' 222 '")) {MessageBox.Show ("update succeeded");            };            if (Db.deldate ("Gw_test", "gw1= ' tes1t ')") {MessageBox.Show ("delete succeeded"); } */

Test class:

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 Sample Program 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", "R            Oot "," 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 ("add success"); }} private void Btn_del_click (object sender, EventArgs e) {if (Db.deldate ("s            Tudent "," 1=1 ")) {MessageBox.Show (" delete succeeded "); }} 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) {RE                Sult + = 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 ("update succeeded");        };      }  private void textBox1_TextChanged (object sender, EventArgs e) {} private void Label1_click (object sender, EventArgs e) {} private void Add_click (object sender, EventArgs e) {Strin G 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 ("add success");            } 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 ("add success");            } row Newdata=new row ();            Newdata.addrowitem (New Rowitem ("Gw2", "55555555"));            if (Db.updatedata ("Gw_test", NewData, "gw2= ' 222 '")) {MessageBox.Show ("update succeeded");            };            if (Db.deldate ("Gw_test", "gw1= ' tes1t ')") {MessageBox.Show ("delete succeeded"); } */

Experiment:

C # connect to MySQL Database