Link Search Menu Expand Document
RepoDB

Announcing RepoDB version 1.12.5

Dec 30, 2020

Today, we are announcing the availability of RepoDb v1.12.5 and its bundles, the extension libraries at >= v1.1.2. See below.

TL;DR;

These releases comes with the complete support to the DYNAMIC operations. It also comes with a lot of bug fixes and enhancements.

The major release feature is being called DYNAMICS as it is enabling the dynamic capabilities if we are to use it on the context of ORM. Therefore, do not get confused comparing the terminology against the dynamics within .NET ecosystem.

Push Operations

This release is now supporting the capability of pusing an instance of ExpandoObject or IDictionary<string, object> object into the push operations mentioned below.

And an instance of IEnumerable<ExpandoObject> or IEnumerable<IDictionary<string, object>> object into the push operations mentioned below.

Though the use-case ratio percentage is low, but at some cases (edge-cases), it is quitely important to get enabled.

The main target is just to fully enable the support to the dynamic-capabilities in the context of an ORM.

Sample Scenarios

Let us say, you would like to copy the data from one table into another table that is residing on a different database, then the dynamic code snippet below would do the job for you.

using (var sourceConnection = new SqlConnection(sourceConnection))
{
    var people = sourceConnection.QueryAll("[dbo].[Person]");
    using (var destinationConnection = new SqlConnection(destinationConnection))
    {
        destinationConnection.InsertAll("[sales].[Customer]", people);
    }   
}

When you query all the people from the [dbo].[Person] table, it returns an instance of IEnumerable<ExpandoObject>. The InsertAll is now capable of accepting such argument value.

If you think the data is quite big enough, you can instead use the BulkInsert operation like below.

destinationConnection.BulkInsert("[sales].[Customer]", people);

Also, notice that you have not even created a single model to accomplish the job, for as long the structure of the source table [dbo].[Person] is identical to the destination table [sales].[Customer], then you can do insert and/or bulk-process it.

You can as well filter the results based on the target fields from the destination table like below.

using (var sourceConnection = new SqlConnection(sourceConnection))
{
    using (var destinationConnection = new SqlConnection(destinationConnection))
    {
        var fields = DbFieldCache
            .Get("[sales].[Customer]", destinationConnection, transaction: null)
            .Select(e => e.AsField());
        var people = sourceConnection.QueryAll("[dbo].[Person]", fields: fields);
        destinationConnection.InsertAll("[sales].[Customer]", people);
    }   
}

Or, just add your own literal string like below.

using (var sourceConnection = new SqlConnection(sourceConnection))
{
    using (var destinationConnection = new SqlConnection(destinationConnection))
    {
        var fields = Field.From("Name", "Address", "SSN");
        var people = sourceConnection.QueryAll("[dbo].[Person]", fields: fields);
        destinationConnection.InsertAll("[sales].[Customer]", people);
    }   
}

As the ExpandoObject is also a dictionary object underneath, if the identity column is present at the destination table but is absent on the source list (i.e.: IEnumerable<ExpandoObject>, IEnumerable<IDictionary<string, object>>) during the operations (i.e.: Insert, InsertAll and BulkInsert), then, the new key-value-pair (KVP) entry will be created on the underlying dictionary object. The same logic is implied to the Merge, MergeAll and BulkMerge operations.

Operations

Below are the code snippets for each of the affected operation.

Insert

Below is a sample code snippet for Insert operation.

using (var connection = new SqlConnection(connection))
{
    var person = new Dictionary<string, object>
    {
        { "Name", "John Doe" },
        { "Address", "New York" }
    };
    var id = connection.Insert("[dbo].[Person]", person);
}

If the identity column is present on the [dbo].[Person] table, then a new entry of key-value-pair will be added to the person variable.

InsertAll/BulkInsert

Let us say you have this method.

public IEnumerable<IDictionary<string, object>> GetPeople(int count = 10)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Dictionary<string, object>
        {
            { "Name", $"Name {i}" },
            { "Address", $"Address{i}" }
        };
    }
}

Then, below is a sample code snippet for InsertAll operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(100);
    var insertedRows = connection.InsertAll("[dbo].[Person]", people);
}

And below is for BulkInsert operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(10000);
    var insertedRows = connection.BulkInsert("[dbo].[Person]", people);
}

If the identity column is present on the [dbo].[Person] table, then the new entries of the key-value-pairs will be added to each item of the people variable.

Merge

Below is a sample code snippet for Merge operation.

using (var connection = new SqlConnection(connection))
{
    var person = new Dictionary<string, object>
    {
        { "Id", 10045 }
        { "Name", "James Smith" },
        { "Address", "New York" }
    };
    var id = connection.Merge("[dbo].[Person]", person);
}

If the identity column is present on the [dbo].[Person] table, then a new entry of key-value-pair will be added to the person variable.

By default, the primary and/or identity column will be used as qualifier, but you can specify your custom qualifiers like below.

var id = connection.Merge("[dbo].[Person]", people, qualifiers: Field.From("Name"));

MergeAll/BulkMerge

Let us say you have this method.

public IEnumerable<IDictionary<string, object>> GetPeople(int count = 10)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Dictionary<string, object>
        {
            { "Id", i }
            { "Name", $"Name {i} - Merged" },
            { "Address", $"Address{i} - Merged" }
        };
    }
}

Then, below is a sample code snippet for MergeAll operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(100);
    var mergedRows = connection.MergeAll("[dbo].[Person]", people);
}

And below is for BulkMerge operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(10000);
    var mergedRows = connection.BulkMerge("[dbo].[Person]", people);
}

If the identity column is present on the [dbo].[Person] table, then the new entries of the key-value-pairs will be added to each item of the people variable.

By default, the primary and/or identity column will be used as qualifier, but you can specify your custom qualifiers like below.

// MergeAll
var mergedRows = connection.MergeAll("[dbo].[Person]",
    people,
    qualifiers: Field.From("Name"));

// BulkMerge
var mergedRows = connection.BulkMerge("[dbo].[Person]",
    people,
    qualifiers: Field.From("Name"));

Update

Below is a sample code snippet for Update operation.

using (var connection = new SqlConnection(connection))
{
    var person = new Dictionary<string, object>
    {
        { "Id", 10045 },
        { "Name", "John Doe" },
        { "Address", "California" },
        { "UpdatedUtc", DateTime.UtcNow }
    };
    var updatedRows = connection.Update("[dbo].[Person]", person);
}

By default, the primary and/or identity column will be used as qualifier, but you can specify your custom qualifiers like below.

var updatedRows = connection.Update("[dbo].[Person]", people, qualifiers: Field.From("Name"));

UpdateAll/BulkUpdate

Let us say you have this method.

public IEnumerable<IDictionary<string, object>> GetPeople(int count = 10)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Dictionary<string, object>
        {
            { "Id", i }
            { "Name", $"Name {i} - Updated" },
            { "Address", $"Address{i} - Updated" }
        };
    }
}

Then, below is a sample code snippet for Updated operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(100);
    var mergedRows = connection.UpdateAll("[dbo].[Person]", people);
}

And below is for BulkUpdate operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(10000);
    var updatedRows = connection.BulkUpdate("[dbo].[Person]", people);
}

By default, the primary and/or identity column will be used as qualifier, but you can specify your custom qualifiers like below.

// UpdateAll
var updatedRows = connection.UpdateAll("[dbo].[Person]",
    people,
    qualifiers: Field.From("Name"));

// BulkUpdate
var updatedRows = connection.BulkUpdate("[dbo].[Person]",
    people,
    qualifiers: Field.From("Name"));

BulkDelete

Let us say you have this method.

public IEnumerable<IDictionary<string, object>> GetKeys(int count = 10)
{
    for (var i = 0; i < count; i++)
    {
        yield return new Dictionary<string, object>
        {
            { "Id", i },
            { "Name", $"Name {i}" }
        };
    }
}

And below is for BulkDelete operation.

using (var connection = new SqlConnection(connection))
{
    var people = GetPeople(10000);
    var deletedRows = connection.BulkDelete("[dbo].[Person]", people);
}

By default, the primary and/or identity column will be used as qualifier, but you can specify your custom qualifiers like below.

var deletedRows = connection.BulkDelete("[dbo].[Person]", people, qualifiers: Field.From("Name"));

Or, you can also just bulk-delete through the list of primary keys.

var keys = new [] { 10045, ..., 11211 };
using (var connection = new SqlConnection(connection))
{
    var deletedRows = connection.BulkDelete("[dbo].[Person]", primaryKeys: keys);
}

Bug Fixes and Enhancements

Credits

Thank you to our contributors and to all the collaborators.

Contributors

  • fredliex - for issuing the following PRs
    • #657 - fix #650 TypeExtension.IsPlainType seems to misjudge the string property
    • #658 - some unit test case assign culture en-US when convert System.DateTime to string.
    • #680 - fix convert error for type level with propertyHandler. 🚀 This is awesome!
  • SergerGood - for issuing the following PRs. Btw, thanks for being a frequent PR contributor.
    • #625 - Benchmarks for the UpdateAll methods
    • #620 - Benchmarks for the GetAll methods
  • Swoorup - for issuing a PR #619
  • SpaceOgre - for issuing a PR #678
  • stefandevo - for issuing a PR #642 - Bug: Incorrect syntax near the keyword ‘WITH’ BulkMergeAsync
  • bobduncan - for issuing a PR #649 - #645 Correctly passed through tableName parameter in the DeleteAll

Collaborators

For our active collaborators on this release:

For the other collaborators of this release (filing the Issues/Bugs, Questions, Recommendations, Verification/Validations, etc)

Please continue to do so and many thanks!