Common SQL stored procedures for ASP. NET Learning

Using Stored Procedures in ASP. NET projects can first improve database security, and secondly improve the speed of running SQL code, which is generally essential for large projects. Visual Studio. NET provides powerful support for SQL stored procedures. You can use visual studio.net to create a stored procedure, or directly run it in the SQL Server Query analyzer, it can also be created through the Enterprise Manager for ease of use. We have always mistakenly believed that SQL stored procedures are a relatively "advanced" technology. In fact, there is no major problem in mastering general syntax, and we are in the use of storage tutorials is also mainly added to delete the operation, learn to use the general T-SQL is very easy to get started.
Let's take a look at how to create a stored procedure in SQL Server. We can use SQL command statements to create a stored procedure, or use the Enterprise Manager in SQL Server to create a stored procedure, but in fact, they can't do without writing their own statements. Of course, we don't need to change the system stored procedures (stored procedures include system stored procedures, local stored procedures, temporary stored procedures, and remote stored procedures, extended Stored Procedures), while local stored procedures are stored in our own stored procedures, which are also called user stored procedures.

When creating a stored procedure, you must determine the three components of the stored procedure.
All input parameters and output parameters passed to the caller
The executed database-specific operation statements include statements that call other stored procedures.
The status value returned to the caller to indicate whether the call is successful or failed.
1. Create a stored procedure
1. Use the Enterprise Manager to create a stored procedure;
Open the Enterprise Manager and find the database for which you want to create the stored procedure:

We can see that there is a stored procedure project in the database, and we need to use it. Select the Stored Procedure project, we can see that there are a lot of stored procedures in the database itself, but these are included in the database itself, we can see that its type is System (2 ), if we create a stored procedure, the type is user.

On the left side of the control tree, right-click the stored procedure and choose create stored procedure from the shortcut menu"

Select this option and a new window will appear. This window is used to write the stored procedure.

In actual application, the stored procedure we created is not as complex as we imagined, and none of the above parameters are used, however, before using the query analyzer to create a stored procedure, we will use the following statement to check whether a stored procedure with the same name already exists in the database. If so, delete it first.

If exists (Select name from sysobjects where name = 'stored procedure name' and type = 'P ')
Drop procedure 'stored procedure name'
Go

Here, we know how to delete a stored procedure, that is, using the drop procedure keyword + stored procedure name.

Let's first list several common stored procedures:

A. stored procedures without Parameters
/*
Purpose: Query all company directories
Taipa was created on
*/
Create procedure com_select
As
Select * from Company
Go

B. Stored Procedures with Parameters
/*
Select the corresponding Admin
Created by: dezi
Created on: 2006-4-20
*/
Create procedure admin_select
@ Adminusername char (50 ),
@ Adminpassword char (50)
As
Select * From superadmin where
[Admin_name] = @ adminusername and [admin_password] = @ adminpassword
Go

C. stored procedures in which output reserved words have returned values are used
Create procedure salequa
@ Stor_id char 4,
@ Sum smallint output
As
Select
Ord_num, ord_date,
PAYTERMS, title_id,
Qty
From sales
Where stor_id = @ stor_id
Select @ sum = sum Qty
From sales
Where stor_id = @ stor_id
Go

The above several stored procedures are basic stored procedures. At the same time, we can see that the annotations in the stored procedures are in the form of/* Annotations.

Next, let's talk about the use of stored procedures in net.

----------------

Ii. Storage Process usage

1. Execute in SQL
Run the Execute Command to execute the created stored procedure. The syntax is as follows:
[Execute]
{[@ Return_statur =]
{Procedure_name [; number] | @ procedure_name_var}
[[@ Parameter =] {value | @ variable [Output] | [Default] [,… N]
[With recompile]
The meanings of parameters are as follows:
@ Return_status
Is an optional integer variable used to store the value returned by the stored procedure to the caller.
@ Procedure_name_var
Is a variable name used to represent the name of the stored procedure
The meanings of other parameter data and reserved words are the same as those described in create procedure.

For example, we have a stored procedure named student_list_info to execute. In the query analyzer, you only need to write
Execute student_list_info
Go
You can.
If a Stored Procedure contains a stored procedure with a returned value, you must specify the parameter value. See the following example.
This example is taken from programmer's guide.
Create procedure salequa @ stor_id char 4, @ sum smallint output
As
Select ord_num, ord_date, PAYTERMS, title_id, Qty
From sales
Where stor_id = @ stor_id
Select @ sum = sum Qty
From sales
Where stor_id = @ stor_id
Go

To execute this stored procedure, specify the parameter @ sort_id and @ sum.
Declare @ totalqua smallint
Execute salequa '000000', @ totalqua output
If @ totalqua <= 50
Select 'sales information' = 'sale Level 3 sales volume '+ rtrim cast @ totalqua as varchar 20
If @ totalqua> 50 and @ totalqua <= 100
Select 'sales information' = 'sale Level 2 sales volume '+ rtrim cast @ totalqua as varchar 20
If @ totalqua> 100
Select 'sales information' = 'sales grade is 1 Sales Volume '+ rtrim cast @ totalqua as varchar 20
The running result is
Ord_num ord_date PAYTERMS title_id Qty
-------------------------------------------------------------------------
N914008 1994-09-14 00:00:00. 000 net 30 ps2091 20
N914014 1994-09-14 00:00:00. 000 net 30 mc3021 25
P3087a 1993-05-29 00:00:00. 000 net 60 ps1372 20
P3087a 1993-05-29 00:00:00. 000 net 60 ps2106 25
P3087a 1993-05-29 00:00:00. 000 net 60 ps3333 15
P3087a 1993-05-29 00:00:00. 000 net 60 ps7777 25
6 row s affected
Sales Information
-----------------------------------------
The sales level is 1. The sales volume is 130.

2. Use stored procedures in ASP. NET
In ASP. net (C # is used here). First, check whether system is referenced on the page. data. sqlclient; of course, database connection is essential. We know that the steps for calling data in ASP. NET are as follows:

Create a database connection object (sqlconnection) → open the database to be operated using the open () method → create a sqlcommand or sqldataadapter object → executenonquery () for SQL commands or stored procedures () method or executereader () method to perform data operations → read or input data to the database → close the connection using the close () method.

We can see that before using stored procedures, we should use the sqlcommand object or sqldataadapter object to fill the dataset or co-existence in the use of stored procedures. however, the method used is not very different from the method used to directly execute SQL statements in net. We can use examples to illustrate how to call a stored procedure.
(1) Use a sqlcommand object

Program code:

[Copy code to clipboard]

String spid = request. querystring ["supplyid"]. Trim ();
Sqlconnection conndb = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Conndb. open ();
Sqlcommand strselect = new sqlcommand ("supplyinfo_select_supplyid", conndb );
Strselect. commandtype = commandtype. storedprocedure;
Strselect. Parameters. Add ("@ supply_id", spid );
Sqldatareader reader = strselect. executereader ();
If (reader. Read ())
{
Lblid. Text = reader ["supply_id"]. tostring (). Trim ();
Lbltitle. Text = reader ["supply_subject"]. tostring (). Trim ();
Lblbigclass. Text = reader ["supply_catid"]. tostring (). Trim ();
Lbldesc. Text = reader ["supply_details"]. tostring (). Trim ();
Lblpurtype. Text = reader ["supply_purchasetype"]. tostring (). Trim ();
If (Int. parse (Reader ["supply_ischecked"]. tostring (). Trim () = 1)
{
Lblischk. Text = "approved ";
}
Else
{
Lblischk. Text = "not approved ";
}
If (Int. parse (Reader ["supply_isrcmd"]. tostring (). Trim () = 1)
{
Lblisrcmd. Text = "recommended ";
}
Else
{
Lblisrcmd. Text = "not set as Recommendation ";
}
Switch (Reader ["supply_reader_level"]. tostring (). Trim ())
{
Case "0 ":
Lbllevel. Text = "setting everyone can see this information ";
Break;
Case "1 ":
Lbllevel. Text = "set this information for registered members ";
Break;
Case "2 ":
Lbllevel. Text = "set VIP members to see this information ";
Break;
}
}

From the above we can see that the key statement for calling a stored procedure using the sqlcommand object is:
Sqlcommand strselect = new sqlcommand ("supplyinfo_select_supplyid", conndb );
Strselect. commandtype = commandtype. storedprocedure;
Strselect. Parameters. Add ("@ supply_id", spid );
Simple Explanation: declare a sqlcommand object and call the Stored Procedure supplyinfo_select_supplyid through sqlcommand,
At the same time, it contains an input parameter @ supply_id whose value is the variable spid. At the same time, it queries data related to the data through the executereader () method and displays the data through the label control.

(2) Use the sqldataadapter object

Program code:

[Copy code to clipboard]

Private void buycatalog ()
{
Sqlconnection conndb = new sqlconnection (system. configuration. configurationsettings. deleettings ["conn"]);
Conndb. open ();
Sqldataadapter strselect = new sqldataadapter ("productclass", conndb );

Strselect. selectcommand. commandtype = commandtype. storedprocedure;

Dataset DS = new dataset ();

Strselect. Fill (DS );

Dlstbuycatalog. datasource = Ds;

Dlstbuycatalog. datakeyfield = "pdtcat_id ";

Dlstbuycatalog. databind ();

Conndb. Close ();
}

The above method is to use the sqldataadapter to call the SQL stored procedure productclass, fill the data in DS through dataset, and specify that the data source of the datalist control dlstbuycatalog is DS, and the primary key is pdtcat_id, then re-bind the datalist control. in this method, we can see that the key to calling the stored procedure with sqldataadapter is:
Sqldataadapter strselect = new sqldataadapter ("productclass", conndb );
Strselect. selectcommand. commandtype = commandtype. storedprocedure;
What should we do when there are parameters in the stored procedure? In fact, this is similar to sqlcommand. We only need to add one more sentence.
Strselect. selectcommand. Parameter. Add ("@ pdt_name", txtpdtname. Text ());
Here, @ pdt_name is the parameter variable name declared in the stored procedure, while txtpdtname. Text () is the value assigned to the variable @ pdt_name in. net. Take a closer look at the following stored procedure:
From the above, we can know that the most critical object in the stored procedure is the command object. This object can be executed through the executereader () method, and a single value query can be returned, you can also use the executescalar () method to perform relevant data statistics, or use the executenonquery () method to update data, add, delete, modify, and execute operations. When you execute these SQL operations, it is often used in combination with the related control DataGrid, datalist, and repeat controls.

(3) Examples of common stored procedures
The following are some of the stored procedures used in a recent project. They may not be well written due to their limited level, however, most of the results I want are implemented. These stored procedures can be executed normally and sent to you (please forgive me for keeping the database confidential). I hope it will be useful to you, I also hope to correct the error. Thank you.

(1) select all records

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Query all records in sellinfo
Date: 2006-3-23
*/
Create procedure sellinfo_select
As
Select * From sellinfo
Go

(2) Delete the specified ID record

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Delete the ID record specified by the input parameter @ sell_id in sellinfo.
Date: 2006-3-23
*/
Create procedure sellinfo_delete
@ Sell_id bigint
As
Delete from [sellinfo]
Where
Sell_id = @ sell_id
Go

(3) update the corresponding record

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Modify the corresponding sub-class name
Date: 2006-4-5
*/
Create procedure prosmallclass_update_id
@ Smallid int,
@ Smallname char (50)
As
Update [productcats]
Set
Pdtcat_name = @ smallname
Where
Pdtcat_id = @ smallid
Go

(4) Verify Login

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Use the obtained @ user_name @ user_password to verify logon.
Date: 2006-3-21
*/
Create procedure user_login
@ User_name varchar (50 ),
@ User_password varchar (50)
As
Select * From usercompany where [user_name] = @ user_name and [user_pwd] = @ user_password
If @ rowcount> 0
Begin
Update [users] Set user_logintimes = user_logintimes + 1 where [user_name] = @ user_name and [user_pwd] = @ user_password
End
Go

(5) password Modification

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: first check the user password and then change the new password.
Date: 2006-3-23
*/
Create procedure user_pwd
@ User_name varchar (30 ),
@ User_oldpwd varchar (30 ),
@ User_newpwd varchar (30 ),
@ Ioutput int output
As
If exists (select * from users where user_name = @ user_name and user_pwd = @ user_oldpwd)
Begin
Update users set user_pwd = @ user_newpwd where user_name = @ user_name and user_pwd = @ user_oldpwd
Set @ ioutput = 1
End
Else
Set @ ioutput =-1
Go

(6) Add new records

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Add a new message.
Date: 2006-4-8
*/
Create procedure gb_add
@ Gbusername char (50 ),
@ Gbusermemberid char (50 ),
@ Gbuseremail char (50 ),
@ Gbusersubject char (50 ),
@ Gbusercontent char (1500)
As
Insert GB
(
Gbusername,
Gbusermemberid,
Gbuseremail,
Gbsubject,
Gbcontent

)
Values
(
@ Gbusername,
@ Gbusermemberid,
@ Gbuseremail,
@ Gbusersubject,
@ Gbusercontent
)
Go

(7) Statistical data

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: used to count the total number of all information on the website, including news, products, companies, etc.
Date: 2006-3-23
*/

Create procedure datacount
As
Declare @ membercount int
Declare @ membervip int
Declare @ membernorm int
Declare @ memberunchkreg int
Declare @ memberlblregchk int

Declare @ companycount int
Declare @ companyrcmd int

Declare @ sellcount int
Declare @ sellrcmd int
Declare @ sellunchk int
Declare @ sellchk int

Declare @ countsupply int
Declare @ supplyrcmd int
Declare @ supplyunchk int
Declare @ supplychk int

Declare @ newscount int
Declare @ newsrcmd int
Declare @ newsclasscount int

Declare @ supplyclass int
Declare @ sellclass int
Declare @ msgcount int

Declare @ probigclass int
Declare @ prosmallclass int

Select @ membercount = count (user_id) from users
Select @ membervip = count (user_id) from users where user_level = 2
Select @ membernorm = count (user_id) from users where user_level = 1
Select @ memberunchkreg = count (user_id) from users where user_ischecked = 0
Select @ memberlblregchk = count (user_id) from users where user_ischecked = 1

Select @ companycount = count (com_id) from Company
Select @ companyrcmd = count (com_id) from company where com_isrcmd = 1

Select @ sellcount = count (sell_id) from sellinfo
Select @ sellrcmd = count (sell_id) from sellinfo where sell_isrcmd = 1
Select @ sellunchk = count (sell_id) from sellinfo where sell_ischecked = 0
Select @ sellchk = count (sell_id) from sellinfo where sell_ischecked = 1

Select @ countsupply = count (supply_id) from supplyinfo
Select @ supplyrcmd = count (supply_id) from supplyinfo where supply_isrcmd = 1
Select @ supplyunchk = count (supply_id) from supplyinfo where supply_ischecked = 0
Select @ supplychk = count (supply_id) from supplyinfo where supply_ischecked = 1

Select @ newscount = count (news_id) from news
Select @ newsrcmd = count (news_id) from news where news_recommand = 1
Select @ newsclasscount = count (news_id) from news

Select @ probigclass = count (pdtcat_sortid) from productcats where pdtcat_sortid = 0
Select @ prosmallclass = count (pdtcat_sortid) from productcats where pdtcat_sortid <> 0

Select @ msgcount = count (msg_id) from msg
Select
Membercount = @ membercount,
Membervip = @ membervip,
Membernorm = @ membernorm,
Memberunchkreg = @ memberunchkreg,
Memberlblregchk = @ memberlblregchk,
Companycount = @ companycount,
Companyrcmd = @ companyrcmd,
Sellcount = @ sellcount,
Sellrcmd = @ sellrcmd,
Sellunchk = @ sellunchk,
Sellchk = @ sellchk,
Countsupply = @ countsupply,
Supplyrcmd = @ supplyrcmd,
Supplyunchk = @ supplyunchk,
Supplychk = @ supplychk,
Newscount = @ newscount,
Newsrcmd = @ newsrcmd,
Newsclasscount = @ newsclasscount,
Probigclass = @ probigclass,
Prosmallclass = @ prosmallclass,
Msgcount = @ msgcount
Go

(8) fuzzy search

Program code:

[Copy code to clipboard]

/*
Author: dezi
Purpose: Query sell_info
Date: 2006-4-10
*/
Create procedure sellinfo_search
@ Keyword nvarchar (20)
As
Select sell_subject from sellinfo where sell_subject like '%' + @ keyword + '%'
Go