How to import excel data in MS SQL server database in Asp.net MVC

Here I will explain how to import excel data into MS SQL database in Asp.net MVC using C#. We map column of excel file to database table and insert relevant data to table

Introduction:

      In my previous article, we have seen How to export excel data from html table using Jquery, Today in this article, I will explain how to import excel data in Asp.net MVC.

In this example we will use C# for importing data from excel to MS SQL database

Ok, let’s start importing Excel data into MS SQL database using C# in asp.net

 

Fallow these steps in order to implement “Import Excel data in MS SQL server database in Asp.net MVC”

Step1: Create New Project.

 Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to add button > It will show new dialog window for select template > here we will select empty project > then click to ok

Step2: Create table in database.

In this example, I have created fallowing table for store Registration information from excel

CREATE TABLE [dbo].[tbl_registration] (

    [Sr_no]    INT            IDENTITY (1, 1) NOT NULL,

    [Email]    NVARCHAR (100) NULL,

    [Password] NVARCHAR (MAX) NULL,

    [Name]     VARCHAR (MAX)  NULL,

    [Address]  NVARCHAR (MAX) NULL,

    [City]     NVARCHAR (MAX) NULL,

    CONSTRAINT [PK_tbl_registration] PRIMARY KEY CLUSTERED ([Sr_no] ASC)

);

 

Run above script in MS SQL Server and click to execute button

 

Step3: Add Connection string in web.config file

 

Here I have add connection string in web.config file under Configuration section as fallows

 

<connectionStrings>

    <add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

 

 

Step4: Create a controller.

Go to Solutions Explorer > right click on controller folder > Add Controller > Enter Controller name > Select template “empty MVC Controller   ” > Add.

Here I have created a controller “HomeController”

Now we will add a view for Index action where we will in insert data in database from importing excel.

 

Step5: Add view for Index action.

Right click on index action method > Add View > Enter View name > Select empty under template dropdown > uncheck use a layout page > Add.

 

HTML Code

@{

    Layout = null;

}

 

<!DOCTYPE html>

 

<html>

<head>

    <meta name="viewport" content="width=device-width" />

    <title>Index</title>

</head>

<body>

    <div>

 

        <h2>Excel Import</h2>

 

        <form method="post" enctype="multipart/form-data">

            <div>

                <input name="file" type="file" required />

                <button type="submit">Import</button>

            </div>

        </form>

    </div>

</body>

</html>

 

Step6: Add new namespaces in Controller ( HomeController )

Here I have fallowing some more namespace for importing data from excel

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Data.OleDb;

using System.IO;

 

Step7: Add Connection string for SQL Server and OLEDB

 

SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

OleDbConnection Econ;

private void ExcelConn(string filepath)

        {

            string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);

            Econ = new OleDbConnection(constr);

 

        }

 

Step8:  Create method for import excel data

 Here I have create method for import data from Excel file in MS SQL database

 

private void InsertExceldata(string fileepath,string filename)

        {

            string fullpath = Server.MapPath("/excelfolder/") + filename;

            ExcelConn(fullpath);

            string query = string.Format("Select * from [{0}]", "Sheet1$");

            OleDbCommand Ecom = new OleDbCommand(query, Econ);

            Econ.Open();

 

            DataSet ds = new DataSet();

            OleDbDataAdapter oda = new OleDbDataAdapter(query ,Econ);

            Econ.Close();

            oda.Fill(ds);

 

            DataTable dt = ds.Tables[0];

 

            SqlBulkCopy objbulk = new SqlBulkCopy(con);

            objbulk.DestinationTableName = "tbl_registration";

            objbulk.ColumnMappings.Add("Email", "Email");

            objbulk.ColumnMappings.Add("Password", "Password");

            objbulk.ColumnMappings.Add("Name", "Name");

            objbulk.ColumnMappings.Add("Address", "Address");

            objbulk.ColumnMappings.Add("City", "City");

            con.Open();

            objbulk.WriteToServer(dt);

            con.Close();

        }

 

Step9: Add Post method in HomeController.

Here I have added a method Index() for uploading excel in sever folder and import excel data in SQL Server database

 

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.Mvc;

using System.Data;

using System.Data.SqlClient;

using System.Configuration;

using System.Data.OleDb;

using System.IO;

namespace excelimport.Controllers

{

   

    public class HomeController : Controller

    {

        //

        // GET: /Home/

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);

        OleDbConnection Econ;

        public ActionResult Index()

        {

            return View();

        }

        [HttpPost]

        public ActionResult Index(HttpPostedFileBase file)

        {

            string filename = Guid.NewGuid() + Path.GetExtension(file.FileName);

            string filepath = "/excelfolder/" + filename;

            file.SaveAs(Path.Combine(Server.MapPath("/excelfolder"), filename));

            InsertExceldata(filepath, filename);

 

            return View();

        }

 

        private void ExcelConn(string filepath)

        {

            string constr = string.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES;""", filepath);

            Econ = new OleDbConnection(constr);

 

        }

 

        private void InsertExceldata(string fileepath,string filename)

        {

            string fullpath = Server.MapPath("/excelfolder/") + filename;

            ExcelConn(fullpath);

            string query = string.Format("Select * from [{0}]", "Sheet1$");

            OleDbCommand Ecom = new OleDbCommand(query, Econ);

            Econ.Open();

 

            DataSet ds = new DataSet();

            OleDbDataAdapter oda = new OleDbDataAdapter(query ,Econ);

            Econ.Close();

            oda.Fill(ds);

 

            DataTable dt = ds.Tables[0];

 

            SqlBulkCopy objbulk = new SqlBulkCopy(con);

            objbulk.DestinationTableName = "tbl_registration";

            objbulk.ColumnMappings.Add("Email", "Email");

            objbulk.ColumnMappings.Add("Password", "Password");

            objbulk.ColumnMappings.Add("Name", "Name");

            objbulk.ColumnMappings.Add("Address", "Address");

            objbulk.ColumnMappings.Add("City", "City");

            con.Open();

            objbulk.WriteToServer(dt);

            con.Close();

        }

 

    }

}

 

Step 10: Run Application.

 

We have done all steps, Now It’s time to run the application