public async Task <bool> ImportUkrlpData(UkrlpDataEntry ukrlpData)
{
string sql = $"INSERT INTO [dbo].[UkrlpData] " +
"([UKPRN], " +
"[LegalName], " +
"[TradingName], " +
"[PrimaryVerificationSource], " +
"[CompanyNumber], " +
"[CharityNumber], " +
"[Status]) " +
"VALUES " +
"(@ukprn, @legalName, @tradingName, @primaryVerificationSource, @companyNumber, @charityNumber, @status)";
var recordCreated = await _connection.ExecuteAsync(sql,
new
{
ukrlpData.UKPRN,
ukrlpData.LegalName,
ukrlpData.TradingName,
ukrlpData.PrimaryVerificationSource,
ukrlpData.CompanyNumber,
ukrlpData.CharityNumber,
ukrlpData.Status
});
var success = await Task.FromResult(recordCreated > 0);
return(await Task.FromResult(success));
}
public async Task <bool> ImportUkrlpData(StreamReader streamReader)
{
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);
List <UkrlpDataEntry> entries = new List <UkrlpDataEntry>();
using (var reader = ExcelReaderFactory.CreateReader(streamReader.BaseStream))
{
// skip the first 6 sheets until we get to the 'UKRLP Data' sheet
for (var sheet = 1; sheet <= 6; sheet++)
{
reader.NextResult();
}
// skip the first 4 rows that contain summary information
for (var row = 1; row <= 4; row++)
{
reader.Read();
}
try
{
while (reader.Read())
{
var entry = new UkrlpDataEntry();
entry.UKPRN = reader.GetDouble(0).ToString();
entry.LegalName = reader.GetString(1);
entry.TradingName = reader.GetString(2);
entry.Status = reader.GetString(4);
entry.PrimaryVerificationSource = reader.GetString(7);
entry.CompanyNumber = reader.GetString(8);
entry.CharityNumber = reader.GetString(9);
entries.Add(entry);
}
}
catch (NullReferenceException nullReferenceException)
{
_logger.LogError("Unable to retrieve UKRLP data from the imported spreadsheet file", nullReferenceException);
return(await Task.FromResult(false));
}
if (entries.Any())
{
await _testDataRepository.DeleteUkrlpData();
foreach (var entry in entries)
{
bool success = await _testDataRepository.ImportUkrlpData(entry);
if (!success)
{
_logger.LogError("Unable to import UKRLP data into database");
return(await Task.FromResult(false));
}
}
return(await Task.FromResult(true));
}
else
{
_logger.LogWarning("No entries found in UKRLP data");
}
return(await Task.FromResult(false));
}
}
