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

[索引页]
[源码下载]

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

作者：webabcd


介绍
以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,
    @CategoryName nvarchar(15),
    @Description ntext
AS

SET NOCOUNT ON

UPDATE [dbo].[Categories] SET
    [CategoryName] = @CategoryName,
    [Description] = @Description
WHERE
    [CategoryID] = @CategoryID
    
RETURN @@ERROR

ALTER PROCEDURE [dbo].[spDeleteCategory]
    @CategoryID int
AS

SET NOCOUNT ON

DELETE FROM [dbo].[Categories]
WHERE
    [CategoryID] = @CategoryID
    
RETURN @@ERROR

ALTER PROCEDURE [dbo].[spSelectCategory]
    @CategoryID int = null
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
    [CategoryID],
    [CategoryName],
    [Description],
    [Picture]
FROM
    [dbo].[Categories]
WHERE
    @CategoryID IS NULL OR [CategoryID] = @CategoryID

ALTER PROCEDURE [dbo].[spSelectProduct]
    @ProductID int = null
AS

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

SELECT
    [ProductID],
    [ProductName],
    [SupplierID],
    [CategoryID],
    [QuantityPerUnit],
    [UnitPrice],
    [UnitsInStock],
    [UnitsOnOrder],
    [ReorderLevel],
    [Discontinued]
FROM
    [dbo].[Products]
WHERE
    @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>
        &nbsp;&nbsp; 分类描述：<asp:TextBox ID="txtDescription" runat="server"></asp:TextBox>
        &nbsp;&nbsp;
        <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)
    {
        // rtn - 用于获取存储过程的返回值（return）
        int rtn = _ctx.DeleteCategory((int)gvCategory.DataKeys[e.RowIndex].Value);

        Page.ClientScript.RegisterStartupScript(
            this.GetType(),
            "js",
            string.Format("alert('return:{0}')", rtn.ToString()),
            true);

        gvCategory.EditIndex = -1;
        BindCategory();
    }

    protected void gvCategory_RowUpdating(object sender, GridViewUpdateEventArgs e)
    {
        // rtn - 用于获取存储过程的返回值（return）
        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);

        Page.ClientScript.RegisterStartupScript(
            this.GetType(),
            "js",
            string.Format("alert('return:{0}')", rtn.ToString()),
            true);

        gvCategory.EditIndex = -1;
        BindCategory();
    }

    protected void gvCategory_RowEditing(object sender, GridViewEditEventArgs e)
    {
        gvCategory.EditIndex = e.NewEditIndex;
        BindCategory();
    }

    protected void gvCategory_RowCancelingEdit(object sender, GridViewCancelEditEventArgs e)
    {
        gvCategory.EditIndex = -1;
        BindCategory();
    }
}

OK
[源码下载]