步步为营VS 2008 + .NET 3.5(10) - DLINQ(LINQ to SQL)之调用存储过程的添加、查询、更新和删除

步步为营VS 2008 + .NET 3.5(10) - DLINQ(LINQ to SQL)之调用存储过程的添加、查询、更新和删除

介绍

以Northwind为示例数据库，DLINQ(LINQ to SQL)之调用指定存储过程的添加操作、查询操作、更新操作和删除操作

示例

相关的存储过程

ALTER PROCEDURE [dbo].[spInsertCategory] 

        @CategoryName nvarchar(15), 

        @Description ntext, 

        @CategoryID int OUTPUT 

AS 

SET NOCOUNT ON 

INSERT INTO [dbo].[Categories] ( 

        [CategoryName], 

        [Description] 

) VALUES ( 

        @CategoryName, 

        @Description 

) 

SET @CategoryID = SCOPE_IDENTITY() 

RETURN @@ERROR

ALTER PROCEDURE [dbo].[spUpdateCategory] 

        @CategoryID int, 

        @Description ntext 

UPDATE [dbo].[Categories] SET 

        [CategoryName] = @CategoryName, 

        [Description] = @Description 

WHERE 

        [CategoryID] = @CategoryID 

ALTER PROCEDURE [dbo].[spDeleteCategory] 

        @CategoryID int 

DELETE FROM [dbo].[Categories] 

ALTER PROCEDURE [dbo].[spSelectCategory] 

        @CategoryID int = null 

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

SELECT 

        [CategoryID], 

        [Description], 

        [Picture] 

FROM 

        [dbo].[Categories] 

        @CategoryID IS NULL OR [CategoryID] = @CategoryID

ALTER PROCEDURE [dbo].[spSelectProduct] 

        @ProductID int = null 

        [ProductID], 

        [ProductName], 

        [SupplierID], 

        [QuantityPerUnit], 

        [UnitPrice], 

        [UnitsInStock], 

        [UnitsOnOrder], 

        [ReorderLevel], 

        [Discontinued] 

        [dbo].[Products] 

        @ProductID IS NULL OR [ProductID] = @ProductID

SP.aspx

<%@ Page Language="C#" MasterPageFile="~/Site.master" AutoEventWireup="true" CodeFile="SP.aspx.cs" 

        Inherits="LINQ_DLINQ_SP" Title="调用存储过程的添加、查询、更新和删除" %> 

<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server"> 

</asp:Content> 

<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server"> 

        <p> 

                分类名称：<asp:TextBox ID="txtCategoryName" runat="server"></asp:TextBox> 

                   分类描述：<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox> 

                <asp:Button ID="btnAdd" runat="server" Text="添加" OnClick="btnAdd_Click" /> 

        </p> 

        <asp:GridView ID="gvCategory" runat="server" DataKeyNames="CategoryID" OnSelectedIndexChanged="gvCategory_SelectedIndexChanged" 

                OnRowDeleting="gvCategory_RowDeleting" OnRowCancelingEdit="gvCategory_RowCancelingEdit" 

                OnRowEditing="gvCategory_RowEditing" OnRowUpdating="gvCategory_RowUpdating"> 

                <Columns> 

                        <asp:CommandField ShowSelectButton="True" ShowEditButton="True" ShowDeleteButton="True"> 

                        </asp:CommandField> 

                </Columns> 

        </asp:GridView> 

        <br /> 

        <asp:DetailsView ID="dvProduct" runat="server" DataKeyNames="ProductID"> 

        </asp:DetailsView> 

</asp:Content>

SP.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_SP : System.Web.UI.Page 

{ 

        // 实例化一个NorthwindDataContext（DataContext） 

        // 在对象关系设计器（Object Relational Designer）中拖进来存储过程，同时NorthwindDataContext类中就会自动生成调用相应存储过程的相应方法 

        NorthwindDataContext _ctx = new NorthwindDataContext(); 

        protected void Page_Load(object sender, EventArgs e) 

        { 

                if (!Page.IsPostBack) 

                { 

                        BindCategory(); 

                } 

        } 

        private void BindCategory() 

                var categories = _ctx.GetCategory(null); 

                gvCategory.DataSource = categories; 

                gvCategory.DataBind(); 

        protected void btnAdd_Click(object sender, EventArgs e) 

                // categoryId - 用于获取存储过程的输出值（output） 

                int? categoryId = null; 

                // rtn - 用于获取存储过程的返回值（return） 

                int rtn = _ctx.AddCategory(txtCategoryName.Text, txtDescription.Text, ref categoryId); 

                Page.ClientScript.RegisterStartupScript( 

                        this.GetType(), 

                        "js", 

                        string.Format("alert('output:{0},return:{1}')", categoryId.ToString(), rtn.ToString()), 

                        true); 

                gvCategory.EditIndex = -1; 

                BindCategory(); 

        protected void gvCategory_SelectedIndexChanged(object sender, EventArgs e) 

                var products = _ctx.GetProduct((int)gvCategory.SelectedValue); 

                dvProduct.DataSource = products; 

                dvProduct.DataBind(); 

        protected void gvCategory_RowDeleting(object sender, GridViewDeleteEventArgs e) 

                int rtn = _ctx.DeleteCategory((int)gvCategory.DataKeys[e.RowIndex].Value); 

                        string.Format("alert('return:{0}')", rtn.ToString()), 

        protected void gvCategory_RowUpdating(object sender, GridViewUpdateEventArgs e) 

                int rtn = _ctx.UpdateCategory( 

                        (int)gvCategory.DataKeys[e.RowIndex].Value, 

                        ((TextBox)gvCategory.Rows[e.RowIndex].Cells[2].Controls[0]).Text, 

                        ((TextBox)gvCategory.Rows[e.RowIndex].Cells[3].Controls[0]).Text); 

        protected void gvCategory_RowEditing(object sender, GridViewEditEventArgs e) 

                gvCategory.EditIndex = e.NewEditIndex; 

        protected void gvCategory_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e) 

}

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