Import MS Excel data to SQL Server table using C#

If you already have data in MS Excel file, and want to migrate your MS Excel data to SQL Server table, follow the below steps:
MY xls file.

CREATE a SQL Table

CREATE TABLE [dbo].[tblProduct](
 [PID] [int] NULL,
 [ProdName] [varchar](50) NULL,
 [ProdDesc] [varchar](50) NULL,
 [ProdCost] [varchar](50) NULL,
 [CREATED_DATE] [datetime] NULL
)

Your MS Excel sheet and SQL table are ready, now it’s time to write C# code to import the Excel sheet into the tblProduct table.

Create a MVC Project. 
In the homecontroller class add these namespaces.

using System.Data.OleDb;
using System.Data.SqlClient;

In the homecontroller class add these variables in class level

        SqlConnection Sqlcon;
        public static string strFileName = "";
        string strfileLocation = "";
        string constr, Query, sqlconn;

In the homecontroller  create a function below.

 public void UploadBulkData(string FilePath)
        {
            string OleDBConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=Excel 12.0;Persist Security Info=False";
            string Query = string.Empty;
            int BatchId = 0;
            sqlconn = ConfigurationManager.ConnectionStrings["TransactionDB"].ConnectionString;
            Sqlcon = new SqlConnection(sqlconn);
            #region Query
           // Query = @"SELECT * FROM [Sheet1$] WHERE PID IS NOT NULL";
            Query = @"SELECT
                             UCASE(RTRIM(LTRIM(PID))) AS PID,
                             UCASE(RTRIM(LTRIM(ProdName))) AS ProdName,
                             UCASE(RTRIM(LTRIM(ProdDesc))) AS ProdDesc,
                             UCASE(RTRIM(LTRIM(ProdCost))) AS ProdCost
                             FROM [Sheet1$]";
            #endregion

            using (OleDbConnection oledbConn = new OleDbConnection(OleDBConnectionString))
            {
                OleDbCommand cmd = new System.Data.OleDb.OleDbCommand(Query, oledbConn);
                oledbConn.Open();

                DataSet ds = new DataSet();
                OleDbDataAdapter oda = new OleDbDataAdapter(Query, oledbConn);
                oledbConn.Close();
                oda.Fill(ds);
                DataTable Exceldt = ds.Tables[0];

                Exceldt.Columns.Add("CREATED_DATE", typeof(DateTime));
                foreach (DataRow row in Exceldt.Rows)
                {
                    row["CREATED_DATE"] = DateTime.Now.ToShortDateString();
                }

                //creating object of SqlBulkCopy    
                SqlBulkCopy objbulk = new SqlBulkCopy(Sqlcon);

                //assigning Destination table name    
                objbulk.DestinationTableName = "tblProduct";

                // inserting Datatable Records to DataBase
                Sqlcon.Open();
                objbulk.WriteToServer(Exceldt);
                Sqlcon.Close();
            }
        }

Now, It's time to call the above function.

        [HttpPost]
        public ActionResult Index(ProductModel model)
        {
            strfileLocation = @"D:\Projects\WebApplication1\WebApplication1\ProductSheet.xls"; //Server.MapPath("~/ProductSheet.xls");
            UploadBulkData(strfileLocation);
            return View();
        }

When you post the Index function, then it'll import all data from xls to your Sql table.