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

Video Tutorial:

Download Full Source Code:

Download here

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>

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>

<title>Index</title>

</head>

<body>

<div>

<h2>Excel Import</h2>

<div>

<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);