C#中使用MySQLDriverCS连接MYSQL

C#中使用MySQLDriverCS连接MYSQL

项目添加引用MySQLDriverCS.dll。

using MySQLDriverCS;
//连接1：
MySQLConnection conn = null;
try
{
String constr = "Data Source=test;Password=;User ID=root;Location=localhost";
conn = new MySQLConnection(constr);
conn.Open();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
//连接2：
MySQLConnection conn = new MySQLConnection( new MySQLConnectionString("localhost","test","root","").AsString );
COMMAND：
MySQLCommand cmd;
cmd = new MySQLDriverCS.MySQLCommand("DROP TABLE IF EXISTS test.mysqldrivercs_test",conn);
cmd.ExecuteNonQuery();
cmd.Dispose();

cmd = new MySQLDriverCS.MySQLCommand("CREATE TABLE test.mysqldrivercs_test("+"SettingID tinyint(3) unsigned NOT NULL auto_increment,"+"SettingValue text, "+
"PRIMARY KEY (SettingID), UNIQUE KEY SettingID(SettingID), KEY SettingID_2 (SettingID))"+" TYPE=MyISAM COMMENT='MySQL test table'",conn);
cmd.ExecuteNonQuery();
cmd.Dispose();


INSERT:
string Value = "Value";
int SettingID = 1;

new MySQLInsertCommand(conn,new object[,] {{"SettingID",SettingID},{"SettingValue",Value}},"mysqldrivercs_test");

UPDATE:
Value = "Value2";
new MySQLUpdateCommand(conn,new object[,] {{"SettingValue",Value}},"mysqldrivercs_test",new object[,] {{"SettingID","=",SettingID}},null);

SELECT:
DataTable dt = new MySQLSelectCommand(conn,new string[] {"SettingID","SettingValue"},new string[] {"mysqldrivercs_test"},new object[,] {{"SettingID","=",SettingID}},null,null).Table;
string storedValue = dt.Rows[0]["SettingValue"].ToString();

DELETE:
new MySQLDeleteCommand(conn,"mysqldrivercs_test",new object[,] {{"SettingID","=",SettingID}},null);

READ:
query ="select * from test.dbtable";
MySQLCommand cmd = new MySQLCommand(query, conn);
conn.Open();
MySQLDataReader reader = cmd2.ExecuteReaderEx();
reader.Read();
String tmp = reader[0..].ToString();

第一步:安装MySql 我装的是mysql-5.0.45-win32

第二步:安装MySQL-Front 为了能可视化使用MySql

在这里,我新建了一个数据库 gm_first 新建了一张表 test_table, 表中加了几个字段,

设置了root的密码为123456

第三步:安装mysql-connector-net 我用的版本是 5.1.4的

第四步:将C:\Program Files\MySQL\MySQL Connector Net 5.1.4\Binaries\.NET 2.0

即 我的mysql-connector-net的安装目录下的MySql.Data.dll文件复制到

我网站的根目录下新建的 bin文件夹里.

第五步:就是开始用了.

在.net 下面

using MySql.Data.MySqlClient;
 public partial class mysqltest : System.Web.UI.Page
 {
 protected void Page_Load(object sender, EventArgs e)
 {
 show();
 }
 protected void show()
 { 
MySqlConnection myConnection;;
 MySqlDataAdapter myDataAdapter;
 DataSet myDataSet;
 
String strSQL;
 int iRecordCount;
 
myConnection = new MySqlConnection("server=localhost;user id=root;password=123456;database=gm_first;pooling=true");
 strSQL = "select * from test_table";
 myDataAdapter = new MySqlDataAdapter(strSQL, myConnection);
 myDataSet = new DataSet();
 myDataAdapter.Fill(myDataSet, "mytable");
 Label1.Text= myDataSet.Tables["mytable"].Rows[1][1].ToString(); 
}
 }