C # Call a stored procedure

1 1. Execute the stored procedure without the input parameter
 2              1 : First, write a stored procedure in the database, such as creating an adduser stored procedure.
 3 Create proc adduser
 4 @ ID Int ,
 5 @ Name varchar ( 20 ),
 6 @ Sex varchar ( 20 )
7 As
 8 Insert into users values (@ ID, @ name, @ sex)
 9  
 10              2 : Create a sqlcommand object and the initial sqlcommand object is as follows:
 11 Sqlcommand cmd = New Sqlcommand ();
 12 Cmd. commandtext = "  Adduser  " ;//  Determine which stored procedure to call  
  13 Cmd. commandtype = commandtype. storedprocedure; //  The SQL command type is stored procedure, and the default is SQL statement.  
  14 Cmd. Connection = con; //  Set connection  
  15   3 : Add stored procedure parameters to the sqlcommand object
 16 Sqlparameter Param = New Sqlparameter (); //  Define a parameter object  
  17 Param. parametername = "  @ ID  " ; //  Stored Procedure Parameter Name  
  18 Param. value = txtid. Text. Trim (); // Value of this parameter  
  19 Cmd. Parameters. Add (PARAM ); //  Sqlcommand object to add this parameter object  
  20 Param = New Sqlparameter ( "  @ Name  " , Txtname. Text. Trim ()); //  Abbreviations  
 21 Cmd. Parameters. Add (PARAM );
 22   4 : The sqlcommand object calls the function that executes the SQL statement. For example:
 23 Cmd. executenonquery ();
 24            
 25 2. Execute the stored procedure with the return parameter (output)
 26               1 : First, write a stored procedure in the database, such as creating a queryuser stored procedure.
 27 Create proc queryuser
 28 @ IDInt ,
 29 @ Suc varchar ( 10 ) Output
 30 As
 31                  Select @ Suc = '  False  ' 
 32                  If Exists (select * from users Where U_id = @ ID)
33                          Select @ Suc = '  Success  ' 
 34   2 : Create a sqlcommand object and the initial sqlcommand object is as follows:
 35 Sqlcommand cmd = New Sqlcommand ();
 36 Cmd. commandtext = "  Queryuser  " ;//  Determine which stored procedure to call  
  37 Cmd. commandtype = commandtype. storedprocedure; //  The SQL command type is stored procedure, and the default is SQL statement.  
  38 Cmd. Connection = con; //  Set connection  
  39   3 : Add stored procedure parameters to the sqlcommand object
 40 Sqlparameter param1 = New Sqlparameter ( "  @ ID  " , Txtid. Text ); //  Add input parameters  
  41 Cmd. Parameters. Add (param1 );
 42 Sqlparameter param2 = New Sqlparameter (); //  Add output parameters  
 43 Param2.parametername = "  @ Suc  " ; //  Name  
  44 Param2.sqldbtype = sqldbtype. varchar; //  SQL type of output parameters  
  45 Param2.size = 10 ;//  SQL type size of output parameters  
  46 Param2.direction = parameterdirection. output; //  Specify this parameter object as the output parameter type  
  47 Cmd. Parameters. Add (param2 );
 48   4 : The sqlcommand object calls the function that executes the SQL statement. For example:
 49 Cmd. executenonquery ();
 50 MessageBox. Show (param2.value. tostring ()); //  Output parameter value  
  51  
 52 Example of the stored procedure of input parameters:
 53      Try 
 54 {
 55 Sqlcommand cmd = New Sqlcommand ();
 56 Cmd. Connection = con;
 57 Cmd. commandtype = commandtype. storedprocedure;
 58 Cmd. commandtext = "  Adduser  " ;
 59      
 60 Sqlparameter Param = New Sqlparameter ();
 61 Param. parametername = "  @ ID  " ;
 62 Param. value = txtid. Text. Trim ();
 63 Cmd. Parameters. Add (PARAM );
 64 Param = New Sqlparameter ( "  @ Name  " , Txtname. Text. Trim ());
 65 Cmd. Parameters. Add (PARAM );
 66 Param = New Sqlparameter ();
 67 Param. parametername ="  @ Sex  " ;
 68 Param. value = txtsex. Text. Trim ();
 69 Cmd. Parameters. Add (PARAM );
 70   //  Da. insertcommand = cmd;  
  71      
 72       If (CMD. executenonquery () = 1 )
73 {
 74 MessageBox. Show ( "  Added  " );
 75 }
 76       Else 
 77 {
 78 MessageBox. Show ( "  Failed  " );
79 }
 80 }
 81      Catch (Sqlexception ex)
 82 {
 83 MessageBox. Show (ex. Message );
 84 }
 85  
 86 Example of the stored procedure for output parameters:
 87       Try 
88 {
 89 Sqlcommand cmd = New Sqlcommand ();
 90 Cmd. commandtext = "  Queryuser  " ;
 91 Cmd. commandtype = commandtype. storedprocedure;
 92 Cmd. Connection = con;
 93      
 94 Sqlparameter param1 =New Sqlparameter ( "  @ ID  " , Txtid. Text );
 95 Cmd. Parameters. Add (param1 );
 96 Sqlparameter param2 = New Sqlparameter ();
 97 Param2.parametername = "  @ Suc  " ;
 98 Param2.sqldbtype = sqldbtype. varchar;
 99 Param2.size = 10 ;
 100 Param2.direction = parameterdirection. output;
 101 Cmd. Parameters. Add (param2 );
 102      
 103 Cmd. executenonquery ();
 104  
 105 MessageBox. Show (param1.value. tostring ());
 106 MessageBox. Show (param2.value. tostring ());
 107 }
 108      Catch (Sqlexception ex)
 109 {
 110 MessageBox. Show (ex. Message );
 111 }