In my web application, I have some Excel sheets i need to insert it into database here i am using sqlbulkcopy to insert data into sqlserver data base.
i am used this code:
using System;
using System.Data;
using System.IO;
using System.Data.OleDb;
using System.Configuration;
using System.Data.SqlClient;
public partial class _Default : System.Web.UI.Page
{
OleDbConnection Econ;
SqlConnection con;
string constr,Query,sqlconn;
protected void Page_Load(object sender, EventArgs e)
{
}
private void ExcelConn(string FilePath)
{
constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", FilePath);
Econ = new OleDbConnection(constr);
}
private void connection()
{
sqlconn = ConfigurationManager.ConnectionStrings["SqlCom"].ConnectionString;
con = new SqlConnection(sqlconn);
}
private void InsertExcelRecords(string FilePath)
{
ExcelConn(FilePath);
Query = string.Format("Select [Name],[City],[Address],[Designation] FROM [{0}]", "Sheet1$");
OleDbCommand Ecom = new OleDbCommand(Query, Econ);
Econ.Open();
DataSet ds=new DataSet();
OleDbDataAdapter oda = new OleDbDataAdapter(Query, Econ);
Econ.Close();
oda.Fill(ds);
DataTable Exceldt = ds.Tables[0];
connection();
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
//assigning Destination table name
objbulk.DestinationTableName = "Employee";
//Mapping Table column
objbulk.ColumnMappings.Add("Name", "Name");
objbulk.ColumnMappings.Add("City", "City");
objbulk.ColumnMappings.Add("Address", "Address");
objbulk.ColumnMappings.Add("Designation", "Designation");
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
}
protected void Button1_Click(object sender, EventArgs e)
{
string CurrentFilePath = Path.GetFullPath(FileUpload1.PostedFile.FileName);
InsertExcelRecords(CurrentFilePath);
}
}
and i converting my requirement like this:
I take connection globally
public void Excelconnection(string path)
{
string[] sheet = path.Split('.');
string sheetname = sheet[0];
string Query = string.Format("Select [ID],[Description],[Credits],[QTY %],[Amount],[Amount %] FROM", sheetname);
localContext = uno.util.Bootstrap.bootstrap();
_multiServiceFactory = (XMultiServiceFactory)localContext.getServiceManager();
_componentLoader = (XComponentLoader)_multiServiceFactory.createInstance("com.sun.star.frame.Desktop");
_urlConverter = (XFileIdentifierConverter)_multiServiceFactory.createInstance("com.sun.star.ucb.FileContentProvider");
SqlCommand Ecom = new SqlCommand(Query, con);
con.Open();
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(Query, con);
con.Close();
da.Fill(ds);
DataTable Exceldt = ds.Tables[0];
//creating object of SqlBulkCopy
SqlBulkCopy objbulk = new SqlBulkCopy(con);
// connection();
//assigning Destination table name
objbulk.DestinationTableName = "Ads";
//Mapping Table column
objbulk.ColumnMappings.Add("ID", "ID");
objbulk.ColumnMappings.Add("Description", "Description");
objbulk.ColumnMappings.Add("Credits", "Credits");
objbulk.ColumnMappings.Add("QTYInPercentage", "QTY %");
objbulk.ColumnMappings.Add("Amount", "Amount");
objbulk.ColumnMappings.Add("AmountInPercentage", "Amount %");
objbulk.ColumnMappings.Add("Client", ddlClient.SelectedItem.Text);
//inserting Datatable Records to DataBase
con.Open();
objbulk.WriteToServer(Exceldt);
con.Close();
}
protected void btnUpload_Click(object sender, EventArgs e)
{
string filename = Path.GetFileName(FileUpload1.PostedFile.FileName);
Excelconnection(filename);
}
but i am getting error in `string Query` in my code which query i take and where i did mistake can anyone please tell me is it have any changes will require how can i do this please help me.
Thank you