//todo: transaction handling needs better error detection and recovery // this is primarily for bulk inserts/updates public static SQLTransactionData StartTransaction(string StoredProcedureName) { SQLTransactionData TransData = new SQLTransactionData(); TransData.SQLConn = new SqlConnection(SQLConnHandler.ConnectionString); TransData.SQLConn.Open(); TransData.Transaction = TransData.SQLConn.BeginTransaction(); TransData.Command = new SqlCommand(StoredProcedureName, TransData.SQLConn); TransData.Command.Transaction = TransData.Transaction; TransData.Command.CommandType = CommandType.StoredProcedure; return(TransData); }
public static void EndTransaction(SQLTransactionData TransData) { try { TransData.Transaction.Commit(); } catch (Exception) { TransData.Transaction.Rollback(); throw; } finally { TransData.SQLConn.Close(); TransData.SQLConn.Dispose(); } }
public void InsertWFInstance(SortedDictionary <int, ValkWFStep> ToInsert) { SQLTransactionData TransData = SQLConnHandler.StartTransaction("InsertWFInstance"); foreach (KeyValuePair <int, ValkWFStep> Step in ToInsert) { SQLConnHandler.ExecuteNonQuerySPTrans(TransData, new SQLParameter() { ParamName = "@InstanceKey", ParamValue = Step.Value.InstanceKey }, new SQLParameter() { ParamName = "@WFTemplateID", ParamValue = Step.Value.WFTemplateID }, new SQLParameter() { ParamName = "@Status", ParamValue = "inactive" }, new SQLParameter() { ParamName = "@WFTemplateStepID", ParamValue = Step.Value.WFTemplateStepID }, new SQLParameter() { ParamName = "@UserID", ParamValue = 1 }, new SQLParameter() { ParamName = "@StartTime", ParamValue = SqlDateTime.MinValue }, new SQLParameter() { ParamName = "@SyncCount", ParamValue = Step.Value.SyncCount }, new SQLParameter() { ParamName = "@ExceptionID", ParamValue = -1 } ); } SQLConnHandler.EndTransaction(TransData); }
public static int ExecuteNonQuerySPTrans(SQLTransactionData TransData, params SQLParameter[] Params) { int Rows = -1; try { TransData.Command.Parameters.Clear(); for (int i = 0; i < Params.Length; i++) { TransData.Command.Parameters.Add(new SqlParameter(Params[i].ParamName, Params[i].ParamValue)); } Rows = TransData.Command.ExecuteNonQuery(); } catch (Exception Ex) { Rows = -1; } finally { } return(Rows); }
public void UpdateSteps(List <ValkQueueWFMessage> Updates) { //insert all the pending updates SQLTransactionData TransData = SQLConnHandler.StartTransaction("InsertUpdates"); foreach (ValkQueueWFMessage Step in Updates) { SQLConnHandler.ExecuteNonQuerySPTrans(TransData, new SQLParameter() { ParamName = "@InstanceKey", ParamValue = Step.InstanceKey }, new SQLParameter() { ParamName = "@WFTemplateID", ParamValue = Step.Step.WFTemplateID }, new SQLParameter() { ParamName = "@NewStatus", ParamValue = Step.Step.Status }, new SQLParameter() { ParamName = "@WFTemplateStepID", ParamValue = Step.Step.WFTemplateStepID } ); } //temporarily replace command so we can run a different one //run updates in SQL SqlCommand command = new SqlCommand("RunUpdates", TransData.SQLConn); command.Transaction = TransData.Transaction; TransData.Command = command; SQLConnHandler.ExecuteNonQuerySPTrans(TransData); SQLConnHandler.EndTransaction(TransData); }