public Mobility Update(Mobility mobility)
{
mobilityRepository.Update(mobility);
return mobility;
}
private List<Mobility> InsertMobiltiy(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++;
}
if (excelSheets.Length <= 0) throw new Exception("Cannot find any sheet!");
// Loop through all of the sheets if you want too...
for (int j = 0; j < excelSheets.Length; 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 >= 17)
{
List<Mobility> listMobility = new List<Mobility>();
foreach (DataRow row in table.Rows)
{
Mobility mobi = new Mobility();
mobi.Id = Guid.NewGuid();
if (row[0] != null)
{
DateTime minValue = DateTime.MinValue;
DateTime.TryParse(row[0].ToString(), out minValue);
if (minValue > DateTime.MinValue)
{
mobi.DateRun = minValue;
}
}
mobi.ClientName = row[1] != null ? row[1].ToString() : string.Empty;
mobi.LicenseNumber = row[2] != null ? row[2].ToString() : string.Empty;
mobi.MD = row[3] != null ? row[3].ToString() : string.Empty;
mobi.PickupAddress = row[4] != null ? row[4].ToString() : string.Empty;
if (row[5] != null)
{
mobi.DueTime = String.Format("{0:hh:mm tt}", DateTime.Parse(row[5].ToString()));
}
if (row[6] != null)
{
mobi.PUTime = String.Format("{0:hh:mm tt}", DateTime.Parse(row[6].ToString()));
}
mobi.DropOffAddress = row[7] != null ? row[7].ToString() : string.Empty;
mobi.Phone = row[8] != null ? row[8].ToString() : string.Empty;
if (row[9] != null)
{
mobi.DOTime = String.Format("{0:hh:mm tt}", DateTime.Parse(row[9].ToString()));
}
mobi.Early = row[10] != null ? row[10].ToString() : string.Empty;
mobi.Late = row[11] != null ? row[11].ToString() : string.Empty;
mobi.NS = row[12] != null ? row[12].ToString() : string.Empty;
mobi.Hold = row[13] != null ? row[13].ToString() : string.Empty;
mobi.CAB = row[14] != null ? row[14].ToString() : string.Empty;
mobi.CL = row[15] != null ? row[15].ToString() : string.Empty;
decimal amount = 0;
decimal.TryParse(row[16] != null ? row[16].ToString() : string.Empty, out amount);
mobi.Amount = amount;
listMobility.Add(mobi);
}
if (listMobility.Count > 0)
{
//bulk insert to database
mobilityService.BulkInsertMobility(listMobility);
}
return listMobility;
}
}
}
return null;
}
catch (Exception ce)
{
throw ce;
}
return null;
}
catch (Exception ex)
{
throw ex;
}
finally
{
// Clean up.
if (objConn != null)
{
objConn.Close();
objConn.Dispose();
}
if (dt != null)
{
dt.Dispose();
}
}
}
public Mobility Add(Mobility mobility)
{
mobilityRepository.Add(mobility);
return mobility;
}
