5. Stored Procedure

Summary:

Linqto SQL is very convenient to use. In the example of the last message board, we know that as long as we add a LINQ to SQL classes file and drag the table into the. dbml file, Vs will generate a series of files for you. Similarly, we can drag stored procedures into the. dbml file. Vs will also help you generate corresponding methods and new fields. If you do not want the stored procedure to generate a new field, you can select the field class of the table in the database in returntype.

Content:

The Stored Procedure Code that vs generates for you:

[Global: system. Data. LINQ. Mapping. functionattribute (name = "DBO. proc_selectadmin")]

Publicisingleresult <admin> proc_selectadmin ()

{

Iexecuteresultresult = This. executemethodcall (this, (methodinfo)

(Methodinfo. getcurrentmethod ())));

Return (isingleresult <admin>) (result. returnvalue ));

}

 

Admin:

Login nchar (10) checked

PWD nchar (10) checked

User table:

Login nchar (10) checked

PWD nchar (10) checked

 

Stored Procedure:

Alter proc [DBO]. [proc_deleuserbylogin]

@ Login nchar (10)

As

Delete DBO. [user] Where login = @ Login

 

Alter proc [DBO]. [proc_insertuser]

@ Login nchar (10 ),

@ PWD nchar (10)

As

Insert into DBO. [user] values (@ login, @ PWD)

 

Alter proc [DBO]. [proc_selectadmin]

As

Select * From DBO. Admin

 

Alter proc [DBO]. [proc_selectadminanduser]

As

Select * From DBO. Admin

Select * From DBO. [user]

 

Alter proc [DBO]. [proc_selectcountadminbylogin]

@ Login nchar (10 ),

@ Count int output

As

Set @ COUNT = (select count (*) fromdbo. Admin where login = @ login)

 

Alter proc [DBO]. [proc_selectstringadminbylogin]

@ Login nchar (10)

As

If exists (select 1 from DBO. adminwhere login = @ login)

Return 123

Else

Return 456

 

Alter proc [DBO]. [proc_updateadmin]

@ Oldloginnchar (10 ),

@ Newlogin nchar (10)

As

Update DBO. Admin set login = @ newloginwhere login = @ oldlogin

 

1. General query stored procedures

Lqdemo5dbdatacontext CTX = newlqdemo5dbdatacontext ("connection ");

Gridview1.datasource = CTX. proc_selectadmin ();

Gridview1.databind ();

Alternatively, we can use LINQ to object to query the stored procedure results:

VaR ad = from C in CTX. proc_selectadmin () Where C. login = "admin" select C;

2, with Parameters

Int? M =-1; // indicates the int type that can be empty, short for system. nullable <t>

CTX. proc_selectcountadminbylogin ("admin", ref m );

Response. Write (m );

3, with return values

Response. Write (CTX. proc_selectstringadminbylogin ("admin "));

4. Multiple result sets

// [Global: system. Data. LINQ. Mapping. functionattribute (name = "DBO. proc_selectadminanduser")]

// Publicisingleresult <proc_selectadminanduserresult> proc_selectadminanduser ()

//{

// Iexecuteresult result = This. executemethodcall (this, (methodinfo) (methodinfo. getcurrentmethod ())));

// Return (isingleresult <proc_selectadminanduserresult>) (result. returnvalue ));

//}

To:

[Function (name = "DBO. proc_selectadminanduser")]

[Resulttype (typeof (Admin)]

[Resulttype (typeof (User)]

Public imultipleresultsproc_selectadminanduser ()

{

Iexecuteresult result = This. executemethodcall (this, (methodinfo) (methodinfo. getcurrentmethod ())));

Return (imultipleresults) (result. returnvalue );

}

When using:

Varrusult = CTX. proc_selectadminanduser ();

VaR admin = rusult. getresult <admin> ();

VaR user = rusult. getresult <user> ();

Note: The system sometimes automatically modifies the original content.

5. add data

// Add statements and stored procedures

// User US = new user ();

// Us. login = "usersss ";

// Us. Pwd = "pwdpwd ";

// CTX. Users. insertonsubmit (US); // if no primary key exists, addition, deletion, and modification of the Code cannot be used.

CTX. proc_insertuser ("useraaa", "zxxcz ");

 

 

Download source code here