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()); } }