Import Openoffice calc sheet to sql server using sqlbulkcopy

Question

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

Answer 1

There is no benefit or difference of them, other than that they allow us to update the values of the fields before hand. For example, we may do this:

var person = new Person();
person.Name = "Afzaal Ahmad Zeeshan"; 
person.Age = 20;

But, using the parameters with constructors allows you to pass the values at the object construction,

var person = new Person("Afzaal Ahmad Zeeshan", 20); 

Because rest of the stuff would be handled by the constructor itself:

public Person(string name, int age) {
     Name = name;
     Age = age; 
} 

Otherwise, there is no difference in them. Plus, there is a bit of stuff for "overloading" going on. You must understand that Overloading is a concept of Object-oriented programming. I have an article about overriding and overloading, that may help you a bit more about this. 

 

http://www.c-sharpcorner.com/UploadFile/201fc1/still-do-not-get-the-difference-of-overloading-and-overridin/