步步为营VS 2008 + .NET 3.5(13) - DLINQ(LINQ to SQL)之用户自定义函数

[索引页]
[源码下载]

步步为营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(); 
        } 
}

 

调用标量值用户自定义函数

ALTER FUNCTION dbo.fnHelloFunctionScalar 
( 
        @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 />"; 
        }

运行结果
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

/// <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 />"; 
        }

运行结果
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>"; 
                        } 
                } 
        }

// 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].[ReorderLevel], [t0].[Discontinued] 
// FROM [dbo].[Products]  AS [t0] 
// WHERE [t0].[CategoryID] = @p0 
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [6] 
// -- 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) [7] 
// -- 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) [8] 
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

 

使用数据加载选项加载数据

/// <summary> 
         /// 使用数据加载选项加载数据 
         /// </summary> 
         void DataLoadOptions_LoadWith() 
        { 
                NorthwindDataContext ctx =  new NorthwindDataContext(); 
                System.Data.Linq.DataLoadOptions options =  new System.Data.Linq.DataLoadOptions(); 
 
                 // Category以LEFT JOIN的方式关联Product 
                options.LoadWith<Categories>(c => c.Products); 
                ctx.LoadOptions = options; 
 
                var categories = from c  in ctx.Categories select c; 
 
                 foreach (var c  in categories) 
                { 
                         foreach (var p  in c.Products) 
                        { 
                                 string productName = p.ProductName +  "<br>"; 
                        } 
                } 
        }

// SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID]  AS [CategoryID2], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], ( 
//         SELECT  COUNT(*) 
//         FROM [dbo].[Products]  AS [t2] 
//         WHERE [t2].[CategoryID] = [t0].[CategoryID] 
//        )  AS [ count] 
// FROM [dbo].[Categories]  AS [t0] 
// LEFT  OUTER  JOIN [dbo].[Products]  AS [t1]  ON [t1].[CategoryID] = [t0].[CategoryID] 
// ORDER  BY [t0].[CategoryID], [t1].[ProductID] 
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

 

使用数据加载选项加载数据，并且限制所要加载的数据

/// <summary> 
         /// 使用数据加载选项加载数据，并且限制所要加载的数据 
         /// </summary> 
         void DataLoadOptions_AssociateWith() 
        { 
                NorthwindDataContext ctx =  new NorthwindDataContext(); 
                System.Data.Linq.DataLoadOptions options =  new System.Data.Linq.DataLoadOptions(); 
 
                 // Category以LEFT JOIN的方式关联Product 
                options.LoadWith<Categories>(c => c.Products); 
                 // 限制所关联的Product 
                options.AssociateWith<Categories>(c => c.Products.Where(p => p.UnitPrice > 20)); 
                ctx.LoadOptions = options; 
 
                var categories = from c  in ctx.Categories select c; 
 
                 foreach (var c  in categories) 
                { 
                         foreach (var p  in c.Products) 
                        { 
                                 string productName = p.ProductName +  "<br>"; 
                        } 
                } 
        }

// SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description], [t0].[Picture], [t1].[ProductID], [t1].[ProductName], [t1].[SupplierID], [t1].[CategoryID]  AS [CategoryID2], [t1].[QuantityPerUnit], [t1].[UnitPrice], [t1].[UnitsInStock], [t1].[UnitsOnOrder], [t1].[ReorderLevel], [t1].[Discontinued], ( 
//         SELECT  COUNT(*) 
//         FROM [dbo].[Products]  AS [t2] 
//         WHERE ([t2].[UnitPrice] > @p0)  AND ([t2].[CategoryID] = [t0].[CategoryID]) 
//        )  AS [ count] 
// FROM [dbo].[Categories]  AS [t0] 
// LEFT  OUTER  JOIN [dbo].[Products]  AS [t1]  ON ([t1].[UnitPrice] > @p0)  AND ([t1].[CategoryID] = [t0].[CategoryID]) 
// ORDER  BY [t0].[CategoryID], [t1].[ProductID] 
// -- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [20] 
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

 

相关信息的获取

/// <summary> 
         /// 相关信息的获取 
         /// </summary> 
         void GetInfo() 
        { 
                NorthwindDataContext ctx =  new NorthwindDataContext(); 
 
                var categories = from c  in ctx.Categories select c; 
 
                result.InnerHtml +=  "ProviderType：" + ctx.Mapping.ProviderType.ToString() +  "<br />"; 
 
                result.InnerHtml +=  "DatabaseName：" + ctx.Mapping.DatabaseName +  "<br />"; 
 
                result.InnerHtml +=  "ContextType：" + ctx.Mapping.ContextType.ToString() +  "<br />"; 
 
                result.InnerHtml +=  "TableName：" + ctx.Mapping.GetTable( typeof(Categories)).TableName +  "<br />"; 
 
                result.InnerHtml +=  "Expression：" + categories.Expression.ToString() +  "<br />"; 
 
                result.InnerHtml +=  "T-SQL：" + categories.Provider.ToString() +  "<br />"; 
        }

 

运行结果
ProviderType：System.Data.Linq.SqlClient.SqlProvider
DatabaseName：C:\DOCUMENTS AND SETTINGS\WANGLEI\桌面\VS2008\WEB\APP_DATA\NORTHWIND.MDF
ContextType：DAL.NorthwindDataContext
TableName：dbo.Categories
Expression：Table(Categories).Select(c => c)
T-SQL：SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] FROM [dbo].[Categories] AS [t0]

字段的延迟加载

/// <summary> 
         /// 字段的延迟加载 
         /// </summary> 
         void DelayLoaded() 
        { 
                 // 在对象关系设计器（Object Relational Designer）中设置Categories实体的Picture属性的Delay Loaded为True 
 
                NorthwindDataContext ctx =  new NorthwindDataContext(); 
 
                var category = (from c  in ctx.Categories 
                                                select c).Single(c => c.CategoryID == 1); 
 
                System.Data.Linq.Binary b = category.Picture; 
        }

// SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] 
// FROM [dbo].[Categories]  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].[Picture] 
// FROM [dbo].[Categories]  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

 

在不同的DataContext之间做更新

/// <summary> 
         /// 在不同的DataContext之间做更新 
         /// </summary> 
         void IsVersion() 
        { 
                 // 在Categories实体的主键上添加属性IsVersion=true，即Time Stamp为True 
 
                Categories c =  null; 
 
                 using (NorthwindDataContext ctx =  new NorthwindDataContext()) 
                { 
                        ctx.DeferredLoadingEnabled =  false; 
                        c = ctx.Categories.First(); 
                        c.Description =  "webabcd"; 
                } 
 
                 using (NorthwindDataContext ctx =  new NorthwindDataContext()) 
                { 
                        ctx.Categories.Attach(c,  true); 
                        ctx.SubmitChanges(); 
                } 
        }

// UPDATE [dbo].[Categories] 
// SET [CategoryName] = @p2, [Description] = @p3 
// WHERE ([CategoryID] = @p0)  AND ([CategoryID] = @p1) 
// -- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1] 
// -- @p1: Input Int32 (Size = 0; Prec = 0; Scale = 0) [1] 
// -- @p2: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages] 
// -- @p3: Input String (Size = 7; Prec = 0; Scale = 0) [webabcd] 
// -- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

 

缓存

/// <summary> 
         /// 缓存 
         /// </summary> 
         void ShowCache() 
        { 
                 // 查询后数据会被缓存，如果之后以主键查询的话，会先在缓存里查找 
 
                NorthwindDataContext ctx =  new NorthwindDataContext(); 
 
                Categories category = ctx.Categories.Single(c => c.CategoryName ==  "Beverages"); 
                Categories category2 = ctx.Categories.Single(c => c.CategoryName ==  "Beverages"); 
 
                Categories category3 = ctx.Categories.Single(c => c.CategoryID == 1); 
                Categories category4 = ctx.Categories.Single(c => c.CategoryID == 1); 
 
                Categories category5 = ctx.Categories.Single(c => c.CategoryID == 2); 
                Categories category6 = ctx.Categories.Single(c => c.CategoryID == 2); 
        }

SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] 
FROM [dbo].[Categories]  AS [t0] 
WHERE [t0].[CategoryName] = @p0 
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages] 
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 

SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] 
FROM [dbo].[Categories]  AS [t0] 
WHERE [t0].[CategoryName] = @p0 
-- @p0: Input String (Size = 9; Prec = 0; Scale = 0) [Beverages] 
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1 

SELECT [t0].[CategoryID], [t0].[CategoryName], [t0].[Description] 
FROM [dbo].[Categories]  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

 

OK
[源码下载]

     本文转自webabcd 51CTO博客，原文链接： http://blog.51cto.com/webabcd/345019 ，如需转载请自行联系原作者