Skip to content

Instantly share code, notes, and snippets.

@booyaa
Created October 24, 2012 13:50
Show Gist options
  • Save booyaa/3946157 to your computer and use it in GitHub Desktop.
Save booyaa/3946157 to your computer and use it in GitHub Desktop.
How to call a stored proc with parameters in C#
//Useful for SSIS Script Tasks
SqlConnection conn;
SqlCommand comm;
String logMessage = "";
try
{
Dts.VariableDispenser.GetVariables(ref vars);
logMessage = String.Format("Hello world");
conn = (SqlConnection)Dts.Connections["ADONET.SystemLog"].AcquireConnection(null);
comm = new SqlCommand("[dbo].[usp_AppLogInsInfo]", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@ProcessID", vars["User::ProcessID"].Value.ToString());
comm.Parameters.AddWithValue("@Subsystem", vars["User::Subsystem"].Value.ToString());
comm.Parameters.AddWithValue("@LogMessage", logMessage);
comm.ExecuteNonQuery();
logMessage = "Truncating tables";
comm = new SqlCommand("[dbo].[usp_Logger]", conn);
comm.CommandType = CommandType.StoredProcedure;
comm.Parameters.AddWithValue("@ProcessID", vars["User::ProcessID"].Value.ToString());
comm.Parameters.AddWithValue("@Subsystem", vars["User::Subsystem"].Value.ToString());
comm.Parameters.AddWithValue("@LogMessage", logMessage);
comm.ExecuteNonQuery();
Dts.TaskResult = (int)ScriptResults.Success;
}
catch (Exception ex)
{
Dts.Events.FireError(0, "Script Task Example", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
Dts.TaskResult = (int)ScriptResults.Failure;
}
finally
{
vars.Unlock();
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment