MicroOrm.Net

A dynamic & lightweight object/relation mapping for .net.

MicroOrm.Net use the new dynamic features in the .net framework 4. so, you must use it on the .net framework 4.

Chinese users please visit: http://www.cnblogs.com/mapserver/tag/MicroOrm.Net/

中国用户请访问: http://www.cnblogs.com/mapserver/tag/MicroOrm.Net/

Features

1. Dynamic & lightweight.
2. Non-strongly typed result support.
3. Strongly typed result support.
4. Multiple database support(currently only support sql server).

Open database

Edit ConnectionString section in app.config/web.config: <connectionStrings> <add name="db" connectionString="..." providerName="System.Data.SqlClient"/> </connectionStrings>

Open database: var db = Database.Open("db");

Execute a query

Example usage: ``` var od = db.OrderDetail.Query() .Where(db.OrderDetail.OrderId == "001") .Where(db.OrderDetail.Qty > "0") .Where(db.Order.CustomerId == "001") .GroupBy(db.OrderDetail.OrderId) .Join(db.Order, db.Order.OrderId == db.OrderDetail.OrderId) .Skip(0) .Take(1) .OrderBy(db.OrderDetail.OrderId) .Having(db.OrderDetail.Qty.Sum() > 0) .Select(db.OrderDetail.OrderId, db.OrderDetail.Qty.Sum()) .First();

//Generated SQL: //exec sp_executesql N'SELECT * FROM (SELECT [#0].[OrderId] [OrderId],SUM([#0].[Qty]) [Qty] ,ROW_NUMBER() OVER( ORDER BY [#0].[OrderId] ASC) [_ROW_NUMBER_] FROM [dbo].[OrderDetail] [#0] INNER JOIN [dbo].[Order] [o] ON o.[OrderId]=#0.[OrderId] WHERE [#0].[OrderId] = @p0 AND [#0].[Qty] > @p1 AND [o].[CustomerId] = @p2 GROUP BY [#0].[OrderId] HAVING SUM([#0].[Qty]) > @p3 ) _Data WHERE [_ROW_NUMBER_] BETWEEN 0 AND 1 ',N'@p0 nvarchar(3),@p1 int,@p2 nvarchar(3),@p3 int',@p0=N'001',@p1=0,@p2=N'001',@p3=0 ```

In debug mode, you can trace sql in visual studio output window.

```

SELECT * FROM (SELECT [#0].[OrderId] [OrderId],SUM([#0].[Qty]) [Qty] ,ROW_NUMBER() OVER( ORDER BY [#0].[OrderId] ASC) [_ROW_NUMBER_] FROM [dbo].[OrderDetail] [#0] INNER JOIN [dbo].[Order] [#1] ON #1.[OrderId]=#0.[OrderId] WHERE [#0].[OrderId] = @p0 AND [#0].[Qty] > @p1 AND [#1].[CustomerId] = @p2 GROUP BY [#0].[OrderId] HAVING SUM([#0].[Qty]) > @p3 ) _Data WHERE [_ROW_NUMBER_] BETWEEN 0 AND 1 Name:@p0 | Value:001 | Type:String | Direction:Input Name:@p1 | Value:0 | Type:Int32 | Direction:Input Name:@p2 | Value:001 | Type:String | Direction:Input Name:@p3 | Value:0 | Type:Int32 | Direction:Input ```

Result: ``` //dynamic object var od = db.OrderDetail.Query() .Where(db.OrderDetail.OrderId == "001") .First(); string orderId = od.OrderId; int qty = od.Qty;

public class OrderDetail { public string OrderDetailId { get; set; } public string OrderId { get; set; } public string ProductId { get; set; } public int Qty { get; set; } }

//strongly typed object OrderDetail od = db.OrderDetail.Query() .Where(db.OrderDetail.OrderId == "001") .First(); string orderId = od.OrderId; int qty = od.Qty;

//other methods .Last() .Last()
.ToList() .ToArray() .ToList() .ToArray() ```

Where: ``` db.OrderDatail.Query() .Where(db.OrderDetail.OrderId == "001")

db.OrderDatail.Query() .Where(db.OrderDetail.OrderId == "001" && db.OrderDetail.Qty > 0)

db.OrderDatail.Query() .Where(db.OrderDetail.OrderId == "001" && (db.OrderDetail.Qty > 0 || db.OrderDetail.OrderDetailId == "001")

db.OrderDatail.As("od").Query() //table alias .Where(db.od.OrderId == "001") .Where(db.OrderDetail.Qty > 0) .Where(db.od.ProductId.Like("CA%") //like ```

Select: ``` db.OrderDetail.Query() .Select(db.OrderDetail.OrderId)

db.OrderDetail.Query() .Select(db.OrderDetail.OrderId.As("Oid"), db.OrderDetail.Qty)

db.OrderDetail.Query() .Select(db.OrderDetail.OrderId + "_Order", (db.OrderDetail.Qty + 1).As("Q"))

db.OrderDetail.Query() .Select((new MathE("Order_", db.OrderDetail.OrderId, MathOperator.Add)).As("Oid")) ```

Join: ``` db.OrderDetail.Query() .Join(db.Order, db.Order.OrderId == db.OrderDetail.OrderId)

db.OrderDetail.Query() .LeftJoin(db.Order, db.Order.OrderId == db.OrderDetail.OrderId) .LeftOuterJoin(..., ...) .InnerJoin(..., ...) .RightJoin(..., ...) .RightOuterJoin(..., ...) ```

Order By: ``` db.OrderDetail.Query() .OrderBy(db.OrderDetail.OrderId)

db.OrderDetil.Query() .Join(db.Order, db.Order.OrderId == db.OrderDetail.OrderId) .OrderBy(db.Order.CustomerId) .OrderByDesc(db.OrderDetail.Qty)

db.OrderDetail.Query() .OrderByAsc(...) .OrderByDesc(...) ```

Group By & Having: ``` db.OrderDetail.Query() .LeftJoin(db.Order, db.Order.OrderId == db.OrderDetail.OrderId) .GroupBy(db.Order.CustomerId) .Select(db.Order.CustomerId, db.OrderDetail.Qty.Sum().As("Sum"))

db.OrderDetail.Query() .LeftJoin(db.Order, db.Order.OrderId == db.OrderDetail.OrderId) .GroupBy(db.Order.CustomerId) .OrderBy(db.Order.CustomerId) .Having(db.OrderDetail.Qty.Sum() + 1 > 0) .Select(db.Order.CustomerId, db.OrderDetail.Qty.Sum().As("Sum")) ```

Skip & Take: db.OrderDetail.Query() .Skip(1) .Take(100)

Distinct: db.OrderDetail.Query() .Distinct()

Insert

``` Dictionary dict = new Dictionary(); dict["OrderId"] = Guid.NewGuid().ToString(); dict["CustomerId"] = "001";

db.Order.Insert(dict); db.Order.Insert(OrderId: Guid.NewGuid().ToString(), CustomerId: "001"); db.Order.Insert(new Order(){ OrderId = Guid.NewGuid().ToString(), CustomerId = "001" }); db.Order.Insert(new { OrderId = Guid.NewGuid().ToString(), CustomerId = "001" }); ```

Update

``` Dictionary dict = new Dictionary(); dict["OrderId"] = "001"; dict["CustomerId"] = "001";

db.Order.Update(dict); db.Order.Update(OrderId: "001", CustomerId: "001"); db.Order.Update(new Order(){ OrderId = "001", CustomerId = "001" }); db.Order.Update(new { OrderId = "001", CustomerId = "001" });

db.Order.Update(db.Order.OrderId == "001", dict); db.Order.Update(db.Order.OrderId == "001", CustomerId: "001"); db.Order.Update(db.Order.OrderId == "001", new Order(){ CustomerId = "001" }); db.Order.Update(db.Order.OrderId == "001", new { CustomerId = "001" }); ```

Delete

``` Dictionary dict = new Dictionary(); dict["OrderId"] = "001"; dict["CustomerId"] = "001";

db.Order.Delete(dict); db.Order.Delete(OrderId: "001", CustomerId: "001"); db.Order.Delete(new Order(){ OrderId = "001", CustomerId = "001" }); db.Order.Delete(new { OrderId = "001", CustomerId = "001" }); db.Order.Delete(db.Order.OrderId == "001" && db.Order.CustomerId == "001"); ```

TransactionScope

``` //auto commit: using (db.BeginTransactionScope()) { ... }

//manual commit: using (TransactionScope scope = db.BeginTransactionScope()) { ... scope.Commit(); } ```

CodeScope

The connection will stay open until codeScope object is destroyed. using (var codeScope = db.BeginCodeScope()) { ... }

Roadmap

1. Native sql support.
2. Stored procedure support.
3. SQLite & Oracle provider.