风云

NQL.NET 是我最近在NDO(DOTNET DataAccess Object)的基础上并吸收了Hibernate,Rails框架中的活动记录,Castle中的活动记录,Grove,ActionPack,OQL的精华思想上建立的一套数据库对象查询语言（NQL，NDO Query Language）它支持 CRUD操作，也包括 select、from、join、on、where、group by、having、order by 和 case 子句。

本文中的代码都以SQL SERVER 2000中的数据库Northwind 做为例子,代码以CSharp为例,其中的例子都是我做过严格的测试.

数据库配置

<add key="ConnectionString" value="Data Source=localhost; Database=Northwind; Integrated Security=true;"/>

  导入NQL包 : using NDO;

1.Filter 过滤器

this.dataGrid1.DataSource = new Query("Products")
                .Filter("UnitPrice*UnitsInStock >=1000")
                .GetDataTable();

上面的程序生成的SQL 语句是

SELECT  * 
FROM Products 
WHERE 
    UnitPrice*UnitsInStock >=1000

2.邦定参数的Filter 过滤器

this.dataGrid1.DataSource = new Query("Products")
                .Filter("UnitPrice*UnitsInStock >=@AA")
                .AddParam("AA",1000)
                .GetDataTable();

SQL 输出

SELECT  * 
FROM Products 
WHERE 
    UnitPrice*UnitsInStock >=@AA

3.Where 的简单用法(该例子中用到了泛性的活动记录)

this.dataGrid1.DataSource = new ActiveRecord("Orders")
                .SelectCommand
                .Where("ShipVia",2)
                .Asc("CustomerID","OrderID")
                .GetDataTable();

SQL 输出

SELECT 
    [OrderID],
    [CustomerID],
    [EmployeeID],
    [OrderDate],
    [RequiredDate],
    [ShippedDate],
    [ShipVia],
    [Freight],
    [ShipName],
    [ShipAddress],
    [ShipCity],
    [ShipRegion],
    [ShipPostalCode],
    [ShipCountry]
FROM
    [Orders]    
WHERE
    ([Orders].[ShipVia]  = @ShipVia)
 ORDER BY CustomerID,OrderID ASC 

4.子查询的运用

Query qryCustomer = new Query("Customers")
                .SelectColumn("CustomerID")
                .Where("ContactTitle","Owner");

            this.dataGrid1.DataSource = new Query("Orders")
                .NewWhere("ShipVia",1)
                .And(Ex.In("CustomerID",qryCustomer)).GetDataTable();

SQL 输出

SELECT  * 
FROM Orders 
WHERE 
    [Orders].[ShipVia]  = @ShipVia
    AND [Orders].[CustomerID]  IN (

                                     SELECT 
                                        [Customers].[CustomerID]
                                    FROM Customers 
                                    WHERE 
                                    ([Customers].[ContactTitle]  = @ContactTitle)

                                    )

5.模糊查询

this.dataGrid1.DataSource = new Query("Customers")
                .NewWhere(Ex.Like("ContactTitle","Sales%"))
                .GetDataTable();

SQL  输出

SELECT  * 
FROM Customers 
WHERE 
    [Customers].[ContactTitle]  LIKE @ContactTitle

6.投影和多表连接操作

Query q = new Query("Orders","a");
            this.dataGrid1.DataSource = q
                .SelectColumn("OrderID")
                ._("OrderDate")
                ._("RequiredDate")
                ._("b","ProductID")
                .InnerJoin("Order Details").As ("b")
                    .On(q.NewCondition("OrderID") == q.NewCondition("b","OrderID"))
                .NewWhere("EmployeeID",5)
                .And("ShipVia",1)
                .GetDataTable();

SQL 输出

SELECT 
    [a].[OrderID],
    [a].[OrderDate],
    [a].[RequiredDate],
    [b].[ProductID]
FROM Orders a 
INNER JOIN [Order Details] b
    ON [a].[OrderID]=b.[OrderID] 
WHERE 
    [a].[EmployeeID]  = @a_EmployeeID
    AND [a].[ShipVia]  = @a_ShipVia

7.简单的插入查询

new SimpleInsertQuery("Region").Values(this.textBox1.Text,this.textBox2.Text).Execute();
  this.dataGrid1.DataSource = new Query("Region").GetDataTable();

SQL 输出

INSERT INTO [Region] VALUES ( 
    @RegionID,@RegionDescription
)

SELECT  * 
FROM Region 

8.插入语句2

new SimpleInsertQuery("Region").Columns("RegionID","RegionDescription").Values(this.textBox1.Text,this.textBox2.Text).Execute();
 this.dataGrid1.DataSource = new Query("Region").GetDataTable();

SQL 输出

INSERT INTO [Region] (
    RegionID,RegionDescription
) VALUES (
    @RegionID,@RegionDescription
)

SELECT  * 
FROM Region 

9.删除

new SimpleDeleteQuery("Region").Where("RegionID",this.textBox1.Text).Execute();
this.dataGrid1.DataSource = new Query("Region").Asc("RegionID").GetDataTable();

SQL 输出

DELETE FROM [Region]
WHERE 
    [Region].[RegionID]  = @RegionID

SELECT  * 
FROM Region 
 ORDER BY RegionID ASC 

10.删除2

new SimpleDeleteQuery("Region")
                .Filter("RegionID=@RegionID")
                .AddParam("RegionID",this.textBox1.Text)
                .Execute();
            this.dataGrid1.DataSource = new Query("Region").Asc("RegionID").GetDataTable();

SQL 输出同上

11.删除3,默认按照数据中的主键进行删除

new SimpleDeleteQuery("Region")
                .Execute(this.textBox1.Text);
            this.dataGrid1.DataSource = new Query("Region").Asc("RegionID").GetDataTable();

SQL 输出同上

12. 更新操作

new SimpleUpdateQuery("Region")
                .Set("RegionDescription",this.textBox2.Text)
                .Where("RegionID",this.textBox1.Text)
                .Execute();

SQL 输出

UPDATE [Region] SET 
    [Region].[RegionDescription]=@RegionDescription
WHERE 
    [Region].[RegionID]  = @RegionID

SELECT  * 
FROM Region 
 ORDER BY RegionID ASC 

13.计算列的加法运算(可以对计算列进行斯则混合运算)

Query q = new Query("Region");
            this.dataGrid1.DataSource = q
                ._(q["RegionID"] + 10)
                ._("RegionDescription")
                .Asc("RegionID")
                .GetDataTable();

SQL 输出

SELECT 
    [Region].[RegionID] + 10 AS [RegionID],
    [Region].[RegionDescription]
FROM Region 
 ORDER BY RegionID ASC 

14.计算列的乘法运算

Query q = new Query("Region");
  this.dataGrid1.DataSource = q
                ._(q["RegionID"] * 2 + 5)
                ._("RegionDescription")
                .Asc("RegionID").GetDataTable();

SQL   输出

SELECT 
    [Region].[RegionID] * 2 + 5 AS [RegionID],
    [Region].[RegionDescription]
FROM Region 
 ORDER BY RegionID ASC

15.计算列的括弧运算

Query q = new Query("Region");
            this.dataGrid1.DataSource = q
                ._((q["RegionID"] + 5)*2)
                ._("RegionDescription")
                .Asc("RegionID").GetDataTable();

SQL  输出

SELECT 
    ([Region].[RegionID] + 5)  * 2 AS [RegionID],
    [Region].[RegionDescription]
FROM Region 
 ORDER BY RegionID ASC 

16.计算列的自增运算(自减运算同自增略)

Query q = new Query("Region");
            QueryColumn col = q["RegionID"];
            col = col ++;
            this.dataGrid1.DataSource = q
                ._(col)
                ._("RegionDescription")
                .Asc("RegionID").GetDataTable();

  SQL 输出

SELECT 
    [Region].[RegionID] + 1 AS [RegionID],
    [Region].[RegionDescription]
FROM Region 
 ORDER BY RegionID ASC 

下一篇