APQuery.net is a lightweight ORM, open source framework. With the objectization of the database, SQL expression, database access layout, business logic process and data entity.
Home page, documentation, and support links: http://leocaan.github.io/APQuery.net/
Some demos will be links: https://github.com/leocaan/APQuery.net-Demo/
- Install APQuery.net Library Package from Nuget, search as the key "APQuery".
- Install APQuery.net Add-in from Expanded and updated, Visual Studio Menu -> Tools -> Expanded and updated, search as the key "APQuery".
- Right click contextmenu on the project folder and click Add new .apgen file to create a business.apgen file, this file is the ORM mapping file.
- If you project is WebSite, ensure the file is in the App_Code folder will be auto generated.
- Or if you project is WebApplication, right click context menu on the file and click Generate the .apgen file item will be generated business.apgen.cs in project.
- Edit Global.asax, add Symber.Web.Compilation.APGenManager.SyncAndInitData(); in the Application_Start method, that will be automatically create and maintain the DATABASE and initialization data.
Look at here simple, the details please refer to the documents.
<businessModel autoSyncDatabase="true" autoInitDatabase="true">
<tables>
<table name="Department" comment="Departments of company">
<columns>
<add name="DepartmentId" type="int" primaryKey="true" identityType="Provider"/>
<add name="ParentId" type="int" comment="Parent DepartmentId"/>
<add name="DeptName" type="string" dataLength="20"/>
<add name="Phone" type="string" dataLength="20"/>
</columns>
<uniques>
<index name="IX_Department_DeptName">
<add name="DeptName" according="Asc"/>
</index>
</uniques>
<aliases>
<add name="Parent"/>
</aliases>
</table>
<table name="Employee" comment="Employees of company">
<columns>
<add name="EmployeeId" type="int" primaryKey="true" identityType="Provider"/>
<add name="DepartmentId" type="int" comment="Employee's DeparentId"/>
<add name="Name" type="string" dataLength="30"/>
<add name="Birthday" type="DateTime"/>
<add name="Email" type="string" dataLength="255"/>
</columns>
<indexes>
<index name="IX_Employee_Name" isDefault="true">
<add name="Name" according="Asc"/>
</index>
</indexes>
</table>
</tables>
</businessModel>---------------------------------------------------------------------
Entity Defined | Department
---------------------------------------------------------------------
Database Struct Defined | APDBDef.Department
---------------------------------------------------------------------
Data Access Layout (Dal) Defined | APDalDef.DepartmentDal
---------------------------------------------------------------------
Business Process Logic (Bpl) Defined | APBplDef.DepartmentBpl
---------------------------------------------------------------------
Insert a data
var dep = new Department(1, 0, "Sales", "000-000-0000");
dep.Insert();Delete a data
Department.PrimaryDelete(1);Condition delete
// short refer name
var t = APDBDef.Department;
Department.ConditionDelete(t.ParentId == 0 & t.DeptName != "Sales");Get data with primary key and update
var dep = Department.PrimaryGet(1);
dep.Phone = "000-000-0001";
dep.Update();Partial update
Department.UpdatePartial(1, new { Phone="000-000-5555", DeptName="New Seals"});Condition query and order
var t = APDBDef.Department;
List<Department> list = Department.ConditionQuery(
t.ParentId == 0 & t.DeptName != "Sales", // condition
t.DepartmentId.Desc); // orderPaging query
var t = APDBDef.Department;
List<Department> list = Department.ConditionQuery(
t.ParentId == 0 & t.DeptName != "Sales", // condition
t.DepartmentId.Desc, // order
20, 20); // take 20 records, skip ahead 20 recordsQuery count
Department.ConditionQueryCount(t.ParentId != 0);Transcation and Dal
using (APDBDef db = new APDBDef())
{
db.BeginTrans();
try
{
db.DepartmentDal.PrimaryDelete(1);
db.EmployeeDal.ConditionDelete(APDBDef.Employee.DepartmentId == 1);
db.Commit();
}
catch
{
db.Rollback();
}
}Sometimes the ORM can't meet our requirement, so we can directly use SQL Expression. Of course, the core of ORM is also dependent on SQL Expression.
Asterisk Query
var dep = APDBDef.Department;
using (APDBDef db = new APDBDef())
{
IEnumerable<Department> result = APQuery
.select(dep.Asterisk).distinct()
.from(dep)
.where(dep.ParentId == 0 & dep.Phone != null)
.order(dep.DeptName.Desc)
.query(db, dep.Map);
}Execute on SQLServer provider.
SELECT DISTINCT Department.*
FROM Department
WHERE Department.ParentId = 0 AND Department.Phone IS NOT NULL
ORDER BY Department.DeptName DESCIn the following, we only write SQL Expression.
Value and through expression (New)
'' is a escape character, when the first letter is '' while be parse after the letter through.
APQuery
.select(dep.DepartmentId, 3, "abc", "~3*3", null)
.from(dep);SELECT Department.DepartmentId, 3, 'abc', 3*3, NULL
FROM DepartmentColumn Alias
APQuery
.select(dep.DepartmentId, dep.DeptName.As("Name"), dep.Phone.As("Dept Phone"))
.from(dep);SELECT Department.DepartmentId, Department.DeptName AS Name,
Department.Phone AS [Dept Phone]
FROM DepartmentMulti-table query
var d = APDBDef.Department;
var e = APDBDef.Employee;
APQuery
.select(e.EmployeeId, d.DeptName, e.Name)
.from(d, e.JoinInner(d.DepartmentId == e.DepartmentId))
.where(d.ParentId.NotIn(2, 3, 4));SELECT Employee.EmployeeId, Department.DeptName, Employee.Name
FROM Department
INNER JOIN Employee ON Department.DepartmentId == Employee.DepartmentId
WHERE Department.DepartmentId IN ( 2, 3, 4 )Alias table query
var d = APDBDef.Department;
var dp = APDBDef.Department.Parent;
APQuery
.select(d.DepartmentId, d.DeptName, dp.DeptName.As ("ParentName"))
.from(d, dp.JoinLeft(d.ParentId == dp.DepartmentId))
.where(dp.DeptName.Match("ale"));SELECT Department.DepartmentId, Department.DeptName, Parent.DeptName AS 'ParentName'
FROM Department
LEFT JOIN Department AS Parent ON Department.ParentId == Parent.DepartmentId
WHERE Parent.DeptName LIKE '%ale%'Subquery
var d = APDBDef.Department;
var subQuery = APQuery
.select(d.DepartmentId)
.from(d)
.where(d.ParentId == 0);
APQuery
.select(d.Asterisk)
.from(d)
.where(subQuery.exist());SELECT Department.*
FROM Department
WHERE ( EXISTS (
SELECT Department.DepartmentId
FROM Department
WHERE Department.ParentId = 0
) )Paging Query
var d = APDBDef.Department;
var e = APDBDef.Employee;
var query = APQuery
.select(e.EmployeeId, e.Name, d.DeptName)
.from(e, d.JoinInner(e.DepartmentId == d.DepartmentId))
.primary(e.EmployeeId)
.take(20)
.skip(20);
using (APDBDef db = new APDBDef())
{
int total = db.ExecuteSizeOfSelect(query);
IDataReader records = db.ExecuteReader(query);
}Execute on SQLServer provider.
SELECT COUNT(*)
FROM Employee,
INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
SELECT TOP 20 Employee.EmployeeId, Employee.Name, Department.DeptName
FROM Employee,
INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
WHERE Employee.EmployeeId NOT IN (
SELECT TOP 20 Employee.EmployeeId
FROM Employee
INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
)Execute on Oracle provider.
SELECT COUNT(*)
FROM Employee,
INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
SELECT * FROM ( SELECT query_alias.*, ROWNUM query_rownum FROM (
SELECT Employee.EmployeeId, Employee.Name, Department.DeptName
FROM Employee,
INNER JOIN Department ON Employee.DepartmentId = Department.DepartmentId
) query_alias WHERE ROWNUM <= 40 ) WHERE query_rownum > 20)Aggregation & Group By
var t = APDBDef.Department;
var e = APDBDef.Employee;
APQuery
.select(t.DepartmentId, t.DeptName, e.EmployeeId.Count())
.from(t, e.JoinLeft(t.DepartmentId == e.DepartmentId))
.group_by(t.DepartmentId, t.DeptName, e.EmployeeId)
.having(e.EmployeeId.Count() > 0);SELECT Department.DepartmentId, Department.DeptName, COUNT(Employee.EmployeeId)
FROM Department
LEFT JOIN Employee ON Department.DepartmentId = Employee.DepartmentId
GROUP BY Department.DepartmentId, Department.DeptName, Employee.EmployeeId
HAVING COUNT(Employee.EmployeeId) > 0Aggregation with Date
var t = APDBDef.Employee;
APQuery
.select(t.Birthday.DateGroup(APSqlDateGroupMode.Month),
new APSqlAggregationExpr(t.Birthday.DateGroup(APSqlDateGroupMode.Month), APSqlAggregationType.COUNT)))
.from(t)
.group_by(t.Birthday.DateGroup(APSqlDateGroupMode.Month));Execute on SQLServer provider.
SELECT DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 ),
COUNT( DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 ) )
FROM Employee
GROUP BY DATEADD( mm, DATEDIFF( mm, 0, Employee.Birthday ), 0 )Execute on Oracle provider.
SELECT to_char(Employee.Birthday, 'yyyy-mm' ),
COUNT( to_char(Employee.Birthday, 'yyyy-mm' ) )
FROM Employee
GROUP BY to_char(Employee.Birthday, 'yyyy-mm' )Insert
var d = APDBDef.Department;
APQuery
.insert(d)
.set(d.DepartmentId, 5)
.set(d.DeptName, "HR")
.set(d.Phone, "000-111-2222");INSERT INTO Department
(Department.DepartmentId, Department.DeptName, Department.Phone)
Values (5, 'HR', '000-111-2222')Update
var d = APDBDef.Department;
APQuery
.update(d)
.set(d.Phone, "000-111-3333")
.where(d.DepartmentId == 5);UPDATE Department
SET Department.Phone = '000-111-3333'
WHERE Department.DepartmentId = 5Delete
var d = APDBDef.Department;
APQuery
.delete(d)
.where(d.ParentId == 0);DELETE Department
WHERE Department.ParentId = 0Anonymous return
var d = APDBDef.Department;
var query = APQuery
.select(d.DepartmentId, d.DeptName)
.from(d);
using (APDBDef db = new APDBDef())
{
var records = db.Query(query, r =>
{
return new
{
id = d.DepartmentId.GetValue(r),
name = d.DeptName.GetValue(r)
};
});
}Copyright (c) 2014-2015 Leo Caan. The license see LICENSE file.