[索引页]
[源码下载]
[源码下载]
步步为营VS 2008 + .NET 3.5(13) - DLINQ(LINQ to SQL)之用户自定义函数、在不同的DataContext之间做更新、缓存、获取信息、数据加载选项和延迟加载
作者:webabcd
介绍
以Northwind为示例数据库,DLINQ(LINQ to SQL)之调用标量值用户自定义函数;调用表值用户自定义函数;使用数据加载选项加载数据;相关信息的获取;字段的延迟加载;在不同的DataContext之间做更新;缓存
示例
Others.aspx.cs
using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using DAL;
public partial class LINQ_DLINQ_Others : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// 调用标量值用户自定义函数
Call_HelloFunctionScalar();
// 调用表值用户自定义函数
Call_HelloFunctionTable();
// 不使用数据加载选项
DataLoadOptions_Original();
// 使用数据加载选项加载数据
DataLoadOptions_LoadWith();
// 使用数据加载选项加载数据,并且限制所要加载的数据
DataLoadOptions_AssociateWith();
// 相关信息的获取
GetInfo();
// 字段的延迟加载
DelayLoaded();
// 在不同的DataContext之间做更新
IsVersion();
// 缓存
ShowCache();
}
}
using System.Data;
using System.Configuration;
using System.Collections;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Xml.Linq;
using DAL;
public partial class LINQ_DLINQ_Others : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
// 调用标量值用户自定义函数
Call_HelloFunctionScalar();
// 调用表值用户自定义函数
Call_HelloFunctionTable();
// 不使用数据加载选项
DataLoadOptions_Original();
// 使用数据加载选项加载数据
DataLoadOptions_LoadWith();
// 使用数据加载选项加载数据,并且限制所要加载的数据
DataLoadOptions_AssociateWith();
// 相关信息的获取
GetInfo();
// 字段的延迟加载
DelayLoaded();
// 在不同的DataContext之间做更新
IsVersion();
// 缓存
ShowCache();
}
}
调用标量值用户自定义函数
ALTER FUNCTION dbo.fnHelloFunctionScalar
(
@param nvarchar(100)
)
RETURNS nvarchar(200)
AS
BEGIN
RETURN 'Hello:' + @param
END
(
@param nvarchar(100)
)
RETURNS nvarchar(200)
AS
BEGIN
RETURN 'Hello:' + @param
END
/// <summary>
/// 调用标量值用户自定义函数
/// </summary>
void Call_HelloFunctionScalar()
{
NorthwindDataContext ctx = new NorthwindDataContext();
result.InnerHtml += ctx.HelloFunctionScalar("webabcd");
result.InnerHtml += "<br />";
result.InnerHtml += "<br />";
}
/// 调用标量值用户自定义函数
/// </summary>
void Call_HelloFunctionScalar()
{
NorthwindDataContext ctx = new NorthwindDataContext();
result.InnerHtml += ctx.HelloFunctionScalar("webabcd");
result.InnerHtml += "<br />";
result.InnerHtml += "<br />";
}
运行结果
Hello:webabcd
调用表值用户自定义函数
ALTER FUNCTION dbo.fnHelloFunctionTable
(
@ID int,
@name nvarchar(100)
)
RETURNS @tbl TABLE ([ID] int, [Name] nvarchar(100))
AS
BEGIN
INSERT INTO @tbl VALUES (@ID, @name)
RETURN
END
(
@ID int,
@name nvarchar(100)
)
RETURNS @tbl TABLE ([ID] int, [Name] nvarchar(100))
AS
BEGIN
INSERT INTO @tbl VALUES (@ID, @name)
RETURN
END
/// <summary>
/// 调用表值用户自定义函数
/// </summary>
void Call_HelloFunctionTable()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var p = (ctx.HelloFunctionTable(1, "webabcd")).Single();
result.InnerHtml += "ID:" + p.ID;
result.InnerHtml += "<br />";
result.InnerHtml += "Name:" + p.Name;
result.InnerHtml += "<br />";
result.InnerHtml += "<br />";
}
/// 调用表值用户自定义函数
/// </summary>
void Call_HelloFunctionTable()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var p = (ctx.HelloFunctionTable(1, "webabcd")).Single();
result.InnerHtml += "ID:" + p.ID;
result.InnerHtml += "<br />";
result.InnerHtml += "Name:" + p.Name;
result.InnerHtml += "<br />";
result.InnerHtml += "<br />";
}
运行结果
ID:1
Name:webabcd
不使用数据加载选项
/// <summary>
/// 不使用数据加载选项
/// </summary>
void DataLoadOptions_Original()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var categories = from c in ctx.Categories select c;
foreach (var c in categories)
{
// 每一次迭代都会执行一条SQL语句来返回当前Category的Product
foreach (var p in c.Products)
{
string productName = p.ProductName + "<br>";
}
}
}
/// 不使用数据加载选项
/// </summary>
void DataLoadOptions_Original()
{
NorthwindDataContext ctx = new NorthwindDataContext();
var categories = from c in ctx.Categories select c;
foreach (var c in categories)
{
// 每一次迭代都会执行一条SQL语句来返回当前Category的Product
foreach (var p in c.Products)
{
string productName = p.ProductName + "<br>";
}
}
}
//SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture]
//FROM [dbo].[Categories] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0
//FROM [dbo].[Categories] AS [t0]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0].[ReorderLevel], [t0].[Discontinued]
//FROM [dbo].[Products] AS [t0]
//WHERE [t0].[CategoryID] = @p0
//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5]
//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1
//SELECT [t0].[ProductID], [t0].[ProductName], [t0].[SupplierID], [t0].[CategoryID], [t0].[QuantityPerUnit], [t0].[UnitPrice], [t0].[UnitsInStock], [t0].[UnitsOnOrder], [t0