.net core 3.1 demo
using System.IO;
using System.Data;
using MySql.Data.MySqlClient;
using System.Collections.Generic;
using Microsoft.Extensions.Configuration;
using System;
using System.Reflection;
namespace Ado.Net
{
public class MySqlHelper
{
public string ConnectionString { get; }
public MySqlHelper()
{
var build = new ConfigurationBuilder();
build.SetBasePath(Directory.GetCurrentDirectory());
build.AddJsonFile("appsettings.json", true, true);
ConnectionString = build.Build()["ConnectionStrings:MySql"];
}
#region ExecuteNonQuery
public int ExecuteNonQuery(string commandText)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
return command.ExecuteNonQuery();
}
}
}
public int ExecuteNonQuery(string commandText, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
command.Parameters.AddRange(parameters);
connection.Open();
return command.ExecuteNonQuery();
}
}
}
#endregion ExecuteNonQuery
#region ExecuteReader
public MySqlDataReader ExecuteReader(string commandText)
{
MySqlConnection connection = new MySqlConnection(ConnectionString);
MySqlCommand command = new MySqlCommand(commandText, connection);
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
public MySqlDataReader ExecuteReader(string commandText, params MySqlParameter[] parameters)
{
MySqlConnection connection = new MySqlConnection(ConnectionString);
MySqlCommand command = new MySqlCommand(commandText, connection);
command.Parameters.AddRange(parameters);
connection.Open();
return command.ExecuteReader(CommandBehavior.CloseConnection);
}
#endregion ExecuteReader
#region ExecuteDataSet
public DataSet ExecuteDataSet(string commandText)
{
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
{
DataSet dataSet = new DataSet();
dataAdapter.Fill(dataSet);
return dataSet;
}
}
public DataSet ExecuteDataSet(string commandText, params MySqlParameter[] parameters)
{
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
{
DataSet dataSet = new DataSet();
dataAdapter.SelectCommand.Parameters.AddRange(parameters);
dataAdapter.Fill(dataSet);
return dataSet;
}
}
#endregion ExecuteDataSet
#region ExecuteDataTable
public DataTable ExecuteDataTable(string commandText)
{
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
{
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
return dataTable;
}
}
public DataTable ExecuteDataTable(string commandText, params MySqlParameter[] parameters)
{
using (MySqlDataAdapter dataAdapter = new MySqlDataAdapter(commandText, ConnectionString))
{
DataTable dataTable = new DataTable();
dataAdapter.SelectCommand.Parameters.AddRange(parameters);
dataAdapter.Fill(dataTable);
return dataTable;
}
}
#endregion ExecuteDataTable
#region ExecuteScalar
public object ExecuteScalar(string commandText)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
return command.ExecuteScalar();
}
}
}
public object ExecuteScalar(string commandText, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
command.Parameters.AddRange(parameters);
connection.Open();
return command.ExecuteScalar();
}
}
}
#endregion ExecuteScalar
#region ExecuteTransaction
public int ExecuteTransaction(List<string> list)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
int result = 0;
foreach (var item in list)
{
command.CommandText = item;
result = command.ExecuteNonQuery();
}
transaction.Commit();
return result;
}
catch (System.Exception)
{
transaction.Rollback();
return 0;
}
}
}
}
public int ExecuteTransaction(List<KeyValuePair<string, MySqlParameter[]>> list)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand())
{
connection.Open();
MySqlTransaction transaction = connection.BeginTransaction();
command.Connection = connection;
command.Transaction = transaction;
try
{
int result = 0;
foreach (var item in list)
{
command.CommandText = item.Key;
command.Parameters.Clear();
command.Parameters.AddRange(item.Value);
result = command.ExecuteNonQuery();
}
transaction.Commit();
return result;
}
catch (System.Exception)
{
transaction.Rollback();
return 0;
}
}
}
}
#endregion ExecuteTransaction
#region ExecuteMySqlScript
public int ExecuteMySqlScript(string path)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand())
{
using (StreamReader streamReader = new StreamReader(path, System.Text.Encoding.UTF8))
{
command.Connection = connection;
command.CommandText = streamReader.ReadToEnd();
connection.Open();
return command.ExecuteNonQuery();
}
}
}
}
#endregion ExecuteMySqlScript
#region GetEntities
public List<T> GetEntities<T>(string commandText)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
using (MySqlDataReader dataReader = command.ExecuteReader())
{
List<T> list = new List<T>();
while (dataReader.Read())
{
List<string> field = new List<string>(dataReader.FieldCount);
for (int i = 0; i < dataReader.FieldCount; i++)
{
field.Add(dataReader.GetName(i).ToLower());
}
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
}
}
list.Add(model);
}
return list;
}
}
}
}
public List<T> GetEntities<T>(string commandText, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
command.Parameters.AddRange(parameters);
using (MySqlDataReader dataReader = command.ExecuteReader())
{
List<T> list = new List<T>();
while (dataReader.Read())
{
List<string> field = new List<string>(dataReader.FieldCount);
for (int i = 0; i < dataReader.FieldCount; i++)
{
field.Add(dataReader.GetName(i).ToLower());
}
T model = Activator.CreateInstance<T>();
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
}
}
list.Add(model);
}
return list;
}
}
}
}
#endregion GetEntities
#region GetEntity
public T GetEntity<T>(string commandText)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
using (MySqlDataReader dataReader = command.ExecuteReader())
{
T model = Activator.CreateInstance<T>();
if (dataReader.Read())
{
List<string> field = new List<string>(dataReader.FieldCount);
for (int i = 0; i < dataReader.FieldCount; i++)
{
field.Add(dataReader.GetName(i).ToLower());
}
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
}
}
}
return model;
}
}
}
}
public T GetEntity<T>(string commandText, params MySqlParameter[] parameters)
{
using (MySqlConnection connection = new MySqlConnection(ConnectionString))
{
using (MySqlCommand command = new MySqlCommand(commandText, connection))
{
connection.Open();
command.Parameters.AddRange(parameters);
using (MySqlDataReader dataReader = command.ExecuteReader())
{
T model = Activator.CreateInstance<T>();
if (dataReader.Read())
{
List<string> field = new List<string>(dataReader.FieldCount);
for (int i = 0; i < dataReader.FieldCount; i++)
{
field.Add(dataReader.GetName(i).ToLower());
}
foreach (PropertyInfo property in model.GetType().GetProperties(BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance))
{
if (field.Contains(property.Name.ToLower()))
{
property.SetValue(model, Convert.ChangeType(dataReader[property.Name], property.PropertyType), null);
}
}
}
return model;
}
}
}
}
#endregion GetEntity
}
}
