C#--MySQL数据库基本操作（增、删、改、查）
写在前⾯：
常⽤数据库：
SQLserver：
Oracle：
MySQL：
Access：
在⽇常的⼯作中，通常⼀个项⽬会⼤量⽤的数据库的各种基本操作，因此⼩编⼏个常见的数据库的操作封装成了⼀个dll⽅便后续的开发使⽤。本⽂则主要是记录了C#对MySQL的连接、增、删、改、查的基本操作，如有什么问题还请各位⼤佬指教。后续也将对其他⼏个常⽤的数据库进⾏相应的整理。话不多说，直接开始码代码。
引⽤：
MySQL的引⽤需要去⼿动下载
using MySql.Data.MySqlClient;  //MySQL引⽤集
using System.Data;              //DataSet引⽤集
先声明⼀个MySqlConnection 便于后续使⽤。
private MySqlConnection mysql_con;
MySQL打开：
/// <summary>
/// MySQL open
/// </summary>
/// <param name="link">link statement</param>
/// <returns>Success:success; Fail:reason</returns>
public string MySQL_Open(string link)
{
  try
  {
    mysql_con = new MySqlConnection(link);
    mysql_con.Open();
    return "success";
  }
  catch (Exception ex)
  {
    return ex.Message;
  }
}
MySQL关闭：
/// <summary>
/// MySQL close
/// </summary>
/// <returns>Success:success Fail:reason</returns>
public string MySQL_Close()
{
  try
  {
    if (mysql_con == null)
    {
      return "No database connection";
    }
    if (mysql_con.State == ConnectionState.Open || mysql_con.State == ConnectionState.Connecting)
    {
      mysql_con.Close();
      mysql_con.Dispose();
    }
    else
    {
      if (mysql_con.State == ConnectionState.Closed)
      {
        return "success";
      }
      if (mysql_con.State == ConnectionState.Broken)
      {
        return "ConnectionState:Broken";
      }
    }
    return "success";
  }
  catch (Exception ex)
  {
    return ex.Message;
  }
}
MySQL的增删改：
/// <summary>
/// MySQL insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <returns>Success:success + Number of affected rows; Fail:reason</returns> public string MySQL_Insdelupd(string sql)
{
  try
  {
    int num = 0;
    if (mysql_con == null)
    {
      return "Please open the database connection first";
    }
    if (mysql_con.State == ConnectionState.Open)
    {
      MySqlCommand sqlCommand = new MySqlCommand(sql, mysql_con);      num = sqlCommand.ExecuteNonQuery();
    }
    else
    {
      if (mysql_con.State == ConnectionState.Closed)
      {
        return "Database connection closed";
      }
      if (mysql_con.State == ConnectionState.Broken)
      {
        return "Database connection is destroyed";
      }
      if (mysql_con.State == ConnectionState.Connecting)
      {
        return "The database is in connection";
      }
    }
    return "success" + num;
  }
  catch (Exception ex)
  {
    return ex.Message.ToString();
  }
}
MySQL的查：
/// <summary>
/// MySQL select
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="record">Success:success; Fail:reason</param>
/// <returns>select result</returns>
public DataSet MySQL_Select(string sql, out string record)
{
  try
  {
    //储存数据的⼯具初始化
    DataSet dataSet = new DataSet();
    if (mysql_con == null)
    {
      record = "Please open the database connection first";
      return dataSet;
    }
    if (mysql_con.State == ConnectionState.Open)
    {
      MySqlDataAdapter sqlDataAdapter = new MySqlDataAdapter(sql, mysql_con);
      sqlDataAdapter.Fill(dataSet, "sample");
      sqlDataAdapter.Dispose();
      record = "success";
      return dataSet;
    }
    if (mysql_con.State == ConnectionState.Closed)
    {
      record = "Database connection closed";
      return dataSet;
    }
    if (mysql_con.State == ConnectionState.Broken)
    {
      record = "Database connection is destroyed";
      return dataSet;
    }
    if (mysql_con.State == ConnectionState.Connecting)
    {
      record = "The database is in connection";
      return dataSet;
    }
    record = "ERROR";
    return dataSet;
  }
  catch (Exception ex)
  {
    DataSet dataSet = new DataSet();
    record = ex.Message.ToString();
    return dataSet;
  }
}
⼩编发现以上这种封装⽅式还是很⿇烦，每次对MySQL进⾏增删改查的时候还得先打开数据库，最后还要关闭，实际运⽤起来⽐较⿇烦。因此对上⾯两个增删改查的⽅法进⾏了重载，在每次进⾏操作时都先打开数据库，然后关闭数据库。
/// <summary>
/// MySQL insert,delete,update
/// </summary>
/// <param name="sql">insert,delete,update statement</param>
/// <param name="link">link statement</param>
/// <returns>Success:success + Number of affected rows; Fail:reason</returns>
public string MySQL_Insdelupd(string sql, string link)
{
  try
  {
    int num = 0;
    using (MySqlConnection con = new MySqlConnection(link))
    {
      con.Open();
      //操作数据库的⼯具SqlCommand
      MySqlCommand cmd = new MySqlCommand(sql, con); //(操作语句和链接⼯具)
      num = cmd.ExecuteNonQuery(); //执⾏操作返回影响⾏数
      con.Close();
      return "success" + num;
    }
  }
  catch (Exception ex)
  {
    return ex.Message.ToString();
  }
}
/// <summary>
/// MySQL select基本的增删改查语句
/// </summary>
/// <param name="sql">select statement</param>
/// <param name="link">link statement</param>
/// <param name="record">Success:success; Fail:reason</param>
/// <returns>select result</returns>
public DataSet MySQL_Select(string sql, string link, out string record)
{
  try
  {
    //储存数据的⼯具初始化
    DataSet ds = new DataSet();
    //相当于链接数据库的⼀个⼯具类（连接字符串）
    using (MySqlConnection con = new MySqlConnection(link))
    {
      con.Open(); //打开
      //⽤SqlConnection⼯具链接数据库，在通过sql查询语句查询结果现存⼊sql适配器      MySqlDataAdapter sda = new MySqlDataAdapter(sql, con); //(查询语句和连接⼯具)      sda.Fill(ds, "sample"); //将适配器数据存⼊DataSet⼯具中
      con.Close(); //⽤完关闭SqlConnection⼯具
      sda.Dispose(); //⼿动释放SqlDataAdapter
      record = "success";
      return ds;
    }
  }
  catch (Exception ex)
  {
    DataSet dataSet = new DataSet();
    record = ex.Message.ToString();
    return dataSet;
  }
}

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。