Triggering a stored procedure from C#
how to tigering a sp from c#???
Software/Hardware used:
Software/Hardware used:
ASKED:
October 14, 2008 5:24 AM
UPDATED:
November 23, 2008 6:46 PM
Answer Wiki:
Assuming you mean that you want to call a stored procedure from c# (and that the stored procedure is written in T-SQL): One way is to do as follows:
<pre>
string _connString = //your <a href="http://www.connectionstrings.com/">connection string</a>
string _storedProcedure = //your stored procedure's name, ex. sp_MyProcedure
object parameterValue, parameterValue2 = //these need to be of the same type as your sp's
//parameter types
try
{
using (SqlConnection conn = new SqlConnection(_connString))
{
using (SqlCommand cmd = new SqlCommand(_storedProcedure, conn))
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
//Add all of your parameters with their values here
//Note that @MyParameter & @MyParameter2 must be the exact names used in the
//T-SQL stored procedure
cmd.Parameters.AddWithValue("@MyParameter", parameterValue);
cmd.Parameters.AddWithValue("@MyParameter2", parameterValue2);
conn.Open();
cmd.ExecuteNonQuery();
}
}
}
catch (SqlException sqle)
{
//Handle your sql exception here
}
</pre>
The above code would be used to execute a stored procedure if there were no result sets to be returned (like from a SELECT statement). If however you want to execute a stored procedure from c# that returns a result set, then it is handled slightly differently. Everything is the same as above with exception to the following line: <pre>cmd.ExecuteNonQuery();</pre>
Instead replace that with the following:
<pre>
using (SqlDataReader dataReader = cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection))
{
while (dataReader.Read())
{
//Now for each column of returned data, you want to extract that from the dataReader.
//For example, if you have a column named 'ID' that is of type int, then...
int id = (int)dataReader["ID">;
//And then say you also have a column named 'Notes" that is of type varchar(32), then...
string notes = dataReader["Notes">.ToString();
//Every itereation through this loop will give you a record from the recordset in the order
//it was returned from the stored procedure, so if there is more than one record, you
//may want to store them in a List<> or some form of Array.
}
}
</pre>
To see all answers submitted to the Answer Wiki: View Answer History.
RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
