//internal DataTable ExportActiveRecords(string SrcRecordId, string ImportProcess, string Tag)
//{
// try
// {
// StoredProcedureEntity sproc = new StoredProcedureEntity();
// sproc.StoredProcedureName = "oi.ExportActiveRecords";
// sproc.StoredProceduresParameter.Add(GetParam("@SrcRecordId", string.IsNullOrEmpty(SrcRecordId) ? null : SrcRecordId.ToString(), SQLServerDatatype.VarcharDataType));
// sproc.StoredProceduresParameter.Add(GetParam("@ImportProcess", !string.IsNullOrEmpty(ImportProcess) ? ImportProcess : null, SQLServerDatatype.VarcharDataType));
// sproc.StoredProceduresParameter.Add(GetParam("@Tag", !string.IsNullOrEmpty(Tag) ? Tag : null, SQLServerDatatype.VarcharDataType));
// DataTable dt;
// dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc);
// return dt;
// }
// catch (Exception Ex)
// {
// throw;
// }
//}
internal SqlDataReader ExportActiveDataQueue(int UserId, string SrcRecordId, string LOBTag, string Tag, string ImportProcess)
{
SqlDataReader reader;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "oi.ExportActiveDataQueue";
sproc.StoredProceduresParameter.Add(GetParam("@UserId", UserId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SrcRecordId", string.IsNullOrEmpty(SrcRecordId) ? null : SrcRecordId.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@LOBTag", !string.IsNullOrEmpty(LOBTag) ? LOBTag : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tag", !string.IsNullOrEmpty(Tag) ? Tag : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ImportProcess", !string.IsNullOrEmpty(ImportProcess) ? ImportProcess : null, SQLServerDatatype.VarcharDataType));
reader = sql.ExecuteDataReader(CommandType.StoredProcedure, sproc);
return(reader);
}
catch (Exception)
{
throw;
}
}
internal List <ImportJobQueueEntity> GetImportJobSettings()
{
List <ImportJobQueueEntity> results = new List <ImportJobQueueEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "mapp.GetImportJobSettings";
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc);
if (dt != null && dt.Rows.Count > 0)
{
results = new ImportJobQueueAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal List <MultiPassGroupConfiguration> GetVerificationGroupLookupList(int providerCode, string tag)
{
List <MultiPassGroupConfiguration> lstGroups = new List <MultiPassGroupConfiguration>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "mpm.GetLookupsByVerificationGroup";
sproc.StoredProceduresParameter.Add(GetParam("@ProviderCode", Convert.ToString(providerCode), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tag", string.IsNullOrEmpty(tag) ? null : tag, SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "");
lstGroups = CommonConvertMethods.ConvertDataTable <MultiPassGroupConfiguration>(dt);
}
catch (Exception)
{
//Put log to db here
throw;
}
return(lstGroups);
}
internal List <OIExportJobSettingsEntity> GetExportJobSettings()
{
List <OIExportJobSettingsEntity> results = new List <OIExportJobSettingsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "capp.GetExportJobSettings"; // MP-846 Admin database cleanup and code cleanup.
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc);
if (dt != null && dt.Rows.Count > 0)
{
results = new OIExportJobSettingsAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal List <ThirdPartyEnrichmentsEntity> GetThirdPartyEnrichments()
{
List <ThirdPartyEnrichmentsEntity> results = new List <ThirdPartyEnrichmentsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetThirdPartyEnrichments";
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "");
if (dt != null && dt.Rows.Count > 0)
{
results = CommonConvertMethods.ConvertDataTable <ThirdPartyEnrichmentsEntity>(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal string ModifyRule(int providerCode, string tag, string verificationGroups, string precedenceSteps, bool delete)
{
string message = string.Empty;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "mpm.ModifyRule";
sproc.StoredProceduresParameter.Add(GetParam("@ProviderCode", Convert.ToString(providerCode), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tag", tag, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@VerificationGroups", string.IsNullOrEmpty(verificationGroups) ? "" : verificationGroups, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PrecedenceSteps", string.IsNullOrEmpty(precedenceSteps) ? "" : precedenceSteps, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Delete", delete.ToString(), SQLServerDatatype.BitDataType));
sql.ExecuteNoReturn(CommandType.StoredProcedure, sproc);
}
catch (Exception ex)
{
message = ex.Message;
}
return(message);
}
internal List <NotificationProfileEntity> GetAllNotificationProfileNames()
{
List <NotificationProfileEntity> results = new List <NotificationProfileEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetNotificationProfileNames";
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new NotificationProfileAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal string InsertiResearchInvestigationFailedCalls(iResearchEntity objViewEntity)
{
string result = string.Empty;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "[dnb].[InsertiResearchInvestigationFailedCalls]";
sproc.StoredProceduresParameter.Add(GetParam("@UserId", objViewEntity.UserId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@RequestBody", !string.IsNullOrEmpty(objViewEntity.RequestBody) ? objViewEntity.RequestBody : "", SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ResponseJSON", !string.IsNullOrEmpty(objViewEntity.RequestResponseJSON) ? objViewEntity.RequestResponseJSON : "", SQLServerDatatype.VarcharDataType));
result = Convert.ToString(sql.ExecuteScalar(CommandType.StoredProcedure, sproc));
}
catch (Exception)
{
return(result);
}
return(result);
}
internal void UpdateMonitoringExportJobSettings(MonitoringNotificationJobSettingsEntity obj, bool IsPorcessStart, bool IsRevert, int RetryCount, string ErrorMessage)
{
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "mapp.UpdateExportJobSettings";
sproc.StoredProceduresParameter.Add(GetParam("@Id", obj.Id.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@IsProcessComplete", obj.IsProcessComplete.ToString(), SQLServerDatatype.BitDataType));
sproc.StoredProceduresParameter.Add(GetParam("@FilePath", obj.FilePath == null ? null : obj.FilePath.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@IsPorcessStart", IsPorcessStart.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@IsRevert", IsRevert.ToString(), SQLServerDatatype.BitDataType));
sproc.StoredProceduresParameter.Add(GetParam("@RetryCount ", RetryCount.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ErrorMessage", !string.IsNullOrEmpty(Convert.ToString(ErrorMessage)) ? ErrorMessage.ToString() : null, SQLServerDatatype.VarcharDataType));
sql.ExecuteNoReturn(CommandType.StoredProcedure, sproc);
//result = Convert.ToInt32(sql.ExecuteScalar(CommandType.StoredProcedure, sproc));
}
catch (Exception)
{
throw;
}
}
internal string GetNewSearch(int InputId, string MatchURL, string ResponseJSON)
{
string Message = string.Empty;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "oi.StewNewSearch";
StoredProceduresParameterEntity param = new StoredProceduresParameterEntity();
sproc.StoredProceduresParameter.Add(GetParam("@InputId", InputId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@MatchURL", MatchURL.ToString(), SQLServerDatatype.NvarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ResponseJSON", ResponseJSON.ToString(), SQLServerDatatype.NvarcharDataType));
sql.ExecuteNoReturn(CommandType.StoredProcedure, sproc);
return(Message);
}
catch (Exception ex)
{
return(ex.Message);
//Put log to db here
}
}
internal List <DnbAPIEntity> GetDnBAPIDetailList()
{
List <DnbAPIEntity> results = new List <DnbAPIEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetDnBAPIDetailList";
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new DnbAPIAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal int InsertExternalDataStore(DataSourceConfigurationEntity obj)
{
int result = 0;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.InsertExternalDataStore";
sproc.StoredProceduresParameter.Add(GetParam("@id", obj.Id == null ? "0" : Convert.ToString(obj.Id), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@externalDataStoreName", !string.IsNullOrEmpty(obj.ExternalDataStoreName) ? obj.ExternalDataStoreName : "", SQLServerDatatype.NvarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@externalDataStoreTypeId", obj.externalDataStoreType.Id == null ? "0" : Convert.ToString(obj.externalDataStoreType.Id), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@dataStoreConfiguration", !string.IsNullOrEmpty(obj.DataStoreConfiguration) ? obj.DataStoreConfiguration : null, SQLServerDatatype.NvarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@userId", obj.UserId == null ? "0" : Convert.ToString(obj.UserId), SQLServerDatatype.IntDataType));
result = Convert.ToInt32(sql.ExecuteScalar(CommandType.StoredProcedure, sproc));
}
catch (Exception ex)
{
throw ex;
}
return(result);
}
internal string OIAddRecordAsNewCompany(string MatchURL, string ResponseJSON, string OrbNumber, string SrcRecordId, string Tags, int UserId)
{
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "oi.AddRecordAsNewCompany";
StoredProceduresParameterEntity param = new StoredProceduresParameterEntity();
sproc.StoredProceduresParameter.Add(GetParam("@MatchURL", MatchURL.ToString(), SQLServerDatatype.NvarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ResponseJSON", ResponseJSON.ToString(), SQLServerDatatype.NvarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@OrbNumber", OrbNumber.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SrcRecordId", SrcRecordId.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tags", Tags.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@UserId", UserId.ToString(), SQLServerDatatype.IntDataType));
sql.ExecuteScalar(CommandType.StoredProcedure, sproc);
return(string.Empty);
}
catch (SqlException)
{
throw;
}
}
internal List <DataBlocksEntity> GetAllDataBlocks()
{
List <DataBlocksEntity> results = new List <DataBlocksEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetAllDataBlocks";
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "");
if (dt != null && dt.Rows.Count > 0)
{
results = new DataBlocksAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal List <ImportFileTemplates> GetAllImportFileTemplates()
{
List <ImportFileTemplates> results = new List <ImportFileTemplates>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.GetAllImportFileTemplates";
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new ImportJobDataAdapter().AdaptTemplate(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal string UpsertDataBlockGroups(DataBlockGroupsEntity obj)
{
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.UpsertDataBlockGroups";
sproc.StoredProceduresParameter.Add(GetParam("@DataBlockGroupId", obj.DataBlockGroupId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@DataBlockGroupName", obj.DataBlockGroupName, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@DataBlocksIds", obj.DataBlocksIds, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@DataBlocks", obj.DataBlocks, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@APIURL", obj.APIURL, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@TradeUp", string.IsNullOrEmpty(obj.TradeUp) ? null : obj.TradeUp, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@CustomerReference", string.IsNullOrEmpty(obj.CustomerReference) ? "" : obj.CustomerReference, SQLServerDatatype.VarcharDataType));
sql.ExecuteNoReturn(CommandType.StoredProcedure, sproc);
return(string.Empty);
}
catch (Exception)
{
throw;
}
}
internal List <DashboardBackgroundProcessStatsEntity> DashboardV2GetBackgroundProcessStats()
{
List <DashboardBackgroundProcessStatsEntity> result = new List <DashboardBackgroundProcessStatsEntity>();
try
{
DataTable dt = new DataTable();
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.DashboardV2GetBackgroundProcessStats";
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc);
if (dt != null && dt.Rows.Count > 0)
{
result = new DashboardBackgroundProcessStatsAdapter().Adapt(dt);
}
return(result);
}
catch (Exception ex)
{
throw ex;
}
}
internal List <DashboardImportProcessDataQueueStatisticsEntity> DashboardV2GetDataQueueStatisticsByTag()
{
List <DashboardImportProcessDataQueueStatisticsEntity> dashboardDatadt = new List <DashboardImportProcessDataQueueStatisticsEntity>();
try
{
DataTable dt = new DataTable();
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.DashboardV2GetDataQueueStatisticsByTags";
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.ReadWrite.ToString());
if (dt != null && dt.Rows.Count > 0)
{
dashboardDatadt = new DashboardImportProcessDataQueueStatisticsAdapter().Adapt(dt);
}
return(dashboardDatadt);
}
catch (Exception ex)
{
throw ex;
}
}
internal void UpdateClient(int ClientId, string ClientName, string PrimaryClientDUNSNumber, string PrimaryContactName, string PrimaryEamilAddress, string PrimaryContactPhone, string SecondaryContactName, string SecondaryEamilAddress, string SecondaryContactPhone, int UserId, string Notes, bool Active, string ClientLogo)
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "mapp.UpdateClient";
sproc.StoredProceduresParameter.Add(GetParam("@ClientId", ClientId.ToString().Trim(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ClientName", ClientName.ToString().Trim(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PrimaryClientDUNSNumber", Convert.ToString(PrimaryClientDUNSNumber), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PrimaryContactName", PrimaryContactName.ToString().Trim(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PrimaryEamilAddress", PrimaryEamilAddress.ToString().Trim(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PrimaryContactPhone", PrimaryContactPhone.ToString().Trim(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SecondaryContactName", Convert.ToString(SecondaryContactName), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SecondaryEamilAddress", Convert.ToString(SecondaryEamilAddress), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SecondaryContactPhone", Convert.ToString(SecondaryContactPhone), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@UserId", UserId.ToString().Trim(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Notes", Convert.ToString(Notes), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Active", Active.ToString(), SQLServerDatatype.BitDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ClientLogo", Convert.ToString(ClientLogo), SQLServerDatatype.VarcharDataType));
sql.ExecuteNoReturn(CommandType.StoredProcedure, sproc);
}
internal OIlstMatchMetaDetails GetStewOIMatchMetadata(int inputId, string OrbNum)
{
OIlstMatchMetaDetails oIlstMatchDetails = new OIlstMatchMetaDetails();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "oi.StewGetMatchMetadata";
StoredProceduresParameterEntity param = new StoredProceduresParameterEntity();
sproc.StoredProceduresParameter.Add(GetParam("@InputId", inputId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@OrbNum", OrbNum.ToString(), SQLServerDatatype.VarcharDataType));
DataSet ds;
ds = sql.ExecuteDataSet(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (ds != null)
{
if (ds.Tables[0] != null && ds.Tables[0].Rows != null && ds.Tables[0].Rows.Count > 0)
{
OICompanyAdapter ta = new OICompanyAdapter();
foreach (DataRow rw in ds.Tables[0].Rows)
{
oIlstMatchDetails.lstOICompanyInput = ta.AdaptItem(rw);
}
}
if (ds.Tables[1] != null && ds.Tables[1].Rows != null && ds.Tables[1].Rows.Count > 0)
{
oIlstMatchDetails.lstMatchQualityMetadatas = new OICompanyAdapter().SearchMatchMetaDataAdapt(ds.Tables[1]);
}
if (ds.Tables[2] != null && ds.Tables[2].Rows != null && ds.Tables[2].Rows.Count > 0)
{
oIlstMatchDetails.lstMatchMetaDatas = new OICompanyAdapter().MatchMetaDataDetailAdapt(ds.Tables[2]);
}
}
return(oIlstMatchDetails);
}
catch (Exception)
{
//Put log to db here
throw;
}
}
internal List <ThirdPartyAPICredentialsEntity> GetThirdPartyAPICredentialsForRefresh(string ThirdPartyProvider)
{
List <ThirdPartyAPICredentialsEntity> results = new List <ThirdPartyAPICredentialsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.GetThirdPartyAPICredentialsForRefresh";
sproc.StoredProceduresParameter.Add(GetParam("@ThirdPartyProvider", ThirdPartyProvider, SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc);
if (dt != null && dt.Rows.Count > 0)
{
results = new ThirdPartyAPICredentialsAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal List <GlobalThirdPartyAPICredentialsEntity> GetThirdPartyAPICredentialsForUser(int UserId)
{
List <GlobalThirdPartyAPICredentialsEntity> results = new List <GlobalThirdPartyAPICredentialsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.GetThirdPartyAPICredentialsForUser";
sproc.StoredProceduresParameter.Add(GetParam("@UserId", UserId.ToString(), SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new GlobalThirdPartyAPICredentialsAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal ThirdPartyAPICredentialsEntity GetThirdPartyAPICredentialsById(int CredentialId)
{
ThirdPartyAPICredentialsEntity results = new ThirdPartyAPICredentialsEntity();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "cfg.GetThirdPartyAPICredentialsById";
sproc.StoredProceduresParameter.Add(GetParam("@CredentialId", CredentialId.ToString(), SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new ThirdPartyAPICredentialsAdapter().AdaptItem(dt.Rows[0]);
}
}
catch (Exception)
{
throw;
}
return(results);
}
public int ExecuteReader(System.Data.CommandType CommandType, StoredProcedureEntity Sproc)
{
System.Data.SqlClient.SqlDataReader result = default(System.Data.SqlClient.SqlDataReader);
List <SqlParameter> SQLParams = new List <SqlParameter>();
int i = 0;
try
{
SqlConnection selectConnection = default(SqlConnection);
SQLParams = GetParameters(Sproc);
using (selectConnection = new SqlConnection(_ConnectionString))
{
selectConnection.Open();
SqlCommand selectCMD = new SqlCommand(Sproc.StoredProcedureName, selectConnection);
selectCMD.CommandType = CommandType;
selectCMD.CommandTimeout = 180;
if (SQLParams.Count > 0)
{
selectCMD.Parameters.AddRange(SQLParams.ToArray());
}
result = selectCMD.ExecuteReader();
while (result.Read())
{
i += 1;
}
result.Close();
result.Dispose();
selectConnection.Close();
}
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
return(i);
}
public void ExecuteNoReturnInTransaction(System.Data.CommandType CommandType, StoredProcedureEntity Sproc)
{
try
{
List <SqlParameter> SQLParams = new List <SqlParameter>();
SQLParams = GetParameters(Sproc);
SqlCommand selectCMD = new SqlCommand(Sproc.StoredProcedureName, InsertConnection);
selectCMD.Transaction = InsertTransaction;
selectCMD.CommandType = CommandType;
selectCMD.CommandTimeout = 180;
if (SQLParams.Count > 0)
{
selectCMD.Parameters.AddRange(SQLParams.ToArray());
}
selectCMD.ExecuteNonQuery();
}
catch (Exception exc)
{
throw new Exception(exc.Message);
}
}
internal DataSet SearchPreviewMatchData(string Tag, string ImportProcess, string LOBTag, string SrcRecID, bool SrcRecIdExactMatch, string ConfidenceCodes, string AcceptedBy, int UserId, int PageNumber, int PageSize, out int TotalRecords)
{
try
{
TotalRecords = 0;
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.PreviewCompanyData";
sproc.StoredProceduresParameter.Add(GetParam("@LOBTag", !string.IsNullOrEmpty(LOBTag) ? LOBTag.ToString() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tags", !string.IsNullOrEmpty(Tag) ? Tag.ToString() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ImportProcess", !string.IsNullOrEmpty(ImportProcess) ? ImportProcess.ToString() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SrcRecordId", string.IsNullOrEmpty(SrcRecID) ? null : SrcRecID.ToString(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SrcRecIdExactMatch", SrcRecIdExactMatch.ToString(), SQLServerDatatype.BitDataType));
sproc.StoredProceduresParameter.Add(GetParam("@ConfidenceCodes", !string.IsNullOrEmpty(ConfidenceCodes) ? ConfidenceCodes.ToString() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AcceptedBy", !string.IsNullOrEmpty(AcceptedBy) ? AcceptedBy.ToString() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@UserId", UserId.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PageNumber", PageNumber.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PageSize", PageSize.ToString(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@TotalRecords", TotalRecords.ToString(), SQLServerDatatype.IntDataType, ParameterDirection.Output));
string outParam = "";
DataSet ds;
DataTable dt = new DataTable(), dtMessage = new DataTable();
ds = sql.ExecuteDataSetWithOutputParam(CommandType.StoredProcedure, sproc, out outParam);
if (ds != null)
{
dt = ds.Tables[0];
}
if (dt != null && dt.Rows.Count > 0)
{
TotalRecords = Convert.ToInt32(outParam);
}
return(ds);
}
catch (Exception)
{
throw;
}
}
internal List <TagsEntity> GetTagByTypeCode(string TagTypeCode)
{
List <TagsEntity> results = new List <TagsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "[dnb].[GetTagByTypeCode]";
sproc.StoredProceduresParameter.Add(GetParam("@TagTypeCode", TagTypeCode.ToString(), SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new TagsAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal List <NotificationProfileEntity> GetNotificationProfile(int CredentialId)
{
List <NotificationProfileEntity> results = new List <NotificationProfileEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetNotificationProfile";
sproc.StoredProceduresParameter.Add(GetParam("@CredentialId", CredentialId.ToString(), SQLServerDatatype.IntDataType));
DataTable dt;
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.ReadWrite.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new NotificationProfileAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
internal int InsertOIStgInputCompany(OIInpCompanyEntity objCompany)
{
int result = 0;
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "ext.InsertOIStgInputCompany";
sproc.StoredProceduresParameter.Add(GetParam("@ImportProcessId", objCompany.ImportProcessId.ToString().Trim(), SQLServerDatatype.IntDataType));
sproc.StoredProceduresParameter.Add(GetParam("@SrcRecordId", objCompany.SrcRecordId.ToString().Trim(), SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@CompanyName", !string.IsNullOrEmpty(objCompany.CompanyName) ? objCompany.CompanyName.ToString().Trim() : "", SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Address1", !string.IsNullOrEmpty(objCompany.Address1) ? objCompany.Address1.ToString().Trim() : "", SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Address2", !string.IsNullOrEmpty(objCompany.Address2) ? objCompany.Address2.ToString().Trim() : "", SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@City", !string.IsNullOrEmpty(objCompany.City) ? objCompany.City.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@State", !string.IsNullOrEmpty(objCompany.State) ? objCompany.State.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PostalCode", !string.IsNullOrEmpty(objCompany.PostalCode) ? objCompany.PostalCode.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Country", !string.IsNullOrEmpty(objCompany.Country) ? objCompany.Country.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@PhoneNbr", !string.IsNullOrEmpty(objCompany.PhoneNbr) ? objCompany.PhoneNbr.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Tags", !string.IsNullOrEmpty(objCompany.OrbSingleEntryTags) ? objCompany.OrbSingleEntryTags.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@EIN", !string.IsNullOrEmpty(objCompany.EIN) ? objCompany.EIN.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@OrbNum", !string.IsNullOrEmpty(objCompany.OrbNum) ? objCompany.OrbNum.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@CEOName", !string.IsNullOrEmpty(objCompany.CEOName) ? objCompany.CEOName.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Website", !string.IsNullOrEmpty(objCompany.Website) ? objCompany.Website.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltCompanyName", !string.IsNullOrEmpty(objCompany.AltCompanyName) ? objCompany.AltCompanyName.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltAddress1", !string.IsNullOrEmpty(objCompany.AltAddress1) ? objCompany.AltAddress1.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltAddress2", !string.IsNullOrEmpty(objCompany.AltAddress2) ? objCompany.AltAddress2.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltCity", !string.IsNullOrEmpty(objCompany.AltCity) ? objCompany.AltCity.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltState", !string.IsNullOrEmpty(objCompany.AltState) ? objCompany.AltState.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltPostalCode", !string.IsNullOrEmpty(objCompany.AltPostalCode) ? objCompany.AltPostalCode.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@AltCountry", !string.IsNullOrEmpty(objCompany.AltCountry) ? objCompany.AltCountry.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
sproc.StoredProceduresParameter.Add(GetParam("@Email", !string.IsNullOrEmpty(objCompany.Email) ? objCompany.Email.ToString().Trim() : null, SQLServerDatatype.VarcharDataType));
result = Convert.ToInt32(sql.ExecuteScalar(CommandType.StoredProcedure, sproc));
}
catch (Exception)
{
throw;
}
return(result);
}
internal List <TagsEntity> GetAutoAcceptanceFilterTags(string LOBTag)
{
List <TagsEntity> results = new List <TagsEntity>();
try
{
StoredProcedureEntity sproc = new StoredProcedureEntity();
sproc.StoredProcedureName = "dnb.GetAutoAcceptanceFilterTags";
sproc.StoredProceduresParameter.Add(GetParam("@LOBTag", string.IsNullOrEmpty(LOBTag) ? null : Convert.ToString(LOBTag), SQLServerDatatype.VarcharDataType));
DataTable dt = new DataTable();
dt = sql.ExecuteDataTable(CommandType.StoredProcedure, sproc, "", DBIntent.Read.ToString());
if (dt != null && dt.Rows.Count > 0)
{
results = new TagsAdapter().Adapt(dt);
}
}
catch (Exception)
{
throw;
}
return(results);
}
