Loading, please wait...

A to Z Full Forms and Acronyms

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

Are you preparing for the next job interviews in Microsoft ASP.NET MVC? If yes, trust me this post will help you also we'll suggest you check out a big collection for Programming Full Forms that may help you in your interview:

List of Programming Full Forms 

Introduction:

In my previous article, we have seen How to export excel data from an 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 the 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 the above script in MS SQL Server and click to execute button.

Step3: Add Connection string in web.config file

Here I have added connection string in the web.config file under the 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 insert data in the 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 a method for import excel data

 Here I have created a method for import data from Excel file in the 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.

More Interview Questions and Answers:

A to Z Full Forms and Acronyms

Related Article

ADVERTISEMENT
ADVERTISEMENT