mysql C# tutorial 一些简单的范例

从C#访问mysql数据库的简单范例

需要安装安装mysql-connector-net，并且在project中加入mysql.data

http://dev.mysql.com/downloads/connector/net/

 

Connection, Command, DataReader, DataSet 以及 DataProvider是C#与mysql交互的核心部分。

查询MySQL version

using System;
using MySql.Data.MySqlClient; 

public class Example
{

    static void Main() 
    {
        string cs = @"server=localhost;userid=user12;
            password=34klq*;database=mydb";

        MySqlConnection conn = null;

        try 
        {
          conn = new MySqlConnection(cs);
          conn.Open();
          Console.WriteLine("MySQL version : {0}", conn.ServerVersion);

        } catch (MySqlException ex) 
        {
          Console.WriteLine("Error: {0}",  ex.ToString());

        } finally 
        {          
          if (conn != null) 
          {
              conn.Close();
          }
        }
    }
}

 

查询MySQL version 另一种方法

采用直接执行mysql语句的方式，查询mysql版本

主要函数：

MySqlCommand cmd = new MySqlCommand(stm, conn);

string version = Convert.ToString(cmd.ExecuteScalar());

 

创建并对表单进行操作

用下面的数据进行示范，在mysql建立以下数据

DROP TABLE IF EXISTS Books, Authors;

CREATE TABLE IF NOT EXISTS Authors(Id INT PRIMARY KEY AUTO_INCREMENT, 
    Name VARCHAR(25)) ENGINE=INNODB;

INSERT INTO Authors(Id, Name) VALUES(1, 'Jack London');
INSERT INTO Authors(Id, Name) VALUES(2, 'Honore de Balzac');
INSERT INTO Authors(Id, Name) VALUES(3, 'Lion Feuchtwanger');
INSERT INTO Authors(Id, Name) VALUES(4, 'Emile Zola');
INSERT INTO Authors(Id, Name) VALUES(5, 'Truman Capote');

CREATE TABLE IF NOT EXISTS Books(Id INT PRIMARY KEY AUTO_INCREMENT, 
    AuthorId INT, Title VARCHAR(100), 
    FOREIGN KEY(AuthorId) REFERENCES Authors(Id) ON DELETE CASCADE)
    ENGINE=INNODB;

INSERT INTO Books(Id, AuthorId, Title) VALUES(1, 1, 'Call of the Wild');
INSERT INTO Books(Id, AuthorId, Title) VALUES(2, 1, 'Martin Eden');
INSERT INTO Books(Id, AuthorId, Title) VALUES(3, 2, 'Old Goriot');
INSERT INTO Books(Id, AuthorId, Title) VALUES(4, 2, 'Cousin Bette');
INSERT INTO Books(Id, AuthorId, Title) VALUES(5, 3, 'Jew Suess');
INSERT INTO Books(Id, AuthorId, Title) VALUES(6, 4, 'Nana');
INSERT INTO Books(Id, AuthorId, Title) VALUES(7, 4, 'The Belly of Paris');
INSERT INTO Books(Id, AuthorId, Title) VALUES(8, 5, 'In Cold blood');
INSERT INTO Books(Id, AuthorId, Title) VALUES(9, 5, 'Breakfast at Tiffany');

下面通过C#插入一个新的作者

 

从数据库中读取数据

 static void Main() 
    {

string cs = @"server=localhost;userid=root;
password=;database=db_fe";

        MySqlConnection conn = null;
        MySqlDataReader rdr = null;

        try 
        {
            conn = new MySqlConnection(cs);
            conn.Open();
        
            string stm = "SELECT * FROM Authors";
            MySqlCommand cmd = new MySqlCommand(stm, conn);
            rdr = cmd.ExecuteReader();

            while (rdr.Read()) 
            {
                Console.WriteLine(rdr.GetInt32(0) + ": " 
                    + rdr.GetString(1));
            }

        } catch (MySqlException ex) 
        {
            Console.WriteLine("Error: {0}",  ex.ToString());

        } finally 
        {
            if (rdr != null) 
            {
                rdr.Close();
            }

            if (conn != null) 
            {
                conn.Close();
            }

        }
    }

跟上面的结果是一致的

MySqlDataReader 是最快捷有效的以只读方式访问数据表单的方法

        static void Main(string[] args)
        {
            string cs = @"server=localhost;userid=root;
                        password=;database=db_fe";

            MySqlConnection conn = null;
            MySqlDataReader rdr = null;

            try
            {
                conn = new MySqlConnection(cs);
                conn.Open();

                string stm = "SELECT * FROM Authors";
                MySqlCommand cmd = new MySqlCommand(stm, conn);
                rdr = cmd.ExecuteReader();  //将读取的结果存入MySqlDataReader rdr当中

                while (rdr.Read()) //如果还有下一条记录，那么返回true
                {
                    Console.WriteLine(rdr.GetInt32(0) + ": "
                        + rdr.GetString(1)); //采用数组下标的方式访问数据
                }

            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());

            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close(); //记得要调用close方法
                }

                if (conn != null)
                {
                    conn.Close();
                }

            }

        }      

 

加入复杂一些的mysql语句

 static void Main(string[] args)
        {
            string cs = @"server=localhost;userid=root;
                        password=;database=db_fe";

            MySqlConnection conn = null;
            MySqlDataReader rdr = null;

            try
            {
                conn = new MySqlConnection(cs);
                conn.Open();

                string stm = @"SELECT Name, Title From Authors,
                Books WHERE Authors.Id=Books.AuthorId";

                MySqlCommand cmd = new MySqlCommand(stm, conn);
                rdr = cmd.ExecuteReader();

                Console.WriteLine("{0} {1}", rdr.GetName(0),
                    rdr.GetName(1).PadLeft(18));

                while (rdr.Read())
                {
                    Console.WriteLine(rdr.GetString(0).PadRight(18) +
                        rdr.GetString(1));
                }

            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());

            }
            finally
            {
                if (rdr != null)
                {
                    rdr.Close();
                }

                if (conn != null)
                {
                    conn.Close();
                }

            }

        }       

查询每个作者对应的著作

 

DataSet 和 MySqlDataAdapter

重量级的DataSet和MySqlDataAdapter

一个DataSet包含了数据表单中的数据和数据之间相互的关系。通常数据较多操作，或者要给winform绑定数据的时候需要用到，

MySqlDataAdapter提供了数据源和DataSet之间的接口

static void Main(string[] args)
        {
            string cs = @"server=localhost;userid=root;
                        password=;database=db_fe";

            MySqlConnection conn = null;

            try
            {

                conn = new MySqlConnection(cs);
                conn.Open();

                string stm = "SELECT * FROM Authors";
                MySqlDataAdapter da = new MySqlDataAdapter(stm, conn); 

                DataSet ds = new DataSet();

                da.Fill(ds, "Authors");
                DataTable dt = ds.Tables["Authors"];

                dt.WriteXml("authors.xml"); //写入到xml文件当中

                foreach (DataRow row in dt.Rows)
                {
                    foreach (DataColumn col in dt.Columns)
                    {
                        Console.WriteLine(row[col]);
                    }

                    Console.WriteLine("".PadLeft(20, '='));
                }

            }
            catch (MySqlException ex)
            {
                Console.WriteLine("Error: {0}", ex.ToString());

            }
            finally
            {
                if (conn != null)
                {
                    conn.Close();
                }

            }

           }

关键步骤在于将数据存入MySqlDataAdapter，再放入到DataSet da变量当中，

 

下一个例子，将数据绑定到一个winform控件

using System;
using System.Windows.Forms;
using System.Drawing;
using System.Data;
using MySql.Data.MySqlClient; 


class MForm : Form
{

    private DataGrid dg = null;
    private MySqlConnection conn = null;
    private MySqlDataAdapter da = null;        
    private DataSet ds = null;

    public MForm()
    {

       this.Text = "DataGrid";
       this.Size = new Size(350, 300);
       
       this.InitUI();
       this.InitData();
       
       this.CenterToScreen();
    }
    
    void InitUI()
    {    
        dg = new DataGrid();

        dg.CaptionBackColor = System.Drawing.Color.White;
        dg.CaptionForeColor = System.Drawing.Color.Black;
        dg.CaptionText = "Authors";

        dg.Location = new Point(8, 0);
        dg.Size = new Size(350, 300);
        dg.TabIndex = 0;
        dg.Parent = this;        
    }

    void InitData()
    {    
        string cs = @"server=localhost;userid=root;
                        password=;database=db_fe";

        string stm = "SELECT * FROM Authors";


        try 
        {
            conn = new MySqlConnection(cs);          
            conn.Open();
            ds = new DataSet();
            da = new MySqlDataAdapter(stm, conn);
            da.Fill(ds, "Authors");  
            
            dg.DataSource = ds.Tables["Authors"];

        } catch (MySqlException ex) 
        {
            Console.WriteLine("Error: " + ex.ToString());

        } finally 
        {
            if (conn != null) 
            {
                conn.Close();
            }
        }    
    }
}

class MApplication 
{
    public static void Main() 
    {
        Application.Run(new MForm());
    }
}