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
  • Enforces a maximum time span on the current command.
  • The default timeout can be set instead via OrmConfiguration.DefaultSqlStatementOptions.
• AttachToTransaction
  • Attaches the current command to an existing transaction.
• WithEntityMappingOverride
  • Overrides the entity mapping for the current statement. This is useful for Multiple mappings.
• OrderBy
  • Adds a SQL condition to order the records.
• Top&Skip
  • Limits the records returned by the query.
  • Don't forget to use OrderBy when using any of these.
• Where
  • Filters the records returned by the query.
• WithParameters
  • Sets the parameters to be used by the statement.
• When
  • Branches the statement builder based on a condition.
• WithAlias
  • Assigns an alias to the main entity, so that it can be referenced in the SQL clauses.
  • It is recommended to always use aliases when the statement contains multiple entities.
  • Check SQL statements and clauses for more information.
• Join
  • Includes a referenced entity into the query. More information on the JOINs can be found here. The following join options can be passed:
    • WithAlias
      • Sets up an alias for the referenced entity to be used in a relationship.
      • It is recommended to assign aliases to all the entities.
    • MapResults
      • If set tot true (default), the unique referenced entities will be set on the navigation properties.
      • This flag can be overridden on an individual relationship (see below)
    • LeftOuterJoin
      • Specifies a LEFT OUTER JOIN to be used.
    • InnerJoin
      • Specifies an INNER JOIN.
    • Referencing
      • Optionally specifies the entity on the left side of the join. This can be used to provide more information necessary to locate the desired relationship registration, to override the relationship registration or to set up the relationship when no relationship has been set up through the mappings:
        • FromAlias
          • Specifies the alias of the referencing entity. This should've been already set up with a call to WithAlias.
        • FromProperty
          • The navigation property on the referencing entity.
        • ToProperty
          • The navigation property on the referenced entity.

e.g.:

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