Home

Usage

using Dapper.FastCrud
OrmConfiguration.DefaultDialect = SqlDialect.MsSql|MySql|SqLite|PostgreSql

Insert

dbConnection.Insert(newEntity);  

newEntity will have its properties partially updated with the db generated values on return

Select

Select by primary key(s)

dbConnection.Get(new Asset {Id = 10});

Select All

dbConnection.Find<Entity>();

Select record set

var selectParams = new {
  FirstName = "John"
}
dbConnection.Find<Entity>(statement => statement  
            .Where($"{nameof(Entity.FirstName):C} = {nameof(selectParams.FirstName):P}")  
            .OrderBy($"{nameof(Entity.LastName):C} DESC")  
            .Skip(10)  
            .Top(20)  
            .WithParameters(selectparams);

:C and :P used here are string formatter specifiers. Please refer to SQL statements and clauses for all the available formatter specifiers.

Update

Update by primary key(s)

dbConnection.Update(updatedEntity);   

updatedEntity will have its properties partially updated with the db generated values on return

Update all

dbConnection.BulkUpdate(new Asset {Name = "Unknown"});

Update record set

• Option 1: Create a new temporary mapping for partial updating the entities.

var bulkUpdateParams = new {
  AssetName = "workstation"
};
var partialUpdateMapping = OrmConfiguration.GetDefaultEntityMapping<EmployeeDbEntity>
                                           .UpdatePropertiesExcluding(prop => prop.IncludeInUpdates(false),
                                                                      nameof(Asset.IsLost));

dbConnection.BulkUpdate(new Asset {IsLost = true}, statement => statement  
            .Where($"{nameof(Asset.Name):C} = {nameof(bulkUpdateParams.AssetName):P}"))
            .WithEntityMappingOverride(partialUpdateMapping);

• Option 2: Create a new entity pointing to the same table, having just the primary keys and the properties you require for the update, that can be used strictly for bulk updates.

• Option 3: Create your own statement using FastCrud's built-in SQL formatter and run it straight through Dapper. That way you benefit from all the mappings done through FastCrud and the FastCrud's formatter, while still having the flexibility of creating your own SQL statements.

string statement = Sql.Format<Asset>($@"
     UPDATE {nameof(Asset):T} 
     SET {nameof(Asset.IsLost):C} = 1
     WHERE {nameof(Asset.Name):C} = {nameof(bulkUpdateParams.AssetName):P}
");

Delete

Delete by primary key(s)

dbConnection.Delete(new Asset {Id = 10});

Delete all

dbConnection.BulkDelete<Entity>();

Delete record set

dbConnection.BulkDelete<Asset>(statement => statement 
            .Where($"{nameof(Asset.IsLost):C}=1"));

Count

Count all

    dbConnection.Count<Entity>();

Count record set

    var countParams = new {
        AssetName = "workstation";
    };

    dbConnection.Count<Asset>(statement => statement  
                .Where($"{nameof(Asset.Name):C} = {nameof(countParams.AssetName:P}")  
                .WithParameters(countParams));

Async

Async methods have an identical usage

Options

As you've noticed in the previous examples, a number of options are immediately available for tweaking the statement. Their availability varies by type of operation.

• WithTimeout()
  • The default timeout can be set instead via OrmConfiguration.DefaultSqlStatementOptions.
• WithAlias()
  • It is recommended to always use aliases when the statement contains multiple entities.
• AttachToTransaction()
• WithEntityMappingOverride()
• OrderBy()
• Top&Skip()
  • Don't forget to use OrderBy when using any of these.
• StreamResults()
• Where()
• WithParameters()

e.g.:

dbConnection.Insert(entityToInsert, statement =>statement    
  .AttachToTransaction(dbTransaction)
  .WithTimeout(TimeSpan.FromSeconds(10)));