Skip to content

Get Started

Jump to bottom
ErnestoDeLucia edited this page Apr 12, 2017 · 17 revisions

Welcome to the FluentSql wiki!

FluentSql has a Dapper dependency, and it has been tested with Dapper versions 1.4 to 1.52. You will need a reference to Dapper in your project.

FluentSql reads the database(s) metadata, so it can produce the Sql queries without having to rely on model attributes. On system start up, FluentSql needs to be initialized. The easiest way to initialize FluentSql is by using the database connection object.

var mapper = new EntityMapper(dbConnection);

This line of code will do the following

  1. Read the database metadata that is specified in the connection object. FluentSql collects information about every single column of every table.
  2. Search the executing libraries for types that match the database table names. A preferred method, is to provide the assembly where the Models that correspond to the database tables.
  3. FluentSql puts the above information in a public static dictionary for runtime use.

While there are other EntityMapper constructors, the following is the constructor with all the available options:

 public EntityMapper(IDbConnection dbConnection, IEnumerable<Database> targetDatabases, 
                     Assembly[] assembliesOfModelTypes = null, IDatabaseMapper defaultDatabaseMapper = null, 
                     Action onPostEntityMapping = null, ISqlGenerator sqlGenerator = null)
  1. dbConnection object.
  2. IEnumerable<Database> targetDatabases :If your project has more than one database, you can use the Database object in FluentSql.DatabaseMappers.Common to describe the databases. The NameSpace field is optional, but it is useful when there are two types with the same name.
  3. Assembly[] assembliesOfModelTypes: Assemblies where the model classes are located. These classes are matched with the table names.
  4. IDatabaseMapper defaultDatabaseMapper: Currently FluentSql only supports Sql server syntax. So this defaults to SqlServerDatabaseMapper. In the future, one will be able to select from other providers such Postgres, MySql and others.
  5. Action onPostEntityMapping: By providing an action one can adjust or customize the Entity mapping. The EntityMapper class exposes the Entities dictionary property that is used at runtime to create the Sql statements.
  6. ISqlGenerator sqlGenerator: Currently FluentSql only supports Sql server syntax. So this defaults to SqlServerSqlGenerator.

For a more complex initialization example, please refer to the Bootstrap.cs file in the test project.

Concurrent Dictionary

After FluentSql reads the database(s) structure, it will load a concurrent dictionary with this information. The end result is that the database(s) description is available at runtime. This means that we do not need to add attributes to our models because that information is available from the EntityMapper class as a static property:

public static ConcurrentDictionary<Type, EntityMap> Entities

Furthermore, the EntityMap class has a public property that describes each field in the database table.

public List<PropertyMap> Properties { get; internal set; }

This information can be useful to validate data such as string lengths and fields that must have a value.

Coding

FluentSql provides the EntityStore class that helps with the simple querying tasks like asynchronous / synchronous gets. It also provides methods to get the query object to do more complex querying.

CRUD Operations

The following examples show only the operating statements where the connection and store objects are thought to be created before hand. These are normally private class members:

DbConnection _dbConnection = new DbConnectionTest(connectionString);

EntityStore _store = new EntityStore(_dbConnection);

Insert statement:

var employee = new Employee { FirstName = "Grant", LastName = "Rogers" };

employee = _store.Insert(employee);

The Id field of the Employee entity is a numeric key field (please refer to the test project). After the insert statement, FluentSql returns a new Entity and its Id field has the key value. In addition, any entity property that maps to a column in the database which has a default constraint AND is left null, FluentSql brings in the default value from the database. This is particularly helpful for the datetime fields like the following in the Employee Model:

public DateTime? Created { get; set; }

Consequently, the resulting Employee entity from the example above, will have the Created property set to the current date since the default constraint in the database is to set it to the current date.

One can also use the InsertMany method:

var customers = new List<Customer>
{
    new Customer
    {
        CompanyName = "SHIELD",
        ContactName = "James Buchanan",
        Address = "3037 La Follette Circle",
        City = "Santa Clara",
        Region = "CA"
    },
    new Customer
    {
        CompanyName = "Stark Enterprises",
        ContactName = "Colonel Chester Phillips",
        Address = "905 Sunnyside Terrace",
        City = "Spokane",
        Region = "WA"
    },
    new Customer
    {
        CompanyName = "Marvel",
        ContactName = "Private Lorraine",
        Address = "69232 Ramsey Park",
        City = "Sacramento",
        Region = "CA"
    }
};

var newCustomers = store.InsertMany(customers);

Update statement:

One can use the UpdateByKey method of the EntityStore class to update an entity:

var employee7 = _store.GetSingle<Employee>(e => e.Id == 7);

employee7.FirstName = "Timothy";
employee7.LastName = "Dugan";
employee7.Address = "100 Fletcher Drive";
employee7.City = "Gainesville";

var i = _store.UpdateByKey(employee7);

If updating more than one Entity, using the GetUpdateQuery method of the EntityStore class:

var updateQuery = _store.GetUpdateQuery<Employee>()
                    .Set( new { Birthdate = DateTime.Now.AddYears(-27)})
                    .Where(e => e.State == "TX");

var iModifiedRecords = _store.ExecuteScalar(updateQuery);

If updating more than one Entity with multiple joins, using the GetUpdateQuery method of the EntityStore in the following way:

var updateQuery = _store.GetUpdateQuery<OrderDetail>()
                            .Set(new { Discount = 0.05 })
                            .JoinOn<Order>((od, o) => od.OrderId == o.Id)
                            .JoinOn<Order, Employee>((o, e) => o.EmployeeId == e.Id)
                            .Where<OrderDetail, Employee>((od, e) => od.Quantity >= 50 && e.LastName == "Rogers");

var iModifiedRecords = _store.ExecuteScalar(updateQuery);

Delete statement:

One can use the DeleteByKey method of the EntityStore class to delete an entity:

var employee21 = _store.GetSingle<Employee>(e => e.Id == 21);   

var iDeleted = _store.DeleteByKey<Employee>(employee21);

If deleting more than one Entity, using the GetDeleteQuery method of the EntityStore class:

var deleteQuery = _store.GetDeleteQuery<OrderDetail>()
                            .JoinOn<Order>((od, o) => od.OrderId == o.Id)
                            .JoinOn<Order, Employee>((o, e) => o.EmployeeId == e.Id)
                            .Where<OrderDetail, Order, Employee>((od, o, e) => e.Username == "SRogers" && od.UnitPrice == 45.99M);

var iModifiedRecords = _store.ExecuteScalar(deleteQuery);

Synchronous methods

var store = new EntityStore(_dbConnection);
var customer = store.GetSingle<Customer>(c => c.Id == 1);
var employee = store.GetSingle<Employee>(e => e.UserName == "srogers" && e.Id == 1);

The GetByKey method accepts a dynamic type. One can pass an entity, number, or an anonymous type where the method looks to query the database by the table key.

var SteveEmployeeId = 1;
var employee = store.GetByKey<Employee>(1);
var employee1 = store.GetByKey<Employee>(new { Id = 1 });
var employee2 = store.GetByKey<Employee>(new Employee { Id = 1, FirstName = "Steve", LastName = "Rogers" });
var employee3 = store.GetByKey<Employee>(SteveEmployeeId);
var order1 = store.GetByKey<Order>(new { Id = 3 });

Asynchronous methods

var loginRequest = new LoginRequest { Username = "SRogers" };
var store = new EntityStore(_dbConnection);

var employee = await store.GetSingleAsync<Employee>(u => u.Username == loginRequest.Username);
var employee1 = await entityStore.GetByKeyAsync<Employee>(new { Id = 1 });

\\ UpdateByKey example
var employee20 = await _store.GetSingleAsync<Employee>(e => e.Id == 20);

employee20.FirstName = "Bucky";
employee20.LastName = "Barnes";
employee20.Address = "2020 University Ave";
employee20.City = "Gainesville";

var i = await _store.UpdateByKeyAsync<Employee>(employee20);

Complex Queries

For more complex queries one can use the GetSelectQuery, GetUpdateQuery, GetDeleteQuery methods. It takes two lines of code. The first line to declare the query, and the second line to execute the query.

 // Select query
 var selectQuery = store.GetSelectQuery<Order>()  
                        .JoinOn<Employee>((o, e) => e.Id == o.EmployeeId)  
                        .OrderBy(o => o.OrderDate)  
                        .OrderBy<Employee>(e => e.LastName);  

 var orderEmployeeSet = store.ExecuteQuery<Order, Employee>(selectQuery);

 // Update query
 var updateQuery = store.GetUpdateQuery<Employee>()
                         .Set( new { Birthdate = DateTime.Now.AddYears(-27)})
                         .Where(e => e.State == "TX");

 var iUpdatedRecords = store.ExecuteScalar(updateQuery);

 // Delete query
 var deleteQuery = _store.GetDeleteQuery<OrderDetail>()
                          .JoinOn<Order>((od, o) => od.OrderId == o.Id)
                          .JoinOn<Order, Employee>((o, e) => o.EmployeeId == e.Id)
                          .Where<OrderDetail, Order, Employee>((od, o, e) => e.Username == "SRogers" &&
                                                               od.UnitPrice == 45.99M);

 var iModifiedRecords = _store.ExecuteScalar(deleteQuery);

Here is a transaction example:

        using (var dbTran = _store.DbConnection.BeginTransaction())
        {
            var selectQuery = _store.GetSelectQuery<Employee>()
                                    .Where(e => e.Id == 25);

            var employees = _store.ExecuteQuery<Employee>(selectQuery, dbTran);

            Xunit.Assert.NotNull(employees);

            var employee25 = employees.FirstOrDefault();

            Xunit.Assert.NotNull(employee25);

            var deleteQuery = _store.GetDeleteQuery<Employee>()
                                    .Where(e => e.Id == 25);

            var iDeleted = _store.ExecuteScript(deleteQuery.ToSql(), deleteQuery.Parameters, dbTran, CommandType.Text);

            Xunit.Assert.True((int)iDeleted == 1);

            var insertQuery = _store.GetInsertQuery<Employee>(employee25);

            var insertedEntities = _store.ExecuteQuery<Employee>(insertQuery, dbTran);

            Xunit.Assert.NotNull(insertedEntities);

            var insertedEntity = insertedEntities.FirstOrDefault();

            Xunit.Assert.NotNull(insertedEntity);
            Xunit.Assert.IsType<Employee>(insertedEntity);
            Xunit.Assert.True(insertedEntity.Id != 25);

            dbTran.Commit();
        }

Please refer to the Test project for code samples.

Home

Get Started

Creating and Executing multiple join queries

DateAdd and DatePart Sql Functions

Wire up the Test Project

Clone this wiki locally