how to insert values from an excel sheet to sqlserver 2005

i have an excel sheet where i try to upload my excel sheet to sqlserver all having same colum name. now i do not want to add dll files as an web refer开发者_开发技巧ence in my project. rather place the (dll) in an folder and call them dynamically in .cs side. now i am doing like this

var assembly = Assembly.LoadFrom(@"d:\abc\microsoft.office.interop.excel.dll");

now in my .cs page i need to generate this property or methods of an excel dll which i have loaded dynamically

microsoft.officce.interop.excel.applicationClass excel= null

so that after loading my excel dl dynamically i need to sent values from my excel sheet to sqlserver 2005

is there a way to achive this thank you

---

Can you not use OPENROWSET?
i.e Create a stored procedure that takes the path of the excel file which you want to insert into a given table. Use OPENROWSET function inside it to get a hold of the excel sheet.

---

Aother option is to possibly use SSIS to do this (SQL Server Integration Services). You would have more flexibility and could turn it into a small ETL project.

You could also use Excel code to transmit the data to the database either with a button or a macro. That only works if you can control the Excel file.

Just throwing other options out there.

---

First add a linked server to your Database instance..

Exec sp_dropServer 'myExcel',@droplogins='dropLogins'    
EXEC sp_addlinkedserver 'myExcel',
       'ACE 12.0',
       'Microsoft.ACE.OLEDB.12.0',
       'D:\SAABZX01D\EXCEL_books\ExpressLane\LMI\client carrier conversion.xls',
       NULL,
       'Excel 12.0'
exec sp_linkedServers

Then you insert to myTable in yourDatabase

Insert myTable(cola,colb,colc)
select cola,colb,colc from openQuery(myExcel,'select cola,colb,colc) from sheet1$')

---

You can open an excel file like a database (described here). After this you can load the data into some DataSet (I hope you know how to work with datasets) and upload all to the SqlServer database or to load in some custom structures, update some data if need and insert it into SqlServer database.