ag.DbData.MySql

A library for working with MySql databases in .NET Framework, .NET Core and .NET Standard projects.

Usage

1. Add section to settings file (optional)

{
  "MySqlDbDataSettings": {
    "AllowExceptionLogging": false, // optional, default is "true"
    "ConnectionString": "YOUR_CONNECTION_STRING" // optional
  }
}

2. Add appropriate usings:

using ag.DbData.MySql.Extensions;
using ag.DbData.MySql.Factories;

3. Register services with extension method:

// ...
services.AddAgMySql();
// or
services.AddAgMySql(config.GetSection("MySqlDbDataSettings"));
// or
services.AddAgMySql(opts =>
{
    opts.AllowExceptionLogging = false; // optional
    opts.ConnectionString = YOUR_CONNECTION_STRING; // optional
});

4. Inject IMySqlDbDataFactory into your classes:

private readonly IMySqlDbDataFactory _mySqlFactory;

public MyClass(IMySqlDbDataFactory mySqlFactory)
{
    _mySqlFactory = mySqlFactory;
}

5. Obtain new instance of IDbDataObject by calling factory's Create method. IDbDataObject interface implements IDisposable, so use it into using directive:

using (var mySqlDbData = _mySqlFactory.Create(YOUR_CONNECTION_STRING))
{
    using (var t = mySqlDbData.FillDataTable("SELECT * FROM YOUR_TABLE"))
    {
        foreach (DataRow r in t.Rows)
        {
            Console.WriteLine(r[0]);
        }
    }
}

// in case you have defined connection string in configuration setting you may call Create() method
// without parameter
using (var mySqlDbData = _mySqlFactory.Create())
{
    using (var t = mySqlDbData.FillDataTable("SELECT * FROM YOUR_TABLE"))
    {
        foreach (DataRow r in t.Rows)
        {
             Console.WriteLine(r[0]);
        }
    }
}

Installation

Use Nuget package manager.

• ag.DbData.MySql

Available properties and methods

DbDataSettings properties

bool AllowExceptionLogging;

Specifies whether exceptions logging is allowed. Default value is true.

string ConnectionString;

Specifies connection string

Extension methods

IServiceCollection AddAgMySql(this IServiceCollection services)

Appends the registration of IDbDataFactory and IDbDataObject to IServiceCollection.

IServiceCollection AddAgMySql(this IServiceCollection services, IConfigurationSection configurationSection)

Appends the registration of IDbDataFactory and IDbDataObject to IServiceCollection and registers a configuration instance.

IServiceCollection AddAgMySql(this IServiceCollection services, Action<DbDataSettings> configureOptions)

Appends the registration of IDbDataFactory and IDbDataObject to IServiceCollection and configures the options.

IDbDataFactory methods

IDbDataObject Create()

Creates IDbDataObject, using connection string specified in settings.

IDbDataObject Create(string connectionString)

Creates IDbDataObject, specifying database connection string.

IDbDataObject Create(int defaultCommandTimeout)

Creates IDbDataObject, specifying default command timeout.

IDbDataObject properties

int? DefaultCommandTimeout { get;set; }

Defines default command timeout.

IDbDataObject methods

DataSet FillDataSet(string query);

Fills DataSet accordingly to specified SQL query. Returns DataSet.

DataSet FillDataSet(string query, int timeout);

Fills DataSet accordingly to specified SQL query and command timeout. Returns DataSet.

DataSet FillDataSet(string query, IEnumerable<string> tables);

Fills DataSet accordingly to specified SQL query, storing results in tables with names specified in tables parameter. Returns DataSet.

DataSet FillDataSet(string query, IEnumerable<string> tables, int timeout);

Fills DataSet accordingly to specified SQL query and command timeout, storing results in tables with names specified in tables parameter. Returns DataSet.

DataSet FillDataSetInTransaction(string query);

Fills DataSet in transaction accordingly to specified SQL query. Returns DataSet.

DataSet FillDataSetInTransaction(string query, int timeout);

Fills DataSet in transaction accordingly to specified SQL query and command timeout. Returns DataSet.

DataSet FillDataSetInTransaction(string query, IEnumerable<string> tables);

Fills DataSet in transaction accordingly to specified SQL query, storing results in specified tables. Returns DataSet.

DataSet FillDataSetInTransaction(string query, IEnumerable<string> tables, int timeout);

Fills DataSet in transaction accordingly to specified SQL query and command timeout, storing results in specified tables. Returns DataSet.

DataTable FillDataTable(string query);

Fills DataTable accordingly to specified SQL query. Returns DataTable.

DataTable FillDataTable(string query, int timeout);

Fills DataTable accordingly to specified SQL query and command timeout. Returns DataTable.

DataTable FillDataTableInTransaction(string query);

Fills DataTable in transaction accordingly to specified SQL query. Returns DataTable.

DataTable FillDataTableInTransaction(string query, int timeout);

Fills DataTable in transaction accordingly to specified SQL query and command timeout. Returns DataTable.

DataTable FillDataTable(DbCommand dbCommand);

Fills DataTable accordingly to specified DbCommand. Returns DataTable.

DataTable FillDataTable(DbCommand dbCommand, int timeout);

Fills DataTable accordingly to specified DbCommand with command timeout. Returns DataTable.

DataTable FillDataTableInTransaction(DbCommand dbCommand);

Fills DataTable in transaction accordingly to specified DbCommand. Returns DataTable.

DataTable FillDataTableInTransaction(DbCommand dbCommand, int timeout);

Fills DataTable in transaction accordingly to specified DbCommand with command timeout. Returns DataTable.

int Execute(string query);

Executes specified query. Returns numbers of rows affected by execution.

int Execute(string query, int timeout);

Executes specified query with specified command timeout. Returns numbers of rows affected by execution.

int ExecuteInTransaction(string query);

Executes specified query in transaction. Returns numbers of rows affected by execution.

int ExecuteInTransaction(string query, int timeout);

Executes specified query in transaction with specified command timeout. Returns numbers of rows affected by execution.

DbDataReader GetDataReader(string query);

Gets DbDataReader for specified SQL query. Returns DataReader.

DbDataReader GetDataReader(string query, int timeout);

Gets DbDataReader for specified SQL query with specified command timeout. Returns DataReader.

DbDataReader GetDataReader(string query, CommandBehavior commandBehavior);

Gets DbDataReader for specified SQL query, using one of the CommandBehavior values. Returns DataReader.

DbDataReader GetDataReader(string query, CommandBehavior commandBehavior, int timeout);

Gets DbDataReader for specified SQL query with specified command timeout, using one of the CommandBehavior values. Returns DataReader.

int ExecuteCommand(DbCommand cmd);

Executes DbCommand. Returns number of rows affected by execution.

int ExecuteCommand(DbCommand cmd, int timeout);

Executes DbCommand with specified command timeout. Returns number of rows affected by execution.

int ExecuteCommandInTransaction(DbCommand cmd);

Executes DbCommand in transaction. Returns number of rows affected by execution.

int ExecuteCommandInTransaction(DbCommand cmd, int timeout);

Executes DbCommand in transaction with specified command timeout. Returns number of rows affected by execution.

DataTable GetSchema();

Gets schema information for the data source of DbDataObject connection. Returns DataTable.

DataTable GetSchema(string collectionName);

Gets schema information for the data source of DbDataObject connection using the specified string for the schema name. Returns DataTable.

DataTable GetSchema(string collectionName, string[] restrictedValues);

Gets schema information for the data source of DbDataObject connection using the specified string for the schema name and the specified string array for the restriction values. Returns DataTable.

object GetScalar(string query);

Gets scalar value for specified SQL query. Returns object.

object GetScalar(string query, int timeout);

Gets scalar value for specified SQL query and command timeout. Returns object.

object GetScalarInTransaction(string query);

Gets scalar value for specified SQL query in transaction. Returns object.

object GetScalarInTransaction(string query, int timeout);

Gets scalar value for specified SQL query and command timeout in transaction. Returns object.

bool BeginTransaction();

Begins transaction on current database. Returns true if transaction has been started, false otherwise.

bool BeginTransaction(string connectionString);

Begins transaction on database specified in connection string. Returns true if transaction has been started, false otherwise.

void CommitTransaction();

Commits transaction.

void RollbackTransaction();

Rolls back transaction.

IDbDataObject asynchronous methods

Task<int> ExecuteAsync(string query);

Asynchronously executes specified SQL query. Returns a task representing the asynchronous operation.

Task<int> ExecuteAsync(string query, int timeout);

Asynchronously executes specified SQL query with specified command timeout. Returns a task representing the asynchronous operation.

Task<int> ExecuteAsync(string query, CancellationToken cancellationToken);

Asynchronously executes specified SQL query with cancellation token. Returns a task representing the asynchronous operation.

Task<int> ExecuteAsync(string query, int timeout, CancellationToken cancellationToken);

Asynchronously executes specified SQL query with command timeout and cancellation token. Returns a task representing the asynchronous operation.

Task<object> GetScalarAsync(string query);

Asynchronously gets scalar value for specified SQL query. Returns a task representing the asynchronous operation.

Task<object> GetScalarAsync(string query, int timeout);

Asynchronously gets scalar value for specified SQL query with command timeout. Returns a task representing the asynchronous operation.

Task<object> GetScalarAsync(string query, CancellationToken cancellationToken);

Asynchronously gets scalar value for specified SQL query with cancellation token. Returns a task representing the asynchronous operation.

Task<object> GetScalarAsync(string query, int timeout, CancellationToken cancellationToken);

Asynchronously gets scalar value for specified SQL query with command timeout and cancellation token. Returns a task representing the asynchronous operation.

Task<DataTable> FillDataTableAsync(string query);

Asynchronously fills DataTable accordingly to specified SQL query. Returns a task representing the asynchronous operation.

Task<DataTable> FillDataTableAsync(string query, int timeout);

Asynchronously fills DataTable accordingly to specified SQL query with command timeout. Returns a task representing the asynchronous operation.

Task<DataTable> FillDataTableAsync(string query, CancellationToken cancellationToken);

Asynchronously fills DataTable accordingly to specified SQL query with cancellation token. Returns a task representing the asynchronous operation.

Task<DataTable> FillDataTableAsync(string query, int timeout, CancellationToken cancellationToken);

Asynchronously fills DataTable accordingly to specified SQL query with command timeout and cancellation token. Returns a task representing the asynchronous operation.

Credits

ag.DbData.MySql is built with the following projects:

• MySQL.Data