Articles → .NET → Bulk Upload In Asp.Net

Bulk Upload In Asp.Net






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