public RTA Update(RTA company)
{
companyRepository.Update(company);
return company;
}
private List<RTA> InsertRTA(string excelFile, string fileExt)
{
OleDbConnection objConn = null;
System.Data.DataTable dt = null;
try
{
// Connection String. Change the excel file to the file you
// will search.
String connString = string.Empty;
if (fileExt == ".xls" || fileExt == "XLS")
{
connString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" + excelFile + "'" + "; Extended Properties ='Excel 8.0;HDR=Yes'";
}
else if (fileExt == ".xlsx" || fileExt == "XLSX")
{
connString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + excelFile + "; Extended Properties ='Excel 8.0;HDR=Yes'";
}
// Create connection object by using the preceding connection string.
objConn = new OleDbConnection(connString);
// Open connection with the database.
try
{
objConn.Open();
// Get the data table containg the schema guid.
dt = objConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
throw new Exception("File invalid!");
}
String[] excelSheets = new String[dt.Rows.Count];
int i = 0;
// Add the sheet name to the string array.
foreach (DataRow row in dt.Rows)
{
excelSheets[i] = row["TABLE_NAME"].ToString();
i++;
}
// Loop through all of the sheets if you want too...
if (excelSheets.Length <= 0) throw new Exception("Cannot find any sheet!");
for (int j = 0; j < 1; j++)
{
DataTable table = new DataTable();
string query = String.Format("Select * from [{0}]", excelSheets[j]);
OleDbDataAdapter adp = new OleDbDataAdapter(query, objConn);
adp.Fill(table);
if (table.Rows != null && table.Rows.Count > 0)
{
//get data for insert to database
if (table.Columns != null && table.Columns.Count == 14)
{
List<RTA> listRTA = new List<RTA>();
foreach (DataRow row in table.Rows)
{
RTA rta = new RTA();
rta.Id = Guid.NewGuid();
if (row[0] != null)
{
DateTime minValue = DateTime.MinValue;
DateTime.TryParse(row[0].ToString(), out minValue);
if (minValue > DateTime.MinValue)
{
rta.DateRun = (DateTime)row[0];
}
}
rta.ClientName = row[1] != null ? row[1].ToString() : string.Empty;
rta.LicenseNumber = row[2] != null ? row[2].ToString() : string.Empty;
rta.PickupAddress = row[3] != null ? row[3].ToString() : string.Empty;
if (row[4] != null)
{
rta.DueTime =String.Format("{0:hh:mm tt}",DateTime.Parse(row[4].ToString()));
}
if (row[5] != null)
{
rta.PUTime = String.Format("{0:hh:mm tt}", DateTime.Parse(row[5].ToString()));
}
if (row[6] != null)
{
rta.DOTime = String.Format("{0:hh:mm tt}", DateTime.Parse(row[6].ToString()));
}
rta.DropOffAddress = row[7] != null ? row[7].ToString() : string.Empty;
rta.Phone = row[8] != null ? row[8].ToString() : string.Empty;
rta.CAB = row[9] != null ? row[9].ToString() : string.Empty;
rta.CL = row[10] != null ? row[10].ToString() : string.Empty;
decimal amount = 0;
decimal.TryParse(row[11] != null ? row[11].ToString() : string.Empty, out amount);
rta.Amount = amount;
if (row[12] != null)
{
DateTime minValue = DateTime.MinValue;
DateTime.TryParse(row[12].ToString(), out minValue);
if (minValue > DateTime.MinValue)
{
rta.PaidDate = minValue;
}
}
rta.Confirmed = row[13] != null ? row[13].ToString() : string.Empty;
listRTA.Add(rta);
}
if (listRTA.Count > 0)
{
//bulk insert to database
rtaService.BulkInsertRTA(listRTA);
}
return listRTA;
}
}
}
return null;
}
catch (Exception ce)
{
throw ce;
}
return null;
}
catch (Exception ex)
{
return null;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
public RTA Add(RTA company)
{
companyRepository.Add(company);
return company;
}