Example #1
0
        //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);
        }
Example #2
0
 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);
        }
Example #4
0
        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);
        }