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

2022-01-06 20:16:31

作者：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>
                   分类描述：<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

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

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
[源码下载]

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

码农公寓

相关文章