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

步步为营VS 2008 + .NET 3.5(13) - DLINQ(LINQ to SQL)之用户自定义函数、在不同的DataContext之间做更新、缓存、获取信息、数据加载选项和延迟加载

介绍

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

        }

运行结果

Hello:webabcd

调用表值用户自定义函数

ALTER FUNCTION dbo.fnHelloFunctionTable 

        @ID int, 

        @name nvarchar(100) 

RETURNS @tbl TABLE ([ID] int, [Name] nvarchar(100)) 

        INSERT INTO @tbl VALUES (@ID, @name) 

        RETURN 

/// <summary> 

        /// 调用表值用户自定义函数 

        void Call_HelloFunctionTable() 

                var p = (ctx.HelloFunctionTable(1, "webabcd")).Single(); 

                result.InnerHtml += "ID：" + p.ID; 

                result.InnerHtml += "Name：" + p.Name; 

ID：1

Name：webabcd

不使用数据加载选项

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

        void DataLoadOptions_Original() 

                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] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [3] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [4] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [5] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [6] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [7] 

//-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [8] 

//-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

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

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

        void DataLoadOptions_LoadWith() 

                System.Data.Linq.DataLoadOptions options = new System.Data.Linq.DataLoadOptions(); 

                // Category以LEFT JOIN的方式关联Product 

                options.LoadWith<Categories>(c => c.Products); 

                ctx.LoadOptions = options; 

//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] 

//LEFT OUTER JOIN [dbo].[Products] AS [t1] ON [t1].[CategoryID] = [t0].[CategoryID] 

//ORDER BY [t0].[CategoryID], [t1].[ProductID] 

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

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

        void DataLoadOptions_AssociateWith() 

                // 限制所关联的Product 

                options.AssociateWith<Categories>(c => c.Products.Where(p => p.UnitPrice > 20)); 

//        WHERE ([t2].[UnitPrice] > @p0) AND ([t2].[CategoryID] = [t0].[CategoryID]) 

//LEFT OUTER JOIN [dbo].[Products] AS [t1] ON ([t1].[UnitPrice] > @p0) AND ([t1].[CategoryID] = [t0].[CategoryID]) 

//-- @p0: Input Currency (Size = 0; Prec = 19; Scale = 4) [20] 

相关信息的获取

        /// 相关信息的获取 

        void GetInfo() 

                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]

字段的延迟加载

        /// 字段的延迟加载 

        void DelayLoaded() 

                // 在对象关系设计器（Object Relational Designer）中设置Categories实体的Picture属性的Delay Loaded为True 

                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] 

//SELECT [t0].[Picture] 

在不同的DataContext之间做更新

        /// 在不同的DataContext之间做更新 

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

                        ctx.Categories.Attach(c, true); 

                        ctx.SubmitChanges(); 

//UPDATE [dbo].[Categories] 

//SET [CategoryName] = @p2, [Description] = @p3 

//WHERE ([CategoryID] = @p0) AND ([CategoryID] = @p1) 

//-- @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] 

缓存

        /// 缓存 

        void ShowCache() 

                // 查询后数据会被缓存，如果之后以主键查询的话，会先在缓存里查找 

                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 

WHERE [t0].[CategoryID] = @p0 

-- @p0: Input Int32 (Size = 0; Prec = 0; Scale = 0) [2] 

-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.20706.1

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