C # Calling stored procedures

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.