Visual Studio.NET provides powerful support for SQL stored procedures, either by creating new stored procedures from Visual Studio.NET, or by running them directly in Query Analyzer in SQL Server, or through Enterprise Manager. It is also very convenient to use. Everyone has been mistaken for the SQL stored procedure is a relatively "advanced" technology, in fact, master the general syntax is not a big problem, and we use the storage tutorial is mainly to increase the operation of the deletion, learn to use the general T-SQL is easy to get started.
Let's take a look at how to create a stored procedure in Sql-server, we can create it using SQL command statements, or we can create it through the Enterprise Manager in SQL Server, but we don't really have to write our own statements. Of course, we don't have to move the system stored procedures (stored procedures are divided into system stored procedures, local stored procedures, temporary stored procedures, remote stored procedures, extended stored procedures), and the local storage process is our own written stored procedures, but also called user stored procedures.
You need to determine the three components of a stored procedure when you create a stored procedure
all input parameters and output parameters passed to the caller
an action statement executed against the database includes statements that invoke other stored procedures
The state value returned to the caller to indicate whether the call succeeded or failed
First, stored procedure creation
1. Use Enterprise Manager to create stored procedures;
Let's first open Enterprise Manager and find the database where we want to create the stored procedure, as shown in Figure one:
We can see that there is a stored procedure project in the database, that's what we're going to use, check the stored procedure, and we can see that there are a lot of stored procedures in the database itself, but these are the databases themselves, and we can see that his type is the system (Figure II), If we create the stored procedure ourselves, the type is the user.
On the left side of the control tree, we click the right mouse button after selecting the stored procedure, and we can see a "option to create a stored procedure"
By selecting this, a new window will appear, which is used to write the stored procedure.
In practice, the stored procedures we create are not as complex as we think, the above parameters are not all to use, but generally we use Query Analyzer to create a stored procedure, will be through the following statement to query whether the database already has the same named stored procedures, if it exists , it is deleted first.
If exists (select name from sysobjects where name= ' stored procedure name ' and type= ' P ')
Drop procedure ' Stored procedure name '
Go
Here we also know how to delete a stored procedure by using the drop procedure keyword + stored procedure name.
Let's first enumerate a few common stored procedures:
A. Stored procedures that do not use parameters
Copy Code code as follows:
/*
Use: Search all the company directory
Tak Tsai was founded in 2006-3-29
*/
CREATE PROCEDURE Com_select
As
SELECT * FROM Company
Go
B. Stored procedures with parameters
/*
Select the corresponding admin
Created by: Tak Tsai
Date Created: 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 return values are used in this stored procedure
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
Several of the above stored procedures are basic stored procedures, and we can see that in the stored procedure the annotation is in the/* Comment Form/*.
Let's talk about the use of stored procedures in net for the next time.
----------------
Second, the stored procedure uses the article
1. Executing in SQL
Execute the created stored procedure using the Execute command with the following syntax
[EXECUTE]
{[@return_statur =]
{Procedure_name[;number] | @procedure_name_var}
[[@parameter =] {value | @variable [OUTPUT] | [DEFAULT] [,... N]
[With RECOMPILE]
The meaning of each parameter is 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 meaning of other parameter data and reserved words is the same as that described in Create PROCEDURE
For example, we have a stored procedure named Student_list_info to execute, in Query Analyzer you just write
Execute Student_list_info
Go
It's okay.
If the stored procedure contains a stored procedure that has a return value, then we must specify the parameter value. Look at the following example
This example is excerpted from the SQL Server Programmer's Guide book
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, we specify the parameter @sort_id, the parameter value @sum.
DECLARE @totalqua smallint
Execute Salequa ' 7131 ', @totalqua output
If @totalqua <=50
Select ' Sales Information ' = ' Sales level is 3 sold as ' +rtrim cast @totalqua as varchar 20
If @totalqua >50 and @totalqua <=100
Select ' Sales Information ' = ' Sales level is 2 sold as ' +rtrim cast @totalqua as varchar 20
If @totalqua >100
Select ' Sales Information ' = ' Sales level is 1 sold as ' +rtrim cast @totalqua as varchar 20
Run result is
Ord_num ord_date payterms title_id Qty
-------------------- --------------------------- ------------ -------- ------
N914008 1994-09-14 00:00:00.000 Net PS2091 20
N914014 1994-09-14 00:00:00.000 Net MC3021 25
p3087a 1993-05-29 00:00:00.000 Net PS1372 20
p3087a 1993-05-29 00:00:00.000 Net PS2106 25
p3087a 1993-05-29 00:00:00.000 Net PS3333 15
p3087a 1993-05-29 00:00:00.000 Net PS7777 25
6 row S affected
Sales information
-----------------------------------------
Sales level is 1 sales to 130
2. Using stored procedures in asp.net
To use a stored procedure in asp.net (here in C #), first check to see if the page refers to System.Data.Sqlclient, and of course the database connection is essential. We know that generally, the steps we call data in asp.net are as follows:
Create a new Database connection object (generally with SqlConnection) → Open the database we want to manipulate by using the open () method → creating a SqlCommand or SqlDataAdapter object → for SQL commands or stored procedures ExecuteNonQuery () method or the ExecuteReader () method to perform data operations → Read or enter data to the database → Close the connection with the closing () method.
So, before you use a stored procedure, We want to use the SqlCommand object or the SqlDataAdapter object to make the dataset or a total of it useful in the application of the stored procedure. But its approach is not very large with the direct execution of SQL statements in net, we can use examples to illustrate how the stored procedure is called .
(1) Adopt SqlCommand object
Program code:
Copy Code code as follows:
String spid=request.querystring["Supplyid"]. Trim ();
SqlConnection conndb=new SqlConnection (system.configuration.configurationsettings.appsettings["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= "has passed the audit";
}
Else
{
Lblischk.text= "failed to pass the audit";
}
if (int. Parse (reader["Supply_isrcmd"). ToString (). Trim ()) ==1)
{
Lblisrcmd.text= "has been set as recommended";
}
Else
{
Lblisrcmd.text= "not set as recommended";
}
Switch (reader["Supply_reader_level"]. ToString (). Trim ())
{
Case "0":
lbllevel.text= "Set Everyone can see this information";
Break
Case "1":
lbllevel.text= "Set up registered members to see this information";
Break
Case "2":
lbllevel.text= "Set up VIP members can see this information";
Break
}
}
As you can see, the key statement that uses the SqlCommand object to invoke the stored procedure is:
SqlCommand strselect = new SqlCommand ("Supplyinfo_select_supplyid", conndb);
Strselect.commandtype= CommandType.StoredProcedure;
Strselect. Parameters.Add ("@supply_ID", spid);
Simple explanation: Declare a SqlCommand image, call the stored procedure via SqlCommand Supplyinfo_select_supplyid,
Also contains an input parameter @supply_id, the value is the variable spid, and through the ExecuteReader () method, query data related data, through the label control, the data display.
(2) Adopt SqlDataAdapter object
Program code:
Copy Code code as follows:
private void Buycatalog ()
{
SqlConnection conndb= New SqlConnection (system.configuration.configurationsettings.appsettings["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 method above is to call the SQL stored procedure Productclass through the SqlDataAdapter, populate the DS with the dataset, and specify that the DataList control Dlstbuycatalog data Source is the DS. The primary key is pdtcat_id, and finally rebind the DataList control. By this method we can see that the key to using the SqlDataAdapter to invoke the stored procedure is:
SqlDataAdapter strselect = new SqlDataAdapter ("Productclass", conndb);
Strselect.SelectCommand.CommandType = CommandType.StoredProcedure;
When there are parameters in the stored procedure, what should we do first? In fact, this is similar to SqlCommand, we just add a sentence
STRSELECT.SELECTCOMMAND.PARAMETER.ADD ("@pdt_name", Txtpdtname. Text ());
is OK, where @pdt_name is the name of the parameter variable declared in the stored procedure, and Txtpdtname.text () is the value assigned to the variable @pdt_name in. Net. A careful look at the following stored procedure is clear:
We know from the above that the most critical object in the invocation of a stored procedure is the command object, which can execute a data query through the ExecuteReader () method, return a single value query, and pass the ExecuteScalar () Methods to carry out related data statistics, can also be ExecuteNonQuery () method for data updates, additions and deletions to perform operations, and in the execution of these SQL operations, often with the relevant control DataGrid, Datalist,repeat control in conjunction with.
(3) Some common examples of stored procedures
Here are some of the stored procedures that you've used in a recent project. Perhaps because of their limited level, some write is not very normative, but most of them achieve the results I want, these stored procedures can be properly executed, send these to everyone (database for confidentiality please forgive me), I hope to use, At the same time hope to correct the mistakes, thank you.
(1) Select all the records
Program code:
Copy Code code as follows:
/*
Author: Tak Tsai
Usage: Check all the 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 code as follows:
/*
Author: Tak Tsai
Purpose: Deletes the ID record specified by the input parameter @sell_id in the 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 code as follows:
/*
Author: Tak Tsai
Use: Modify the corresponding small 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 the landing
Program code:
Copy Code code as follows:
/*
Author: Tak Tsai
Use: Login through the obtained @user_name @user_password verification
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 code as follows:
/*
Author: Tak Tsai
Use: First check the user's password, and then modify 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 code as follows:
/*
Author: Tak Tsai
Use: 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]
Copy Code code as follows:
/*
Author: Tak Tsai
Use: To count the total number of information on the station, 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 the 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 query
Program code:
Copy Code code as follows:
/*
Author: Tak Tsai
Use: Used for 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
Above is only oneself in the study asp.net a little personal experience, because individual level limit, unavoidably wrong, welcome everybody to correct, and please advise!
