C # Calling stored procedures

using System.Data.SqlClient;                    // If the stored procedure does not have input and output parameters and does not return query results       new SqlCommand ("Stored Procedure name", conn);    = CommandType.StoredProcedure;  Cmd. ExecuteNonQuery ();

                         //If the stored procedure has input parametersSqlCommand cmd=NewSqlCommand ("Stored Procedure name", conn); Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("stored procedure input parameter variable name", data type));//such as CMD. Parameters.Add (New SqlParameter ("@riqi", Sqldbtype.datetime, 8));//pass the specific value to the input parameterCmd. parameters["stored Procedure input parameters"]. Value =the specific value;//such as CMD. parameters["@riqi"]. Value = This.textBox1.Text;//Executing stored proceduresCmd. ExecuteNonQuery ();

                           //If the stored procedure has output parametersSqlCommand cmd=NewSqlCommand ("Stored Procedure name", conn); Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("stored procedure output parameter variable name", data type)); Cmd. parameters["stored procedure output parameter variable name"]. Direction =ParameterDirection.Output; Cmd. ExecuteNonQuery ();//displays the value of the output parameter, cmd. parameters["stored procedure output parameter variable name"]. Value//such as This.textBox3.Text = cmd. parameters["@ItemCount"]. Value.tostring ();

                          //If the stored procedure has output and output parametersSqlCommand cmd=NewSqlCommand ("Stored Procedure name", conn); Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("stored procedure input parameter variable name", data type)); Cmd. parameters["stored Procedure input parameters"]. Value =the specific value; Cmd. Parameters.Add (NewSqlParameter ("stored procedure output parameter variable name", data type)); Cmd. parameters["stored procedure output parameter variable name"]. Direction =ParameterDirection.Output; Cmd. ExecuteNonQuery ();//displays the value of the output parameter, cmd. parameters["stored procedure output parameter variable name"]. Value//such as This.textBox3.Text = cmd. parameters["@ItemCount"]. Value.tostring ();

call a stored procedure with input parameters "Example 2" to create a stored procedure addnewcategory implement add a new product category to the category table, and the new commodity category name CategoryName as input parameter. CREATE PROCEDURE addnewcategory (@categoryName nvarchar) asINSERT into Category (CategoryName) VALUES (@categoryName)

"Example" calls the stored procedure above addnewcategoryTry            {                //Database connection String                stringConnStr ="server=localhost;uid=sa;pwd=;d atabase=eshop"; //Create a Connection objectSqlConnection conn =NewSqlConnection (CONNSTR); //Open a database connectionConn.                Open (); SqlCommand cmd=NewSqlCommand ("addnewcategory", conn); Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("@categoryName", SqlDbType.NVarChar, -)); Cmd. parameters["@categoryName"]. Value = This. TextBox1.Text; Cmd.                ExecuteNonQuery (); MessageBox.Show ("Insert Successful"); }       Catch{MessageBox.Show ("Operation not successful"); return; }

                                   call a stored procedure with input and output parameters Example 1 creates a stored procedure Shoppingcartitemcount gets the number of species purchased in a shopping cart and outputs it as an output parameter, and the shopping cart number Cartid as the input parameter. CREATE Procedure [dbo]. [Shoppingcartitemcount] (   @CartID    nvarchar (),    int  OUTPUT) asselect  =  COUNT (ProductID)  from  shoppingcart    WHERE  = @CartID

"Example" calls the stored procedure above ShoppingcartitemcountTry            {                //Database connection String                stringConnStr ="server=localhost;uid=sa;pwd=;d atabase=eshop"; //Create a Connection objectSqlConnection conn =NewSqlConnection (CONNSTR); //Open a database connectionConn.                Open (); SqlCommand cmd=NewSqlCommand ("Shoppingcartitemcount", conn); Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("@CartID", SqlDbType.NVarChar, -)); Cmd. parameters["@CartID"]. Value = This. TextBox2.Text; Cmd. Parameters.Add (NewSqlParameter ("@ItemCount", SqlDbType.Int)); Cmd. parameters["@ItemCount"]. Direction =ParameterDirection.Output; Cmd.               ExecuteNonQuery (); //displays the value of the output parameter, cmd. parameters["stored procedure output parameter variable name"]. Value                  This. TextBox3.Text = cmd. parameters["@ItemCount"].                             Value.tostring (); }                 Catch{MessageBox.Show ("Operation not successful"); return; }

Practical Examples:

 stringConstr ="server=192.168.9.111;database=111;uid=sa;pwd=1111234"; using(SqlConnection conn=NewSqlConnection (CONSTR)) {Conn.                    Open (); using(SqlCommand cmd =NewSqlCommand ("P_pageselect", conn)) {Cmd.commandtype=CommandType.StoredProcedure; Cmd. Parameters.Add (NewSqlParameter ("@num", SqlDbType.Int)); Cmd. Parameters.Add (NewSqlParameter ("@tableName", SqlDbType.NVarChar, -)); Cmd. Parameters.Add (NewSqlParameter ("@startNum", SqlDbType.Int)); Cmd. parameters["@num"]. Value = This. Tbnum.text; Cmd. parameters["@tableName"]. Value = This. Cmbtablename.text; Cmd. parameters["@startNum"]. Value = This. Tbstartnum.text; SqlDataAdapter SDA=NewSqlDataAdapter (CMD); DataSet DS=NewDataSet (); Sda.                       Fill (DS); Dgvdata.datasource= ds. tables[0]; }                }

C # Calling stored procedures