Live Chat Icon For mobile
Live Chat Icon

How do I call a SQL stored procedure?

Platform: WinForms| Category: Data Binding

You can call stored procedures in basically the same manner as executing other SQL commands.

When creating the SqlCommand, set the query string to be the name of the stored procedure, and then set the CommandType to be CommandType.StoredProcedure.

if(sqlConn.State == ConnectionState.Closed)sqlConn.Open();

SqlCommand cmd = new SqlCommand('sp_my_stored_procedure',sqlConn);
cmd.CommandTimeout = 180;
cmd.CommandType = CommandType.StoredProcedure;

After you setup the command type, you need to pass in any parameters required for the stored procedure. Here is an example of one input and one output parameter.

SqlParameter parm;

parm = cmd.Parameters.Add(new SqlParameter('@oid', SqlDbType.VarChar,50)); 
parm.Direction = ParameterDirection.Input; 
cmd.Parameters['@oid'].Value = OrderID;

parm = cmd.Parameters.Add(new SqlParameter('@custName', SqlDbType.VarChar,50)); 
parm.Direction = ParameterDirection.Output; 

If the stored procedure is returning a selection query at the end, you can fill your DataSet and retrieve any tables.

SqlDataAdapter tempDA = new SqlDataAdapter();
tempDA.TableMappings.Add('your mapping','your mapping');
tempDA.SelectCommand = cmd;
DataSet dataSet1 = new DataSet();
DataTable resultTable = dataSet1.Tables[0];

Or, if no tables are being returned, you can execute the command as a non-query


Share with

Related FAQs

Couldn't find the FAQs you're looking for?

Please submit your question and answer.