Articles → .NET → Bulk Upload In Asp.Net

Bulk Upload In Asp.Net

In this article I will explain how to bulk upload the data in asp.net.

Software Requirement

Before you start reading this tutorial you should make sure that

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

Prerequisite Knowledge

Before you go ahead, it is required that you should know

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

Steps For Execution

In this tutorial I will execute certain steps for the demonstration of bulk upload in asp.net. These steps are

Create a new web project
Create a new table
Create an excel file to upload
Add web controls on the page
Add code on button click

Let us execute each step

Create A New Web Project

Create a new asp.net website. See the screen shot below for project structure

Picture showing the project structure in solution explorer

Click to Enlarge

Create A New Table

For this article I have created one table. See the screen shot below for table schema

Picture showing the schema of the database table

Click to Enlarge

Create An Excel File To Upload

For this article I have an excel file with 350 records. See the screen shot below

Click to Enlarge

Add Web Controls On The Page

Add 2 controls on the webpage

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

See the screen shot below

Picture showing the file and button control on web page

Click to Enlarge

Add Code On Button Click

On button click of upload button write the following code

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);
			}
		}
	}
}

In the first line of code I have created a connection string for excel and assigned to a string variable.

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);

In the second line I have created an OLEDB connection object

using (OleDbConnection connection = new OleDbConnection(excelConnectionString))

In the fourth line I have created the OLEDB command object and inside the method I have passed 2 parameters i.e. query and connection object. See the code section below.

Here Sheet1 is the workbook name. As the workbook name changes we have to change the query accordingly.

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

In the fifth line I have opened the connection for excel

connection.Open();

In the sixth line I have created an object of data reader and call the ExecuteReader() method.

using (DbDataReader dr = command.ExecuteReader())

In the eighth line I have created an object of SQLBulkCopy class and I am passing the connection string. Here connection string is of database where you want to upload data from excel.

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

In the subsequent code we are assigning the database table name to a property DestinationTableName. Then we are mapping the excel header with the database table column name. Here ‘Member Name’ is the excel header and ‘member_name’ is the table column. Finally WriteToServer() method will write data into database table.

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

Output

Run the program. Select the file and click on Upload

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/Feedback	Characters remaining 250	**