/// <summary> /// Clear the AuditLog table. /// Implemented using a proc for raw speed. /// </summary> public static void ClearAuditLogTable() { string vendorImportProc = "pClearAuditLogTable"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (var cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } } }
private void ProjectCodeImportJson(int socrataDataMartRawJsonImportID) { Logger.Info($"Starting '{JobName}' ProjectCodeImportJson"); string vendorImportProc = "dbo.pProjectCodeImportJson"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (SqlCommand cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportID", socrataDataMartRawJsonImportID); cmd.ExecuteNonQuery(); } } Logger.Info($"Ending '{JobName}' ProjectCodeImportJson"); }
private void ClearGrantAllocationExpenditureTables(int bienniumFiscalYear) { Logger.Info($"Starting '{JobName}' ClearGrantAllocationExpenditureTables"); string vendorImportProc = "pClearGrantAllocationExpenditureTables"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (var cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@bienniumFiscalYear", bienniumFiscalYear); cmd.ExecuteNonQuery(); } } Logger.Info($"Ending '{JobName}' ClearGrantAllocationExpenditureTables"); }
/// <summary> /// Clears *ALL* entries in the table. /// </summary> public static void ClearSocrataDataMartRawJsonImportsTable() { ILog logger = LogManager.GetLogger(typeof(SocrataDataMartUpdateBackgroundJob)); logger.Info($"Starting pClearSocrataDataMartRawJsonImportsTable"); string vendorImportProc = "pClearSocrataDataMartRawJsonImportsTable"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (var cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); } } logger.Info($"Ending pClearSocrataDataMartRawJsonImportsTable"); }
// /// <summary> /// Clears only *OLD* entries in the Socrata imports table /// </summary> public static void ClearOutdatedSocrataDataMartRawJsonImportsTableEntries() { ILog logger = LogManager.GetLogger(typeof(SocrataDataMartUpdateBackgroundJob)); logger.Info($"Starting pClearOutdatedSocrataDataMartRawJsonImports({StaleEntriesDayCutoff} days)"); string vendorImportProc = "pClearOutdatedSocrataDataMartRawJsonImports"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (var cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@daysOldToRemove", StaleEntriesDayCutoff); cmd.ExecuteNonQuery(); } } logger.Info($"Ending pClearOutdatedSocrataDataMartRawJsonImports({StaleEntriesDayCutoff} days)"); }
public static int GetBienniumFiscalYearForDate(DateTime dateToEvaluate) { Logger.Info($"Starting GetBienniumFiscalYearForDate"); string fGetFiscalYearBienniumForDate = "dbo.fGetFiscalYearBienniumForDate"; int bienniumFiscalYear; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = $"Select {fGetFiscalYearBienniumForDate}('{dateToEvaluate}')"; cmd.Connection = sqlConnection; object executeScalarResult = cmd.ExecuteScalar(); bienniumFiscalYear = System.Convert.ToInt32(executeScalarResult); } Logger.Info($"Ending GetBienniumFiscalYearForDate"); return(bienniumFiscalYear); }
public void MarkJsonImportStatus(int socrataDataMartRawJsonImportID, JsonImportStatusType jsonImportStatusType) { // Because these objects are so huge, we try to avoid bringing them into memory directly, hence // the proc to keep it at arm's length. Logger.Info($"Starting '{JobName}' MarkJsonImportStatus"); string markJsonImportStatus = "dbo.pMarkJsonImportStatus"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { Logger.Info($"'{JobName}' MarkJsonImportStatus - Marking socrataDataMartRawJsonImportID {socrataDataMartRawJsonImportID} as JsonImportStatusType {jsonImportStatusType.JsonImportStatusTypeName}"); using (SqlCommand cmd = new SqlCommand(markJsonImportStatus, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportID", socrataDataMartRawJsonImportID); cmd.Parameters.AddWithValue("@JsonImportStatusTypeID", jsonImportStatusType.JsonImportStatusTypeID); cmd.ExecuteNonQuery(); } } Logger.Info($"Ending '{JobName}' MarkJsonImportStatus"); }
public SuccessfulJsonImportInfo LatestSuccessfulJsonImportInfoForBienniumAndImportTableType(int socrataDataMartRawJsonImportTableTypeID, int?optionalBienniumFiscalYear) { // Because these objects are so huge, we try to avoid bringing them into memory directly, hence // the proc to keep it at arm's length. Logger.Info($"Starting '{JobName}' LatestSuccessfulJsonImportInfoForBienniumAndImportTableType"); string vendorImportProc = "dbo.pLatestSuccessfulJsonImportInfoForBienniumAndImportTableType"; using (SqlConnection sqlConnection = SqlHelpers.CreateAndOpenSqlConnection()) { using (SqlCommand cmd = new SqlCommand(vendorImportProc, sqlConnection)) { cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@SocrataDataMartRawJsonImportTableTypeID", socrataDataMartRawJsonImportTableTypeID); cmd.Parameters.AddWithValue("@OptionalBienniumFiscalYear", optionalBienniumFiscalYear); using (SqlDataReader dataReader = cmd.ExecuteReader()) { SuccessfulJsonImportInfo importInfo = new SuccessfulJsonImportInfo(); bool queryReturnedData = dataReader.Read(); if (!queryReturnedData) { return(null); } importInfo.SocrataDataMartRawJsonImportTableTypeID = (int)dataReader["SocrataDataMartRawJsonImportTableTypeID"]; if (dataReader["BienniumFiscalYear"] != System.DBNull.Value) { importInfo.BienniumFiscalYear = (int?)dataReader["BienniumFiscalYear"]; } importInfo.JsonImportDate = (DateTime)dataReader["JsonImportDate"]; importInfo.FinanceApiLastLoadDate = (DateTime)dataReader["FinanceApiLastLoadDate"]; Logger.Info($"Ending '{JobName}' pLatestSuccessfulJsonImportInfoForBienniumAndImportTableType"); return(importInfo); } } } }