C#实现MySQL通⽤操作类
如有错误，欢迎各位提出哈~
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using MySql.Data.MySqlClient;
namespace WebApp.Common
{
public class MySqlHelper
{
/// <summary>
/// 连接字符串
/// </summary>
private static readonly string connectionString = ConfigurationManager.AppSettings["ConnectionString"].ToString();
/// <summary>
/// 执⾏查询操作，返回DataSet
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
mysql操作官方文档public static DataSet ExecuteDataSet(string commandText, CommandType commandType,params MySqlParameter[] parameters) {
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
MySqlCommand command =new MySqlCommand();
PrepareCommand(connection, command,null, commandText, commandType, parameters);
using(MySqlDataAdapter adapter =new MySqlDataAdapter(command))
{
DataSet dataSet =new DataSet();
adapter.Fill(dataSet);
command.Parameters.Clear();
return dataSet;
}
}
}
/// <summary>
/// 执⾏查询操作，返回DataTable
/// </summary>
/// <param name="commandText"></param>
/
// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static DataTable ExecuteDataTable(string commandText, CommandType commandType,params MySqlParameter[] parameters) {
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
MySqlCommand command =new MySqlCommand();
PrepareCommand(connection, command,null, commandText, commandType, parameters);
using(MySqlDataAdapter adapter =new MySqlDataAdapter(command))
{
DataTable dataTable =new DataTable();
adapter.Fill(dataTable);
command.Parameters.Clear();
return dataTable;
return dataTable;
}
}
}
/// <summary>
/// 执⾏查询操作，返回MySqlDataReader
/// </summary>
/// <param name="commandText"></param>
/
// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static MySqlDataReader ExecuteReader(string commandText, CommandType commandType,params MySqlParameter[] parameters) {
MySqlConnection connection =new MySqlConnection(connectionString);
MySqlCommand command =new MySqlCommand();
try
{
PrepareCommand(connection, command,null, commandText, commandType, parameters);
MySqlDataReader reader = command.ExecuteReader();
command.Parameters.Clear();
return reader;
}
catch
{
command.Parameters.Clear();
connection.Close();
throw new Exception();
}
}
/// <summary>
/// 执⾏查询操作，返回第⼀⾏第⼀列
/
// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/// <returns></returns>
public static object ExecuteScalar(string commandText, CommandType commandType,params MySqlParameter[] parameters)
{
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
MySqlCommand command =new MySqlCommand();
PrepareCommand(connection, command,null, commandText, commandType, parameters);
object obj = command.ExecuteScalar();
command.Parameters.Clear();
return obj;
}
}
/// <summary>
/// 执⾏⾮查询操作，返回受影响的⾏数
/// </summary>
/// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
/
// <returns></returns>
public static int ExecuteNonQuery(string commandText, CommandType commandType,params MySqlParameter[] parameters)
{
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
MySqlCommand command =new MySqlCommand();
PrepareCommand(connection, command,null, commandText, commandType, parameters);
try
{
int result = command.ExecuteNonQuery();
int result = command.ExecuteNonQuery();
command.Parameters.Clear();
return result;
}
catch
{
command.Parameters.Clear();
return0;
}
}
}
/// <summary>
/
// 执⾏数据库事务，不带参数
/// </summary>
/// <param name="list"></param>
/// <returns></returns>
public static int ExecuteNonQuery(IList<string> list)
{
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command =new MySqlCommand();
MySqlTransaction transaction = connection.BeginTransaction();
PrepareCommand(connection, command, transaction,null, CommandType.Text,null);
try
{
int count =0;
foreach(string sql in list)
{
command.CommandText = sql;
count += command.ExecuteNonQuery();
}
transaction.Commit();
return count;
}
catch
{
transaction.Rollback();
return0;
}
}
}
/// <summary>
/// 执⾏数据库事务，带参数
/// </summary>
/
// <param name="hashtable"></param>
/// <returns></returns>
public static int ExecuteNonQuery(Hashtable hashtable)
{
using(MySqlConnection connection =new MySqlConnection(connectionString))
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
try
{
int count =0;
foreach(DictionaryEntry entity in hashtable)
{
MySqlCommand command =new MySqlCommand();
PrepareCommand(connection, command, transaction, entity.Key.ToString(), CommandType.Text, entity.Value as MySqlParameter[]);                        count += command.ExecuteNonQuery();
}
transaction.Commit();
return count;
return count;
}
catch
{
transaction.Rollback();
return0;
}
}
}
/// <summary>
/// 设置MySqlCommand
/// </summary>
/// <param name="connection"></param>
/// <param name="command"></param>
/// <param name="transaction"></param>
/
// <param name="commandText"></param>
/// <param name="commandType"></param>
/// <param name="parameters"></param>
private static void PrepareCommand(MySqlConnection connection, MySqlCommand command, MySqlTransaction transaction,string commandText, CommandType commandType,params MySqlParameter[] parameters)
{
// 建⽴连接
if(connection.State != ConnectionState.Open)
{
connection.Close();
connection.Open();
}
command.Connection = connection;
// 设置SQL
if(!string.IsNullOrEmpty(commandText))
{
command.CommandText = commandText;
}
command.CommandType = commandType;
// 开启事务
if(transaction !=null)
{
command.Transaction = transaction;
}
// 设置参数
if(parameters !=null)
{
foreach(MySqlParameter parameter in parameters)
{
if(parameter.Value ==null|| parameter.Value.ToString()=="")
{
parameter.Value = DBNull.Value;
}
command.Parameters.Add(parameter);
}
}
}
}
}

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