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 }