Read the data from Excel file and update in database table using c#.net

QuestionsCategory: C#Read the data from Excel file and update in database table using c#.net
AnynoumusAnynoumus asked 1 year ago

Read the data from Excel file and update in database table using c#.net

1 Answers
Best Answer
Mahesh DeshmaneMahesh Deshmane answered 1 year ago

Use SQL Bulk Copy to update the excel data into SQL Database.

Below is Sample C# Code which read the export all the data from sheet1 to SQL Database Student table, please change respective place holders like excel sheet path , column names, SQL connection string etc.

using System.Data;
using System.Data.OleDb;
using System.Data.SqlClient;
namespace General
{
  class Program
  {
    static void Main()
    {
       //Change the excel file path
       var excelConnectionString =
@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Core\Desktop\Blog\Demo.xlsx;Extended Properties=""Excel 12.0 Xml;HDR=YES;""";
       using (var excelConnection = new OleDbConnection(excelConnectionString))
      {
          //Change excel file column name and excel sheet name
          var excelQuery = "Select [FirstName],[LastName],[DOB] FROM [Sheet1$]";
          var excelOledbConnection = new OleDbCommand(excelQuery, excelConnection);
          excelConnection.Open();
          //Insert data into data table
          var ds = new DataSet();
          using (var dataAdapter = new OleDbDataAdapter(excelQuery, excelConnection))
         {
             excelConnection.Close();
             dataAdapter.Fill(ds);

             var excelDataTable = ds.Tables[0];
             //Change SQL Server name , database name , user name and password
            var sqlConnectionString = @"Server=ServerName\Instance;Database=TestDatabase;User Id=sa;Password=sa123;";
            using (var sqlConnection = new SqlConnection(sqlConnectionString))
            {
                 //Change the table name
                 using (var sqlBulkCopy = new SqlBulkCopy(sqlConnection) { DestinationTableName = "Student" })
                 {
                     //Map the excel and database table column's
                     sqlBulkCopy.ColumnMappings.Add("FirstName", "FirstName");
                     sqlBulkCopy.ColumnMappings.Add("LastName", "LastName");
                     sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");

                     sqlConnection.Open();
                     //Write excel data to the sql server
                     sqlBulkCopy.WriteToServer(excelDataTable);
                      
                  }

           }
        }
     }
   }
 }
}