This code is running properly in local machine
As in local machine i can upload and save that file in a path.
but how do that in server .
changing the
Data Source=.\SQLEXPRESS
to
Data Source=10.129.10.1
but it doesnot work to upload the file in server machine
SO i want to read the excel file ...
and get the data into SQL server(table)in server
webconfig:
<appSettings>
<add key="FolderPath" value="Files/"/>
</appSettings>
<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0}; Extended Properties='Excel 8.0;HDR={1}'"/>
<add name="myConnectionString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=rdba; User ID=rw;pwd=rw@123; " providerName="System.Data.SqlClient"/>
</connectionStrings>
code:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Data.OleDb;
using System.Data.Common;
using System.IO;
using System.Drawing;
using System.Configuration;
public partial class ImportExcel : System.Web.UI.Page
{
string con_str = ConfigurationManager.ConnectionStrings["myConnectionString"].ConnectionString;
protected void Page_Load(object sender, EventArgs e)
{
}
protected void PageIndexChanging(object sender, GridViewPageEventArgs e)
{
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FileName = GridView1.Caption;
string Extension = Path.GetExtension(FileName);
string FilePath = Server.MapPath(FolderPath + FileName);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
GridView1.PageIndex = e.NewPageIndex;
GridView1.DataBind();
}
protected void Button1_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
string FolderPath = ConfigurationManager.AppSettings["FolderPath"];
string FilePath = Server.MapPath(FolderPath + FileName);
FileUpload1.SaveAs(FilePath);
Import_To_Grid(FilePath, Extension, rbHDR.SelectedItem.Text);
}
}
private void Import_To_Grid(string FilePath, string Extension, string isHDR)
{
string conStr = "";
switch (Extension)
{
case ".xls":
conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
.ConnectionString;
break;
case ".xlsx":
conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
.ConnectionString;
break;
}
conStr = String.Format(conStr, FilePath, isHDR);
OleDbConnection connExcel = new OleDbConnection(conStr);
OleDbCommand cmdExcel = new OleDbCommand();
OleDbDataAdapter oda = new OleDbDataAdapter();
DataTable dt = new DataTable();
cmdExcel.Connection = connExcel;
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string SheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + SheetName + "]";
oda.SelectCommand = cmdExcel;
oda.Fill(dt);
GridView1.Caption = Path.GetFileName(FilePath);
GridView1.DataSource = dt;
GridView1.DataBind();
OleDbDataReader dr = cmdExcel.ExecuteReader();
SqlBulkCopy bulkInsert = new SqlBulkCopy(con_str);
bulkInsert.DestinationTableName = "DBtable";
bulkInsert.WriteToServer(dr);
connExcel.Close();
}
}