Load Data From Database Using Web API

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 perform 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.

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.

Image Courtesy : blogs.msdn.com

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.

Create a Controller

Now, we will create a controller in our project.

Select Empty API Controller as template.

As you can notice, we have selected Empty API Controller instead of selecting a normal controller. There are few differences 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, the next step that we need to do is create 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

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

Now will add a new table to our database.

You can see the query to create a table below:

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

It seems our database is ready now.

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.

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.

@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>
   }

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.

<div id="myGrid"></div>

Please note that the 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:

public List<tbl_Subscribers> getSubscribers()
        {
            try
            {
                using (var db = new SibeeshPassionEntities())
                {
                    Subscriber sb = new Subscriber();
                    return (sb.getSubcribers(db).ToList());
                }
                
            }
            catch (Exception)
            {

                throw;
            }
        }

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.

public List<tbl_Subscribers> getSubcribers(SibeeshPassionEntities sb)
       {
           try
           {
               if (sb != null)
               {
                   return sb.tbl_Subscribers.ToList();
               }
               return null;
           }
           catch (Exception)
           {
               throw;
           }
       }

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

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

So the data is inserted. Isn’t it?

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.

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

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

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

    </style>

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

That is all. We did it. Happy coding.

Conclusion

Did I miss anything that you may think 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 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.