Import Excel Data to SQL Server in ASP.NET

Then, I create other connection string for SQL Server database to export the data from the Excel sheet to be saved into the desired table.

Now, it's time to work with SQLBulkCopy to save the Excel data into SQL Server table. Here you need to give the destination table name where the data get stored in Database and at last call the WritetoServer method to inset the Excel data row into the table.

// SQL Server Connection String

string sqlConnectionString = @"Data Source=MYPC;Initial Catalog=Student;User ID=sa;Password=wintellect";

// Bulk Copy to SQL Server

SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);

bulkInsert.DestinationTableName = "Student_Record";

bulkInsert.WriteToServer(dr);

Here is the full code of the .aspx.cs file:

using System;

using System.Collections.Generic;

using System.Data.Common;

using System.Data.OleDb;

using System.Data.SqlClient;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

namespace ImportDataFromExcelToSQlServer

{

public partial class WebForm1 : System.Web.UI.Page

{

protected void btnUpload_Click(object sender, EventArgs e)

{

if (!FileUpload1.HasFile)

{

try

{

string path = string.Concat(Server.MapPath("~/Uploaded Folder/" + FileUpload1.FileName));

FileUpload1.SaveAs(path);

// Connection String to Excel Workbook

string excelConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=Excel 8.0", path);

OleDbConnection connection = new OleDbConnection();

connection.ConnectionString = excelConnectionString;

OleDbCommand command = new OleDbCommand("select * from [Sheet1$]", connection);

connection.Open();

// Create DbDataReader to Data Worksheet

DbDataReader dr = command.ExecuteReader();

// SQL Server Connection String

string sqlConnectionString = @"Data Source=MYPC;Initial Catalog=Student;User ID=sa;Password=wintellect";

// Bulk Copy to SQL Server

SqlBulkCopy bulkInsert = new SqlBulkCopy(sqlConnectionString);

bulkInsert.DestinationTableName = "Student_Record";

bulkInsert.WriteToServer(dr);

Label1.Text = "Ho Gaya";

}

catch(Exception ex)

{

Label1.Text = ex.Message;

}

}

}

}

}

Step 6

Now, build the application and run it to see the output.



Select the file using the FileUpload control and click on the Export Button.

You will see that the data of the Excel sheet is being imported into the specified table in the database.