Loading Excel values to database
If you want to load the excel values to database (eg:SQL server database) follow the steps:
1) Create a empty table with columns.
CREATE TABLE [dbo].[users](
[user_name] [varchar](50) NOT NULL,
[Password] [nchar](10) NULL )
Check the data must be empty.
2) Create an excel sheet with your data. In the excel the top row
must contain same column names as table created by us and
remember that you have your data in Sheet1 of excel sheet.
3) In code behind page write this code:
This code can be written in the event you like.
For now I am writing this code in Button_click
Event.
string excelConnectionString = @”Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\users.xls;Extended Properties=””Excel 8.0;HDR=YES;”””;
OleDbConnection conn = new
OleDbConnection(excelConnectionString);
//Here Sheet1 means the current sheet in excel file If your data is in sheet2 you can give Sheet2.
string selqry = “select * from [Sheet1$]”;
OleDbCommand cmd = new OleDbCommand(selqry, conn);
conn.Open();
OleDbDataReader dr;
dr = cmd.ExecuteReader();
string sqlConnectionString = “Data Source=MNCXP08;Initial
Catalog=test;Persist Security Info=True;User
ID=sa;Password=admin123″;
SqlBulkCopy bcp = new SqlBulkCopy(sqlConnectionString);
//Here users is the table we created
bcp.DestinationTableName = “users”;
bcp.WriteToServer(dr);
4) Check the users table you will get all the results.