CommonLounge Archive

Project Part 1: Create a REST API with ASP.NET and SQL Server Management Studio

August 30, 2018

Overview

In this project, we will create a simple REST API with database integration, and then use the API to display the data on Android UI.

Project Goals

During this project, you will:

  1. Develop a simple student database in SQL Server Management Studio (SSMS) which will store basic information of students.
  2. Make a RESTful API in ASP.NET and connect it with the database.
  3. Make an Android app with a simple and clean use of this REST API to display student information on the Android UI.
  4. Work with JSON objects in Android.
  5. Use the Volley library to query the API and fetch data.
  6. Understand how to make a local REST API accessible remotely through the command line using IIS-Express Proxy library.

Prerequisites:

  1. A desktop/laptop running Windows.
  2. Microsoft Visual Studio 2017 community edition: you can download it here
  3. Microsoft SQL Server Express Edition and Microsoft SQL Server Management Studio 2017
  4. IIS-Express Proxy open source library to make your REST API accessible over the network.

After downloading all these, you are ready to jump into the tutorial. Let’s get started!

If you get stuck at any stage, you can download all the source files for this project here.

Step 1: Create a Database

Open SQL Server Management Studio and connect to your database engine as shown:

After successful connection to the database engine, we’ll be creating a new database named Student_db. Right click on the databases option in the right taskbar of SSMS.

After successful creation of the database, we’ll create a table in this database named Student with some attributes such as ID, RollNo, SName, CollegeName, Mob, and PercentageMarks.

For creating the table, we’ll use the following “create” command as:

create table Student (ID uniquidentifier primary key, RollNo varchar(50) unique 
not null,  SName varchar(MAX) not null, CollegeName varchar(100) not null, 
Mob varchar(15) not null, PercentageMarks varchar(10) not null)

Step 2: Create Stored Procedures

We will now create some stored procedures in our database to access student data. We do this to keep the data logic separate from business logic and to reduce server access time.

A stored procedure is a set of Structured Query Language (SQL) statements with an assigned name, which are stored in a relational database management system as a group, so it can be reused and shared by multiple programs.

The first stored procedure we’re going to create is to check whether a student’s Roll No is a valid one in the database. We’ll name this stored procedure as “GetStudentCred”

CREATE procedure [dbo].[GetStudentCred]
(
@id varchar(50)   //Parameters to this stored procedure
)
as begin
if exists(select 1 from Student where RollNo=@id)  //Check if student with RollNo=id exists
begin
select 'true'  //If exists, select “true” (return true)
end
else
begin 
select 'false'
end
end

The second Stored Procedure is going to select a student record based on the RollNo provided. We’ll name it as GetStudent:

CREATE procedure [dbo].[GetStudent]
(
@id varchar(50)
)
as begin
select SName, CollegeName, Mob, PercentageMarks from Student where RollNo = @id;
end

Our database is ready after performing all these steps.

Step 3: Creating a Web API in ASP.NET

ASP.NET is a framework for building Web API’s, i.e HTTP based services on top of the .NET framework.

ASP.NET Web API is based on the “Model, View, and Controller” architecture, or the MVC architecture in short. MVC is a loosely coupled development framework as it is divided into three layers. This loose coupling helps in reducing the complexity of the web application, making it easy to maintain and providing better Test Driven Development.

Open Visual Studio Community Edition 2017, and go to File -> New -> Project and select ASP.NET Web Application (.NET Framework) as shown below, Name the project as “StudentAPI” and click OK. This will create a boilerplate environment for your WEB API.

Step 4: Add JSON Formatter to Config File

The ASP.NET Web API is configured to use XML formatter by default. So we’ll need to add the JSON formatter to the Web API.

To add JSON Formatter to the project, open “WebApiConfig.cs” file which resides in the App_start directory of your project.

Add the following lines at the end of the file:

config.Formatters.Remove(config.Formatters.XmlFormatter);  //Remove default XML formatter
//Add JSON Formatter
config.Formatters.JsonFormatter.SerializerSettings.Formatting = Newtonsoft.Json.Formatting.Indented;
//Add CamelCase Property
 config.Formatters.JsonFormatter.SerializerSettings.ContractResolver = new CamelCasePropertyNamesContractResolver();

Step 5: Add DataSet to the Project

In order to query the database, one of the several ways provided by .NET framework is using what are called DataSets.

DataSet is a tabular representation of data. Tabular representation means it represents data in a row and column format.

About DataSet: This class is counted in a disconnected architecture in .NET Framework. This means it is found in the “System.Data” namespace. The DataSet can hold records of more than one Database tables or DataTables. We can use DataSet in combination with DataAdapter Class: you can build and fill each DataTable in a DataSet with data from a data source using a DataAdapter.

To add a DataSet to the project, simply right click on the root folder of the project in the solution explorer, then Add->New Item->DataSet.

Step 6: Connect Web API to database and dataset

Open “Server Explorer” from the “View” option in the taskbar. Right click on the “Data Connections” option and select “Add Connection”. Enter your SQL Server Name and select the desired database, in our case, “Student_db”

Once the database connection is established with the Web API, you can now go ahead and add database stored procedures to the databases. For that, Open the Student.xsd dataset GUI View and drag and drop stored procedure from the server explorer into the dataset as:

Right click on the dragged stored procedure, Click on Add->Query->Use Existing Stored Procedure, then click next. You’re prompted to select from the existing stored procedure. Select the name of the dragged stored procedure, click Next. You’ll be prompted with the following window:

You have to see here if your stored procedure is returning a single value or tabular data (multiple rows are returned). Select single value for “StudentCred” and tabular data for “GetStudent” stored procedures respectively.

Now we’re all set to write our Web API REST methods.

Step 7: Write REST Methods (GET, POST)

ASP.NET framework already provides you with most of the boilerplate code for the Web API, you just have to add your business logic to the REST methods.

In order to write the logic for getting student’s data and student’s credentials, open ValuesController.cs file, where you can find the methods such as, GET, POST, DELETE etc. that have already been added.

For Getting the Student’s data corresponding to the RollNo provided, we’ll be using GET method. In order to use Custom names for the methods, you have to decorate the methods with the corresponding method you are trying to use, for example, HttpGet, HttpPost etc.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Net;
using System.Net.Http;
using System.Web.Http;
using StudentAPI.StudentTableAdapters;    //Including student data set adapters
using StudentAPI.Common;
namespace StudentAPI.Controllers
{
    public class ValuesController : ApiController
    {
        [HttpGet]  //decorator
        //accepts one parameter, i.e student’s RollNo.
        public IEnumerable<Student> getStudent(string id)    
        {
        }

In place of returning every single record of student separately, we’ll be creating a common class which holds all the records in it and we can simply return the object of that class to the calling source.

Add a new directory to the project, and name it “common”. Add a class file to this directory and and name it “class.cs”.

Its structure will look like:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace StudentAPI.Common
{
    //creating a class with all the records and their getter setter methods
    public class Student   
    {
        public string SName { get; set; }
        public string CollegeName { get; set; }
        public string Mob { get; set; }
        public string Percentage_marks { get; set; }
    }
    public class StudentCred
    {
        public string ID { get; set; }
    }
}

In order to fetch data from the stored procedure, we have to make an object of data adapter and call the function of that data adapter that is being linked to that stored procedure via dataset.

Add the following lines of code to your HttpGet Method:

List<Student> list = new List<Student>();
//creating adapter object
GetStudentTableAdapter gs = new GetStudentTableAdapter(); 
//calling method
foreach (var item in gs.GetStudent(id))
{
    list.Add(new Student { SName = item.SName, CollegeName =   item.CollegeName, Mob = item.Mob, Percentage_marks = item.Percentage_marks});
}
return list;

Adding the POST method to check Student’s Credentials:

For this function, add the decorator to the POST method as “HttpPost” and name the methods as CheckCredentials. This method will validate a Student’s RollNo in the database. We’ll be returning different HttpStatusCode to the calling route so that it can be validated against status codes.

Here’s a quick recap of the HttpStatusCodes:

  1. 200 OK: Standard response for successful HTTP requests.
  2. 201 Created: The request has been fulfilled, resulting in the creation of a new resource.
  3. 302 Found: Indicates that the record is found.
  4. 404 Not Found: The requested resource could not be found but may be available in the future. Subsequent requests by the client are permissible.

In order to query the database through stored procedure, we have to make an object of data adapter and call the function of that dataadapter that is being linked to that stored procedure via dataset.

We’ll add some code to our HttpPost method as:

//Creating Adapter Object
GetStudentCredTableAdapter r = new GetStudentCredTableAdapter();
object obj = r.GetStudentCred(Obj.ID);
String value = Convert.ToString(obj);
//Checking for value returned from stored procedure
if (value.Equals("true"))
{
    String message = Request.CreateResponse(HttpStatusCode.Found).ToString();
    return message;
}
else
{
    String message = Request.CreateErrorResponse(HttpStatusCode.NotFound, Obj.ID).ToString();
    return message;
}

Try running the API using “run” icon on the task menu.

After successful building of the project, we’ll see something like this:

Step 8: Make the Web API accessible remotely

In order to make this REST API available over the network, we’ll be using an Open source project called iisexpress-proxy. You can download it here. You can also download this project using NPM ( Package Manager) globally and run the following command:

iisexpress-proxy localPort to proxyPort

For instance, if your local port (port on which your ASP.NET Web API is running) is 32415, run the command as:

iisexpress-proxy 32415 to 3000

After running the following command, we’ll get an IP address which we can use to access the Web API remotely.

By completing these steps, our database and REST API is ready to be consumed by several applications which want to access the database.

In the next project, we’ll be creating a simple Android Application to validate a student’s RollNo and on validation, display the record of the student.

© 2016-2022. All rights reserved.