//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);
}