C#MySQL操作

1.链接

//链接字符串
var str="server=localhost;port=3306;user=root;password=areyouok; database=myData;charset=utf8;"
//建立链接
MySqlConnection connection=new MySqlConnection(str);
connection.Open();

2.查询

1.无参数

//建立命令字符串
var str="SELECT * FROM Test;"
//执行命令
MySqlCommand cmd = new MySqlCommand(str,connection);
var reader=cmd.ExecuteReader();
//循环读取
while(reader.Read())
{
    WriteLine(reader.GetInt32(字段字符串));
}

2.有参数

//建立命令字符串
var str="SELECT * FROM Test WHERE id=@p1;"
//执行命令
MySqlCommand cmd = new MySqlCommand(str,connection);
cmd.Parameters.AddWithValue("p1",100);
var reader=cmd.ExecuteReader();
//循环读取
while(reader.Read())
{
    WriteLine(reader.GetInt32(字段字符串));
}

3.增加删除修改

//建立命令字符串
var str="INSERT INTO USER(username,password,registerdate) values('@p1','@p2','@p3')";
//执行命令
MySqlCommand cmd = new MySqlCommand(str,connection);
cmd.Parameters.AddWithValue("p1","小明");
cmd.Parameters.AddWithValue("p2",15668966);
cmd.Parameters.AddWithValue("p3","2015-5-6");
cmd.ExecuteNonQuery();

4.事务

//链接sql
string connetStr = "server=localhost;user=root;password=areyouok;database=myData;";
MySqlConnection connect = new MySqlConnection(connetStr);
connect.Open();
//建立事务
var transaction = connect.BeginTransaction();
//SQL语句
var code=ReadLine();
try
{
    //执行命令
    MySqlCommand cmd = new MySqlCommand(code, connect);
    cmd.ExecuteNonQuery();
}
catch (MySqlException ex)
{
    //失败滚回
    transaction.Rollback();
    connect.Close();
}
finally
{
    //提交
    if (connect.State != System.Data.ConnectionState.Closed)
    {
        transaction.Commit();//提交
        connect.Close();//关闭
    }
}