Procedure from entry to proficiency (Article C)

① Why should stored procedures be used?
Because it is faster than SQL statement execution.

② What is a stored procedure?
Combine a bunch of SQL statements and execute SQL statements without passing through the conditions. (ax points of view in writing this article)

③ A simple Stored Procedure
Create procedure DBO. testprocedure_ax
As
Select userid from users order by userid DESC

Note: DBO. testprocedure_ax is the name of the stored procedure you created. You can change it to axzhz and so on. Don't conflict with keywords. as is an SQL statement. If you do not write an SQL statement, avoid it.

④ How can I call this stored procedure in ASP. NET?
The two rows below are sufficient.
Public static string getcustomercname (ref arraylist arraycname, ref arraylist arrayid)
{
Sqlconnection con = adconnection. createconnection ();
Sqlcommand cmd = new sqlcommand ("testprocedure_ax", con );
Cmd. commandtype = commandtype. storedprocedure;
Con. open ();
Try
{
Sqldatareader DR = cmd. executereader ();
While (dr. Read ())
{
If (Dr [0]. tostring () = "")
{
Arraycname. Add (Dr [1]. tostring ());
}
}
Con. Close ();
Return "OK! ";
}
Catch (exception ex)
{
Con. Close ();
Return ex. tostring ();
}
}
Note:
Sqlcommand cmd = new sqlcommand ("select userid from users order by userid DESC", con );
Replace the SQL statement with the stored procedure name, and mark the CMD type as commandtype. storedprocedure (stored procedure)

⑤ Write a stored procedure with parameters. The above is a little simple, but it is quite practical.
There are two parameters, and one parameter has no face. It's so cool.

Create procedure DBO. axzhz
/*
Write comments here
*/
@ Startdate varchar (16 ),
@ Enddate varchar (16)
As
Select ID from table_ax where commentdatetime> @ startdate and commentdatetime <@ enddate order by contentownerid DESC

Note: @ startdate varchar (16) declares the variable @ startdate. Multiple Variable names are separated by [,]. The variable can be used in the subsequent SQL statements.

⑥ How can I call this stored procedure with parameters in ASP. NET?

Public static string getcustomercnamecount (string startdate, string enddate, ref dataset DS)
{
Sqlconnection con = adconnection. createconnection ();
// ------------------------- Pay attention to the token --------------------------------------------------------------------------------------------------------
Sqldataadapter da = new sqldataadapter ("axzhz", con );

Para0 = new sqlparameter ("@ startdate", startdate );
Para1 = new sqlparameter ("@ enddate", enddate );
Da. selectcommand. Parameters. Add (para0 );
Da. selectcommand. Parameters. Add (para1 );
Da. selectcommand. commandtype = commandtype. storedprocedure;
// Configure //-------------------------------------------------------------------------------------------------------------------------------

Try
{
Con. open ();
Da. Fill (DS );
Con. Close ();
Return "OK ";
}
Catch (exception ex)
{
Return ex. tostring ();
}
}

Note: If you add the command parameters, it will be OK.
It's too junk to change the font color.

7. I also want to see if the SQL command is successfully executed.
See the following three lines of red statements:

Create procedure DBO. axzhz
/*
@ Parameter1 User Name
@ Parameter2 New Password
*/
@ Password nvarchar (20 ),
@ Username nvarchar (20)
As
Declare @ err0 int
Update wl_user SET Password = @ password where username = @ username
Set @ err0 =@@ Error
Select @ err0 as err0

Note: declare an integer variable @ err0 first, and assign the value @ error to it (whether the statement automatically given by the system is successfully executed, 0 is successful, and others are failed ), finally, select it through select. A senior said that the return can be returned, which is beyond the scope of my cognition.

Then how can we get the execution success value from the background?
The following code tells you the answer:
Public static string getcustomercname ()
{
Sqlconnection con = adconnection. createconnection ();

Sqlcommand cmd = new sqlcommand ("axzhz", con );
Cmd. commandtype = commandtype. storedprocedure;
Para0 = new sqlparameter ("@ startdate", "2006-9-10 ");
Para1 = new sqlparameter ("@ enddate", "2006-9-20 ");
Da. selectcommand. Parameters. Add (para0 );
Da. selectcommand. Parameters. Add (para1 );
Con. open ();
Try
{
Int32 Re = (int32) cmd. executescalar ();
Con. Close ();
If (RE = 0)
Return "OK! ";
Else
Return "false ";
}
Catch (exception ex)
{
Con. Close ();
Return ex. tostring ();
}
}
Note: This value is retrieved through the executescalar () method of sqlcommand. This sentence is found on msdn and I think it should be changed:
Int Re = (INT) cmd. executescalar (); 99% is correct. Now there is no time for verification. We look forward to your test !!!

Explain: I want to determine which SQL statement to execute based on the input parameters !!~
The following stored procedure can meet our requirements. It turns out to be PASCAL/Vb, begin ---- end, not {}. For me who use C, this syntax is a bit disgusting .........

Alter procedure DBO. selectcustomercnamecount
@ Customerid int
As
If @ customerid =-1
Begin
Select contentownerid, usercname, count (*) as countall from view_usercomment group by contentownerid, usercname order by contentownerid DESC
End
Else
Begin
Select contentownerid, usercname, count (*) as countall from view_usercomment where contentownerid = @ customerid group by contentownerid, usercname order by contentownerid DESC
End

Well, the level of my skills only stops here. It's enough for cainiao to have a pot of drinks. There's more to wait for us to discover, endless journey !!!!!!!!!!!