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