how can i import data from excel shit in sql server using c#

Question

Right now m working on a e-commerce website and i have to provide product uploading user interface top vender. from dere hi/she will upload product with the help of excel sheet.i mean he will upload an excel sheet and after all product will be updated in database .how can i import data from excel shit in sql server 2010 using c# ?

Abhishek Singh · Answer

hi Please try this. I a trying to use bulkcopy for data transfer. Code below Create sql database table where you want to store data. public class FileUpload { void importdata(string excelfilepath) { //declare variables - edit these based on your particular situation string ssqltable = tTableExcel; // make sure your sheet name is correct here sheet name is sheet1 so you can change your sheet name if have string myexceldataquery = select studentrollnocourse from [sheet1$]; try { //create our connection strings string sexcelconnectionstring = @provider=microsoft.jet.oledb.4.0;data source= + excelfilepath + ;extended properties= + \excel 8.0;hdr=yes;\; string ssqlconnectionstring = server=mydatabaseservername;userid=dbuserid;password=dbuserpassword;database=databasename;connection reset=false; //execute a query to erase any previous data from our destination table string sclearsql = delete from  + ssqltable; SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring); SqlCommand sqlcmd = new SqlCommand(sclearsql sqlconn); sqlconn.Open(); sqlcmd.ExecuteNonQuery(); sqlconn.Close(); //series of commands to bulk copy data from the excel file into our sql table OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring); bulkcopy.DestinationTableName = ssqltable; while (dr.Read()) { bulkcopy.WriteToServer(dr); } oledbconn.Close(); } catch (Exception ex) { //handle exception } } } Hope this help.

satyam sharma · Answer

thx abhishek  i'll try this one .i hope this will help me ...

Abhishek Singh · Answer

Satyam In that case I would advise to use new table which you are using to store data from excelsheet by the query select studentrollnocourse from [sheet1$]; as temp table in database and can modify the code as you have excel data avaible in temp table for multi table insertion. Hope this helps. Abhishek

satyam sharma · Answer

thanks abhishek  bt here i have to insert data in multiple table bcz m working on e-commerce website so there we generating product id by code. i have to insert a product in table after that i have pick product id from product table then i have to inter product category in product_category table from category table where types of category already defined.then i have to insert product attribute value from attribute value table. i can understand the flow bt m getting problem in insertion ...