FluentData使用Oracle

by kevin 8. 十月 2015 14:42 >
FlentData3.0.1 好像不支持,或者是我没找到使用的方法,3.0.1支持。 代码: new DbContext().ConnectionStringName(@"default", new OracleProvider()); .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } Web.Config配置 <add name="default" connectionString="data source=localhost/orali;persist security info=True;user id=System;password=System;" providerName="Oracle.ManagedDataAccess.Client" /> .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } .csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; } 或者 <add name="default" connectionString="data source=localhost/oral;persist security info=True;user id=system;password=system;" providerName="System.Data.OracleClient" /> 主要是连接字符串的providerName必填,根据自己安装的客户端填写。

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入门(三)--Query

by kevin 13. 九月 2013 16:08 >
查询一组数据 返回一组dynamic对象(new in .NET 4.0) 1: List<dynamic> products = Context.Sql("select * from Product").QueryMany<dynamic>(); 返回一组强类型对象 1: List<Product> products = Context.Sql("select * from Product").QueryMany<Product>(); 返回一个自定义的Collection 1: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>(); 返回单个对象 返回一个dynamic对象 1: dynamic product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle<dynamic>(); 返回一个强类型对象: 1: Product product = Context.Sql(@"select * from Product where ProductId = 1").QuerySingle<Product>(); 返回一个DataTable: 1: DataTable products = Context.Sql("select * from Product").QuerySingle<DataTable>(); 其实QueryMany<DataTable>和QuerySingle<DataTable>都可以用来返回DataTable,但考虑到QueryMany<DataTable>返回的是List<DataTable>,所以使用QuerySingle<DataTable>来返回DataTable更方便。 查询一个标量值 1: int numberOfProducts = Context.Sql(@"select count(*) from Product").QuerySingle<int>(); 返回一组标量值 1: List<int> productIds = Context.Sql(@"select ProductId from Product").QueryMany<int>(); 设置查询参数: 索引顺序形式的参数 1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1", 1, 2).QueryMany<dynamic>(); 或者 1: dynamic products = Context.Sql(@"select * from Product where ProductId = @0 or ProductId = @1").Parameters(1, 2).QueryMany<dynamic>(); 名字形式的参数: 1: var command = Context.Sql(@"select @ProductName = Name from Product 2: where ProductId=1") 3: .ParameterOut("ProductName", DataTypes.String, 100); 4: command.Execute(); 5:  6: string productName = command.ParameterValue<string>("ProductName"); 7:  8: List of parameters - in operator: 9: List<int> ids = new List<int>() { 1, 2, 3, 4 }; 10:  11: dynamic products = Context.Sql(@"select * from Product 12: where ProductId in(@0)", ids).QueryMany<dynamic>(); Output 参数: 1: dynamic products = Context.Sql(@"select * from Product 2: where ProductId = @ProductId1 or ProductId = @ProductId2") 3: .Parameter("ProductId1", 1) 4: .Parameter("ProductId2", 2) 5: .QueryMany<dynamic>(); Query for a list of items Return a list of dynamic objects (new in .NET 4.0): List<dynamic> products = Context.Sql("select * from Product").QueryMany<dynamic>(); Return a list of strongly typed objects: List<Product> products = Context.Sql("select * from Product").QueryMany<Product>(); Return a list of strongly typed objects in a custom collection: ProductionCollection products = Context.Sql("select * from Product").QueryMany<Product, ProductionCollection>(); Return a DataTable: See Query for a single item. Query for a single item Return as a dynamic object: dynamic product = Context.Sql(@"select * from Product                 where ProductId = 1").QuerySingle<dynamic>(); Return as a strongly typed object: Product product = Context.Sql(@"select * from Product             where ProductId = 1").QuerySingle<Product>(); Return as a DataTable: DataTable products = Context.Sql("select * from Product").QuerySingle<DataTable>(); Both QueryMany<DataTable> and QuerySingle<DataTable> can be called to return a DataTable, but since QueryMany returns a List<DataTable> then it's more convenient to call QuerySingle which returns just DataTable. Eventhough the method is called QuerySingle then multiple rows will still be returned as part of the DataTable. Query for a scalar value int numberOfProducts = Context.Sql(@"select count(*)             from Product").QuerySingle<int>(); Query for a list of scalar values List<int> productIds = Context.Sql(@"select ProductId                 from Product").QueryMany<int>(); Parameters Indexed 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 parameters: dynamic products = Context.Sql(@"select * from Product             where ProductId = @ProductId1 or ProductId = @ProductId2")             .Parameter("ProductId1", 1)             .Parameter("ProductId2", 2)             .QueryMany<dynamic>(); Output parameter: 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 of parameters - in operator: List<int> ids = new List<int>() { 1, 2, 3, 4 }; dynamic products = Context.Sql(@"select * from Product             where ProductId in(@0)", ids).QueryMany<dynamic>();

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.

fluentdata的ExpandoObject异常

by kevin 4. 六月 2013 21:42 >
现在开发的项目中使用fluentdata进行数据访问,简单易用,但是,也是有坑爹的bug。 先记录2个: 1 IStoredProcedureBuilderDynamic StoredProcedure(string storedProcedureName, ExpandoObject item) 参数类型明明是ExpandoObject,还是需要做一次显示转换。 A.  Bad 1: var command = DefaultContext.StoredProcedure("InsertSPName", Entity) 2: .AutoMap().ParameterOut("EntityId", DataTypes.Int32, 4); 3: command.Execute(); 4: return command.ParameterValue<int>("EntityId"); B.  OK 1: var command = DefaultContext.StoredProcedure("InsertSPName", (ExpandoObject)DanymicObject) 2: .AutoMap().ParameterOut("EntityId", DataTypes.Int32, 4); 3: command.Execute(); 4: return command.ParameterValue<int>("EntityId"); 2. IStoredProcedureBuilder Parameter(string name, object value); 参数类型是object , 但是如果传入的参数是ExpandoObject的一个属性,返回值就变成object了。需要将传入的参数显示转换成简单类型。 A. Bad 1: public int Update(dynamic entity) 2: { 3: IStoredProcedureBuilder iStoredProcedureBuilder = DefaultContext.StoredProcedure("EntityUpdate") 4: .Parameter("Title", entity.Title) 5: .Parameter("Entity_Id", entity.EntityId); 6: return iStoredProcedureBuilder.Execute(); 7: } B. OK 1: public int Update(dynamic entity) 2: { 3: IStoredProcedureBuilder iStoredProcedureBuilder = DefaultContext.StoredProcedure("EntityUpdate") 4: .Parameter("Title", entity.Title as string) 5: .Parameter("Entity_Id", (int)entity.EntityId); 6: return iStoredProcedureBuilder.Execute(); 7: }

打赏请我喝果汁咯

支付宝 微信

关于我

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

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

遇建Kevin

FluentData交流群:477926269

Fluentdata