FluentData入门

by kevin 27. 九月 2013 13:46 >
FluentData是一个微型的ORM框架,使用可以很容易的对数据库进行数据操作。前段时间,花了点时间,对FluentData3.0的使用手册做了翻译,求点评。 FluentData入门(一)--核心概念 FluentData入门(二)--创建DbContext FluentData入门(三)—Query FluentData入门(四)—Mapping FluentData入门(五)—Insert, Update, Delete FluentData入门(六)--存储过程和事务

FluentData入门(六)--存储过程和事务

by kevin 23. 九月 2013 13:44 >
存储过程 使用SQL语句: 1: var rowsAffected = Context.Sql("ProductUpdate") 2: .CommandType(DbCommandTypes.StoredProcedure) 3: .Parameter("ProductId", 1) 4: .Parameter("Name", "The Warren Buffet Way") 5: .Execute(); 使用builder: 1: var rowsAffected = Context.StoredProcedure("ProductUpdate") 2: .Parameter("Name", "The Warren Buffet Way") 3: .Parameter("ProductId", 1).Execute(); 使用builder,并且自动映射 1: var product = Context.Sql("select * from Product where ProductId = 1") 2: .QuerySingle<Product>(); 3:  4: product.Name = "The Warren Buffet Way"; 5:  6: var rowsAffected = Context.StoredProcedure<Product>("ProductUpdate", product) 7: .AutoMap(x => x.CategoryId).Execute(); 使用Lambda表达式 1: var product = Context.Sql("select * from Product where ProductId = 1") 2: .QuerySingle<Product>(); 3: product.Name = "The Warren Buffet Way"; 4:  5: var rowsAffected = Context.StoredProcedure<Product>("ProductUpdate", product) 6: .Parameter(x => x.ProductId) 7: .Parameter(x => x.Name).Execute(); 事务 FluentData 支持事务。如果使用事务,最好使用using语句将代码包起来,已保证连接会被关闭。默认的,如果查询过程发生异常,如事务不会被提交,会进行回滚。 1: using (var context = Context.UseTransaction(true)) 2: { 3: context.Sql("update Product set Name = @0 where ProductId = @1") 4: .Parameters("The Warren Buffet Way", 1) 5: .Execute(); 6:  7: context.Sql("update Product set Name = @0 where ProductId = @1") 8: .Parameters("Bill Gates Bio", 2) 9: .Execute(); 10:  11: context.Commit(); 12: } 13:  实体工厂 实体工厂负责在自动映射的时候,生成POCO实例。如果需要生成复杂的实例,可以自定义实体工厂: 1: List<Product> products = Context.EntityFactory(new CustomEntityFactory()) 2: .Sql("select * from Product") 3: .QueryMany<Product>(); 4:  5: public class CustomEntityFactory : IEntityFactory 6: { 7: public virtual object Resolve(Type type) 8: { 9: return Activator.CreateInstance(type); 10: } 11: }   Stored procedure Using SQL: var rowsAffected = Context.Sql("ProductUpdate")             .CommandType(DbCommandTypes.StoredProcedure)             .Parameter("ProductId", 1)             .Parameter("Name", "The Warren Buffet Way")             .Execute(); Using a builder: var rowsAffected = Context.StoredProcedure("ProductUpdate")             .Parameter("Name", "The Warren Buffet Way")             .Parameter("ProductId", 1).Execute(); Using a builder with automapping: 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(); Using a builder with automapping and expressions: 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(); Transactions FluentData supports transactions. When you use transactions its important to wrap the code inside a using statement to make sure that the database connection is closed. By default, if any exception occur or if Commit is not called then Rollback will automatically be called. 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 creating object instances during automapping. If you have some complex business objects that require special actions during creation, you can create your own custom 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);     } }

FluentData入门(五)—Insert, Update, Delete

by kevin 17. 九月 2013 13:26 >
插入数据 使用 SQL 语句: 1: int productId = Context.Sql(@"insert into Product(Name, CategoryId) 2: values(@0, @1);") 3: .Parameters("The Warren Buffet Way", 1) 4: .ExecuteReturnLastId<int>(); 使用builder: 1: int productId = Context.Insert("Product") 2: .Column("Name", "The Warren Buffet Way") 3: .Column("CategoryId", 1) 4: .ExecuteReturnLastId<int>(); 使用builder,并且自动映射 1: Product product = new Product(); 2: product.Name = "The Warren Buffet Way"; 3: product.CategoryId = 1; 4:  5: product.ProductId = Context.Insert<Product>("Product", product) 6: .AutoMap(x => x.ProductId) 7: .ExecuteReturnLastId<int>(); 8:  将ProductId作为AutoMap方法的参数,是要指明ProductId不需要进行映射,因为它是一个数据库自增长字段。 更新数据 使用SQL语句: 1: int rowsAffected = Context.Sql(@"update Product set Name = @0 2: where ProductId = @1") 3: .Parameters("The Warren Buffet Way", 1) 4: .Execute(); 使用builder: 1: int rowsAffected = Context.Update("Product") 2: .Column("Name", "The Warren Buffet Way") 3: .Where("ProductId", 1) 4: .Execute(); 使用builder,并且自动映射: 1: Product product = Context.Sql(@"select * from Product 2: where ProductId = 1") 3: .QuerySingle<Product>(); 4: product.Name = "The Warren Buffet Way"; 5:  6: int rowsAffected = Context.Update<Product>("Product", product) 7: .AutoMap(x => x.ProductId) 8: .Where(x => x.ProductId) 9: .Execute(); 将ProductId作为AutoMap方法的参数,是要指明ProductId不需要进行映射,因为它不需要被更新。 Insert and update - common Fill method 1: var product = new Product(); 2: product.Name = "The Warren Buffet Way"; 3: product.CategoryId = 1; 4:  5: var insertBuilder = Context.Insert<Product>("Product", product).Fill(FillBuilder); 6:  7: var updateBuilder = Context.Update<Product>("Product", product).Fill(FillBuilder); 8:  9: public void FillBuilder(IInsertUpdateBuilder<Product> builder) 10: { 11: builder.Column(x => x.Name); 12: builder.Column(x => x.CategoryId); 13: } 14:  15: Delete 删除数据 使用SQL语句: 1: int rowsAffected = Context.Sql(@"delete from Product 2: where ProductId = 1") 3: .Execute(); 使用builder: 1: int rowsAffected = Context.Delete("Product") 2: .Where("ProductId", 1) 3: .Execute(); 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 a builder: int productId = Context.Insert("Product")             .Column("Name", "The Warren Buffet Way")             .Column("CategoryId", 1)             .ExecuteReturnLastId<int>(); Using a builder with automapping: Product product = new Product(); product.Name = "The Warren Buffet Way"; product.CategoryId = 1; product.ProductId = Context.Insert<Product>("Product", product)             .AutoMap(x => x.ProductId)             .ExecuteReturnLastId<int>(); We send in ProductId to the AutoMap method to get AutoMap to ignore and not map the ProductId since this property is an identity field where the value is generated in the database. Update data Using SQL: int rowsAffected = Context.Sql(@"update Product set Name = @0             where ProductId = @1")             .Parameters("The Warren Buffet Way", 1)             .Execute(); Using a builder: int rowsAffected = Context.Update("Product")             .Column("Name", "The Warren Buffet Way")             .Where("ProductId", 1)             .Execute(); Using a builder with automapping: Product product = Context.Sql(@"select * from Product             where ProductId = 1")             .QuerySingle<Product>(); product.Name = "The Warren Buffet Way"; int rowsAffected = Context.Update<Product>("Product", product)             .AutoMap(x => x.ProductId)             .Where(x => x.ProductId)             .Execute(); We send in ProductId to the AutoMap method to get AutoMap to ignore and not map the ProductId since this is the identity field that should not get updated. Insert and update - common Fill method 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 a builder: int rowsAffected = Context.Delete("Product")             .Where("ProductId", 1)             .Execute();

FluentData入门(四)--Mapping

by kevin 13. 九月 2013 17:55 >
映射 自动映射 – 在数据库对象和.Net object自动进行1:1匹配 1: List<Product> products = Context.Sql(@"select * 2: from Product") 3: .QueryMany<Product>(); 自动映射到一个自定义的Collection: 1: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>(); 如果数据库字段和POCO类属性名不一致,使用SQL别名语法AS: 1: List<Product> products = Context.Sql(@"select p.*, 2: c.CategoryId as Category_CategoryId, 3: c.Name as Category_Name 4: from Product p 5: inner join Category c on p.CategoryId = c.CategoryId") 6: .QueryMany<Product>(); 在这里p.*中的ProductId和ProductName会自动映射到Prodoct.ProductId和Product.ProductName,而Category_CategoryId和Category_Name 将映射到 Product.Category.CategoryId 和 Product.Category.Name. 使用dynamic自定义映射规则 1: List<Product> products = Context.Sql(@"select * from Product") 2: .QueryMany<Product>(Custom_mapper_using_dynamic); 3:  4: public void Custom_mapper_using_dynamic(Product product, dynamic row) 5: { 6: product.ProductId = row.ProductId; 7: product.Name = row.Name; 8: } 使用datareader进行自定义映射: 1: List<Product> products = Context.Sql(@"select * from Product") 2: .QueryMany<Product>(Custom_mapper_using_datareader); 3:  4: public void Custom_mapper_using_datareader(Product product, IDataReader row) 5: { 6: product.ProductId = row.GetInt32("ProductId"); 7: product.Name = row.GetString("Name"); 8: } 或者,当你需要映射到一个复合类型时,可以使用QueryComplexMany或者QueryComplexSingle。 1: var products = new List<Product>(); 2: Context.Sql("select * from Product").QueryComplexMany<Product>(products, MapComplexProduct); 3:  4: private void MapComplexProduct(IList<Product> products, IDataReader reader) 5: { 6: var product = new Product(); 7: product.ProductId = reader.GetInt32("ProductId"); 8: product.Name = reader.GetString("Name"); 9: products.Add(product); 10: } 多结果集 FluentData支持多结果集。也就是说,可以在一次数据库查询中返回多个查询结果。使用该特性的时候,记得使用类似下面的语句对查询语句进行包装。需要在查询结束后把连接关闭。 1: using (var command = Context.MultiResultSql) 2: { 3: List<Category> categories = command.Sql( 4: @"select * from Category; 5: select * from Product;").QueryMany<Category>(); 6:  7: List<Product> products = command.QueryMany<Product>(); 8: } 执行第一个查询时,会从数据库取回数据,执行第二个查询的时候,FluentData可以判断出这是一个多结果集查询,所以会直接从第一个查询里获取需要的数据。 分页 1: List<Product> products = Context.Select<Product>("p.*, c.Name as Category_Name") 2: .From(@"Product p 3: inner join Category c on c.CategoryId = p.CategoryId") 4: .Where("p.ProductId > 0 and p.Name is not null") 5: .OrderBy("p.Name") 6: .Paging(1, 10).QueryMany(); 调用 Paging(1, 10),会返回最先检索到的10个Product。   Mapping Automapping - 1:1 match between the database and the .NET object: List<Product> products = Context.Sql(@"select *             from Product")             .QueryMany<Product>(); Automap to a custom collection: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>(); Automapping - Mismatch between the database and the .NET object, use the alias keyword in SQL: Weakly typed: 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>(); Here the p.* which is ProductId and Name would be automapped to the properties Product.Name and Product.ProductId, and Category_CategoryId and Category_Name would be automapped to Product.Category.CategoryId and Product.Category.Name. Custom mapping using dynamic: 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; } Custom mapping using a datareader: 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 if you have a complex entity type where you need to control how it is created then the QueryComplexMany/QueryComplexSingle can be used: 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 resultsets. This allows you to do multiple queries in a single database call. When this feature is used it's important to wrap the code inside a using statement as shown below in order to make sure that the database connection is closed. using (var command = Context.MultiResultSql) {     List<Category> categories = command.Sql(             @"select * from Category;             select * from Product;").QueryMany<Category>();     List<Product> products = command.QueryMany<Product>(); } The first time the Query method is called it does a single query against the database. The second time the Query is called, FluentData already knows that it's running in a multiple result set mode, so it reuses the data retrieved from the first query. Select data and Paging A select builder exists to make selecting data and paging easy: 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(); By calling Paging(1, 10) then the first 10 products will be returned.

FluentData入门(二)--创建DbContext

by kevin 7. 九月 2013 13:58 >
如何创建和初始化一个DbContext 可以在*.config文件中配置connection string,将connection string name或者将整个connection string 作为参数传递给DbContext来创建DbContext。 重要配置 IgnoreIfAutoMapFails – IDbContext.IgnoreIfAutoMapFails返回一个IDbContext,该实例中,如果自动映射失败时是否抛出异常 通过*.config中配置的ConnectionStringName创建一个DbContext 1: public IDbContext Context() 2: { 3: return new DbContext().ConnectionStringName("MyDatabase", 4: new SqlServerProvider()); 5: }   调用DbContext的ConnectionString方法显示设置connection string来创建   1: public IDbContext Context() 2: { 3: return new DbContext().ConnectionString( 4: "Server=MyServerAddress;Database=MyDatabase;Trusted_Connection=True;", new SqlServerProvider()); 5: } 其他可以使用的Provider 只有通过使用不同的Provider,就可以切换到不同类型的数据库服务器上,比如 AccessProvider, DB2Provider, OracleProvider, MySqlProvider, PostgreSqlProvider, SqliteProvider, SqlServerCompact, SqlAzureProvider, SqlServerProvider.   Create and initialize a DbContext The connection string on the DbContext class can be initialized either by giving the connection string name in the *.config file or by sending in the entire connection string. Important configurations IgnoreIfAutoMapFails - Calling this prevents automapper from throwing an exception if a column cannot be mapped to a corresponding property due to a name mismatch. Create and initialize a DbContext The DbContext can be initialized by either calling ConnectionStringName which will read the connection string from the *.config file: public IDbContext Context() {     return new DbContext().ConnectionStringName("MyDatabase",             new SqlServerProvider()); } or by calling the ConnectionString method to set the connection string explicitly: public IDbContext Context() {     return new DbContext().ConnectionString(     "Server=MyServerAddress;Database=MyDatabase;Trusted_Connection=True;", new SqlServerProvider()); } Providers If you want to work against another database than SqlServer then simply replace the new SqlServerProvider() in the sample code above with any of the following: AccessProvider, DB2Provider, OracleProvider, MySqlProvider, PostgreSqlProvider, SqliteProvider, SqlServerCompact, SqlAzureProvider, SqlServerProvider.

FluentData入门(一)--核心概念

by kevin 7. 九月 2013 13:17 >
DbContext类 这是FluentData的核心类,可以通过配置ConnectionString来定义这个类,如何连接数据库和对具体的哪个数据库进行数据查询操作。 DbCommand类 这个类负责在相对应的数据库执行具体的每一个数据操作。 Events DbContext类定义了以下这些事件: OnConnectionClosed OnConnectionOpened OnConnectionOpening OnError OnExecuted OnExecuting 可以在事件中,记录每个SQL查询错误或者SQL查询执行的 时间等信息。 Builders Builder用来创建Insert, Update, Delete等相关的DbCommand实例。  Mapping FluentData可以将SQL查询结果自动映射成一个POCO(POCO - Plain Old CLR Object)实体类,也可以转换成一个dynamic类型。 自动转成实体类: 如果字段名中不包含下划线("_"),将映射到具有相同名字的属性上,例如:字段名 "Name" 将映射到属性名 "Name"。 如果字段名中不包含下划线("_"),将映射到内嵌的属性上,例如:字段名 "CategoryName" 将映射到属性名 "Category.Name"。 如果数据库字段名和属性名不一致,可以使用SQL的as让他们一致。 自动转换成dynamic类型: 对应dynamic类型,会为每个字段生成一个同名的属性,例如:字段名 "Name" 将映射到属性名 "Name"。 什么时候应该主动释放资源? 如果使用UseTransaction或者UseSharedConnection,那么DbContext需要主动释放。 如果使用UseMultiResult或者MultiResultSql,那么DbCommand需要主动释放。 如果使用UseMultiResult,那么StoredProcedureBuilder需要主动释放。 其他所有的类都会自动释放,也就是说,一个数据库连接只在查询开始前才进行,查询结束后会马上关闭。   DbContext This class is the starting point for working with FluentData. It has properties for defining configurations such as the connection string to the database, and operations for querying the database. DbCommand This is the class that is responsible for performing the actual query against the database. Events The DbContext class has support for the following events: OnConnectionClosed OnConnectionOpened OnConnectionOpening OnError OnExecuted OnExecuting By using any of these then you can for instance write to the log if an error has occurred or when a query has been executed. Builders A builder provides a nice fluent API for generating SQL for insert, update and delete queries. Mapping FluentData can automap the result from a SQL query to either a dynamic type (new in .NET 4.0) or to your own .NET entity type (POCO - Plain Old CLR Object) by using the following convention: Automap to an entity type: If the field name does not contain an underscore ("_") then it will try to try to automap to a property with the same name. For instance a field named "Name" would be automapped to a property also named "Name". If a field name does contain an underscore ("") then it will try to map to a nested property. For instance a field named "CategoryName" would be automapped to the property "Category.Name". If there is a mismatch between the fields in the database and in the entity type then the alias keyword in SQL can be used or you can create your own mapping method. Check the mapping section below for code samples. Automap to a dynamic type: For dynamic types every field will be automapped to a property with the same name. For instance the field name Name would be automapped to the Name property. When should you dispose? DbContext must be disposed if you have enabled UseTransaction or UseSharedConnection. DbCommand must be disposed if you have enabled UseMultiResult (or MultiResultSql). StoredProcedureBuilder must be disposed if you have enabled UseMultiResult. In all the other cases dispose will be handled automatically by FluentData. This means that a database connection is opened just before a query is executed and closed just after the execution has been completed.

打赏请我喝果汁咯

支付宝 微信

关于我

80后,单身,平庸的程序员。

喜欢看书,乐于交友,向往旅游。

遇建Kevin

FluentData交流群:477926269

Fluentdata