public bool ImportFile(string countryID, string addrStart, string addrDestination, string srcFilename, Guid routeUID) { bool ret = false; if (String.IsNullOrEmpty(addrStart)) { return(false); } else { addrStart = addrStart.Trim(); } if (String.IsNullOrEmpty(addrDestination)) { return(false); } else { addrDestination = addrDestination.Trim(); } // Wurde ein Dateiname angegeben? if (String.IsNullOrEmpty(srcFilename)) { return(false); } else { srcFilename = srcFilename.Trim(); } // Ist es eine GPX Datei? if (!srcFilename.ToLower().EndsWith(".gpx")) { return(false); } // Existiert die Datei? if (!System.IO.File.Exists(srcFilename)) { return(false); } // Pfad für die GPX Dateien zusammenbauen string gpxPath = Environment.GetFolderPath(Environment.SpecialFolder.Personal); gpxPath = System.IO.Path.Combine(gpxPath, "GPX"); // Verzeichnis im zweifelsfall neu anlegen if (!System.IO.Directory.Exists(gpxPath)) { System.IO.Directory.CreateDirectory(gpxPath); } try { System.IO.File.Copy(srcFilename, System.IO.Path.Combine(gpxPath, routeUID.ToString() + ".gpx")); ret = true; } catch (Exception) { // TODO logging } // Wenn das kopieren der Datei geklappt hat, die Route in der Datenbank hinterlegen if (ret) { ret = new DBRoute().AddNewRoute(routeUID, countryID, addrStart, addrDestination); } return(ret); }
protected void btnUpload_Click(object sender, EventArgs e) { //Upload and save the file string excelPath = Server.MapPath("~/Tabs/Dispatch/") + Path.GetFileName(FileUpload1.PostedFile.FileName); FileUpload1.SaveAs(excelPath); string conString = string.Empty; string extension = Path.GetExtension(FileUpload1.PostedFile.FileName); switch (extension) { case ".xls": //Excel 97-03 conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString; break; case ".xlsx": //Excel 07 or higher conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString; break; } conString = string.Format(conString, excelPath); using (OleDbConnection excel_con = new OleDbConnection(conString)) { excel_con.Open(); string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString(); DataTable dtExcelData = new DataTable(); //[OPTIONAL]: It is recommended as otherwise the data will be considered as String by default. dtExcelData.Columns.AddRange(new DataColumn[3] { new DataColumn("AgentID", typeof(int)), new DataColumn("TypeID", typeof(string)), new DataColumn("SlabID", typeof(string)) }); using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT * FROM [" + sheet1 + "]", excel_con)) { oda.Fill(dtExcelData); } excel_con.Close(); dtExcelData.Columns.Add("CreatedBy", typeof(int)); for (int i = 0; i <= dtExcelData.Rows.Count - 1; i++) { dtExcelData.Rows[i]["CreatedBy"] = GlobalInfo.Userid; } dtExcelData.Columns.Add("CreatedDate", typeof(string)); for (int i = 0; i <= dtExcelData.Rows.Count - 1; i++) { dtExcelData.Rows[i]["CreatedDate"] = DateTime.Now.ToString("dd-MM-yyyy"); } dtExcelData.Columns.Add("IsArchive", typeof(bool)); for (int i = 0; i <= dtExcelData.Rows.Count - 1; i++) { dtExcelData.Rows[i]["IsArchive"] = false; } DBRoute dbr = new DBRoute(); dbr.ClearBindSlab(); string consString = ConfigurationManager.ConnectionStrings["projectconnection"].ConnectionString; using (SqlConnection con = new SqlConnection(consString)) { using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con)) { //Set the database table name sqlBulkCopy.DestinationTableName = "dbo.BindSlab"; //[OPTIONAL]: Map the Excel columns with that of the database table sqlBulkCopy.ColumnMappings.Add("AgentID", "AgentID"); sqlBulkCopy.ColumnMappings.Add("TypeID", "TypeID"); sqlBulkCopy.ColumnMappings.Add("SlabID", "SlabID"); sqlBulkCopy.ColumnMappings.Add("CreatedBy", "CreatedBy"); sqlBulkCopy.ColumnMappings.Add("CreatedDate", "CreatedDate"); sqlBulkCopy.ColumnMappings.Add("IsArchive", "IsArchive"); con.Open(); sqlBulkCopy.WriteToServer(dtExcelData); con.Close(); } } ScriptManager.RegisterClientScriptBlock(this, this.GetType(), "alertMessage", "alert('Bindslab completed successfully')", true); } }