From: http://www.cnblogs.com/stg609/archive/2008/09/25/1298967.html
Today, another friend asked me about the C # Stored Procedure Call and found that it was useless for a long time. I forgot about it. Fortunately, I have written the results in other blogs before... Naturally, it is solved.
I wanted to take the previous one directly, but I felt very impressed by writing it again.
Debugging environment: vs. NET 2005, SQL 2000, and Windows XP SP2.
Language: C #
Call the band-to-band InterfaceInboundParameter Stored Procedure
The first step is to create a stored procedure in the query analyzer ~~ As follows:
Create Proc Proc_1
@ Uid Int ,
@ Pwd Varchar ( 255 )
As
Select Username From Users Where UID = @ Uid And Password = @ Pwd
Go
Next, let's take a look at how to use C # To call this stored procedure in VS 2005.
Method 1:
Sqlconnection Conn = New Sqlconnection ( " Server = (local); database = sql1; uid = sa; Pwd = " );
Conn. open (); // Open Database Connection
Sqlcommand cmd = New Sqlcommand ( " Exec proc_1 @ uid = 1, @ Pwd = Admin " , Conn ); // Write the statement that calls the stored procedure in command
// As you can see, the execution in the query analyzer is very similar. "Exec stored procedure name @ parameter 1 = parameter value, @ parameter 2 = parameter value"
Sqldatareader SDR = Cmd. executereader (); // Execute the Stored Procedure
While (SDR. Read ())
{
Response. Write (SDR [ " Username " ]. Tostring ()); // Output The queried Value
}
SDR. Close ();
Conn. Close ();
Method 2:
Sqlconnection Conn = New Sqlconnection ( " Server = (local); database = sql1; uid = sa; Pwd = " );
Conn. open (); // Open Database Connection
Sqlcommand cmd = New Sqlcommand ( " Proc_1 " , Conn ); // Stored Procedure name
Cmd. commandtype = Commandtype. storedprocedure; // Set the command type to Stored Procedure
Sqlparameter [] Param = New Sqlparameter [] {
New Sqlparameter ( " UID " , Sqldbtype. Int, 4 ),
New Sqlparameter ( " PWD " , Sqldbtype. varchar, 255 ),
}; // Define parameters. These are the parameters to be called in the stored procedure. You can also directly add parameters using the cmd. addwithvalue method.
Param [ 0 ]. Value = 1 ; // Assign Parameters
Param [ 1 ]. Value = " Admin " ;
Cmd. Parameters. addrange (PARAM ); // Remember to add the defined parameter to the CMD parameter. Otherwise, the previous parameter will be white.
String Sname = ( String ) Cmd. executescalar (); // If the stored procedure returns a single value, we can directly retrieve the value.
Response. Write (sname );
SDR. Close ();
Conn. Close ();
Call the band-to-band InterfaceOutputParameter Stored Procedure
The above is just a stored procedure for calling input parameters. The following describes how to call a stored procedure with output parameters.
First, modify the stored procedure as follows:
Create Proc Proc_1
@ Uid Int ,
@ Pwd Varchar ( 255 ),
@ Username Varchar ( 255 ) Output -- Here we add an output variable. Remember to add the output variable.
As
Select @ Username = Username From Users Where UID = @ Uid And Password = @ Pwd
Go
-- The following method is called in the query Analyzer:
Declare @ N Varchar ( 255 ) -- Declare a variable to pass Parameters
Exec Proc_1 1 , Admin, @ N Output -- Note: To mark it as an output variable
Print @ N
Next we will look at the call method in vs. Net:
Sqlconnection Conn = New Sqlconnection ( " Server = (local); database = sql1; uid = sa; Pwd = " );
Conn. open ();
Sqlcommand cmd = New Sqlcommand ( " Proc_1 " , Conn );
Cmd. commandtype = Commandtype. storedprocedure;
Cmd. Parameters. addwithvalue ( " UID " , 1 );
Cmd. Parameters. addwithvalue ( " PWD " , " Admin " );
Cmd. Parameters. Add ( " Username " , Sqldbtype. varchar, 255 );
Cmd. Parameters [ " Username " ]. Direction = Parameterdirection. output; // Set parameters as output parameters
Cmd. executenonquery ();
String Sname = ( String ) Cmd. Parameters [ " Username " ]. Value; // Get the output parameter value
Response. Write (sname );
Conn. Close ();
In this way, you can easily call the Stored Procedure method.
