花了2個多小時,終于成功的使用C#完成對MySQL的操作,查詢、修改和删除,不知道是該高興呢,還是悲傷呢(竟然花了2個小時),總之還是有必要寫下一點經驗,防止以後再走同樣的彎路:
- SQL語句的格式非常重要,比如Table名應使用`Table`符号,不是普通的單引号,如果不清楚如何敲出,建議使用MySQL圖形化工具(如phpMyAdmin)自動生成,然後拷貝,eg: string sql = "SELECT * FROM `365232` WHERE `PICS ID` LIKE 'A.4.1-1/1'"; "INSERT INTO `picsdic`.`365232` (`PICS ID` ,`PICS Description` ,`Ref.` ,`Release` ,`Mnemonic` ,`Comments`)VALUES ('A.1/2', 'test', 'test', 'test', 'test', 'test');" string sqlDelete = "DELETE FROM `picsdic`.`365232` WHERE `365232`.`PICS ID` = 'A.1/3'";
- 不用盲目在internet上導出搜尋,先認真看下官方的文檔,很多時候internet上都是翻譯官方文檔,而且不是很全面
- MSDN和ConnectorNET.chm幫了不少忙,至少比Internet上的資料靠譜多了
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace MySQLConnection
{
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
string connStr = "server=127.1.1.1;user=root;database=PICSDic;port=3306;password=HONGXING;";
MySqlConnection conn = new MySqlConnection(connStr);
try
{
//Console.WriteLine("Connecting to MySQL...");
Label1.Text = "Connecting to MySQL...";
conn.Open();
// Perform database operations
//Query
string sql = "SELECT * FROM `365232` WHERE `PICS ID` LIKE 'A.4.1-1/1'";
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
txt1.Text = rdr[0] + " -- " + rdr[1];
}
rdr.Close();
//Insert
string sqlInsert = "INSERT INTO `picsdic`.`365232` (`PICS ID` ,`PICS Description` ,`Ref.` ,`Release` ,`Mnemonic` ,`Comments`)VALUES ('A.1/3', 'test', 'test', 'test', 'test', 'test');";
MySqlCommand cmdInsert = new MySqlCommand(sqlInsert, conn);
cmdInsert.ExecuteNonQuery();
//Delete
//string sqlDelete = "DELETE FROM `picsdic`.`365232` WHERE `365232`.`PICS ID` = 'A.1/2' AND `365232`.`PICS Description` = 'test' AND `365232`.`Ref.` = 'test' AND `365232`.`Release` = 'test' AND `365232`.`Mnemonic` = 'test' AND `365232`.`Comments` = 'test' LIMIT 1";
string sqlDelete = "DELETE FROM `picsdic`.`365232` WHERE `365232`.`PICS ID` = 'A.1/3'";
MySqlCommand cmdDelete = new MySqlCommand(sqlDelete, conn);
cmdDelete.ExecuteNonQuery();
}