Download Sample Code- Data Upload From Excel To SQL Using CSharp


Data Upload from Excel To SQL



Excel To SQL Data Upload - Introduction

Please download attached sample code - 

Download Sample Code- Data Upload From Excel To SQL Using CSharp

.

In this article we are going to see how  to upload data from excel to page and SQL with working example. We have employee data stored in ‘EmployeeMaster.xlsx’ file stored in ‘App_Data’ folder which we are going to load on page and save into database.

Excel Connection String

string filePath = Request.PhysicalApplicationPath + @"\App_Data\EmployeeMaster.xlsx";

 //create excel connection strings for excel 2010 and above
 string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties=Excel 12.0;";

 //If you are using excel version 2007 and below use following connection string for execel
 //string excelConnectionString = @"provider=microsoft.jet.oledb.4.0;data source=" + filePath + ";extended properties=" + "\"excel 8.0;hdr=yes;\""; 

Loading Data To Page


//series of commands to bulk copy data from the excel file into our sql table
 OleDbConnection oleDbConn = new OleDbConnection(excelConnectionString);
 oleDbConn.Open();

 OleDbCommand oleDbCmd = new OleDbCommand(myExcelDatQuery, oleDbConn);
 OleDbDataReader oleDbDataReader = oleDbCmd.ExecuteReader();

 DataTable dataTable = new DataTable();
 dataTable.Load(oleDbDataReader);
 //you can apply filter if you want
 //dt.DefaultView.RowFilter = " ISNULL([EmployeeID], 0) > 100001"; 

 //Used to display data to page
 this.ExcelData_GridView.DataSource = dataTable.DefaultView.ToTable();
 ExcelData_GridView.DataBind();

 oleDbConn.Close();

Saving Data To SQL


string sqlConnectionString = @"server=YourDatabaseServerName;userid=dbUserID;password=dbUserPassword;database=databaseName;connection reset=false";
 SqlBulkCopy bulkCopy = new SqlBulkCopy(sqlConnectionString);
 bulkCopy.DestinationTableName = "YourTableName";
 //Used to store data to sql 
 bulkCopy.WriteToServer(dataTable.DefaultView.ToTable());