一、开始需要先将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= newDataSet();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(); //在事务提交之前调用事务的回滚方法,可以返回到事务开始前的数据库状态,相当于此次事务内对数据库的操作无效。
}