How to import excel to sql server with some column values transforming into row using asp.net? -

i want import excel file sql server using asp.net c#.

the user upload file web page using file upload control.

here code.

here used outer apply transform columns row.

when run code below error coming.

ierrorinfo.getdescription failed e_fail(0x80004005).

    protected void btnupload_click(object sender, eventargs e)     {         string excelpath = server.mappath("~/files/") + 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();                       string st = “select [sno], [id],[country],[qtr]=case(substring([qtr],1,2) nvarchar(4)), [value]  [“+sheet1+”] outer apply(values((n'q1',q1),(n'q2',q2)) p(qtr,value)”;     using (oledbdataadapter oda = new oledbdataadapter(st, excel_con))              {                 oda.fill(dtexceldata);             }              excel_con.close();              string consstring = configurationmanager.connectionstrings["constr"].connectionstring;              using (sqlconnection con = new sqlconnection(consstring))             {                  using (sqlbulkcopy sqlbulkcopy = new sqlbulkcopy(con))                 {                      //set database table name                      sqlbulkcopy.destinationtablename = "[dbo].[exceldemo]";                      con.open();                      sqlbulkcopy.writetoserver(dtexceldata);                      con.close();                      lblupload.text = "uploaded succesfully";                  } 

below format. please check it.

http://i61.tinypic.com/217pk0.jpg

i have tried unpivot also.

please tell me if thing wrong in above code , give suggestions overcome problem.

thank in advance.

you need more precise on want achieve. working in c# or vb.net? user upload file web page? have tried far?

one way import excel file sql server using asp.net upload file server fileupload. user able choose file wants import.

then, want read data in excel file , put in datatable. enable work in code behind. here example on how read data excel file ( http://www.codeproject.com/tips/509179/read-excel-file-into-dataset-in-asp-net-using-csha). similar reading of database, connect excel file.

now data in datatable, able manipulations want.

afterwards, can write data sql server in many different ways, depends on how write database. ado, nhibernate, ef, stored procedures, dynamic sql? can bulkcopy. copy data datatable database without having need iterate through rows of datatable. need map columns , make sure datatable formatted correctly. (https://msdn.microsoft.com/en-us/library/ex21zs8x(v=vs.110).aspx)

please update question more information , have tried far can give more specific answer.