. Net 3.5 (10)-add, query, update, and delete a stored procedure call using dlinq to SQL

Step by step vs 2008 +. Net 3.5 (10)-add, query, update, and delete a stored procedure call using dlinq to SQL

Author: webabcd

Introduction
Taking northwind as the example database, dlinq (LINQ to SQL) calls the ADD, query, update, and delete operations of a specified stored procedure.

Example
Related stored procedures

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 = "add, query, update, and delete a stored procedure call" %>

<Asp: Content ID = "content1" contentplaceholderid = "head" runat = "server">
</ASP: content>
<Asp: Content ID = "content2" contentplaceholderid = "contentplaceholder1" runat = "server">
<P>
Category name: <asp: textbox id = "txtcategoryname" runat = "server"> </ASP: textbox>
Category Description: <asp: textbox id = "txtdescription" runat = "server"> </ASP: textbox>

<Asp: button id = "btnadd" runat = "server" text = "add" 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. csusing 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
{
// Instantiate a northwinddatacontext (datacontext)
// Drag the stored procedure into the object relational designer. At the same time, the northwinddatacontext class automatically generates a method to call the corresponding stored procedure.
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-used to obtain the output value of a stored procedure)
Int? Categoryid = NULL;
// RTN-used to obtain the return value of a stored procedure (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-used to obtain the return value of a stored procedure (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-used to obtain the return value of a stored procedure (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
[Download source code]

This article is from the "webabcd" blog, please be sure to keep this source http://webabcd.blog.51cto.com/1787395/345010