Triggering a stored procedure from C#

10 pts.
Tags:
C
Stored Procedures
Triggers
How to trigger a SP from C#?

Answer Wiki

Thanks. We'll let you know when a new response is added.

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:

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
}

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: cmd.ExecuteNonQuery();

Instead replace that with the following:

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.
}
}

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • dinuas400
    we cant run a trigger, when we perform a add/update/delete operation on a table the trigger will be executed(If trigger exists on that table)
    510 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following