How to retrieve data from database in Asp.net web API

Introduction:

      In my previous article, we have seen How to insert data into database using Web API 2 in Asp.net MVC, Today in this article, I will explain how to retrieve data from database using Web API in Asp.net MVC.

In this example we will use Asp.net MVC 5 for retrieve data from database using Asp.net Web API.

Fallow these steps in order to implement “Retrieve data from database using Web API in in Asp.net MVC”

Step1: Create table and Stored Procedure.

In this example, I have created fallowing table and stored procedure for retrieve customer detail.

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)

);

The design of the table look like this as follows:

Now create stored procedure for retrieve data from database:

Create proc SpgetDetailbyid

@Sr_no int

as

begin

Select * from tbl_registration where Sr_no=@Sr_no

End

Run above script in MS SQL Server and click to execute button

Step2: Create New Project.

 Go to File > New > Project > Web > Asp.net MVC web project > Enter Application Name > Select your project location > click to OK button > It will show new dialog window for select template > here we will select Web API project > then click to ok

Step3: Add Connection string in web.config file

Here I have add connection string in web.config file under Configuration section as fallows

<connectionStrings>

    <add name="con" connectionString="Data Source=.;Initial Catalog=test;Integrated Security=True" providerName="System.Data.SqlClient"/>

  </connectionStrings>

Step4: Create Database Access layer.

Go to Solutions Explorer > right click on project solution> Add New Folder > Enter Folder name (Here I rename it as ‘database_access_layer’) > right click on folder > Add new class. 

Now add the fallowing method into Database access class named GetRecordbyid that access record on the basis of parameter id.

public DataSet GetRecordbyid(int id)

        {

            SqlCommand com = new SqlCommand("SpgetDetailbyid", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Sr_no", id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

The entire db class file will be fallows 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace MVCAPI.database_Access_Layer

{
    public class db

    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["con"].ConnectionString);
       public DataSet GetRecordbyid(int id)

        {

            SqlCommand com = new SqlCommand("SpgetDetailbyid", con);

            com.CommandType = CommandType.StoredProcedure;

            com.Parameters.AddWithValue("@Sr_no", id);

            SqlDataAdapter da = new SqlDataAdapter(com);

            DataSet ds = new DataSet();

            da.Fill(ds);

            return ds;

        }

    }

}

Step5: Create Get method

 Create a Get method in the Asp.net Web API Controller Class.

Open the ValuesController , delete the existing method and create the Getrecord method and call  the database access class (db.cs) method as the fallowing:

public class ValuesController : ApiController
    {
        database_Access_Layer.db dblayer = new database_Access_Layer.db();
        public DataSet Getrecord(int id)

        {
            DataSet ds = dblayer.GetRecordbyid(id);

            return ds;
        }
    }

Here ValuesController class is inherited from APiController class and we have created the method Getrecord that calls method named Getrecordbyid.

The entire ValuesContoller class will be fallows:

using System;

using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using System.Data;
using System.Data.SqlClient;
namespace MVCAPI.Controllers

{
    public class ValuesController : ApiController

    {
        database_Access_Layer.db dblayer = new database_Access_Layer.db();

        public DataSet Getrecord(int id)

        {

            DataSet ds = dblayer.GetRecordbyid(id);

            return ds;

        }
    }

}

Step6: Configure Asp.net Web API routing

We need to configure the routing of incoming request and return data as JSON. Let us create WebApiConfig.cs by right-clicking on the App_start folder and create the fallowing method as follows:

public static void Register(HttpConfiguration config)

        {
            config.Routes.MapHttpRoute(

            name: "DefaultApi",

            routeTemplate: "api/{controller}/{action}/{id}",

            defaults: new { id = RouteParameter.Optional }

        );
            var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(t => t.MediaType == "application/xml");

            config.Formatters.XmlFormatter.SupportedMediaTypes.Remove(appXmlType);

        }

The entire WebApiController.cs will be as follows:

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web.Http;

 

namespace MVCAPI

{

    public static class WebApiConfig

    {

        public static void Register(HttpConfiguration config)

        {

            config.Routes.MapHttpRoute(

            name: "DefaultApi",

            routeTemplate: "api/{controller}/{action}/{id}",

            defaults: new { id = RouteParameter.Optional }

        );

 

            var appXmlType = config.Formatters.XmlFormatter.SupportedMediaTypes.FirstOrDefault(t => t.MediaType == "application/xml");

            config.Formatters.XmlFormatter.SupportedMediaTypes.Remove(appXmlType);

        }

    }

}

Step7: Call Asp.net Web API Controller method

Call Asp.net Web API Controller method from the html page using JSON.

Now we need to call to Web API Controller method from html page. To do this we need to create a JSON method using JQuery as follows:

<script type="text/javascript">

        $(function () {

            $("#btnAdd").click(function () {

                alert('hi');

                $.ajax({

                    type: "GET",

                    url: '/api/values/Getrecord?id=2',

                    success: function (data, status, xhr) {

                        alert("The result is : " + data.Table[0].Email);

                    },

                    error: function (xhr) {

                        alert(xhr.responseText);

                    }

                });

            });

        });

 

    </script>

 Now the entire test.html page will be fallows:

<!DOCTYPE html>

<html>

<head>

    <title></title>

  

    <script src="Scripts/jquery-1.8.2.min.js"></script>

    <script type="text/javascript">

        $(function () {

            $("#btnAdd").click(function () {

                alert('hi');

                $.ajax({

                    type: "GET",

                    url: '/api/values/Getrecord?id=2',

                    success: function (data, status, xhr) {

                        alert("The result is : " + data.Table[0].Email);

                    },

                    error: function (xhr) {

                        alert(xhr.responseText);

                    }

                });

            });

        });

 

    </script>

</head>

<body>

    <input id="btnAdd" type="button" value="Add" />

</body>

</html>

Also we need to add a JQuery library reference so don’t forgot to add it.

Step 8: Run Application.

We have done all steps, now it’s time to run the application