sql artisan组件之query对象_数据库技巧

概要
       SQL Artisan是.NET基于SQL语句的数据访问组件，它目的是在.NET环境下快速的创建SQL语句并执行，从而让开发人员更有效率地创建数据库访问代码。SQL Artisan除在生成和运行的是标准SQL语句外，在生SQL语句过程中所有值都是通过参数方式进行传递从而解决SQL注入的安全问题。组件的运算符和比较运算符是基于.NET的实现，所以在编译过程中就可以检测语法的合法性，大大减少了开发过程编写SQL 语句拼写错误的问题。组件除了对标准SQL的支持外，还对数据库函进行扩展，让开发人员更灵活地创建数据库操作代码。

Query对象是SQL Artisan组件的主要成员之一，它是SELECT语句基于.NET对象化的实现；最终的目的是通过Query对象来解决全部SELECT查询语句的编写。

Query对象现在所支持的功能

      1）支持单表和多表查询；包括INNER JOIN,LEFT JOIN,RIGHT JOIN等操作。

      2）支持字段的运算符；包括：+，-，*，/

3）支持字段的比较运算符；包括：=,<>,>,>=,<,<=,in,ont in,like, BETWEEN,

not BETWEEN等。

       4）支持聚合函数：Sum,Count,Avg,Max,Min

       5）支持数据库的扩展函数：Year,Month,Len,Left,SubString等。

       6）支持数据分组。

       7）支持数据排序。

Query的处理对象
       在Select语句中主要处理的对象就是表和字段，同样Query中主要处理的对象也是一样。只是表和字段都在.NET中定义相应的类型；分别是HFSoft.Data.Expressions.Table和HFSoft.Data.Expressions.TableField。

        HFSoft.Data.Expressions.Table

     表描述对象，它用于提供Query需要查询的表信息。

    可以通过以下方式来定义一个表对象：

     private HFSoft.Data.Expressions.Table TBL= new  HFSoft.Data.Expressions.Table(“Orders”);

     表对象提供关联方法用于生关联的表对象：

          .INNER JOIN建立两个表的关联表

               1）创建Employees,Orders的关联表对象：

IQuery query = session.CreateQuery(Employees.TBL.INNER(Orders.TBL,Employees.F_EmployeeID))

              2）创建Employees,Orders,Customers的关联表对象：

IQuery query = session.CreateQuery(Employees.TBL.INNER(Orders.TBL,Employees.F_EmployeeID)&Orders.TBL.INNER(Customers.TBL,Orders.F_CustomerID));

          .LEFT JOIN建立两个表的左连接关联表

IQuery query = session.CreateQuery(Employees.TBL.LEFT(Orders.TBL,Employees.F_EmployeeID));

          .RIGHT JOIN建立两个表的右连接关联表

IQuery query = session.CreateQuery(Employees.TBL.RIGHT(Orders.TBL,Employees.F_EmployeeID));

   HFSoft.Data.Expressions.TableField

字段描述对象，它用于提供Query需要查询的字段信息

可以通过以下方式定义一个字段对象：

private HFSoft.Data.Expressions.TableField F_mOrderID = new HFSoft.Data.Expressions.TableField(“OrderID”);

字段对象提供丰富的运算功能，包括比较运算和数据库函数等。

运算功能：+,-,*,/

比较功能：==,!=,>,>=,<,<=,Like,In,NotIn, Match, Between, NotBetween

聚合函数：Max,Min,Count,Sum,Avg

组合运算：&,|

              数据库扩展函数：

                     MSSQL: CAST, ABS, FLOOR, DATEADD,YEAR,MONTH,DAY等。

                     其它数据暂时没提供。

Query组成部分
    Table属性

获取或设置需要查询的表对象，可以通过表的关联方法创建关联表对象。些属性不能为null。

    Selects属性

获取或设置查询的字段对象集，此属可以为null。当为null时如果查询的表是单表则表示返回表的所有字段否则回基础表的所有字段。

可以通过字段的相关组合来产生新的查询字段；如：字段+，-，*，/运算，还可以套用聚合函数和数据库函数等。

1）IQuery query = session.CreateQuery(OrderDetails.TBL);

query.Selects = new TableField[]{SqlMath.Count(OrderDetails.ALL).As(“CountValue”),OrderDetails.F_OrderID};

    2）IQuery query = session.CreateQuery(Employees.TBL);

query.Selects = new TableField[]{(Employees.F_LastName +Employees.F_LastName).As(“Name”),Employees.ALL};

Expreesion属性

       获取或设置条表达式，此属可以为null。当为null时查询获取表的所有记录。

       条件表达式可以通过字段对象的比较运算和组合运算符产生，比较过程中字段可以调用运算符或数据库函数。

1）  IQuery query = session.CreateQuery(Orders.TBL);

        query.Expreesion = Orders.F_EmployeeID ==3 & (Orders.F_OrderDate.Between(“1996-6-1”, “1996-8-1”)|Orders.F_OrderDate.Between(“1997-6-1”, “1997-8-1”));

OrderBy属性

获取或设置排序字段对象集，此属性可以为null。当为null时查询过程不对任何字段进行排序。

1）  IQuery query = session.CreateQuery(Orders.TBL);

query.OrderBy = new TableField[]{Orders.F_EmployeeID.DESC,Orders.F_OrderDate.ASC};

GroupBy属性

获取或设置分组字段对象集，些属性可以为null。当为null时查询过程不进行分组。

1） IQuery query = session.CreateQuery(OrderDetails.TBL);

query.Selects = new TableField[]{SqlMath.Max(OrderDetails.F_Quantity).As(“MaxValue”),OrderDetails.F_OrderID};

query.GroupBy = new TableField[]{OrderDetails.F_OrderID};

System.Data.DataSet ExecuteDataSet()

该方法是生成并执行相关的SQL语句，把获取的数据填充到DataSet对象中并返回。

Query用例
       SQL：

              select *  from orders where orderid>10300 and orderid<10500;

       SQL Artisan：

           using(IDataSession session = MappingContainer.ConfigContainer.OpenSession())

         {

              session.Open();

              IQuery query = session.CreateQuery(Orders.TBL);

              query.Expreesion = (Orders.F_OrderID > 10300 & Orders.F_OrderID < 10500);

              this.dataGrid1.DataSource = query.ExecuteDataSet().Tables[0];

         }

     SQL：

              select orders.*,employees.* from orders inner join employees on orders.employeeid=employees.employeeid;

     SQL Artisan：

           using(IDataSession session = MappingContainer.ConfigContainer.OpenSession())

         {

              session.Open();

              IQuery query = session.CreateQuery(Orders.TBL.INNER(Employees.TBL,Orders.F_EmployeeID));

              query.Selects = new TableField[]{Orders.ALL.At(Orders.TBL),Employees.ALL.At(Employees.TBL)};

                   this.dataGrid1.DataSource = query.ExecuteDataSet().Tables[0];

         }

SQL：

     SELECT * FROM Orders  where ( EmployeeID = 3  And ( (OrderDate BETWEEN  1996-6-1 And 1996-8-1 )  Or (OrderDate BETWEEN  1997-6-1 And 1997-8-1 ) ) )

     SQL Artisan：

           using(IDataSession session = MappingContainer.ConfigContainer.OpenSession())

         {

              session.Open();

              IQuery query = session.CreateQuery(Orders.TBL);

              query.Expreesion = Orders.F_EmployeeID ==3 & (Orders.F_OrderDate.Between( “1996-6-1”, “1996-8-1”)|Orders.F_OrderDate.Between(“1997-6-1”, “1997-8-1”));

                   this.dataGrid1.DataSource = query.ExecuteDataSet().Tables[0];

         }

SQL：

     SELECT Orders.* FROM Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID INNER JOIN Products ON [Order Details].ProductID=Products.ProductID INNER JOIN Categories ON Products.CategoryID=Categories.CategoryID  where Categories.CategoryName like %con%

     SQL Artisan：

           using(IDataSession session = MappingContainer.ConfigContainer.OpenSession())

         {

              session.Open();

              IQuery query = session.CreateQuery(Orders.TBL.INNER(OrderDetails.TBL,Orders.F_OrderID)

                                     &OrderDetails.TBL.INNER(Products.TBL,OrderDetails.F_ProductID)

                                     &Products.TBL.INNER(Categories.TBL,Products.F_CategoryID));

              query.Selects = new TableField[]{Orders.ALL.At(Orders.TBL)};

              query.Expreesion = Categories.F_CategoryName.At(Categories.TBL).Match(“con”);

              this.dataGrid1.DataSource = query.ExecuteDataSet().Tables[0];

         }

SQL：

     SELECT Orders.* FROM Orders INNER JOIN [Order Details] ON Orders.OrderID=[Order Details].OrderID INNER JOIN Products ON [Order Details].ProductID=Products.ProductID INNER JOIN Categories ON Products.CategoryID=Categories.CategoryID  where ( Categories.CategoryID = 3  And (Orders.OrderDate BETWEEN  1996-1-1 And 1996-12-31 ) )

     SQL Artisan：

           using(IDataSession session = MappingContainer.ConfigContainer.OpenSession())

         {

              session.Open();

              IQuery query = session.CreateQuery(Orders.TBL.INNER(OrderDetails.TBL,Orders.F_OrderID)&

                                                             OrderDetails.TBL.INNER(Products.TBL,OrderDetails.F_ProductID)&

                                                             Products.TBL.INNER(Categories.TBL,Products.F_CategoryID));

              query.Selects = new TableField[]{Orders.ALL.At(Orders.TBL)};

              query.Expreesion = Categories.F_CategoryID.At(Categories.TBL) ==3 &

                                        Orders.F_OrderDate.At(Orders.TBL).Between(“1996-1-1″,”1996-12-31”);

              this.dataGrid1.DataSource = query.ExecuteDataSet().Tables[0];

         }

–用例代码下载–

SQL Artisan和SQL参照表