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

SqlCommand cmd = new SqlCommand ("Stored Procedure name", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. ExecuteNonQuery ();

If the stored procedure has input parameters

SqlCommand cmd = new SqlCommand ("Stored Procedure name", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.Add (New SqlParameter ("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 parameter
Cmd. parameters["Stored procedure input parameters"]. value = specific values;
such as CMD. parameters["@riqi"]. Value = This.textBox1.Text;
Executing stored procedures
Cmd. ExecuteNonQuery ();

If the stored procedure has output parameters

SqlCommand cmd = new SqlCommand ("Stored Procedure name", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.Add (New SqlParameter ("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 parameters

SqlCommand cmd = new SqlCommand ("Stored Procedure name", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.Add (New SqlParameter ("Stored procedure input parameter variable name", data type));
Cmd. parameters["Stored procedure input parameters"]. value = specific values;
Cmd. Parameters.Add (New SqlParameter ("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 ();

Calling a stored procedure with input parameters

Example 2 creates a stored procedure addnewcategory a new product category is added to the category table, and the new commodity classification name CategoryName as input parameter.
CREATE PROCEDURE addnewcategory
  (@categoryName nvarchar ())
as
 insert into Category ( CategoryName)  VALUES  (@categoryName)

Example calls the stored procedure above addnewcategory
      try
             {
                //Database connection string
                 string connstr = "server=localhost;uid=sa;pwd=;d atabase=eshop";
               //Create Connection object
                SqlConnection conn = new SqlConnection (CONNSTR);
               //Open a database connection
                Conn. Open ();

                SqlCommand cmd = new SqlCommand ("Addnewcategory", conn);
                cmd. CommandType = CommandType.StoredProcedure;
                cmd. Parameters.Add (New SqlParameter ("@categoryName", sqldbtype.nvarchar,50));
                cmd. parameters["@categoryName"]. Value = This.textBox1.Text;
                cmd. ExecuteNonQuery ();
                MessageBox.Show ( "Insert Success");
           }

Catch
{
MessageBox.Show ("Operation not successful");
Return
}

Calling 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 an input parameter.
CREATE Procedure [dbo]. [Shoppingcartitemcount]
(   @CartID     nvarchar (),
    @ItemCount int OUTPUT
)
As
select  @ItemCount = COUNT (ProductID)   from  shoppingcart    
where  Cartid = @CartID

Example calls the stored procedure above Shoppingcartitemcount
          try
             {
                //Database connection string
                 string connstr = "server=localhost;uid=sa;pwd=;d atabase=eshop";
               //Create Connection object
                SqlConnection conn = new SqlConnection (CONNSTR);
               //Open a database connection
                Conn. Open ();

SqlCommand cmd = new SqlCommand ("Shoppingcartitemcount", conn);
Cmd.commandtype = CommandType.StoredProcedure;
Cmd. Parameters.Add (New SqlParameter ("@CartID", sqldbtype.nvarchar,50));
Cmd. parameters["@CartID"]. Value = This.textBox2.Text;
Cmd. Parameters.Add (New SqlParameter ("@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
}

C # Calling stored procedures