Created
October 24, 2012 13:50
-
-
Save booyaa/3946157 to your computer and use it in GitHub Desktop.
How to call a stored proc with parameters in C#
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
//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