How to import excel data in MS SQL server database in Asp.net MVC
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:
- Top 15 Python Interview Questions
- React Interview Question
- C# Interview Questions and Answers
- Top 10 IoT Interview Questions
- DotNet Core Interview Questions and Answers
- Angular Interview Questions with Answers
- Interview questions for Asp.Net and .Net
- OOPs Interview Questions and Answers
- Blazor Interview Questions and Answer
- Top 10 Interview Questions and Answered for Web Developer of 2020
- OOPs Interview Questions and Answers in C#
- GoogleCloud Interview Questions
- Asp.net MVC Interview Questions and Answers
- Interview Questions and Answers On Selenium Webdriver
