DatabaseObjectMapper 4.0.0-beta

About DatabaseObjectMapper

The DatabaseObjectMapper allows you to quickly map classes to database tables and columns.

This is intended only for use with Microsoft Sql Server and MySQL. (If it works with other databases, it has not been tested.)

This version is a complete rewrite of the original DatabaseObjectMapper to allow for a more database agnostic approach.

This version is not compatible with version 3 and earlier of the DatabaseObjectMapper without major refactoring. Please see the Breaking Changes guide for details.

Getting Started

To begin using the DatabaseObjectMapper as an ORM after the NuGet Packages are installed, include the appropriate using statements.

using DatabaseObjectMapper;

Creating Model Classes

Model classes optionally implement the IDatabaseObject marker interface and use attributes to describe how they map to the database. (If you only plan to use IDbConnection extension methods, the interface is not required.)

[DefaultTable("mocks")]
public class MockModel : IDatabaseObject
{
    [PrimaryKey]
    [DbColumn("mock_id")]
    public int? Id { get; set; }

    [DbColumn("name")]
    public string Name { get; set; } = string.Empty;

    [DbColumn("date_added")]
    public DateTime DateAdded { get; set; } = DateTime.Now;

    [DbColumn("date_updated")]
    public DateTime? DateUpdated { get; set; }
}

Default Values and Table Names

While you can pass override table values when making calls, it is recommended to use the [DefaultTable("mocks")] attribute on the class. This table value will not be used for stored procedure based requests.

If you are using properties such as DateAdded or DateModified, set preferred default values on the properties or in your constructor when instantiating the class.

Initializing the Connection

The DatabaseObjectMapper is database agnostic and accepts whatever IDbConnection you create and configure yourself.

// Install packages for the database engine, e.g. Microsoft.Data.SqlClient, MySqlConnector, etc.
// MySql Example.
using var connection = new MySqlConnection(connectionString);
// Sql Server Example.
using var connection = new SqlConnection(connectionString);

Every database operation requires this connection to be passed in (or held by a DBOList<T>).

The DatabaseObjectMapper will automatically open the connection, and before returning will attempt to set the state of the connection to its prior state before the call.

Usage Option 1: IDbConnection Extension Methods

Use connection extension methods when you want the connection to be the entry point for queries and bulk operations. This is the most common approach for loading lists, running raw SQL, and performing bulk inserts, updates, or deletes.

Loading Data

// Load every row from the [DefaultTable] on MockModel.
List<MockModel> mocks = connection.LoadAll<MockModel>();

// Load with an optional table override or ORDER BY.
List<MockModel> ordered = connection.LoadAll<MockModel>(orderBy: "name");

// Run a parameterized SELECT and map results to a list.
List<MockLinkModel> links = connection.Select<MockLinkModel>(
    "SELECT * FROM mock_links WHERE mock_id = @mock_id",
    false,
    "@mock_id", 1);

Bulk Save, Insert, and Delete

// Insert or update every row based on primary key values (A value of 0 = insert, otherwise update).
// Warning: For faster operation, the primary keys from inserts are not written to the objects.
int rowsSaved = connection.SaveAll(models);

// Fast bulk insert; again, primary keys are not written back to the objects.
int rowsInserted = connection.InsertAll(models);

// Delete only rows whose primary keys match entries in the list.
int rowsDeleted = connection.DeleteAll(models);

Each bulk method accepts an optional overrideTable argument.

Because of bulk inserts, auto-increment values will not be set on the models in the list when calling InsertAll or SaveAll via the connection. These methods return the number of rows inserted or updated when the database engine supports it.

Loading Child Relationships

After loading parent records, populate [Relationship] properties on every item in the list:

List<MockModel> mocks = connection.LoadAll<MockModel>();
connection.LoadRelationships<MockModel, MockLinkModel>(mocks);

Dynamic and Scalar Queries

// Dynamic results do not require a class and will match the exact database column names.
IEnumerable<dynamic?> results = connection.SelectDynamic("SELECT mock_id, name FROM mocks", false, null);

// Immediately materialized dynamic list.
List<dynamic?> list = connection.SelectDynamicList("SELECT mock_id, name FROM mocks");

// Single scalar value.
long count = connection.SelectScalar<long>("SELECT COUNT(*) FROM mocks WHERE name = @name", false, "@name", "First Mock");

Non-Query and Stored Procedures

connection.NonQuery("UPDATE mocks SET name = @name WHERE mock_id = @id", false, "@name", "New Name", "@id", 1);

List<MockModel> results = connection.Select<MockModel>("GetMocksByName", true, "@name", "First Mock");

Every query-capable method accepts an isStoredProcedure flag. When true, the query argument is treated as a stored procedure name.

Convenience wrappers include SelectDynamicText, SelectDynamicStoredProcedure, and SelectDynamicList. These do not require specifying whether isStoredProcedure is true because this is chosen via the method name.

See Passing Parameterized Query Variables for parameter syntax.

Usage Option 2: IDatabaseObject Extension Methods

Use IDatabaseObject extension methods when you are working with a single model instance and want load, save, update, insert, delete, and relationship operations on that object directly.

The object must implement IDatabaseObject. All methods require the connection as an argument.

Load a Single Record by Primary Key

LoadByPK uses the first property decorated with [PrimaryKey] (ordered by Order) to build the WHERE clause.

MockModel model = new MockModel();
bool found = model.LoadByPK(connection, 1);
if (found)
{
    Console.WriteLine(model.Name);
}

LoadByPK returns true when a matching row was found and the object was populated, and false when no row was found or when the class has no [PrimaryKey] attribute. The object is only modified when the method returns true.

An optional table override is available:

model.LoadByPK(connection, 1, "override_table_name");

Save, Insert, Update, and Delete

MockModel model = new MockModel();
model.LoadByPK(connection, 1);
model.Name = "Replace Name";
model.Save<MockModel>(connection);

// Optional table override.
model.Save<MockModel>(connection, "save_to_different_table");

// Delete the row matching this object's primary key.
model.Delete<MockModel>(connection);

When calling Save<T>, the mapper reads [DbColumn] property values and determines INSERT vs UPDATE based on the [PrimaryKey] value (null or 0 = insert; otherwise update). See Auto-Increment Primary Key for details.

Load Child Relationships on a Single Parent

After loading a parent record, populate its [Relationship] properties:

MockModel model = new MockModel();
if (model.LoadByPK(connection, 1))
{
    model.LoadRelationships<MockModel, MockLinkModel>(connection);
    foreach (MockLinkModel link in model.Links)
    {
        Console.WriteLine($"{model.Name}: {link.Url}");
    }
}

Complete Single-Object Example

public class MockExample
{
    private readonly IDbConnection _connection;

    public MockExample(IDbConnection connection)
    {
        _connection = connection;
    }

    public void ExampleLoadAndSave()
    {
        MockModel model = new MockModel();
        model.LoadByPK(_connection, 1);
        if (model.Name == "First Mock")
        {
            model.Name = "Replace Name";
            model.Save<MockModel>(_connection);
        }
    }
}

Usage Option 3: List<T> Extension Methods

List<T> extension methods mirror the connection bulk operations but are called on the list, with the connection passed as the first argument. They are thin wrappers over the IDbConnection methods.

List<MockModel> models = new List<MockModel>();
models.Add(new MockModel() { Name = "Test 1" });
models.Add(new MockModel() { Name = "Test 2" });
models.Add(new MockModel() { Name = "Test 3" });

// Insert or update every row based on primary key values.
models.SaveAll(connection);

// Fast bulk insert.
models.InsertAll(connection);

// Delete rows whose primary keys match entries in the list.
models.DeleteAll(connection);

// Populate [Relationship] properties on every parent in the list.
models.LoadRelationships<MockModel, MockLinkModel>(connection);

// Read the [DefaultTable] attribute from T without needing a connection.
string tableName = models.GetDefaultTable();

Each method accepts an optional overrideTable argument and returns the number of rows affected (except GetDefaultTable and LoadRelationships, which return the list).

Usage Option 4: DBOList<T>

DBOList<T> wraps a List<T> with a built-in connection and convenience methods for select, save, delete, and relationship loading. Use this when you want list behavior with the connection held by the list object.

using var connection = new MySqlConnection(connectionString);
DBOList<MockLinkModel> links = new DBOList<MockLinkModel>(connection);

links.SelectText("SELECT * FROM mock_links WHERE mock_id = @mock_id", "@mock_id", 1);
foreach (MockLinkModel link in links)
{
    // Do something with each link that was found.
}

links.Add(new MockLinkModel { MockId = 1, Url = "https://example.com", Name = "New Link" });
int rowsSaved = links.Save();

int rowsDeleted = links.Delete();

Load child relationships on every item currently in the list:

DBOList<MockModel> mocks = new DBOList<MockModel>(connection);
mocks.SelectText("SELECT * FROM mocks WHERE mock_id > @id", "@id", 100);
mocks.LoadRelationships<MockLinkModel>();

Available DBOList<T> methods:

• Select(query, isStoredProcedure, parameters) / SelectText(query, parameters) / SelectStoredProcedure(query, parameters) — replaces list contents with query results.
• AppendSelect(query, isStoredProcedure, parameters) — appends query results to the current list.
• Save(overrideTable) — INSERT or UPDATE for every entry in the list. Returns the number of rows inserted or updated.
• InsertAll(overrideTable) — bulk INSERT for every entry in the list. Returns the number of rows inserted.
• Delete(overrideTable) — DELETE for every entry matched by primary key. Returns the number of rows deleted.
• LoadRelationships<U>(relationshipTable) — populate [Relationship] child properties on every parent in the list.
• Reset() — clears the list and sets IsDataLoaded to false.
• IsDataLoaded — true after a Select or AppendSelect call.

If no table name is supplied to the constructor, DBOList<T> falls back to the [DefaultTable] attribute on T. T must have a parameterless constructor.

Loading Child Relationships

Use the [Relationship("child_table")] attribute on a List<ChildModel> property to declare a one-to-many relationship. After loading parent records, call LoadRelationships using any of the supported entry points:

Child rows are matched using the parent class database-generated primary key column. In the example below, mock_links.mock_id is queried with the value of MockModel.Id.

The parent primary key must be greater than zero, and the child class must have a parameterless constructor.

[DefaultTable("mocks")]
public class MockModel : IDatabaseObject
{
    [PrimaryKey]
    [DbColumn("mock_id")]
    public int? Id { get; set; }

    [DbColumn("name")]
    public string Name { get; set; } = string.Empty;

    [Relationship("mock_links")]
    public List<MockLinkModel> Links { get; set; } = new List<MockLinkModel>();
}

[DefaultTable("mock_links")]
public class MockLinkModel : IDatabaseObject
{
    [PrimaryKey]
    [DbColumn("mock_link_id")]
    public int? LinkId { get; set; }

    [DbColumn("mock_id")]
    public long MockId { get; set; }

    [DbColumn("url")]
    public string Url { get; set; } = string.Empty;

    [DbColumn("name")]
    public string Name { get; set; } = string.Empty;
}

Full workflow example:

List<MockModel> mocks = connection.LoadAll<MockModel>();
connection.LoadRelationships<MockModel, MockLinkModel>(mocks);

foreach (MockModel mock in mocks)
{
    foreach (MockLinkModel link in mock.Links)
    {
        Console.WriteLine($"{mock.Name}: {link.Url}");
    }
}

The table name is taken from the [Relationship("...")] attribute on the property. You can override it when calling LoadRelationships:

mocks.LoadRelationships<MockModel, MockLinkModel>(connection, "mock_links");

LoadRelationships returns the same list or object instance with child properties assigned. Passing an empty list returns an empty list without querying the database.

Relationships are read-only helpers for loading related data. Saving or deleting child rows still uses Save, SaveAll, Delete, or DeleteAll on the child models directly. Relationships are not automatically saved via these methods.

ASP.NET Core Dependency Injection

Register the connection via dependency injection and create a new connection per request via a factory or a scoped service.

// Program.cs
builder.Services.AddScoped<IDbConnection>(sp =>
{
    var connectionString = builder.Configuration.GetConnectionString("Default");
    return new MySqlConnection(connectionString);
});

The scoped build will dispose at the end of the request.

Inject into your services or within Razor Page:

public class UserService
{
    private readonly IDbConnection _connection;

    public UserService(IDbConnection connection)
    {
        _connection = connection;
    }

    public List<User> GetUsers()
    {
        return _connection.Select<User>("SELECT * FROM users", false, null);
    }
}

Creating New Database Records

If the auto-increment primary key is 0 or null, Save performs an INSERT. If the primary key value is greater than 0, Save performs an UPDATE.

public bool Example(IDbConnection connection)
{
    Vendor vendor = new Vendor();
    vendor.Name = "Bob";
    vendor.Phone = "212-555-1212";
    vendor.Save<Vendor>(connection);
    if (vendor.Id > 0)
        return true;
    return false;
}

When Save succeeds on an insert, the generated primary key is written back to the object (unless [PrimaryKey(DatabaseGenerated = false)]).

If you set the auto-increment primary key back to 0 after loading an object, and then call .Save, it would create a new record.

// In this example, pretend we have a single row with a vendor named 'Bob'.
// Remember to set LIMIT or TOP to only pull one record.
Vendor vendor = connection.SelectFirst<Vendor>("SELECT * FROM vendors WHERE name = @name LIMIT 1", false, "@name", "Bob");
if (vendor.Id > 0)
{
  // We found a result.  If we now set the Id to 0, then save again, it would recreate a new record instead of updating the existing one.
  vendor.Id = 0;
  // Because the Id is a 0, we perform an INSERT.  If we did not change the Id, it would UPDATE instead.
  vendor.Save<Vendor>(connection);
  // We now have two vendors named 'Bob' in the vendors table.
}

Auto-Increment Primary Key

If using an auto-increment and you want auto-assignment, the property with [PrimaryKey] should be the first one. The first primary key receives the generated value.

MySQL / MariaDB and SQLite return 0 if no LAST_INSERT_ID() is on the table.

SQL Server normally returns NULL if no SCOPE_IDENTITY() is returned; this is wrapped to return 0 for consistency across database types.

Non-Auto-Increment and Composite Primary Keys

By default, each [PrimaryKey] is treated as database generated, and the first instance is the auto-increment. Use [PrimaryKey(DatabaseGenerated = false)] to force the INSERT to include the primary key value.

Composite primary keys are supported; all key values must be supplied when creating a new record, and you must set DatabaseGenerated to false for all composite primary keys to perform an INSERT each time.

Loading Rows Into Dynamic Objects

using var connection = new MySqlConnection(connectionString);
dynamic results = connection.SelectDynamic("SELECT mock_id, name FROM mocks", false, null);
if (results != null)
{
    foreach (dynamic d in results)
    {
        Console.WriteLine($"{d.name} - {d.mock_id}");
    }
}

SelectDynamic returns an enumerable of ExpandoObject. Check for null before iterating.

Additional convenience wrappers:

List<dynamic?> results = connection.SelectDynamicList("SELECT mock_id, name FROM mocks");
IEnumerable<dynamic?> textResults = connection.SelectDynamicText("SELECT mock_id, name FROM mocks WHERE name = @name", "@name", "First Mock");
IEnumerable<dynamic?> procResults = connection.SelectDynamicStoredProcedure("GetMocks", "@name", "First Mock");

Scalar Queries

object? result = connection.SelectScalar("SELECT COUNT(*) FROM mocks", false, null);
long count = connection.SelectScalar<long>("SELECT COUNT(*) FROM mocks WHERE name = @name", false, "@name", "First Mock");
string description = connection.SelectScalar<string>("SELECT description FROM mocks WHERE name = @name LIMIT 1", false, "@name", "First Mock");

SelectScalar<T> returns default(T) when the database returns NULL or no rows are returned.

Passing Parameterized Query Variables to Database Requests

The DatabaseObjectMapper supports two ways to supply parameters depending on the operation.

1. Inline parameter list — for raw SQL queries

For methods that accept a raw SQL string (Select, SelectFirst, SelectDynamic, NonQuery, etc.), parameters are supplied as an inline, comma-separated list of alternating parameter names and values in the final params object[] argument.

List<MockLinkModel> links = connection.Select<MockLinkModel>(
    "SELECT * FROM mock_links WHERE mock_id = @mock_id AND name = @name",
    false,
    "@mock_id", 1,
    "@name", "Testing");

Rules:

• Values alternate "@parameterName", value1, "@parameterName2", value2, ....
• Every parameter in the SQL string must appear in the list with matching names.
• Parameter names cannot be empty; the list length must be even.
• Pass null when there are no parameters.
• null values are translated to DBNull.Value.
• Use whatever placeholder syntax your ADO.NET provider expects.

There is no automatic binding of query parameters to class property names when writing raw SQL in Version 4.

2. Object property values — for IDatabaseObject Save, Insert, and Update

When calling Save<T>, Insert<T>, or Update<T> on an IDatabaseObject, no parameter list is needed. The mapper reads [DbColumn] property values automatically.

MockLinkModel link = new MockLinkModel
{
    MockId = 1,
    Url = "https://www.example.com",
    Name = "Example Link"
};
object? newId = link.Save<MockLinkModel>(connection);

link.Name = "Updated Name";
link.Save<MockLinkModel>(connection);

Automatic Column Mapping with [AutoMap]

By default, only properties with [DbColumn("db_column_name")] (or [DbColumn("db_column_name")]) are mapped. With [AutoMap] on the class, unannotated properties are matched to columns case-insensitively with underscores ignored.

[AutoMap]
[DefaultTable("mocks")]
public class MockModel : IDatabaseObject
{
    [PrimaryKey]
    [DbColumn("mock_id")]
    public int Id { get; set; }

    // Automatically mapped to short_description.
    public string ShortDescription { get; set; } = string.Empty;
}

[AutoMap] only affects read operations (Select, SelectFirst, LoadAll, LoadByPK). You can still use [DbColumn] for properties that need explicit names.

Utility Extension Methods

General-purpose helpers under DatabaseObjectMapper.ExtensionMethods (not database-specific):

using DatabaseObjectMapper.ExtensionMethods;

• IEnumerable<T>.ToCSVString(separator = ", ") — joins items with the specified separator.
• T.IsInAny(params T[] list) — checks whether a value matches any of the supplied values.

Error Handling

• DatabaseObjectMapperTableConfigurationException — a method needs a table name but none was supplied and the class has no [DefaultTable] attribute.
• DatabaseObjectMapperException — base exception type for other mapper errors.

Database provider errors (SqlException, MySqlException, etc.) are not wrapped.

Database Provider Support and Identifier Quoting

DatabaseObjectMapper is built and tested against Microsoft SQL Server and MySQL/MariaDB. Table and column identifiers are quoted based on the runtime type of IDbConnection ([column] for SqlConnection, column for MySqlConnection), so the same model class can be reused against either database type when schema and SQL are compatible.

Engine-specific SQL (SELECT TOP 3 vs LIMIT 3) must be adjusted when switching engines.

Release Notes

Version 4

Version 4 is a complete rewrite of the DatabaseObjectMapper.

As such, major changes were necessary. This allows DatabaseObjectMapper to be more database agnostic and does not rely on unnecessary packages to support every database engine.

This version works using whatever IDbConnection you create and configure yourself, rather than managing connection strings internally.

Because of this, version 4 is not compatible with the previous versions of the DatabaseObjectMapper. Even the class attributes may need to be adjusted.

Version 4 Breaking Changes

Version 4 intentionally removes a large amount of functionality from Version 3.x in favor of a smaller surface area and focuses on IDbConnection extension methods. If you are upgrading from a 3.x or earlier release, review the following list carefully:

1. No more base class. Classes no longer inherit from DatabaseObject class. IDatabaseObject is now an empty marker interface, and all behavior (Save, Insert, Update, Delete) is provided via extension methods that require you to pass in the connection. (Not the connection string!)
2. No managed connection string. ConnectionStringManager and DatabaseOptionsManager (which handled global/requested based connection strings via AsyncLocal<string>) have been removed entirely. Every call now requires an explicit IDbConnection argument. This is where the ConnectionString should be set at the time of the connection creation.
3. DefaultTable is no longer a settable instance property. In prior versions, DefaultTable could be assigned at runtime (this.DefaultTable = "mocks";). In Version 4, the table is determined solely by the [DefaultTable("...")] class attribute or by explicitly passing an overrideTable argument to each call.
4. Column Attribute Renamed While Column(tableName) still exists, to avoid conflicts with standard annotations, use DbColumn(tableName) instead when labeling the tables for properties.
5. DatabaseRequest and DatabaseRequestWithDefaults have been removed. These static classes (and their SQL Server/MySQL specific namespaces DatabaseObjectMapper.SqlServer / DatabaseObjectMapper.MySql) have been replaced by extension methods on IDbConnection. So, you will use connection.Select<T>(...); instead of DatabaseRequest.Select<T>(...);;
6. IDatabaseConnector abstraction removed. IDatabaseConnector, SqlServerDatabaseConnector, and MySqlDatabaseConnector no longer exist. The mapper talks directly to the IDbConnection you provide and detects the database type from the connection runtime type name.
7. Relationships behavior changed. The [Relationship] attribute and LoadRelationships<T>() methods exclusively use the auto-increment primary key values. The children, in order to assign values and add them as lists, must possess empty constructors for this assignment to work.
8. Automatic table migrations removed. PerformAutomaticMigrations (and the SQL Server-only automatic table/column creation feature it provided) has been removed. All database tables and columns must exist before the mapper is used with them.
9. Parameter configuration classes removed. SqlParameterConfiguration and MySqlParameterConfiguration no longer exist, along with every method overload that accepted them (SelectFirstWithParameters, SelectWithParameters, SelectScalarWithParameters, NonQueryWithParameters, etc.). Passing a raw List<SqlParameter> or List<MySqlParameter> is also no longer supported. Use the inline "@name", value parameter style described above for every query.
10. No automatic property-to-parameter binding. Previously, calling .Select("... WHERE id = @Id") on a DatabaseObject instance would automatically bind @Id to the object's Id property. This automatic binding has been removed; every parameter must be supplied explicitly in the inline parameter list.
11. Most per-instance load/save/delete helper methods removed. Instance methods such as LoadByColumnValue, MapNullsByPK, MapMissingFields, GetPrimaryKeyName, GetPrimaryKeyValue, IsPrimaryKeySpecified, IsNewRecord, and the no-argument Update()/Insert()/Save()/Delete() methods no longer exist. Saving, inserting, updating, and deleting a single object are performed with the Save, Insert, Update, and Delete extension methods, all of which require the connection to be passed in. LoadByPK has been re-introduced as an IDatabaseObject extension method with a required connection argument — see Usage Option 2 above.
12. AreColumnsAutoMapped instance property changed. Use the equivalent functionality with the class-level [AutoMap] attribute. It is now supported for every database type rather than being SQL Server only.
13. List based extension methods such as .SaveAll() and .DeleteAll() require connection parameter. The List extension methods no longer accept a connection string. Pass the connection instead, e.g. myData.SaveAll(connection);.
14. Miscellaneous static helpers removed. Exists(), BulkInsert()/BulkQuery(), and GetDatabaseNameFromConnectionString() have all been removed with no direct replacement; use SelectScalar or your own ADO.NET calls instead.
15. **DBOList<T> requires a connection and has been simplified.** DBOList<T> no longer has a DatabaseType enum, a settable ConnectionString property, or parameterless constructors that rely on a globally configured connection string. The connection is now required to be passed into the constructor.
16. No bundled database drivers. Version 3 shipped with Microsoft.Data.SqlClient and MySqlConnector as package dependencies. Version 4 has no database driver dependencies at all — add whichever ADO.NET provider package you need (Microsoft.Data.SqlClient, MySqlConnector, etc.) to your own project and construct the IDbConnection yourself.
17. Target framework updated. The minimum supported target framework has moved from net8.0 to net10.0.