public void StandardizeStagingAddresses(int fileID) { List<StagingAddress> standardizeCandidates = new List<StagingAddress>(); // Call SP that will get standardization candidates StoredProcedure proc = new StoredProcedure("[ETL].[spStagingAddressStandardizationCandidates]", this.ConnectionString); proc.Parameters.AddWithValue("@FileID", fileID); DataTable t = proc.ExecuteReader(); foreach (DataRow row in t.Rows) { standardizeCandidates.Add(new StagingAddress(row)); } // Standardize addresses here foreach (StagingAddress a in standardizeCandidates) { try { StagingAddress address = a.Clone(); this.StagingAddressStandardize(ref address); this.StagingAddressUpdateStandardized(ref address); } catch (NullReferenceException) { // don't do anything for these just yet } } }
public int PullHouseholdMarketDataForZip(string zipCode, int jobID) { StoredProcedure proc = new StoredProcedure(SQLResource.PullHouseholdMarketDataForZip, this.ConnectionString); proc.Parameters.AddWithValue("@ZipCode", zipCode); proc.Parameters.AddWithValue("@JobID", jobID); proc.Timeout = (60 * 120); // 120 minutes int affected = proc.ExecuteNonQuery(); return affected; }
public void AccountSave(ref Account account) { StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioAccount, this.ConnectionString); proc.Parameters.AddWithValue("@AccountSID", account.Sid); proc.Parameters.AddWithValue("@FriendlyName", account.FriendlyName); proc.Parameters.AddWithValue("@DateCreated", account.DateCreated); proc.Parameters.AddWithValue("@DateUpdate", account.DateUpdated); proc.ExecuteNonQuery(); }
public List<string> GetServiceAreaZipCodes() { var result = new List<string>(); StoredProcedure proc = new StoredProcedure(SQLResource.GetServiceAreas, this.ConnectionString); DataTable t = proc.ExecuteReader(); foreach (DataRow row in t.Rows) { result.Add(row.Field<string>(0)); } return result; }
public void IncomingPhoneNumberSave(ref IncomingPhoneNumber incomingPhone) { StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioPhoneNumber, this.ConnectionString); proc.Parameters.AddWithValue("@PhoneNumberSID", incomingPhone.Sid); proc.Parameters.AddWithValue("@AccountSID", incomingPhone.AccountSid); proc.Parameters.AddWithValue("@FriendlyName", incomingPhone.FriendlyName); proc.Parameters.AddWithValue("@PhoneNumber", incomingPhone.PhoneNumber); proc.Parameters.AddWithValue("@DateCreated", incomingPhone.DateCreated); proc.Parameters.AddWithValue("@DateUpdated", incomingPhone.DateUpdated); proc.ExecuteNonQuery(); }
public int GetHealthcareSystemId(string ClientPrefix) { StoredProcedure proc = new StoredProcedure("GetHealthcareSystemIDByName", this.ConnectionString); proc.Parameters.AddWithValue("@ClientName", ClientPrefix); DataTable t = proc.ExecuteReader(); if (t.Rows.Count > 0) { return t.Rows[0].Field<int>("HealthSystemID"); } // default to demo database if we cannot find correct value return 999; }
public void CallSave(ref Call phoneCall) { StoredProcedure proc = new StoredProcedure(SQLResource.ProcessTwilioCall, this.ConnectionString); proc.Parameters.AddWithValue("@CallSID", phoneCall.Sid); proc.Parameters.AddWithValue("@ParentCallSID", phoneCall.ParentCallSid ?? string.Empty); proc.Parameters.AddWithValue("@PhoneNumberSID", phoneCall.PhoneNumberSid); proc.Parameters.AddWithValue("@To", phoneCall.To); proc.Parameters.AddWithValue("@From", phoneCall.From); proc.Parameters.AddWithValue("@Status", phoneCall.Status); proc.Parameters.AddWithValue("@StartTime", phoneCall.StartTime); proc.Parameters.AddWithValue("@EndTime", phoneCall.EndTime); proc.Parameters.AddWithValue("@Duration", phoneCall.Duration); proc.Parameters.AddWithValue("@Direction", phoneCall.Direction); proc.Parameters.AddWithValue("@DateCreated", phoneCall.DateCreated); proc.Parameters.AddWithValue("@DateUpdated", phoneCall.DateUpdated); proc.ExecuteNonQuery(); }
/// <summary> /// Calls the Stored Procedures needed to copy data from staging tables into production /// </summary> /// <param name="fileID"></param> public void LoadDataIntoProduction(int fileID) { try { using (TransactionScope scope = new TransactionScope()) { StoredProcedure proc = new StoredProcedure("[ETL].[spEncounterFileLoadIntoProduction]", this.ConnectionString); proc.Parameters.AddWithValue("@FileID", fileID); proc.Timeout = (60 * 120); // wait 120 minutes before timing out proc.ExecuteNonQuery(); scope.Complete(); } } catch (SqlException ex) { log.LogSqlException(ex); throw; } }
public ClientOrganization ClientOrganizationLoadByID(int clientOrganizationID) { StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelect]",this.ConnectionString); proc.Parameters.AddWithValue("@ClientOrganizationID", clientOrganizationID); DataTable t = proc.ExecuteReader(); if (t.Rows.Count > 0) { ClientOrganization result = new ClientOrganization(t.Rows[0]); return result; } return null; }
public void ValidateImportedFile(int fileID, int totalRows, bool hasErrors, string errors) { StoredProcedure proc = new StoredProcedure(SQLResource.ValidateImportedFile, this.ConnectionString); proc.Parameters.AddWithValue("@ImportedFileStatusID", fileID); proc.Parameters.AddWithValue("@TotalRows", totalRows); proc.Parameters.AddWithValue("@HasErrors", hasErrors); proc.Parameters.AddWithValue("@Errors", errors); proc.ExecuteNonQuery(); }
public void AddressFind(ref Address address) { if (address.AddressID > 0) { return; } byte[] hash = address.ComputedHash(); if (hash != null) { StoredProcedure proc = new StoredProcedure(SQLResource.FindAddress, ConnectionString); SqlParameter param = new SqlParameter() { ParameterName = "@AddressHash", SqlDbType = SqlDbType.VarBinary, Value = hash }; proc.Parameters.Add(param); DataTable t = proc.ExecuteReader(); if (t.Rows.Count > 0) { address.AddressID = t.Rows[0].Field<int>("AddressID"); } } }
public void TruncateCommunityStagingTables() { StoredProcedure proc = new StoredProcedure(SQLResource.ClearNHStagingTables, this.ConnectionString); proc.ExecuteNonQuery(); }
public ImportedFileStatus ImportedFileStatusLoadByID(int importedFileStatusID) { ImportedFileStatus result = null; log.DebugFormat("Loading ImportedFileStatusID: {0}", importedFileStatusID); StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelect, ConnectionString); proc.Parameters.AddWithValue("@ImportedFileStatusID", importedFileStatusID); DataTable table = proc.ExecuteReader(); result = new ImportedFileStatus(table.Rows[0]); return result; }
public List<ClientOrganization> ClientOrganizationsList() { List<ClientOrganization> results = new List<ClientOrganization>(); StoredProcedure proc = new StoredProcedure("[App].[spClientOrganizationSelectAll]", this.ConnectionString); DataTable t = proc.ExecuteReader(); foreach (DataRow row in t.Rows) { results.Add(new ClientOrganization(row)); } return results; }
public List<ImportedFileStatus> ImportedFileStatusLoadByTypeStatusFileType(ProcessType processType, ProcessStatus processStatus, ProcessFileType processFileType) { List<ImportedFileStatus> result = new List<ImportedFileStatus>(); StoredProcedure proc = new StoredProcedure(SQLResource.ImportedFileStatusSelectByTypeStatusFileType, ConnectionString); proc.Parameters.AddWithValue("@ProcessTypeID", processType); proc.Parameters.AddWithValue("@ProcessStatusID", processStatus); proc.Parameters.AddWithValue("@ProcessFileTypeID", processFileType); DataTable table = proc.ExecuteReader(); foreach (DataRow row in table.Rows) { ImportedFileStatus file = new ImportedFileStatus(row); result.Add(file); } return result; }
public HealthSystemSettings HealthSystemSettingsLoadByID(int healthSystemID) { HealthSystemSettings result = null; StoredProcedure proc = new StoredProcedure(SQLResource.HealthSystemSettingsLoadByID, this.ConnectionString); proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID); DataTable t = proc.ExecuteReader(); if (t.Rows.Count == 0) { throw new DataException(String.Format("Unable to find settings for HealthSystemID {0}", healthSystemID)); } result = new HealthSystemSettings(t.Rows[0]); return result; }
public void PopulateExplorerSummary() { StoredProcedure proc = new StoredProcedure(SQLResource.PopulateExplorerSummary, this.ConnectionString); proc.ExecuteNonQuery(); }
public HealthSystem HealthSystemLoadByID(int healthSystemID) { HealthSystem result = null; StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemInformation, this.ConnectionString); proc.Parameters.AddWithValue("@HealthSystemID", healthSystemID); DataTable t = proc.ExecuteReader(); if (t.Rows.Count == 0) { throw new DataException(String.Format("Could not Get Health System Information for ID {0}", healthSystemID)); } result = new HealthSystem(t.Rows[0]); return result; }
public void ProcessROI() { StoredProcedure proc = new StoredProcedure(SQLResource.ProcessROI, this.ConnectionString); proc.ExecuteNonQuery(); }
public DataTable ProcedureCodesSelectAll() { StoredProcedure proc = new StoredProcedure(SQLResource.GetProcedureCodes, this.ConnectionString); return proc.ExecuteReader(); }
/// <summary> /// Calls the Stored Procedures needed to perform Patient Matching and Income Lookups /// </summary> /// <param name="fileID"></param> public void PerformPostProcessing(int fileID) { try { using (TransactionScope scope = new TransactionScope()) { StoredProcedure proc = new StoredProcedure("[ETL].[spEncounterFilePostProcessing]", this.ConnectionString) { Timeout = (60 * 30) /* wait 30 minutes before timing out*/ }; proc.Parameters.AddWithValue("@FileID", fileID); proc.ExecuteNonQuery(); scope.Complete(); } } catch (SqlException ex) { log.LogSqlException(ex); throw; } }
public List<HealthSystem> HealthSystemList() { List<HealthSystem> systems = new List<HealthSystem>(); StoredProcedure proc = new StoredProcedure(SQLResource.GetHealthSystemList, ConnectionString); DataTable t = proc.ExecuteReader(); if (t.Rows.Count == 0) { throw new DataException("Could not Get Health System List"); } foreach (DataRow row in t.Rows) { HealthSystem h = new HealthSystem(row); systems.Add(h); } return systems; }
// protected methods... protected int ExecuteStoredProcedure(string procedureName, Dictionary<string, object> parameters = null) { StoredProcedure proc = new StoredProcedure(procedureName, SystemDataProvider.ClientDataProvider.ConnectionString); if (parameters != null) { foreach (KeyValuePair<string, object> entry in parameters) { proc.Parameters.AddWithValue(entry.Key, entry.Value); } } proc.Timeout = 90; return proc.ExecuteNonQuery(); }