How to excecute a stored procedure

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a SQL stored procedure that I need to run by pressing a button on a .Net form. I've created a SQLConnection, the form and a button, what code do I need to run the procedure.
 
Oldhandandy said:
I have a SQL stored procedure that I need to run by pressing a button
on a .Net form. I've created a SQLConnection, the form and a button,
what code do I need to run the procedure.
Click to expand...

Set the command text of a SqlCommand to the stored procedure call, use
parameters as normal, and set the command type to StoredProcedure.
 
Oldhandandy said:
The command now works. What coding do I need behind the button to run
the procedure.
Click to expand...

Just use the command exactly as you would any other - use
ExecuteNonQuery, or use it in a DataAdapter etc.
 
Hi there

I remember first getting to grips with ADO.NET after using ADO and found it a nightmare so I thought I'd give you a couple of examples to get you going:

// This example populates a dataset with the results of the stored procedure
// i.e. an SP that returns recordsets.

SqlCommand _sqlcom = new SqlCommand("uspOrderRetrieval");
_sqlcom.CommandType = CommandType.StoredProcedure;

SqlDataAdapter _sqladp = new SqlDataAdapter();

// The following code adds a parameter to be used by the stored procedure
// if you have no parameters then obviously ignore it.
SqlParameter _sqlpar = new SqlParameter("@OrderID", SqlDbType.Int);
_sqlpar.Value = orderid;
_sqlcom.Parameters.Add(_sqlpar);
// end of parameter addition

// Execute Stored Procedure
_sqlcon.Open();
_sqlcom.Connection = _sqlcon;
_sqladp.SelectCommand = _sqlcom;
_sqladp.Fill(_ds);

// The following example executes a nonquery stored procedure i.e. an SP
// that does some updates but does not return recordsets.

System.Data.SqlClient.SqlParameter _sqlpar;

SqlCommand _sqlcom = new SqlCommand("uspOrderPrinted");
_sqlcom.CommandType = CommandType.StoredProcedure;

// OrderID
_sqlpar = new SqlParameter("@OrderID", SqlDbType.Int);
_sqlpar.Value = orderid;
_sqlcom.Parameters.Add(_sqlpar);

// Execute Stored Procedure
_sqlcon.Open();
_sqlcom.Connection = _sqlcon;
_sqlcom.ExecuteNonQuery();

Please note I have not created the _sqlcon (SqlConnection) object in the examples and have not closed the connection afterwards etc etc.

Hope that helps

Andy
 
Back
Top