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