.net core 3.1 mysql_.NetCore 3.1 MySqlHelper

public sealed classMySqlHelper

{public static readonly string connectionString = AppSettings.Configuration["ConnectionStrings:MySql"];#region ExecuteMySqlScript

public static int ExecuteMySqlScript(stringpath)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand())

{using (StreamReader streamReader = newStreamReader(path, System.Text.Encoding.UTF8))

{

mySqlCommand.Connection=mySqlConnection;

mySqlCommand.CommandText=streamReader.ReadToEnd();

mySqlConnection.Open();returnmySqlCommand.ExecuteNonQuery();

}

}

}

}#endregion ExecuteMySqlScript

#region ExecuteNonQuery

public static int ExecuteNonQuery(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand(commandText, mySqlConnection))

{if (commandParameters != null)

{

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(commandParameters);

}

mySqlConnection.Open();returnmySqlCommand.ExecuteNonQuery();

}

}

}#endregion ExecuteNonQuery

#region GetEntities

public static List GetEntities(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand(commandText, mySqlConnection))

{

mySqlConnection.Open();if (commandParameters != null)

{

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(commandParameters);

}using (MySqlDataReader dataReader =mySqlCommand.ExecuteReader())

{

List list = new List();while(dataReader.Read())

{

List field = new List(dataReader.FieldCount);for (int i = 0; i < dataReader.FieldCount; i++)

{

field.Add(dataReader.GetName(i).ToLower());

}

T model= Activator.CreateInstance();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);

}returnlist;

}

}

}

}#endregion GetEntities

#region GetEntity

public static T GetEntity(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand(commandText, mySqlConnection))

{

mySqlConnection.Open();if (commandParameters != null)

{

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(commandParameters);

}using (MySqlDataReader dataReader =mySqlCommand.ExecuteReader())

{

T model= Activator.CreateInstance();if(dataReader.Read())

{

List field = new List(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);

}

}

}returnmodel;

}

}

}

}#endregion GetEntity

#region ExecuteReader

public static MySqlDataReader ExecuteReader(string commandText, paramsMySqlParameter[] commandParameters)

{

MySqlConnection mySqlConnection= newMySqlConnection(connectionString);

MySqlCommand mySqlCommand= newMySqlCommand(commandText, mySqlConnection);if (commandParameters != null)

{

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(commandParameters);

}

mySqlConnection.Open();returnmySqlCommand.ExecuteReader(CommandBehavior.CloseConnection);

}#endregion ExecuteReader

#region ExecuteScalar

public static object ExecuteScalar(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand(commandText, mySqlConnection))

{if (commandParameters != null)

{

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(commandParameters);

}

mySqlConnection.Open();returnmySqlCommand.ExecuteScalar();

}

}

}#endregion ExecuteScalar

#region ExecuteDataSet

public static DataSet ExecuteDataSet(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlDataAdapter mySqlDataAdapter = newMySqlDataAdapter(commandText, connectionString))

{

DataSet dataSet= newDataSet();if (commandParameters != null)

{

mySqlDataAdapter.SelectCommand.Parameters.Clear();

mySqlDataAdapter.SelectCommand.Parameters.AddRange(commandParameters);

}

mySqlDataAdapter.Fill(dataSet);returndataSet;

}

}#endregion ExecuteDataSet

#region ExecuteDataTable

public static DataTable ExecuteDataTable(string commandText, paramsMySqlParameter[] commandParameters)

{using (MySqlDataAdapter mySqlDataAdapter = newMySqlDataAdapter(commandText, connectionString))

{

DataTable dataTable= newDataTable();if (commandParameters != null)

{

mySqlDataAdapter.SelectCommand.Parameters.Clear();

mySqlDataAdapter.SelectCommand.Parameters.AddRange(commandParameters);

}

mySqlDataAdapter.Fill(dataTable);returndataTable;

}

}#endregion ExecuteDataTable

#region ExecuteTransaction

public static int ExecuteTransaction(Listlist)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand())

{

mySqlConnection.Open();

MySqlTransaction mySqlTransaction=mySqlConnection.BeginTransaction();

mySqlCommand.Connection=mySqlConnection;

mySqlCommand.Transaction=mySqlTransaction;try{int result = 0;foreach (var item inlist)

{

mySqlCommand.CommandText=item;

result+=mySqlCommand.ExecuteNonQuery();

}

mySqlTransaction.Commit();returnresult;

}catch(System.Exception)

{

mySqlTransaction.Rollback();return 0;

}

}

}

}public static int ExecuteTransaction(List>list)

{using (MySqlConnection mySqlConnection = newMySqlConnection(connectionString))

{using (MySqlCommand mySqlCommand = newMySqlCommand())

{

mySqlConnection.Open();

MySqlTransaction mySqlTransaction=mySqlConnection.BeginTransaction();

mySqlCommand.Connection=mySqlConnection;

mySqlCommand.Transaction=mySqlTransaction;try{int result = 0;foreach (var item inlist)

{

mySqlCommand.CommandText=item.Key;

mySqlCommand.Parameters.Clear();

mySqlCommand.Parameters.AddRange(item.Value);

result+=mySqlCommand.ExecuteNonQuery();

}

mySqlTransaction.Commit();returnresult;

}catch(System.Exception)

{

mySqlTransaction.Rollback();return 0;

}

}

}

}#endregion ExecuteTransaction}