一、开始需要先将C#中与mysql相关的引用添加进来

using MySql.Data.MySqlClient;

二、创建一个database

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True");m_conn.Open();MySqlCommand cmd = new MySqlCommand("CREATE DATABASE TEST", m_conn);cmd.CommandTimeout = 12000;cmd.ExecuteNonQuery();

三、创建一个table

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlCommand cmd = new MySqlCommand("CREATE TABLE PERSON(ID INT(4) PRIMARY KEY NOT NULL AUTO_INCREMENT,PERSONCODE VARCHAR(255) DEFAULT NULL,PERSONNAME1 VARCHAR(255) DEFAULT NULL)", m_conn);cmd.CommandTimeout = 12000;cmd.ExecuteNonQuery();

 

四、连接mysql，创建一个连接对象，接着开其启对象

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");//创建mysql的连接对象m_conn.Open();//开启连接

五、查询

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();DataTable dt = new DataTable("ds"); DataSet ds = new DataSet(); //创建一个数据集来存放查询的返回结果MySqlDataAdapter da = new MySqlDataAdapter("SELECT * FROM TEST.PERSON", m_conn); //创建一个数据适配器来查找数据da.SelectCommand.CommandTimeout = 12000;da.Fill(ds, "ds"); //将查询到的数据填充到数据集dataset中，dt=ds.Tables[0];

六、插入

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open(); MySqlCommand cmd = new MySqlCommand("INSERT INTO test.person(personcode,personname1) values('123456','测试老王')", m_conn);cmd.CommandTimeout = 12000;int iRecordAffected = cmd.ExecuteNonQuery();//返回插入了几条数据

七、更新

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlCommand cmd = new MySqlCommand("update test.person set personname1='测试王二麻子' where personcode='123456'", m_conn);cmd.CommandTimeout = 12000;int iRecordAffected = cmd.ExecuteNonQuery();//返回更新了几条数据

八、删除

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlCommand cmd = new MySqlCommand("delete from test.person where personname1='测试王二麻子' and personcode='123456'", m_conn);cmd.CommandTimeout = 12000;int iRecordAffected = cmd.ExecuteNonQuery();//返回删除了几条数据

九、事务查询

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlTransaction m_trans = m_conn.BeginTransaction();DataTable dt = new DataTable("ds");DataSet ds = new DataSet();if (m_trans != null){  MySqlDataAdapter mda = new MySqlDataAdapter("SELECT personname1 FROM person", m_conn);  mda.SelectCommand.CommandTimeout = 12000;  mda.Fill(ds, "ds");  if (ds.Tables.Count > 0)  {      dt = ds.Tables[0];  }   m_trans.Commit();       //事务结束的标志就是调用事务的提交方法commit()}

十、事务插入，事务更新，事务删除除了SQL语句不同其余都一样

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlCommand cmd = new MySqlCommand("INSERT INTO person(personcode,personname1) values('123456','测试老王')", m_conn);MySqlTransaction m_trans = m_conn.BeginTransaction();cmd.Transaction = m_trans;cmd.CommandTimeout = 12000;if (m_trans != null){    int iRecordAffected = cmd.ExecuteNonQuery(); 　　 m_trans.Commit();       //事务结束的标志就是调用事务的提交方法commit()}

十一、事务回滚

MySqlConnection m_conn = new MySqlConnection("server=localhost;user id=root;password=root;persist security info=True;database=test");m_conn.Open();MySqlCommand cmd = new MySqlCommand("INSERT INTO person(personcode,personname1) values('123456','测试老王')", m_conn);MySqlTransaction m_trans = m_conn.BeginTransaction();cmd.Transaction = m_trans;cmd.CommandTimeout = 12000;if (m_trans != null){    int iRecordAffected = cmd.ExecuteNonQuery();     m_trans.Rollback();      //在事务提交之前调用事务的回滚方法，可以返回到事务开始前的数据库状态，相当于此次事务内对数据库的操作无效。}