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
Certifications
Bounties
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
761
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>";
}
}
SQL Server Table
Upload Excelsheet Data
Up Next
Upload Excelsheet Data In SQL Server Table