Articles → .NET → Bulk upload in asp.net

Bulk upload in asp.net





  1. Software requirement
  2. Prerequisite knowledge
  3. Steps for execution
  4. Create a new web project
  5. Create a new table
  6. Create an excel file to upload
  7. Add web controls on the page
  8. Add code on button click
  9. Output

Software requirement




  1. visual studio (2005 or above) is installed on your machine
  2. SQL Server (2005 or above) is installed on your machine

Prerequisite knowledge




  1. How to create web project using visual studio?
  2. What are web forms?
  3. What are web controls?
  4. What are events in web application?
  5. Basics of ADO.NET
  6. Basics of SQL server objects like table.

Steps for execution




  1. Create a new web project
  2. Create a new table
  3. Create an excel file to upload
  4. Add web controls on the page
  5. Add code on button click



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




  1. File upload control – to select the excel file
  2. Button – to write the logic of bulk upload.


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 1, 2012

Query/Feedback


Your Email Id  
 
Subject 
 
Query/FeedbackCharacters remaining 250