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; }