Get Started for MariaDB

RepoDB is a hybrid .NET ORM library for MariaDB RDBMS. The project is hosted at Github and is licensed with Apache 2.0.

MariaDB support ships as two separate, drop-in-compatible driver packages, both exposing the same MariaDbConnection/MariaDbParameter/MariaDbType API surface, each with its own bulk operations add-on:

RepoDb.MariaDb — built on RepoDb.Connector.MariaDb, a thin wrapper over MySql.Data.
RepoDb.MariaDb.BulkOperations — adds BulkInsert, BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey.
RepoDb.MariaDbConnector — built on RepoDb.Connector.MariaDbConnector, a thin wrapper over MySqlConnector.
RepoDb.MariaDbConnector.BulkOperations — adds BulkInsert, BulkMerge, BulkUpdate, BulkDelete and BulkDeleteByKey.

Installation

Install the library via NuGet using the Package Manager Console.

MariaDb (MySql.Data)

> Install-Package RepoDb.MariaDb

After installation, call the globalized setup method to initialize all dependencies for MariaDb.

GlobalConfiguration
    .Setup()
    .UseMariaDb();

To use bulk operations , install the RepoDb.MariaDb.BulkOperations package.

> Install-Package RepoDb.MariaDb.BulkOperations

MariaDbConnector

> Install-Package RepoDb.MariaDbConnector

After installation, call the globalized setup method to initialize all dependencies for MariaDb.

GlobalConfiguration
    .Setup()
    .UseMariaDbConnector();

To use bulk operations, install the RepoDb.MariaDbConnector.BulkOperations package.

> Install-Package RepoDb.MariaDbConnector.BulkOperations

Create a Table

The examples below assume the following table exists in the database.

CREATE TABLE IF NOT EXISTS `Person`
(
    `Id` bigint(20) NOT NULL AUTO_INCREMENT,
    `Name` text,
    `Age` int(11) DEFAULT NULL,
    `CreatedDateUtc` datetime DEFAULT NULL,
    PRIMARY KEY (`Id`)
);

Create a Model

The examples below assume the following model exists in the application.

public class Person
{
    public long Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public DateTime CreatedDateUtc { get; set; }
}

Creating a Record

To insert a row, use the Insert method.

var person = new Person
{
    Name = "John Doe",
    Age = 54,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new MariaDbConnection(ConnectionString))
{
    var id = connection.Insert(person);
}

To insert multiple rows, use the InsertAll operation.

var people = GetPeople(100);
using (var connection = new MariaDbConnection(ConnectionString))
{
    var rowsInserted = connection.InsertAll(people);
}

Insert returns the identity/primary column value, InsertAll returns the number of rows inserted, and both set the identity/primary property back onto the entity model (if present).

Querying a Record

To query a row, use the Query method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var person = connection.Query<Person>(e => e.Id == 1);
    /* Process the result here */
}

To query all rows, use the QueryAll method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var people = connection.QueryAll<Person>();
    /* Process the results here */
}

Merging a Record

To merge a row, use the Merge method.

var person = new Person
{
    Id = 1,
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new MariaDbConnection(ConnectionString))
{
    var id = connection.Merge(person);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

var person = new Person
{
    Name = "John Doe",
    Age = 57,
    CreatedDateUtc = DateTime.UtcNow
};
using (var connection = new MariaDbConnection(ConnectionString))
{
    var id = connection.Merge(person, qualifiers: (p => new { p.Name }));
}

To merge multiple rows, use the MergeAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Merged)");
using (var connection = new MariaDbConnection(ConnectionString))
{
    var affectedRecords = connection.MergeAll<Person>(people);
}

Merge returns the identity/primary column value, MergeAll returns the number of rows affected, and both set the identity/primary property back if present.

Deleting a Record

To delete a row, use the Delete method.

using (var connection = new MariaDbConnection(ConnectionString))
{
	var deletedRows = connection.Delete<Person>(1);
}

Other columns can also be used as qualifiers.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var deletedRows = connection.Delete<Person>(p => p.Name == "John Doe");
}

To delete all rows, use the DeleteAll method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var deletedRows = connection.DeleteAll<Person>();
}

A list of primary keys can also be passed for targeted deletion.

using (var connection = new MariaDbConnection(connectionString))
{
    var primaryKeys = new [] { 10045, 11001, ..., 12011 };
    var deletedRows = connection.DeleteAll<Person>(primaryKeys);
}

Delete and DeleteAll both return the number of rows affected.

Updating a Record

To update a row, use the Update method.

var person = new Person
{
    Id = 1,
    Name = "James Doe",
    Age = 55,
    DateInsertedUtc = DateTime.UtcNow
};
using (var connection = new MariaDbConnection(ConnectionString))
{
    var updatedRows = connection.Update<Person>(person);
}

Specific columns can also be targeted using a dynamic update.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var updatedRows = connection.Update("Person", new { Id = 1, Name = "James Doe" });
}

To update multiple rows, use the UpdateAll method.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new MariaDbConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people);
}

By default, the primary column is used as a qualifier. Custom qualifiers can also be specified.

var people = GetPeople(100);
people
    .AsList()
    .ForEach(p => p.Name = $"{p.Name} (Updated)");
using (var connection = new MariaDbConnection(ConnectionString))
{
    var updatedRows = connection.UpdateAll<Person>(people,
        qualifiers: (p => new { p.Name }));
}

Update and UpdateAll both return the number of rows affected.

Executing a Query

To execute a non-query statement, use the ExecuteNonQuery method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "DELETE FROM `Person` WHERE Id = @Id;";
    var affectedRecords = connection.ExecuteNonQuery(sql, new { Id = 1 });
}

To execute a query and return mapped objects, use the ExecuteQuery method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "SELECT * FROM `Person` ORDER BY Id ASC;";
    var people = connection.ExecuteQuery<Person>(sql);
    /* Process the results here */
}

To execute a query and return a scalar value, use the ExecuteScalar method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "SELECT MAX(Id) FROM `Person`;";
    var maxId = connection.ExecuteScalar<long>(sql);
}

To execute a query and return a DbDataReader, use the ExecuteReader method.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "SELECT * FROM `Person` ORDER BY Id ASC;";
    using (var reader = connection.ExecuteReader(sql))
    {
        /* Process the data reader here */
    }
}

Executing a Stored Procedure

To execute a stored procedure, use any of the execute methods above and pass CommandType.StoredProcedure to the commandType argument. This works the same way regardless of which driver package (RepoDb.MariaDb or RepoDb.MariaDbConnector) is installed.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("sp_GetPeople",
        commandType: CommandType.StoredProcedure);
}

Alternatively, use the CALL command directly, which does not require the commandType argument.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var people = connection.ExecuteQuery<Person>("CALL sp_GetPeople();");
}

Typed Result Execution

Single-column result sets can be mapped to any .NET CLR type via ExecuteQuery.

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "SELECT `Name` FROM `Person`;";
    var names = connection.ExecuteQuery<string>(sql);
}

Enumeration types are also supported.

public enum Gender
{
    Male,
    Female
}

using (var connection = new MariaDbConnection(ConnectionString))
{
    var sql = "SELECT `Gender` FROM `Person`;";
    var genders = connection.ExecuteQuery<Gender>(sql);
}

Returns an IEnumerable object.