private void SaveCompaniesToDB(string fileName)
{
string connectionString = string.Format(_excelConStr, fileName);
using (var adapter = new OleDbDataAdapter("SELECT * FROM [COMPANIES$]", connectionString))
{
using (var ds = new DataSet())
{
adapter.Fill(ds, "AnyName");
var data = ds.Tables["AnyName"].AsEnumerable();
var query = data.Where(x => x.Field<string>("Company Name") != string.Empty).AsQueryable();
List<Company> companies = new List<Company>();
foreach (var item in query)
{
Company company = new Company();
company.AddressLine1 = item["Address Line 1"].ToString();
company.AddressLine2 = item["Address Line 2"].ToString();
company.AddressLine3 = item["Address Line 3"].ToString();
company.Aka = item["aka/dba"].ToString();
company.City = item["City"].ToString();
company.Comments = item["Comments"].ToString();
company.CompanyName = item["Company Name"].ToString();
company.IssuingState = item["Issuing State"].ToString();
company.LicenseNumber = item["License Number"].ToString();
company.State = item["State"].ToString();
company.Zip = item["Zip"].ToString();
companies.Add(company);
}
CCTValidationProvider validationProvider = new CCTValidationProvider();
validationProvider.SaveExcludedCompanies(companies);
}
}
}
public List<Company> GetExcludedCompanies(int? id)
{
using (var dc = new EngageCCTDataClassesDataContext())
{
var query = dc.T_ExcListCompanies.AsQueryable();
if (id.HasValue)
{
query = query.Where(c => c.ExcListCompanyID == id);
}
List<Company> result = new List<Company>();
foreach (T_ExcListCompany tCompany in query.ToArray())
{
Company company = new Company();
company.ID = tCompany.ExcListCompanyID;
company.AddressLine1 = tCompany.elcAddressLine1;
company.AddressLine2 = tCompany.elcAddressLine2;
company.AddressLine3 = tCompany.elcAddressLine3;
company.Aka = tCompany.elcAkaDba;
company.City = tCompany.elcCity;
company.Comments = tCompany.elcComments;
company.CompanyName = tCompany.elcCompanyName;
company.IssuingState = tCompany.elcIssuingState;
company.LicenseNumber = tCompany.elcLicenseNumber;
company.State = tCompany.elcState;
company.Zip = tCompany.elcZip;
result.Add(company);
}
return result;
}
}