In this article we will learn about Loading Data From Database in MVC using Web API. We will use Visual Studio 2015 to create a Web API and performs the operations. In this project we are going to create a database and a table called tbl_Subcribers which actually contains a list of data. We will use our normal jQuery ajax to call the Web API, once the data is ready we will format the same in an html table. I hope you will like this.

Download the source code

Background

What is a Web API?

A Web API is a kind of a framework which makes building HTTP services easier than ever. It can be used almost everywhere including wide range of clients, mobile devices, browsers etc. It contains normal MVC features like Model, Controller, Actions, Routing etc. Support all HTTP verbs like POST, GET, DELETE, PUT.

Why Web API

Image Courtesy : blogs.msdn.com

Why Web API

Image Courtesy : forums.asp.net

Using the code

We will create our project in Visual Studio 2015. To create a project click File-> New-> Project.

CRUD_in_MVC_Using_Web_API

CRUD_in_MVC_Using_Web_API

Create a control

Now we will create a control in our project.

CRUD_in_MVC_Using_Web_API_Adding_Control

Select Empty API Controller as template.

CRUD_in_MVC_Using_Web_API_Select_APIControl

As you can notice that we have selected Empty API Controller instead of selecting a normal controller. There are few difference between our normal controller and Empty API Controller.

Controller VS Empty API Controller

A controller normally render your views. But an API controller returns the data which is already serialized. A controller action returns JSON() by converting the data. You can get rid of this by using API controller.

Find out more: Controller VS API Controller

Create a model

As you all know, we write logic in a class called model in MVC. So next step what we need to do is creating a model.

Right click on Model and click Add new Items and then class. Name it as Subscribers. We are going to handle the subscriber list who all are subscribed to your website.

Now we will create a Database to our application.

Create Database

CRUD_in_MVC_Using_Web_API_Create_Database

Once you created the database, you can see your database in App_Data folder.

CRUD_in_MVC_Using_Web_API_Created_DB

Now will add a new table to our database.

CRUD_in_MVC_Using_Web_API_Adding_Table

You can see the query to create a table below.

[sql]
CREATE TABLE [dbo].[Table]
(
[SubscriberID] INT NOT NULL PRIMARY KEY,
[MailID] NVARCHAR(50) NOT NULL,
[SubscribedDate] DATETIME2 NOT NULL
)
[/sql]

It seems our database is ready now.

Load Data From Database Using Web API

The next thing we need to do is to create a ADO.NET Entity Data Model. SO shall we do that? Right click on your model and click on add new item, in the upcoming dialogue, select ADO.NET Entity Data Model.Name that file, Here I have given the name as SP. And in the next steps select the tables, stored procedures, and views you want.

Load Data From Database Using Web API

So a new file will be created in your model folder.

Now we will create an ajax call so that you can call the web API. We will use normal Ajax with type GET since we need to just retrieve the data.

A web API control does not return any action result or any json result, so we need to manually do this. We will use the index.cshtml file as our view

We are going to call our web API as follows from the view Index.cshtml.

[js]
@section scripts
{
<script>
$(document).ready(function () {
$.ajax(
{
type: ‘GET’,
dataType: ‘json’,
contentType: ‘application/json;charset=utf-8’,
url: ‘http://localhost:3064/api/Subscribers’,
success: function (data) {
try {
debugger;
var html = ‘<table><thead><th>Mail ID</th><th>Subscription ID</th><th>Subscription Date</th></thead><tbody>’;
$.each(data, function (key, val) {
debugger;
html += ‘<tr><td>’
+ ‘<a ‘ + ‘href="mailto:’ + val.MailID + ‘">’ + val.MailID + ‘</a>’ +
‘</td><td>’ + val.SubscriberID + ‘</td><td>’ + val.SubscribedDate + ‘</td></tr>’;
});
html += ‘</tbody></table>’;
$(‘#myGrid’).html(html);
} catch (e) {
console.log(‘Error while formatting the data : ‘ + e.message)
}
},
error: function (xhrequest, error, thrownError) {
console.log(‘Error while ajax call: ‘ + error)
}

}
);
});
</script>

}
[/js]

Once we get the data in the success part of the ajax call we are formulating the data in an HTML table and bind the formatted html to the element myGrid.

[html]
<div id="myGrid"></div>
[/html]

Please be noted that url you give must be correct, or else you will end up with some errors. Your actions won’t work

So we are calling our web api as http://localhost:3064/api/Subscribers. Do you remember we have already created a controller? Now we are going back to that. So we need to create an action which returns the total subscribed list from the database, so for that we will write few lines of codes as follows.

[csharp]
public List<tbl_Subscribers> getSubscribers()
{
try
{
using (var db = new sibeeshpassionEntities())
{
Subscriber sb = new Subscriber();
return (sb.getSubcribers(db).ToList());
}

}
catch (Exception)
{

throw;
}
}
[/csharp]

Here Subscriber is our model class, to get the reference of your model class in controller, you need to include the model namespace. We are getting a list of data in tbl_Subcribers type. Now we will concentrate on model class.

You can see the model action codes here.

[csharp]
public List<tbl_Subscribers> getSubcribers(sibeeshpassionEntities sb)
{
try
{
if (sb != null)
{
return sb.tbl_Subscribers.ToList();
}
return null;
}
catch (Exception)
{
throw;
}
}
[/csharp]

This will return the data which is available in the table tbl_Subcribers in sibeeshpassion DB. It seems everything is set. Now what else we need to do? Yes we need to create some entries in the table. Please see the insertion query here.

[sql]
INSERT INTO [dbo].[tbl_Subscribers] ([SubscriberID], [MailID], [SubscribedDate]) VALUES (1, N’sibikv4u@gmail.com’, N’2015-10-30 00:00:00′)
INSERT INTO [dbo].[tbl_Subscribers] ([SubscriberID], [MailID], [SubscribedDate]) VALUES (2, N’sibeesh.venu@gmail.com’, N’2015-10-29 00:00:00′)
INSERT INTO [dbo].[tbl_Subscribers] ([SubscriberID], [MailID], [SubscribedDate]) VALUES (3, N’ajaybhasy@gmail.com’, N’2015-10-28 00:00:00′)
[/sql]

So the data is inserted. Isn’t it?

Load Data From Database Using Web API

Do you know?
Like we have RouteConfig.cs in MVC, we have another class file called WebApiConfig.cs in Web API which actually sets the routes.

[csharp]
routes.MapRoute(
name: "Default",
url: "{controller}/{action}/{id}",
defaults: new { controller = "Home", action = "Index", id = UrlParameter.Optional }
);
[/csharp]

So shall we run our project and see the output? Before going to run, I suggest you to style the HTML table by applying some CSSs as follows.

[css]
<style>
table,tr,td,th {
border:1px solid #ccc;
border-radius:5px;
padding:10px;
margin:10px;
}

</style>
[/css]

If everything goes fine, you will get the output as follows.

Load Data From Database Using Web API

That is all. We did it. Have a happy coding.

Conclusion

Did I miss anything that you may think which is needed? Did you try Web API yet? Have you ever wanted to do this requirement? Could you find this post as useful? I hope you liked this article. Please share me your valuable suggestions and feedback.

Your turn. What do you think?

A blog isn’t a blog without comments, but do try to stay on topic. If you have a question unrelated to this post, you’re better off posting it on C# Corner, Code Project, Stack Overflow, Asp.Net Forum instead of commenting here. Tweet or email me a link to your question there and I’ll definitely try to help if I can.

Kindest Regards
Sibeesh Venu