NbearV3.7新增强类型查询功能演示教程
来源:互联网 发布:url加端口号 编辑:程序博客网 时间:2024/05/16 17:09
本教程演示了NBear中典型的的各种查询功能。包括:join,group by,paging等的支持。
下载地址:http://nbear.org/Modules/Articles/Detail.aspx?i=59
教程内容(同运行时效果):
Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] WHERE [Products].[UnitsInStock] <= [Products].[ReorderLevel] AND NOT [Products].[Discontinued] = @pqanswaefvd06m7 OR [Products].[UnitPrice] < @p6xa720w2pfnvsv
Parameters:
@pqanswaefvd06m7[Boolean] = 1
@p6xa720w2pfnvsv[Decimal] = 10
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] WHERE [Employees].[HireDate] >= @pwpqblpc0s4le6w ORDER BY [Employees].[Country],[Employees].[EmployeeID] DESC
Parameters:
@pwpqblpc0s4le6w[DateTime] = 1994-1-1 0:00:00
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees]
We can filter items in employeeList2 by strong type query condition through the Filter method of EntityArrayList:
Notice: EntityArrayList.Filter() supports both where and order by condition, it does in-memory filtering and will not cause any database queries.
Employee[] filterredEmps = employeeList2.Filter(Employee._.HireDate >= new DateTime(1994, 1, 1), Employee._.City.Asc && Employee._.EmployeeID.Desc);
Log:
Text SELECT [Products].[ProductName],COUNT([Products].[ProductID]) FROM [Products] GROUP BY [Products].[ProductName] ORDER BY [Products].[ProductName] DESC
Log:
Text SELECT MAX([Products].[ProductID]) FROM [Products] WHERE [Products].[Discontinued] = @pj952ghh7lxivtu
Parameters:
@pj952ghh7lxivtu[Boolean] = 1
int catsPageCount = gateway.CountPage<Category>(Category._.Description != null, 5);
Log:
Text SELECT COUNT(*) FROM [Categories] WHERE NOT [Categories].[Description] IS NULL
Get 1st page with pagesize = 5:
Notice: For SqlServer, getting 1st page using the select top clause.
Category[] firstPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL
Get 3rd page with pagesize = 5:
Notice: For getting specific page, you should specify both the pagesize and the SKIP ITEM COUNT - be careful, it is not Page No but skip item count. e.g. For getting the 3rd page with pagesize 5, value of the second parameter should be (PageNo - 1) * pagesize = (3 - 1) * 5 = 10
Category[] thirdPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5, 10);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE (NOT [Categories].[Description] IS NULL ) AND [Categories].[CategoryID] NOT IN (SELECT TOP 10 [Categories].[CategoryID] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL )
Category[] testStringFunctionsCats = gateway.From<Category>().Where(Category._.CategoryName.Contains("a") && Category._.CategoryName.Length > 2).ToArray<Category>();
Log:
Text SELECT [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE [Categories].[CategoryName] LIKE @p58jl5yv5k10lsh AND LEN([Categories].[CategoryName]) > @pvj5m0lojxkxnew
Parameters:
@p58jl5yv5k10lsh[String] = %a%
@pvj5m0lojxkxnew[Int32] = 2
Notice: PropertyItem.GetCurrentDate() is very important at some situation, because it returns the current datetime on database server, while System.DateTime.Now returns the current datetime on application server.
DataSet testDateFunctionsDs = gateway.From<Employee>().Where(Employee._.HireDate.GetYear() == 1999 && Employee._.HireDate > PropertyItem.GetCurrentDate() - new TimeSpan(1000, 0, 0, 0)).ToDataSet();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] WHERE DATEPART(Year,[Employees].[HireDate]) = @p1v5pc8almjugsf AND [Employees].[HireDate] > GETDATE() - @pn5rtnya2svl87j
Parameters:
@p1v5pc8almjugsf[Int32] = 1999
@pn5rtnya2svl87j[DateTime] = 1902-9-28 0:00:00
Notice: Product._.Category.CategoryName is CategoryName property of Product.Category property and Product._.Supplier.Country is Country property of Product._.Supplier property.
Product[] testImplicitJoinsOfProducts = gateway.From<Product>().Where(Product._.Category.CategoryName.ToUpper() == "TEST" && Product._.Supplier.Country.ToLower() == "china").ToArray<Product>();
Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM ([Products] INNER JOIN [Categories] [Products_Category_Categories] ON [Products_Category_Categories].[CategoryID] = [Products].[CategoryID]) INNER JOIN [Suppliers] [Products_Supplier_Suppliers] ON [Products_Supplier_Suppliers].[SupplierID] = [Products].[SupplierID] WHERE UPPER([Products_Category_Categories].[CategoryName]) = @p0ynfjx4nvi0k80 AND LOWER([Products_Supplier_Suppliers].[Country]) = @pvjqsr9vts4yfmx
Parameters:
@p0ynfjx4nvi0k80[String] = TEST
@pvjqsr9vts4yfmx[String] = china
Notice: Even implict self join is supported:
Employee[] testImplicitJoinsOfEmps = gateway.From<Employee>().Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE [Employees_ReportsToEmployee_Employees].[FirstName] = @p48552pbnvevtc1
Parameters:
@p48552pbnvevtc1[String] = teddy
Product[] testExplicitJoinsOfProducts = gateway.From<Product>().Join<Category>(Product._.CategoryID == Category._.CategoryID).Where(Category._.CategoryName.ToUpper() == "TEST").ToArray<Product>();
Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] INNER JOIN [Categories] ON [Products].[CategoryID] = [Categories].[CategoryID] WHERE UPPER([Categories].[CategoryName]) = @p80k3r1695rewnf
Parameters:
@p80k3r1695rewnf[String] = TEST
Notice: Explicit self join always means you must specify an alias name for join:
Notice: Be careful, when using alias name in joins, you must always use the same alias name in Join() and Where(), you Must use XXX.__Alias(aliasname).Property to access the query property in query condition.
Employee[] testExplicitJoinsOfEmps = gateway.From<Employee>().Join<Employee>("reportToEmp", Employee._.ReportsToEmployeeID == Employee.__Alias("reportToEmp").EmployeeID).Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM ([Employees] INNER JOIN [Employees] [reportToEmp_Employees] ON [Employees].[ReportsTo] = [reportToEmp_Employees].[EmployeeID]) INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE [Employees_ReportsToEmployee_Employees].[FirstName] = @p6kg5b5jnd5jgw9
Parameters:
@p6kg5b5jnd5jgw9[String] = teddy
Log:
Text select * from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000
Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of custom sql matches the order of target entity's properties' definition.
Log:
Text select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000
Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI
Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of stored procedure matches the order of target entity's properties' definition.
Notice: Gateway.FromStoredProcedure() supports adding all the 4 types of sql parameters: AddInputParameter(), AddInputOutputParameter(), AddOutputParameter(), SetReturnParameter(). Be careful to use the correct method matches your parameter type.
Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI
下载地址:http://nbear.org/Modules/Articles/Detail.aspx?i=59
教程内容(同运行时效果):
The StrongTypeQuery tutorial demostrates the usage of the powerful strong type query syntax of NBear.
Gateway.From
Sample1 - Basic Where() & ToArray():
Product[] products = gateway.From<Product>().Where((Product._.UnitsInStock <= Product._.ReorderLevel && !(Product._.Discontinued == true)) || Product._.UnitPrice < 10m).ToArray<Product>;Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] WHERE [Products].[UnitsInStock] <= [Products].[ReorderLevel] AND NOT [Products].[Discontinued] = @pqanswaefvd06m7 OR [Products].[UnitPrice] < @p6xa720w2pfnvsv
Parameters:
@pqanswaefvd06m7[Boolean] = 1
@p6xa720w2pfnvsv[Decimal] = 10
Sample2 - Basic Where() & OrderBy() & ToArrayList():
EntityArrayList<Employee> employeeList = gateway.From<Employee>().Where(Employee._.HireDate >= new DateTime(1994, 1, 1)).OrderBy(Employee._.Country.Asc && Employee._.EmployeeID.Desc).ToArrayList<Employee>();Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] WHERE [Employees].[HireDate] >= @pwpqblpc0s4le6w ORDER BY [Employees].[Country],[Employees].[EmployeeID] DESC
Parameters:
@pwpqblpc0s4le6w[DateTime] = 1994-1-1 0:00:00
Sample3 - No Where() and OrderBy() & ToArrayList() & ToArrayList().Filter():
EntityArrayList<Employee> employeeList2 = gateway.From<Employee>().ToArrayList<Employee>();Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees]
We can filter items in employeeList2 by strong type query condition through the Filter method of EntityArrayList:
Notice: EntityArrayList.Filter() supports both where and order by condition, it does in-memory filtering and will not cause any database queries.
Employee[] filterredEmps = employeeList2.Filter(Employee._.HireDate >= new DateTime(1994, 1, 1), Employee._.City.Asc && Employee._.EmployeeID.Desc);
Sample4 - Count() & GroupBy() & OrderBy() & Select() & ToFirst():
CustOrderHistResult firstCountProductGroupByNameDesc = gateway.From<Product>().GroupBy(Product._.ProductName.GroupBy).OrderBy(Product._.ProductName.Desc).Select(Product._.ProductName, Product._.ProductID.Count()).ToFirst<CustOrderHistResult>();Log:
Text SELECT [Products].[ProductName],COUNT([Products].[ProductID]) FROM [Products] GROUP BY [Products].[ProductName] ORDER BY [Products].[ProductName] DESC
Sample5 - Max() & Where() & ToScalar():
int maxProductUnit = Convert.ToInt32(gateway.From<Product>().Where(Product._.Discontinued == true).Select(Product._.ProductID.Max()).ToScalar());Log:
Text SELECT MAX([Products].[ProductID]) FROM [Products] WHERE [Products].[Discontinued] = @pj952ghh7lxivtu
Parameters:
@pj952ghh7lxivtu[Boolean] = 1
Sample6 - Top & Skip & Paging:
Get page count first:int catsPageCount = gateway.CountPage<Category>(Category._.Description != null, 5);
Log:
Text SELECT COUNT(*) FROM [Categories] WHERE NOT [Categories].[Description] IS NULL
Get 1st page with pagesize = 5:
Notice: For SqlServer, getting 1st page using the select top clause.
Category[] firstPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL
Get 3rd page with pagesize = 5:
Notice: For getting specific page, you should specify both the pagesize and the SKIP ITEM COUNT - be careful, it is not Page No but skip item count. e.g. For getting the 3rd page with pagesize 5, value of the second parameter should be (PageNo - 1) * pagesize = (3 - 1) * 5 = 10
Category[] thirdPageCats = gateway.From<Category>().Where(Category._.Description != null).ToArray<Category>(5, 10);
Log:
Text SELECT TOP 5 [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE (NOT [Categories].[Description] IS NULL ) AND [Categories].[CategoryID] NOT IN (SELECT TOP 10 [Categories].[CategoryID] FROM [Categories] WHERE NOT [Categories].[Description] IS NULL )
Sample7 - String Functions:
Notice: Supported string functions include Contains()/StartsWith()/EndsWith()/Length/IndexOf()/Like()/Replace()/Trim()/SubString()/ToUpper()/ToLower()Category[] testStringFunctionsCats = gateway.From<Category>().Where(Category._.CategoryName.Contains("a") && Category._.CategoryName.Length > 2).ToArray<Category>();
Log:
Text SELECT [Categories].[CategoryID],[Categories].[CategoryName],[Categories].[Description],[Categories].[Picture] FROM [Categories] WHERE [Categories].[CategoryName] LIKE @p58jl5yv5k10lsh AND LEN([Categories].[CategoryName]) > @pvj5m0lojxkxnew
Parameters:
@p58jl5yv5k10lsh[String] = %a%
@pvj5m0lojxkxnew[Int32] = 2
Sample8 - Date Functions:
Notice: Supported date functions include GetYear()/GetMonth()/GetDay()/GetCurrentDate()/Date Comparasion/Add/SubstractNotice: PropertyItem.GetCurrentDate() is very important at some situation, because it returns the current datetime on database server, while System.DateTime.Now returns the current datetime on application server.
DataSet testDateFunctionsDs = gateway.From<Employee>().Where(Employee._.HireDate.GetYear() == 1999 && Employee._.HireDate > PropertyItem.GetCurrentDate() - new TimeSpan(1000, 0, 0, 0)).ToDataSet();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] WHERE DATEPART(Year,[Employees].[HireDate]) = @p1v5pc8almjugsf AND [Employees].[HireDate] > GETDATE() - @pn5rtnya2svl87j
Parameters:
@p1v5pc8almjugsf[Int32] = 1999
@pn5rtnya2svl87j[DateTime] = 1902-9-28 0:00:00
Sample9 - Implicit Join:
Notice: When an entity has properties maked with [FkReverseQuery], which means these properties map to friend key columns, your can use the following simple clause to query your entity, but at internal, the code will be transalated as sql joins.Notice: Product._.Category.CategoryName is CategoryName property of Product.Category property and Product._.Supplier.Country is Country property of Product._.Supplier property.
Product[] testImplicitJoinsOfProducts = gateway.From<Product>().Where(Product._.Category.CategoryName.ToUpper() == "TEST" && Product._.Supplier.Country.ToLower() == "china").ToArray<Product>();
Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM ([Products] INNER JOIN [Categories] [Products_Category_Categories] ON [Products_Category_Categories].[CategoryID] = [Products].[CategoryID]) INNER JOIN [Suppliers] [Products_Supplier_Suppliers] ON [Products_Supplier_Suppliers].[SupplierID] = [Products].[SupplierID] WHERE UPPER([Products_Category_Categories].[CategoryName]) = @p0ynfjx4nvi0k80 AND LOWER([Products_Supplier_Suppliers].[Country]) = @pvjqsr9vts4yfmx
Parameters:
@p0ynfjx4nvi0k80[String] = TEST
@pvjqsr9vts4yfmx[String] = china
Notice: Even implict self join is supported:
Employee[] testImplicitJoinsOfEmps = gateway.From<Employee>().Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM [Employees] INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE [Employees_ReportsToEmployee_Employees].[FirstName] = @p48552pbnvevtc1
Parameters:
@p48552pbnvevtc1[String] = teddy
Sample10 - Explicit Join & Join with alias name:
Notice: When an entity hasn't properties maked with [FkReverseQuery], but has friend key properties, you still can use explicit join clause.Product[] testExplicitJoinsOfProducts = gateway.From<Product>().Join<Category>(Product._.CategoryID == Category._.CategoryID).Where(Category._.CategoryName.ToUpper() == "TEST").ToArray<Product>();
Log:
Text SELECT [Products].[ProductID],[Products].[ProductName],[Products].[SupplierID],[Products].[CategoryID],[Products].[QuantityPerUnit],[Products].[UnitPrice],[Products].[UnitsInStock],[Products].[UnitsOnOrder],[Products].[ReorderLevel],[Products].[Discontinued] FROM [Products] INNER JOIN [Categories] ON [Products].[CategoryID] = [Categories].[CategoryID] WHERE UPPER([Categories].[CategoryName]) = @p80k3r1695rewnf
Parameters:
@p80k3r1695rewnf[String] = TEST
Notice: Explicit self join always means you must specify an alias name for join:
Notice: Be careful, when using alias name in joins, you must always use the same alias name in Join() and Where(), you Must use XXX.__Alias(aliasname).Property to access the query property in query condition.
Employee[] testExplicitJoinsOfEmps = gateway.From<Employee>().Join<Employee>("reportToEmp", Employee._.ReportsToEmployeeID == Employee.__Alias("reportToEmp").EmployeeID).Where(Employee._.ReportsToEmployee.FirstName == "teddy").ToArray<Employee>();
Log:
Text SELECT [Employees].[EmployeeID],[Employees].[LastName],[Employees].[FirstName],[Employees].[Title],[Employees].[TitleOfCourtesy],[Employees].[BirthDate],[Employees].[HireDate],[Employees].[Address],[Employees].[City],[Employees].[Region],[Employees].[PostalCode],[Employees].[Country],[Employees].[HomePhone],[Employees].[Extension],[Employees].[Photo],[Employees].[Notes],[Employees].[ReportsTo],[Employees].[PhotoPath] FROM ([Employees] INNER JOIN [Employees] [reportToEmp_Employees] ON [Employees].[ReportsTo] = [reportToEmp_Employees].[EmployeeID]) INNER JOIN [Employees] [Employees_ReportsToEmployee_Employees] ON [Employees_ReportsToEmployee_Employees].[EmployeeID] = [Employees].[ReportsTo] WHERE [Employees_ReportsToEmployee_Employees].[FirstName] = @p6kg5b5jnd5jgw9
Parameters:
@p6kg5b5jnd5jgw9[String] = teddy
Gateway.FromCustomSql
Sample1 - ToDataSet():
DataSet dsCats = gateway.FromCustomSql("select * from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2").AddInputParameter("p1", DbType.Int32, 100).AddInputParameter("p2", DbType.Int32, 2000).ToDataSet();Log:
Text select * from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000
Sample2 - ToArray():
Category[] cats = gateway.FromCustomSql("select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2").AddInputParameter("p1", DbType.Int32, 100).AddInputParameter("p2", DbType.Int32, 2000).ToDataSet();Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of custom sql matches the order of target entity's properties' definition.
Log:
Text select [CategoryID], [CategoryName], [Description], [Picture] from [Categories] where [CategoryID] > @p1 and [CategoryID] < @p2
Parameters:
@p1[Int32] = 100
@p2[Int32] = 2000
Gateway.FromStoredProcedure
Sample1 - ToDataSet():
DataSet dsCustHits = gateway.FromStoredProcedure("CustOrderHist").AddInputParameter("CustomerID", DbType.String, "ALFKI").ToDataSet();Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI
Sample2 - ToArray():
DataSet dsCustHits = gateway.FromStoredProcedure("CustOrderHist").AddInputParameter("CustomerID", DbType.String, "ALFKI").ToDataSet();Notice: When using XXX.ToArray() or XXX.ToArrayList(), you must ensure the order of select column list of stored procedure matches the order of target entity's properties' definition.
Notice: Gateway.FromStoredProcedure() supports adding all the 4 types of sql parameters: AddInputParameter(), AddInputOutputParameter(), AddOutputParameter(), SetReturnParameter(). Be careful to use the correct method matches your parameter type.
Log:
StoredProcedure CustOrderHist
Parameters:
@CustomerID[String] = ALFKI
- NbearV3.7新增强类型查询功能演示教程
- 发布NBearV3.1.7,包含重要升级内容:强类型实体集合、Gateway缓存等
- 强类型DataSet教程
- NBearV3中文教程总目录
- 扩展功能中新增身份证号码查询功能
- 强类型DataSet简明教程
- NBearV3——VsPlugin使用教程
- NBearV3——VsPlugin使用教程
- NBearV3教程——Cache篇
- NBearV3教程——Web篇
- Redis Geo:Redis新增位置查询功能
- REDIS GEO: REDIS新增位置查询功能
- NBearV3教程——MVP(Model/View/Presenter)
- NBearV3教程——MVP(Model/View/Presenter)篇
- NBearV3教程——实体配置文件加密篇
- NBearV3 Step by Step教程——IoC进阶篇
- NBearV3 Step by Step教程——IoC篇
- NBearV3 Step by Step教程——ORM进阶篇
- YetAnotherForum.Net+ScrewTurn Wiki中文完美汉化增强版(07-16更新:升级到v1.2.3 新增显示论坛版主信息,修复Wiki中文搜索)(GPL协议源码发布)
- 绑定Enum到ASP.NET数据绑定控件的完美解决方案[05/26修订]——增加支持第三方枚举描述,支持二进制与过的枚举值
- New beginning!
- 报数游戏
- 基于NBear的快速开发解决方案
- NbearV3.7新增强类型查询功能演示教程
- 安装 system management server 详细步骤
- 源码发布 - DynamicMethodProxyFactory组件
- 改进的“以非泛型方式调用泛型方法”之基于DynamicMethod的实现
- 实现NBearDataSource控件 - 02-11 20:30 修订 - 新增Master/Detail实体CRUD示例
- ADO.NET、NBear和NHibernate简单读写性能比较
- MSSQL生成日期列表
- Suggested NBear Framework Based FDD Development Steps
- NBearV3教程——MVP(Model/View/Presenter)篇