public void AscGovLicenseRepositoryTest_BulkUpdate()
{
//arrange
var licenses = new AscGovLicense[] {
new AscGovLicense(){City="Sity", MiddleName="21", ExpirationDate=DateTime.Now},
new AscGovLicense(){City="212", MiddleName="21"}};
//act
_repository.BulkUpdate(licenses);
//assert
_repository.Get().Should().HaveCount(2);
}
private void CreateTestDataForAscGovLicense()
{
using (DVSContext context = new DVSContext())
{
var licenses = new AscGovLicense[] {
new AscGovLicense(){ LicenseNumber = "1", AppraiserStateId = "NY", LicenseStateId="NY", ExpirationDate = DateTime.Now, IsStatusInactive = true, City="22"},
new AscGovLicense(){ LicenseNumber = "1", LicenseStateId="FL", ExpirationDate = DateTime.Now.AddYears(1), IsStatusInactive = false },
new AscGovLicense(){ LicenseNumber ="2", LicenseStateId="NY", ExpirationDate = expiredDate, IsStatusInactive = false},
new AscGovLicense(){ LicenseNumber="3", LicenseStateId="NY", ExpirationDate= DateTime.Now.AddYears(1), IsStatusInactive = false }};
foreach (var license in licenses)
context.AscGovLicenses.Add(license);
context.SaveChanges();
}
}
public void UpdateLicenses()
{
Stopwatch stopWatch = Stopwatch.StartNew();
Logger.WriteDebugInfo("AscGovLicenseService. Started");
WebClient wc = new WebClient();
string licenseContext = wc.DownloadString(_configurationHelper.AscGovLicenseServiceUrl);
Logger.WriteDebugInfo("AscGovLicenseService. Downloaded " + stopWatch.ElapsedMilliseconds / 1000);
string[] licenseListSplited = licenseContext.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
List<AscGovLicense> ascGovLicenses = new List<AscGovLicense>();
for (int i = 1; i < licenseListSplited.Length; i++)
{
string[] licenseSplited = licenseListSplited[i].Split('\t');
if (licenseSplited.Length == 17)
{
var ascGovLicense = new AscGovLicense()
{
LicenseStateId = licenseSplited[0],
LicenseNumber = StringUtil.NormalizeLicenseNumber(licenseSplited[1]),
LastName = licenseSplited[2],
FirstName = licenseSplited[3],
MiddleName = licenseSplited[4],
NameSuffix = licenseSplited[5],
StreetAddress = licenseSplited[6],
City = licenseSplited[7],
AppraiserStateId = licenseSplited[8],
Zip = licenseSplited[9],
Company = licenseSplited[10],
Phone = licenseSplited[11],
County = licenseSplited[12],
CountyId = licenseSplited[13],
StatusString = licenseSplited[14],
IsStatusInactive = licenseSplited[14] != "A",
LicenseType = licenseSplited[15],
ExpirationDateString = licenseSplited[16],
ExpirationDate = SafeConvert.ToDateTime(licenseSplited[16])
};
ascGovLicenses.Add(ascGovLicense);
}
}
Logger.WriteDebugInfo("AscGovLicenseService. Prepared to save " + stopWatch.ElapsedMilliseconds / 1000);
_ascGovLicenseRepository.BulkUpdate(ascGovLicenses);
Logger.WriteDebugInfo("AscGovLicenseService. Saved " + stopWatch.ElapsedMilliseconds / 1000);
}
public void Update()
{
#region Read File
string licenseContext = new StreamReader(GetType().Assembly.GetManifestResourceStream("UpdateLicenses.v_Export_All.txt")).ReadToEnd();
string[] licenseListSplited = licenseContext.Split(new string[] { Environment.NewLine }, StringSplitOptions.RemoveEmptyEntries);
List<AscGovLicense> ascGovLicenses = new List<AscGovLicense>();
for (int i = 1; i < licenseListSplited.Length; i++)
{
string[] licenseSplited = licenseListSplited[i].Split('\t');
if (licenseSplited.Length == 17)
{
var ascGovLicense = new AscGovLicense()
{
LicenseStateId = licenseSplited[0],
LicenseNumber = StringUtil.NormalizeLicenseNumber(licenseSplited[1]),
LastName = licenseSplited[2],
FirstName = licenseSplited[3],
MiddleName = licenseSplited[4],
NameSuffix = licenseSplited[5],
StreetAddress = licenseSplited[6],
City = licenseSplited[7],
AppraiserStateId = licenseSplited[8],
Zip = licenseSplited[9],
Company = licenseSplited[10],
Phone = licenseSplited[11],
County = licenseSplited[12],
CountyId = licenseSplited[13],
StatusString = licenseSplited[14],
IsStatusInactive = licenseSplited[14] != "A",
LicenseType = licenseSplited[15],
ExpirationDateString = licenseSplited[16],
ExpirationDate = SafeConvert.ToDateTime(licenseSplited[16])
};
ascGovLicenses.Add(ascGovLicense);
}
}
#endregion
#region Bulk Insert
DataTable dataTable = new DataTable();
dataTable.Columns.Add(new DataColumn("Id"));
dataTable.Columns.Add(new DataColumn("StateId"));
dataTable.Columns.Add(new DataColumn("StateAbbr"));
dataTable.Columns.Add(new DataColumn("LicenseNumber"));
dataTable.Columns.Add(new DataColumn("LastName"));
dataTable.Columns.Add(new DataColumn("FirstName"));
dataTable.Columns.Add(new DataColumn("MiddleName"));
dataTable.Columns.Add(new DataColumn("NameSuffix"));
dataTable.Columns.Add(new DataColumn("StreetAddress"));
dataTable.Columns.Add(new DataColumn("City"));
dataTable.Columns.Add(new DataColumn("Zip"));
dataTable.Columns.Add(new DataColumn("Company"));
dataTable.Columns.Add(new DataColumn("Phone"));
dataTable.Columns.Add(new DataColumn("County"));
dataTable.Columns.Add(new DataColumn("CountyId"));
dataTable.Columns.Add(new DataColumn("StatusString"));
dataTable.Columns.Add(new DataColumn("LicenseType"));
dataTable.Columns.Add(new DataColumn("ExpirationDateString"));
dataTable.Columns.Add(new DataColumn("ExpirationDate"));
dataTable.Columns.Add(new DataColumn("IsStatusInactive"));
int j = 1;
foreach (var item in ascGovLicenses)
{
dataTable.Rows.Add(j++, item.AppraiserStateId, item.LicenseStateId, item.LicenseNumber, item.LastName, item.FirstName,
item.MiddleName, item.NameSuffix, item.StreetAddress, item.City, item.Zip, item.Company,
item.Phone, item.County, item.CountyId, item.StatusString, item.LicenseType,
item.ExpirationDateString, item.ExpirationDate, item.IsStatusInactive);
}
using (TransactionScope scope = new TransactionScope())
{
using (SqlConnection cnn = new SqlConnection(ConfigurationManager.ConnectionStrings["DVSContext"].ConnectionString))
{
SqlCommand command = new SqlCommand("Delete dbo.AscGovLicenses", cnn);
cnn.Open();
command.ExecuteNonQuery();
var sqlBulkCopy = new SqlBulkCopy(cnn);
sqlBulkCopy.DestinationTableName = "dbo.AscGovLicenses";
sqlBulkCopy.WriteToServer(dataTable);
}
scope.Complete();
}
#endregion
}