public void Save(SavedReport report, int userId)
{
bool transWasStarted = false;
OleDbConnection connection = new OleDbConnection(DatabaseData.Instance.AccessConnectionString);
using (connection)
{
connection.Open();
try
{
// START TRANS
OleDbCommand command = new OleDbCommand("BEGIN TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = true;
if (report.Id > 0)
command = new OleDbCommand(@"UPDATE CustomReports SET DisplayName=@DisplayName, ReportOptions=@ReportOptions,
UpdatedById=@UpdatedById, UpdatedAt=@UpdatedAt WHERE ID=@id", connection);
else
command = new OleDbCommand(@"INSERT INTO CustomReports (DisplayName, ReportOptions, UpdatedById,
UpdatedAt, CreatedById, CreatedAt) values (@DisplayName, @ReportOptions, @UpdatedById, @UpdatedAt, @CreatedById,
@CreatedAt)", connection);
command.Parameters.Add(new OleDbParameter("@DisplayName", report.DisplayName));
command.Parameters.Add(new OleDbParameter("@ReportOptions", report.Serialize()));
command.Parameters.Add(new OleDbParameter("@UpdatedById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@UpdatedAt", DateTime.Now));
if (report.Id > 0)
command.Parameters.Add(new OleDbParameter("@id", report.Id));
else
{
command.Parameters.Add(new OleDbParameter("@CreatedById", userId));
command.Parameters.Add(OleDbUtil.CreateDateTimeOleDbParameter("@CreatedAt", DateTime.Now));
}
command.ExecuteNonQuery();
if (report.Id <= 0)
{
command = new OleDbCommand(@"SELECT Max(ID) FROM CustomReports", connection);
report.Id = (int)command.ExecuteScalar();
}
// COMMIT TRANS
command = new OleDbCommand("COMMIT TRANSACTION", connection);
command.ExecuteNonQuery();
transWasStarted = false;
}
catch (Exception)
{
if (transWasStarted)
{
try
{
OleDbCommand cmd = new OleDbCommand("ROLLBACK TRANSACTION", connection);
cmd.ExecuteNonQuery();
}
catch { }
}
throw;
}
}
}
