欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

【C# MySQL】第一回使用C#对MySQL进行操作的心得

程序员文章站 2022-05-16 09:17:43
...

【C# MySQL】第一次使用C#对MySQL进行操作的心得 花了2个多小时,终于成功的使用C#完成对MySQL的操作,查询、修改和删除,不知道是该高兴呢,还是悲伤呢(竟然花了2个小时),总之还是有必要写下一点经验,防止以后再走同样的弯路: SQL语句的式非常重要,比

【C# MySQL】第一次使用C#对MySQL进行操作的心得


花了2个多小时,终于成功的使用C#完成对MySQL的操作,查询、修改和删除,不知道是该高兴呢,还是悲伤呢(竟然花了2个小时),总之还是有必要写下一点经验,防止以后再走同样的弯路:

  1. 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'";
  2. 不用盲目在internet上导出搜索,先认真看下官方的文档,很多时候internet上都是翻译官方文档,而且不是很全面
  3. 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();
                }