sql helper 连接mysql_C#连接MySql数据库的操作教程

1、配置文件

在网上下载MySql.Data.dll文件并放到项目目录下并添加引用，完成配置。

2、MySqlHelper类

using MySql.Data.MySqlClient;

using System;

using System.Collections.Generic;

using System.Data;

using System.Linq;

using System.Text;

using System.Threading.Tasks;

namespace WindowsFormsApp1.SqlHelper

{

class MySqlHelper

{

private static string connstr = "server=localhost;user id=root;password=your password;database=your database;charset=utf8";

public static MySqlDataReader ExecuteReader(string sqlString)

{

MySqlConnection connection = new MySqlConnection(connstr);

MySqlCommand cmd = new MySqlCommand(sqlString, connection);

MySqlDataReader myReader = null;

try

{

connection.Open();

myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw new Exception(e.Message);

}

finally

{

if (myReader == null)

{

cmd.Dispose();

connection.Close();

}

}

}

public static MySqlDataReader ExecuteReader(string sqlString, params MySqlParameter[] cmdParms)

{

MySqlConnection connection = new MySqlConnection(connstr);

MySqlCommand cmd = new MySqlCommand();

MySqlDataReader myReader = null;

try

{

PrepareCommand(cmd, connection, null, sqlString, cmdParms);

myReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);

cmd.Parameters.Clear();

return myReader;

}

catch (System.Data.SqlClient.SqlException e)

{

connection.Close();

throw new Exception(e.Message);

}

finally

{

if (myReader == null)

{

cmd.Dispose();

connection.Close();

}

}

}

public static int ExecuteSql(string sql)

{

using (MySqlConnection conn = new MySqlConnection(connstr))

{

using (MySqlCommand cmd = new MySqlCommand(sql, conn))

{

try

{

conn.Open();

int rows = cmd.ExecuteNonQuery();

return rows;

}

catch (MySql.Data.MySqlClient.MySqlException e)

{

conn.Close();

throw e;

}

finally

{

cmd.Dispose();

conn.Close();

}

}

}

}

public static int ExecuteSql(string sqlString, params MySqlParameter[] cmdParms)

{

using (MySqlConnection connection = new MySqlConnection(connstr))

{

using (MySqlCommand cmd = new MySqlCommand())

{

try

{

PrepareCommand(cmd, connection, null, sqlString, cmdParms);

int rows = cmd.ExecuteNonQuery();

cmd.Parameters.Clear();

return rows;

}

catch (System.Data.SqlClient.SqlException E)

{

throw new Exception(E.Message);

}

finally

{

cmd.Dispose();

connection.Close();

}

}

}

}

public static DataSet GetDataSet(string sql)

{

using (MySqlConnection conn = new MySqlConnection(connstr))

{

DataSet ds = new DataSet();

try

{

conn.Open();

MySqlDataAdapter DataAdapter = new MySqlDataAdapter(sql, conn);

DataAdapter.Fill(ds);

}

catch (Exception ex)

{

throw ex;

}

finally

{

conn.Close();

}

return ds;

}

}

public static DataSet GetDataSet(string sqlString, params MySqlParameter[] cmdParms)

{

using (MySqlConnection connection = new MySqlConnection(connstr))

{

MySqlCommand cmd = new MySqlCommand();

PrepareCommand(cmd, connection, null, sqlString, cmdParms);

using (MySqlDataAdapter da = new MySqlDataAdapter(cmd))

{

DataSet ds = new DataSet();

try

{

da.Fill(ds, "ds");

cmd.Parameters.Clear();

}

catch (System.Data.SqlClient.SqlException ex)

{

throw new Exception(ex.Message);

}

finally

{

cmd.Dispose();

connection.Close();

}

return ds;

}

}

}

public static DataSet RunProcedureForDataSet(string storedProcName, IDataParameter[] parameters)

{

using (MySqlConnection connection = new MySqlConnection(connstr))

{

DataSet dataSet = new DataSet();

connection.Open();

MySqlDataAdapter sqlDA = new MySqlDataAdapter();

sqlDA.SelectCommand = BuildQueryCommand(connection, storedProcName, parameters);

sqlDA.Fill(dataSet);

connection.Close();

return dataSet;

}

}

private static void PrepareCommand(MySqlCommand cmd, MySqlConnection conn, MySqlTransaction trans, string cmdText,

MySqlParameter[] cmdParms)

{

if (conn.State != ConnectionState.Open)

{

conn.Open();

}

cmd.Connection = conn;

cmd.CommandText = cmdText;

if (trans != null)

{

cmd.Transaction = trans;

}

cmd.CommandType = CommandType.Text;

if (cmdParms != null)

{

foreach (MySqlParameter parm in cmdParms)

{

cmd.Parameters.Add(parm);

}

}

}

}

}