BLToolkitLinq-provider - 数据库编程

当前位置：

TOP

BLToolkitLinq-provider(十)

2015-11-21 01:53:40 来源: 作者: 【大中小】浏览:11次

lue(e => e.FirstName, "John") .Value(e => e.LastName, "Shepard") .Value(e => e.Title, () => "Spectre") .Value(e => e.HireDate, () => Sql.CurrentTimestamp) .InsertWithIdentity();

SQL:

INSERT INTO [Employees] 
(
    [FirstName],
    [LastName],
    [Title],
    [HireDate]
)
VALUES
(
    'John',
    'Shepard',
    'Spectre',
    CURRENT_TIMESTAMP
)

SELECT SCOPE_IDENTITY()

Update

The following three methods allow you to update one or more entries in the table.

Upgrading from a predicate:

db.Employee
    .Update(
        e => e.Title == "Spectre",
        e => new Northwind.Employee
        {
            Title = "Commander"
        });

Upgrading from subquery:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Update(e => new Northwind.Employee
    {
        Title = "Commander"
    });

Update read-only properties:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Set(e => e.Title, "Commander")
    .Update();

For all of these queries generated the following SQL:

UPDATE
    [is]
SET
    [Title] = 'Commander'
FROM
    [Employees] [e]
WHERE
    [e].[Title] = 'Spectre'

Another update operation, which involved one of the fields of the table:

db.Employee
  .Where(e => e.Title == "Spectre")
  .Set(e => e.HireDate, e => e.HireDate.Value.AddDays(10))
  .Update();

SQL:

UPDATE
  [is]
SET
  [HireDate] = DateAdd(Day, 10, [e].[HireDate])
FROM
  [Employees] [e]
WHERE
  [e].[Title] = 'Spectre'

Delete

And in the end the delete operation.

Deleting a predicate:

db.Employee.Delete(e => e.Title == "Spectre");

Remove with a subquery:

db.Employee
    .Where(e => e.Title == "Spectre")
    .Delete();

SQL:

DELETE [e]
FROM
    [Employees] [e]
WHERE
    [e].[Title] = 'Spectre'

SQL Function

Along with the standard functions of .NET Framework, BLToolkit allows you to use SQL functions that are available databases. The following two examples are similar to the examples that we have considered above the standard features for .NET Framework, but instead they use features SQL.

Using the string length:

from c in db.Customer
where Sql.Length(c.ContactName) > 5
select c.ContactName;

SQL:

SELECT
    [c].[ContactName]
FROM
    [Customers] [c]
WHERE
    Len([c].[ContactName]) > 5

Function rounding:

from o in db.Order
where  Sql.Round(o.Freight) >= 10
select o.Freight;

SQL:

SELECT
    [o].[Freight]
FROM
    [Orders] [o]
WHERE
    Round([o].[Freight], 0) >= 10

Note, this time caused by rounding function directly as it is present in SQL.

Expanding BLToolkit

SQL Function

BLToolkit allows us not only to use SQL functions, but also easily add their own. Realization of us considered the function of calculating the length of the line is as follows:

[SqlFunction]
[SqlFunction("Access",    "Len")]
[SqlFunction("Firebird",  "Char_Length")]
[SqlFunction("MsSql2005", "Len")]
[SqlFunction("MsSql2008", "Len")]
[SqlFunction("SqlCe",     "Len")]
[SqlFunction("Sybase",    "Len")]
public static int? Length(string str)
{
    return str == null ? null : (int?)str.Length;
}

Thus, SQL function - this is the usual method marked attribute SqlFunction. Attribute SqlFunction includes the following properties:

Name - name of the SQL function. If the name is not specified, it is used as a method name. SqlProvider - name of the SQL provider. If no attribute among the attributes to the desired provider is taken without sp

首页上一页 7 8 9 10 11 12 13 下一页尾页 10/13/13
【大中小】【打印】【繁体】【投稿】【收藏】【推荐】【举报】【评论】【关闭】【返回顶部】
分享到:
上一篇：Mysql Master Slave Config	下一篇：BI－SSAS简介篇

帐　　号:

密码: (新用户注册)

验证码:

表　　情:

内　　容: