Want to become a Vibe Coder? Join Vibe Coding Training here
x
C# Corner
Tech
News
Videos
Forums
Jobs
Books
Events
More
Interviews
Live
Learn
Training
Career
Members
Blogs
Challenges
Certification
Contribute
Article
Blog
Video
Ebook
Interview Question
Collapse
Feed
Dashboard
Wallet
Learn
Achievements
Network
Refer
Rewards
SharpGPT
Premium
Contribute
Article
Blog
Video
Ebook
Interview Question
Register
Login
Upload Excelsheet Data In SQL Server Table
WhatsApp
Vandana
Aug 20
2016
756
0
1
protected
void
btnSend_Click(object sender, EventArgs e) {
try
{
string path = string.Concat(Server.MapPath(
"~/File/"
+ fileuploadExcel.FileName));
fileuploadExcel.SaveAs(path);
string connExcelString = string.Format(
"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";"
, path);
OleDbConnection excelConnection =
new
OleDbConnection(connExcelString);
OleDbCommand cmd =
new
OleDbCommand(
"Select [Name],[Mobile],[Email],[City],[DataId],[Date],[Source] from [Sheet1$]"
, excelConnection);
excelConnection.Open();
OleDbDataReader dReader;
dReader = cmd.ExecuteReader();
SqlBulkCopy sqlBulk =
new
SqlBulkCopy(strConnection);
SqlBulkCopyColumnMapping mapName =
new
SqlBulkCopyColumnMapping(
"Name"
,
"Name"
);
sqlBulk.ColumnMappings.Add(mapName);
SqlBulkCopyColumnMapping mapMobile =
new
SqlBulkCopyColumnMapping(
"Mobile"
,
"Mobile"
);
sqlBulk.ColumnMappings.Add(mapMobile);
SqlBulkCopyColumnMapping mapEmail =
new
SqlBulkCopyColumnMapping(
"Email"
,
"Email"
);
sqlBulk.ColumnMappings.Add(mapEmail);
SqlBulkCopyColumnMapping mapCity =
new
SqlBulkCopyColumnMapping(
"City"
,
"City"
);
sqlBulk.ColumnMappings.Add(mapCity);
//SqlBulkCopyColumnMapping mapState = new SqlBulkCopyColumnMapping("State", "State");
//sqlBulk.ColumnMappings.Add(mapState);
SqlBulkCopyColumnMapping mapDataId =
new
SqlBulkCopyColumnMapping(
"DataId"
,
"DataId"
);
sqlBulk.ColumnMappings.Add(mapDataId);
SqlBulkCopyColumnMapping mapAmount =
new
SqlBulkCopyColumnMapping(
"Date"
,
"Date"
);
sqlBulk.ColumnMappings.Add(mapAmount);
SqlBulkCopyColumnMapping mapSource =
new
SqlBulkCopyColumnMapping(
"Source"
,
"Source"
);
sqlBulk.ColumnMappings.Add(mapSource);
//Give your Destination table name
sqlBulk.DestinationTableName =
"UploadedExcelData"
;
sqlBulk.WriteToServer(dReader);
excelConnection.Close();
UpdateRecords();
lblMsg.Text =
"File Data Uploaded Successfully... "
;
File.Delete(path);
}
catch
(Exception ex) {
lblMsg.Text =
"Something Went Wrong... Plz Check Excel File "
;
//string script = "<script>alert('" + ex.Message + "');</script>";
}
}
Upload Excelsheet Data
SQL Server Table
Up Next
Upload Excelsheet Data In SQL Server Table