How To Import JSON Data In SQL Server Using MVC 5

Farhan Ahmed

8y93.7k30100Article

30 Comments

Join the conversation! Your thoughts help the community grow.

Wen Chang5y ago
I has some problem. Step 6 Create a folder to upload the file with the name FileUpload then Sept 7 FileUpload file disappear . Please tell me What steps did I do wrong? Thank you
araujo milan6y ago
Can you share me the code?
Chinmoy Jena7y ago
<script src="~/scripts/dataTables.bootstrap4.min.js"></script><link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
- Farhan Ahmed7y ago
  Https://cdn.datatables.net/1.10.19/js/dataTables.bootstrap4.min.js
Chinmoy Jena7y ago
Please provide below script file -
Eya Dimessi8y ago
How to get just the ID
- Farhan Ahmed8y ago
  "columns": [ { "data": "Id" }, { "data": "Name" }, { "data": "Price" }, { "data": "Quantity" } ]
- Farhan Ahmed8y ago
  Mention Id only and in table mention id. You will get id.or what ever field you want. You can do so
Rameshkumar Anga8y ago
Public ActionResult GetData() { using(DBModel db=new DBModel()) { List<Product> productList = db.Products.ToList<Product>(); return Json(new { data = productList }, JsonRequestBehavior.AllowGet); } } [the type of namespace 'DBModel' could not be found(are you missing a using directive or an assembly reference?)]
- Farhan Ahmed8y ago
  Add namespace. ctrl+.(dot) and choose namespace enter that it.
Rameshkumar Anga8y ago
Using(DBModel db=new DBModel()) "Here im getting an error ,why that problem came"
- Farhan Ahmed8y ago
  What error......
Manav Pandya8y ago
Step 9 repeated two times
- Farhan Ahmed8y ago
  By mistake
SUDHA R8y ago
Pls share the code for this.Very useful.thank u so much
maha lakshmi8y ago
Nice article
- Farhan Ahmed8y ago
  Thank you.......
Hadshana Kamalanathan8y ago
Thanks for sharing
- Farhan Ahmed8y ago
  Thank you for your comment
First Last8y ago
I click the browse button and select the products.json file from the FileUpload folder defined in the application. I click the UpLoad button and get an error: An exception of type 'System.NullReferenceException' occurred in App_Web_jhv31rd3.dll but was not handled in user code. Additional information: Object reference not set to an instance of an object. I then close the app and rerun and I see that files JSON data was loaded to the database. It seems to be failing on the foreach. It's as though the controller method has not yet completed before the foreach looping of the model starts in the view.
- First Last8y ago
  Should I not point out an error here or any other article in order to help others?
- First Last8y ago
  I'm not sure why I am getting the error as I followed your instructions and I am using your code. I thought you would know as it is your code. Am I doing something wrong? I will supply what I used from this article.
- First Last8y ago
  @{ Layout = null; } <!DOCTYPE html> <html> <head> <meta name="viewport" content="width=device-width" /> <title>How To Import JSON Data In SQL Server Using MVC 5</title> <link href="~/Content/bootstrap.min.css" rel="stylesheet" /> <script src="~/Scripts/jquery-3.3.1.min.js"></script> <script src="~/Scripts/bootstrap.min.js"></script> </head> <body> <div class="container py-5"> @using (Html.BeginForm("Upload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" })) { <div class="row"> <div class="col-sm-1 col-md-6 col-xs-12"> @ViewBag.Error @ViewBag.Success <div class="form-group"> <label>Choose a JSON File:</label> <div class="input-group"> <div class="custom-file"> <input id="JSONFile" name="JSONFile" type="file" class="custom-file-input"/> <label class="custom-file-label"></label> </div> <div class="input-group-append"> <input id="Upload" type="submit" class="btn btn-outline-primary" value="Up Load To Database"/> </div> </div> </div> </div> </div> } <table class="table table-bordered table-striped"> <tr class="bg-primary text-white"> <th>ID</th> <th>Name</th> <th>Price</th> <th>Quantity</th> </tr> @foreach (var product in Model) { <tr> <td>@product.Id</td> <td>@product.Name</td> <td>@product.Price</td> <td>@product.Quantity</td> </tr> } </table> </div> </body> </html>
First Last8y ago
How do I get the name of the file selected to appear in the box after clicking the Browse button?
- Farhan Ahmed8y ago
  Mouse hover on browse button. You will see the file name.
First Last8y ago
I had to use NuGet to load in Newtonsoft.Json. I had to use NuGet to load in Bootstrap and jQuery.
- Farhan Ahmed8y ago
  You can you use download file of jquery and bootstrap 4 or else download from NuGet your wish
Viknaraj Manogararajah8y ago
nice article, thank you for sharing
- Farhan Ahmed8y ago
  Thank you.......

Introduction

In this article. I will demonstrate how to import JSON data into SQL server using MVC 5 and entity framework. I will create a json file and upload it into FileUpload in project. I will also use jQuery datatable for searching, shorting and paging.

Step 1

Open SQL Server 2014 and create a database table.

CREATE TABLE [dbo].[Product](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](50) NULL,
[Price] [money] NULL,
[Quantity] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

Step 2

Open Visual Studio 2015, click on New Project, and create an empty web application project.

After clicking on New Project, one window will appear. Select Web from the left panel, choose ASP.NET Web Application, give a meaningful name to your project, and then click on OK, as shown in the below screenshot.

After clicking on OK one more window will appear; choose empty, check on MVC checkbox and click on OK, as shown below screenshot.

After clicking OK, the project will be created with the name of ImportJSONData_Demo.

Step 3

Add Entity Framework now. For that, right click on Models folder, select Add, then select New Item, then click on it.

After clicking on New item, you will get a window; from there, select Data from the left panel and choose ADO.NET Entity Data Model, give it the name DBModels (this name is not mandatory you can give any name) and click on Add.

After you click on "Add a window", the wizard will open, choose EF Designer from the database and click Next.

After clicking on Next a window will appear. Choose New Connection. Another window will appear, add your server name if it is local, then enter dot (.). Choose your database and click on OK.

The connection will be added. If you wish to save connect as you want. You can change the name of your connection below. It will save connection in web config then click on Next.

After clicking on NEXT another window will appear. Choose database table name as shown in the below screenshot, then click on Finish.

The Entity Framework will be added and the respective class gets generated under Models folder.

The following class will be added.

namespace ImportJSONData_Demo.Models
{
using System;
using System.Collections.Generic;
public partial class Product
{
public int Id { get; set; }
public string Name { get; set; }
public Nullable<decimal> Price { get; set; }
public Nullable<int> Quantity { get; set; }
}
}

Step 4

Right-click on Controllers folder, select Add, then choose Controller as shown in below screenshot.

After clicking on the controller, a window will appear. Choose MVC5 Controller-Empty click on Add.

After clicking on Add, another window will appear with DefaultController. Change the name to HomeController; then click on Add. HomeController will be added under Controllers folder. Remember don’t change the Controller suffix for all controllers, change only highlight, and instead of default just change Home, as shown in the below screenshot.

Complete code for Controller

using MvcImportJSONData_Demo.Models;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using Newtonsoft.Json;
namespace MvcImportJSONData_Demo.Controllers
{
public class HomeController : Controller
{
[HttpGet]
public ActionResult Index()
{
return View();
}
public ActionResult GetData()
{
using (DBModel db = new DBModel())
{
List<Product> productList= db.Products.ToList<Product>();
return Json(new { data = productList}, JsonRequestBehavior.AllowGet);
}
}
[HttpPost]
public ActionResult Upload(HttpPostedFileBase jsonFile)
{
using (DBModel db = new DBModel())
{
if (!jsonFile.FileName.EndsWith(".json"))
{
ViewBag.Error = "Invalid file type(Only JSON file allowed)";
}
else
{
jsonFile.SaveAs(Server.MapPath("~/FileUpload/" + Path.GetFileName(jsonFile.FileName)));
StreamReader streamReader = new StreamReader(Server.MapPath("~/FileUpload/" + Path.GetFileName(jsonFile.FileName)));
string data = streamReader.ReadToEnd();
List<Product> products = JsonConvert.DeserializeObject<List<Product>>(data);
products.ForEach(p =>
{
Product product = new Product()
{
Name = p.Name,
Price = p.Price,
Quantity = p.Quantity
};
db.Products.Add(product);
db.SaveChanges();
});
ViewBag.Success = "File uploaded Successfully..";
}
}
return View("Index");
}
}
}

Step 5

Click on Tools select NuGet Package Manager then choose Manage NuGet Packages for Solution click on it

After that a window will appear choose Browse type NewtonsoftJson and install package in project. As shown.

Step 6

Create a folder to upload the file with the name FileUpload.

Step 7

Right-click on index action method in the Controller. Add View window will appear with default index name unchecked (use a Layout page). Click on Add as shown in the below screenshot. The View will be added in the Views folder under Home folder with name index.

Screenshot for adding view

Step 8

Design View with HTML, cshtml and bootstrap 4 classes.

Complete Index View code

@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width" />
<title>Index</title>
<link href="~/Content/bootstrap.min.css" rel="stylesheet" />
<script src="~/scripts/jquery-3.3.1.min.js"></script>
<script src="~/scripts/bootstrap.min.js"></script>
<link href="~/Content/dataTables.bootstrap4.min.css" rel="stylesheet" />
<script src="~/scripts/jquery.dataTables.min.js"></script>
<script src="~/scripts/dataTables.bootstrap4.min.js"></script>
<script type="text/javascript" src="http://ajax.aspnetcdn.com/ajax/jquery.validate/1.12.0/jquery.validate.min.js"></script>
<style type="text/css">
.error {
color: red;
display: inline-block;
margin-bottom: -57px !important;
width: 100%;
}
</style>
<script type="text/javascript">
$(document).ready(function () {
$('form').validate({
rules: {
JSONFile: {
required: true
}
},
messages: {
JSONFile: "Choose file to upload",
}
})
$('#dataTable').DataTable({
"ajax": {
"url": "/Home/GetData",
"type": "GET",
"datatype": "json"
},
"columns": [
{ "data": "Id" },
{ "data": "Name" },
{ "data": "Price" },
{ "data": "Quantity" }
]
});
});
</script>
</head>
<body>
<div class="container py-5">
@using (Html.BeginForm("Upload", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<div class="row">
<div class="col-sm-1 col-md-6 col-xs-12">
<h5 class="text-danger">@ViewBag.Error</h5>
<h5 class="text-success">@ViewBag.Success</h5>
<div class="form-group">
<label>Choose JSON File:</label>
<div class="input-group">
<div class="custom-file">
<input id="JSONfile" name="JSONFile" type="file" class="custom-file-input" />
<label class="custom-file-label"></label>
</div>
<div class="input-group-append">
<input type="submit" class="btn btn-outline-primary" value="Upload" />
</div>
</div>
</div>
</div>
</div>
}
<table id="dataTable" class="table table-bordered table-striped">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Price</th>
<th>Quantity</th>
</tr>
</thead>
</table>
</div>
</body>
</html>