Preface:

Because of the use of FluentData in recent projects, the access to information is not comprehensive enough, so the Official website The following documents are translated for your reference.

Text:

Start learning

Environmental requirements

• .NET 4.0.

Support database

• MS SQL Server uses local. NET driver
• MS SQL Azure uses local. NET drivers
• MS Access uses local. NET drivers
• Microsoft SQL Server Compact 4.0 Requires Driver Microsoft SQL Server Compact 4.0 driver
• Oracle needs driver ODP.NET driver
• MySQL needs driver MySQL Connector .NET driver
• SQLite requires driver SQLite ADO.NET Data Provider
• PostgreSql Npgsql provide
• IBM DB2
• Sybase by https://github.com/FredoKapo/FLUENT-ORM-ASE-PROVIDER provide

install

Core concept

DbContext

This is the core class of fluentdata. You can define this class by configuring ConnectionString, how to connect the database and the specific database information

DbCommand

This is the class responsible for performing the actual query on the database.

Events

The DbContext class has the following Events (events) support:

• OnConnectionClosed
• OnConnectionOpened
• OnConnectionOpening
• OnErrorOnExecuted
• OnExecuting

Builders

Builders provide a very good API for generating SQL for inserting, updating, and deleting queries.
Builder is used to create relevant DbCommand instances such as Insert, Update, Delete, etc.

Mapping

FluentData can automatically map the results of SQL queries to a POCO (POCO-Plain Old CLR Object) entity class, or to a dynamic (new in.NET 4) type:

If there is a mismatch between field and entity types in the database, you can use alias keywords in SQL or create your own mapping method. Check the mapping section below to get a code example.

When do we need to release resources?

• DbContext needs to be released actively when you are enabling UseTransaction or UseShared Connection
• DbContext needs to be actively released when you enable Use MultiResult (or MultiResult Sql)
• Stored Procedure Builder needs to be actively released when you enable UseMultiResult

Code instance

Create and initialize a DbContext

You can configure the connection string in the *. config file to create a DbContext by passing the connection string name or the entire connection string as a parameter to the DbContext.

Important configuration

IgnoreIfAutoMapFails - IDbContext.IgnoreIfAutoMapFails returns an IDbContext, in which case, if an exception is thrown when the automatic mapping fails

Create and initialize a DbContext

Create a DbContext by ConnectionStringName:MyDatabase configured in *.config

public IDbContext Context()
{
    return new DbContext().ConnectionStringName("MyDatabase",
            new SqlServerProvider());
}

// * Contents in.config file
<connectionStrings>
    <add name="MyDatabase" connectionString="server=MyServerAddress;uid=uid;pwd=pwd;database=MyDatabase;"  /> 
</connectionStrings>

Call the Connection String method of DbContext to display the setting connection string to create

// 
public IDbContext Context()
{
    return new DbContext().ConnectionString(
    "Server=MyServerAddress;Database=MyDatabase;Trusted_Connection=True;", new SqlServerProvider());
}

Other available Provider s

Only by using different providers can you switch to different types of database servers, such as Access Provider, DB2 Provider, Oracle Provider, MySql Provider, PostgreSql Provider, SqliteProvider, Sql Server Compact, SqlAzureProvider, SqlServer Provider.

Query for a list of items

Returns a set of dynamic objects

List<dynamic> products = Context.Sql("select * from Product").QueryMany<dynamic>();

Returns a set of strongly typed objects

List<Product> products = Context.Sql("select * from Product").QueryMany<Product>();

Returns a set of custom Collection s

ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();

Query for a single item

Returns a dynamic object

dynamic product = Context.Sql(@"select * from Product
                where ProductId = 1").QuerySingle<dynamic>();

Returns a strongly typed object

Product product = Context.Sql(@"select * from Product
            where ProductId = 1").QuerySingle<Product>();

Return a DataTable

/**
 * In fact, QueryMany < DataTable > and QuerySingle < DataTable > can be used to return DataTable.
 * But considering that QueryMany <DataTable> returns List<DataTable>,
 * So it's more convenient to use QuerySingle < DataTable > to return to DataTable. 
 */
DataTable products = Context.Sql("select * from Product").QuerySingle<DataTable>();

Query a scalar value

int numberOfProducts = Context.Sql(@"select count(*)
            from Product").QuerySingle<int>();

Query a set of scalar values

List<int> productIds = Context.Sql(@"select ProductId
                from Product").QueryMany<int>();

Query parameters

Index formal parameters:

dynamic products = Context.Sql(@"select * from Product
            where ProductId = @0 or ProductId = @1", 1, 2).QueryMany<dynamic>();

Or:

dynamic products = Context.Sql(@"select * from Product
            where ProductId = @0 or ProductId = @1")
            .Parameters(1, 2).QueryMany<dynamic>();

Named form parameters:

dynamic products = Context.Sql(@"select * from Product
            where ProductId = @ProductId1 or ProductId = @ProductId2")
            .Parameter("ProductId1", 1)
            .Parameter("ProductId2", 2)
            .QueryMany<dynamic>();

OutPut formal parameters:

var command = Context.Sql(@"select @ProductName = Name from Product
            where ProductId=1")
            .ParameterOut("ProductName", DataTypes.String, 100);
command.Execute();

string productName = command.ParameterValue<string>("ProductName");

List formal parameter - in query:

List<int> ids = new List<int>() { 1, 2, 3, 4 };
// Note that there are no operators with spaces around in(...).
dynamic products = Context.Sql(@"select * from Product
            where ProductId in(@0)", ids).QueryMany<dynamic>();

like query:

string cens = "%abc%";
Context.Sql("select * from Product where ProductName like @0",cens);

mapping

Automatic Mapping - Database Objects and. Net Objects are automatically matched 1:1:

List<Product> products = Context.Sql(@"select *
            from Product")
            .QueryMany<Product>();

Automatically map to a custom Collect:

ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>();

If the attribute names of database fields and. Net object classes are inconsistent, use the SQL alias syntax AS:

/*
 * Here, ProductId and ProductName in p. * are automatically mapped to Prodoct.ProductId and ProductName.
 * Category_Category Id and Category_Name map to Product. Category. Category Id and Product.Category.Name
 */
List<Product> products = Context.Sql(@"select p.*,
            c.CategoryId as Category_CategoryId,
            c.Name as Category_Name
            from Product p
            inner join Category c on p.CategoryId = c.CategoryId")
                .QueryMany<Product>();

Use dynamic to customize mapping rules:

List<Product> products = Context.Sql(@"select * from Product")
            .QueryMany<Product>(Custom_mapper_using_dynamic);

public void Custom_mapper_using_dynamic(Product product, dynamic row)
{
    product.ProductId = row.ProductId;
    product.Name = row.Name;
}

Use datareader for custom mapping:

List<Product> products = Context.Sql(@"select * from Product")
            .QueryMany<Product>(Custom_mapper_using_datareader);

public void Custom_mapper_using_datareader(Product product, IDataReader row)
{
    product.ProductId = row.GetInt32("ProductId");
    product.Name = row.GetString("Name");
}

Or when you need to map to a composite type, you can use QueryComplexMany or QueryComplexSingle:

var products = new List<Product>();
Context.Sql("select * from Product").QueryComplexMany<Product>(products, MapComplexProduct);

private void MapComplexProduct(IList<Product> products, IDataReader reader)
{
    var product = new Product();
    product.ProductId = reader.GetInt32("ProductId");
    product.Name = reader.GetString("Name");
    products.Add(product);
}

Multiple result sets

FluentData supports multiple result sets. That is to say, multiple query results can be returned in one database query. When using this feature, remember to wrap query statements with statements similar to the following. You need to close the connection at the end of the query.

/**
 * When the first query is executed, the data is retrieved from the database.
 * When the second query is executed, FluentData can judge that it is a multi-result set query, so it will get the required data directly from the first query.
 */
using (var command = Context.MultiResultSql)
{
    List<Category> categories = command.Sql(
            @"select * from Category;
            select * from Product;").QueryMany<Category>();

    List<Product> products = command.QueryMany<Product>();
}

Selecting data and paging

Selecting a builder makes it easier to select data and paginate:

// By calling Paging(1, 10), the top 10 products will be returned.
List<Product> products = Context.Select<Product>("p.*, c.Name as Category_Name")
                   .From(@"Product p 
                    inner join Category c on c.CategoryId = p.CategoryId")
                   .Where("p.ProductId > 0 and p.Name is not null")
                   .OrderBy("p.Name")
                   .Paging(1, 10).QueryMany();

insert data

Using SQL:

int productId = Context.Sql(@"insert into Product(Name, CategoryId)
            values(@0, @1);")
            .Parameters("The Warren Buffet Way", 1)
            .ExecuteReturnLastId<int>();

Using builder:

int productId = Context.Insert("Product")
            .Column("Name", "The Warren Buffet Way")
            .Column("CategoryId", 1)
            .ExecuteReturnLastId<int>();

Use builder and automatically map:

Product product = new Product();
product.Name = "The Warren Buffet Way";
product.CategoryId = 1;

// Using ProductId as a parameter of the AutoMap method is to indicate that ProductId does not need to be mapped because it is a database self-growth field.
product.ProductId = Context.Insert<Product>("Product", product)
            .AutoMap(x => x.ProductId)
            .ExecuteReturnLastId<int>();

Update data

Using SQL:

int rowsAffected = Context.Sql(@"update Product set Name = @0
            where ProductId = @1")
            .Parameters("The Warren Buffet Way", 1)
            .Execute();

Using builder:

int rowsAffected = Context.Update("Product")
            .Column("Name", "The Warren Buffet Way")
            .Where("ProductId", 1)
            .Execute();

Use builder and automatically map:

Product product = Context.Sql(@"select * from Product
            where ProductId = 1")
            .QuerySingle<Product>();
product.Name = "The Warren Buffet Way";

// Using ProductId as a parameter of the AutoMap method is to indicate that ProductId does not need to be mapped because it does not need to be updated. 
int rowsAffected = Context.Update<Product>("Product", product)
            .AutoMap(x => x.ProductId)
            .Where(x => x.ProductId)
            .Execute();

Set whether the mapping failure exception is thrown (IgnoreIfAutoMapFails)

When read from the database, if some data columns do not reflect entity classes, by default, an exception will be thrown.
If you want to ignore exceptions, or properties do not need to be mapped to database objects, you can set IgnoreIfAutoMapFails(true) so that no exceptions are thrown when mapping errors occur.

context.IgnoreIfAutoMapFails(true);

Insert and update - common filling methods

var product = new Product();
product.Name = "The Warren Buffet Way";
product.CategoryId = 1;

var insertBuilder = Context.Insert<Product>("Product", product).Fill(FillBuilder);

var updateBuilder = Context.Update<Product>("Product", product).Fill(FillBuilder);

public void FillBuilder(IInsertUpdateBuilder<Product> builder)
{
    builder.Column(x => x.Name);
    builder.Column(x => x.CategoryId);
}

Delete data

Using SQL:

int rowsAffected = Context.Sql(@"delete from Product
            where ProductId = 1")
            .Execute();

Using builder:

int rowsAffected = Context.Delete("Product")
            .Where("ProductId", 1)
            .Execute();

stored procedure

Using SQL:

int rowsAffected = Context.Sql(@"delete from Product
            where ProductId = 1")
            .Execute();

Using builder:

var rowsAffected = Context.StoredProcedure("ProductUpdate")
            .Parameter("Name", "The Warren Buffet Way")
            .Parameter("ProductId", 1).Execute();

Use builder and automatically map:

var product = Context.Sql("select * from Product where ProductId = 1")
            .QuerySingle<Product>();

product.Name = "The Warren Buffet Way";

var rowsAffected = Context.StoredProcedure<Product>("ProductUpdate", product)
            .AutoMap(x => x.CategoryId).Execute();

Use builder, and automatically map and express:

var product = Context.Sql("select * from Product where ProductId = 1")
            .QuerySingle<Product>();
product.Name = "The Warren Buffet Way";

var rowsAffected = Context.StoredProcedure<Product>("ProductUpdate", product)
            .Parameter(x => x.ProductId)
            .Parameter(x => x.Name).Execute();

Using things

FluentData supports transactions. If you use transactions, it's better to use the using statement to wrap the code, which ensures that the connection will be closed. By default, if an exception occurs in the query process, the transaction will be rolled back if it is not committed.

using (var context = Context.UseTransaction(true))
{
    context.Sql("update Product set Name = @0 where ProductId = @1")
                .Parameters("The Warren Buffet Way", 1)
                .Execute();

    context.Sql("update Product set Name = @0 where ProductId = @1")
                .Parameters("Bill Gates Bio", 2)
                .Execute();

    context.Commit();
}

Entity factory

The entity factory is responsible for generating object instances when mapping automatically. If you need to generate complex instances, you can customize the entity factory:

List<Product> products = Context.EntityFactory(new CustomEntityFactory())
            .Sql("select * from Product")
            .QueryMany<Product>();

public class CustomEntityFactory : IEntityFactory
{
    public virtual object Resolve(Type type)
    {
        return Activator.CreateInstance(type);
    }
}