Articles → .NET → Bulk upload in asp.net

Bulk upload in asp.net
























Software requirement









Prerequisite knowledge

















Steps for execution

















Create a new web project




Picture showing the project structure in solution explorer

Click to Enlarge


Create a new table




Picture showing the schema of the database table

Click to Enlarge


Create an excel file to upload




Picture showing the data in excel sheet

Click to Enlarge


Add web controls on the page










Picture showing the file and button control on web page

Click to Enlarge


Add code on button click




protected void btnUpload_Click(object sender, EventArgs e) {
	string excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HRD=YES;IMEX=1'", FileUpload1.PostedFile.FileName);
	using(OleDbConnection connection = new OleDbConnection(excelConnectionString)) {
		OleDbCommand command = new OleDbCommand(("Select * FROM [Sheet1$]"), connection);
		connection.Open();
		using(DbDataReader dr = command.ExecuteReader()) {
			using(SqlBulkCopy bulkCopy = new SqlBulkCopy("your_Connection_string")) {
				bulkCopy.DestinationTableName = "Member";
				bulkCopy.ColumnMappings.Add("Member Name", "member_name");
				bulkCopy.WriteToServer(dr);
			}
		}
	}
}




string excelConnectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HRD=YES;IMEX=1'", FileUpload1.PostedFile.FileName);




using (OleDbConnection connection = new OleDbConnection(excelConnectionString))






OleDbCommand command = new OleDbCommand(("Select * FROM [Sheet1$]"), connection);




connection.Open();




using (DbDataReader dr = command.ExecuteReader())




using (SqlBulkCopy bulkCopy = new SqlBulkCopy("your_Connection_string"))




bulkCopy.DestinationTableName = "Member";
bulkCopy.ColumnMappings.Add("Member Name", "member_name");
bulkCopy.WriteToServer(dr);



Output




Picture showing the selecting the excel file and click on the upload button

Click to Enlarge

Picture showing the data inserted into database table from excel

Click to Enlarge


Posted By  -  Karan Gupta
 
Posted On  -  Saturday, December 01, 2012

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250