NQL.Net 简介
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();
.Filter("UnitPrice*UnitsInStock >=1000")
.GetDataTable();
上面的程序生成的SQL 语句是
SELECT *
FROM Products
WHERE
UnitPrice*UnitsInStock >=1000
FROM Products
WHERE
UnitPrice*UnitsInStock >=1000
2.邦定参数的Filter 过滤器
this.dataGrid1.DataSource = new Query("Products")
.Filter("UnitPrice*UnitsInStock >=@AA")
.AddParam("AA",1000)
.GetDataTable();
.Filter("UnitPrice*UnitsInStock >=@AA")
.AddParam("AA",1000)
.GetDataTable();
SQL 输出
SELECT *
FROM Products
WHERE
UnitPrice*UnitsInStock >=@AA
FROM Products
WHERE
UnitPrice*UnitsInStock >=@AA
3.Where 的简单用法(该例子中用到了泛性的活动记录)
this.dataGrid1.DataSource = new ActiveRecord("Orders")
.SelectCommand
.Where("ShipVia",2)
.Asc("CustomerID","OrderID")
.GetDataTable();
.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
[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();
.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 (
FROM Orders
WHERE
[Orders].[ShipVia] = @ShipVia
AND [Orders].[CustomerID] IN (
SELECT
[Customers].[CustomerID]
FROM Customers
WHERE
([Customers].[ContactTitle] = @ContactTitle)
[Customers].[CustomerID]
FROM Customers
WHERE
([Customers].[ContactTitle] = @ContactTitle)
)
5.模糊查询
this.dataGrid1.DataSource = new Query("Customers")
.NewWhere(Ex.Like("ContactTitle","Sales%"))
.GetDataTable();
.NewWhere(Ex.Like("ContactTitle","Sales%"))
.GetDataTable();
SQL 输出
SELECT *
FROM Customers
WHERE
[Customers].[ContactTitle] LIKE @ContactTitle
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();
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
[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();
this.dataGrid1.DataSource = new Query("Region").GetDataTable();
SQL 输出
INSERT INTO [Region] VALUES (
@RegionID,@RegionDescription
)
SELECT *
FROM Region
@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();
this.dataGrid1.DataSource = new Query("Region").GetDataTable();
SQL 输出
INSERT INTO [Region] (
RegionID,RegionDescription
) VALUES (
@RegionID,@RegionDescription
)
SELECT *
FROM 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();
this.dataGrid1.DataSource = new Query("Region").Asc("RegionID").GetDataTable();
SQL 输出
DELETE FROM [Region]
WHERE
[Region].[RegionID] = @RegionID
SELECT *
FROM Region
ORDER BY RegionID ASC
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();
.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();
.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();
.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
[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();
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
[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();
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
[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();
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
([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();
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
[Region].[RegionID] + 1 AS [RegionID],
[Region].[RegionDescription]
FROM Region
ORDER BY RegionID ASC