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
}
}
.NetCore 3.1 MySqlHelper（一）
标签：href access ddr lis obj try amr pen text
