c# mysql修改_C#--MySQL数据库基本操作（增、删、改、查）

写在前面：

常用数据库：

在日常的工作中，通常一个项目会大量用的数据库的各种基本操作，因此小编几个常见的数据库的操作封装成了一个dll方便后续的开发使用。本文则主要是记录了C#对MySQL的连接、增、删、改、查的基本操作，如有什么问题还请各位大佬指教。后续也将对其他几个常用的数据库进行相应的整理。话不多说，直接开始码代码。

引用：

MySQL的引用需要去手动下载

using MySql.Data.MySqlClient;　　//MySQL引用集

using System.Data; //DataSet引用集

先声明一个MySqlConnection 便于后续使用。

private MySqlConnection mysql_con;

MySQL打开：

///

/// MySQL open

///

/// link statement

/// Success:success; Fail:reason

public string MySQL_Open(string link)

{

try

{

mysql_con = new MySqlConnection(link);

mysql_con.Open();

return "success";

}

catch (Exception ex)

{

return ex.Message;

}

}

MySQL关闭：

///

/// MySQL close

///

/// Success:success Fail:reason

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的增删改：

///

/// MySQL insert,delete,update

///

/// insert,delete,update statement

/// Success:success + Number of affected rows; Fail:reason

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的查：

///

/// MySQL select

///

/// select statement

/// Success:success; Fail:reason

/// select result

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进行增删改查的时候还得先打开数据库，最后还要关闭，实际运用起来比较麻烦。因此对上面两个增删改查的方法进行了重载，在每次进行操作时都先打开数据库，然后关闭数据库。

///

/// MySQL insert,delete,update

///

/// insert,delete,update statement

/// link statement

/// Success:success + Number of affected rows; Fail:reason

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

}

}

///

/// MySQL select

///

/// select statement

/// link statement

/// Success:success; Fail:reason

/// select result

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;

}

}