Restful API for Android Using ASP.net and SQL Server Part 1 Tutorial

(1)

Most of the android applications use an API (Application Programming Interface) for send and receive data from servers to client (App). API is very useful when application need to show dynamic data to end user. For an

instance think how Gmail app receives emails? It uses an API to communicate with Google mail server. When there is a new email, server notiﬁes the

application through API. Flipboard application also uses the same mechanism to update flipboard. When flipboard server database is updated it notifies the app then application download the data through API. This is the basic usage of

Message from Author

Following tutorial is currently not up to date and

there are lots of bug in it. Please don’t use this

tutorial to any of your production work.

Please click below link to the new tutorial in restful api Restful API for Android and IOS using ASP.NET Web API 2

Thank you

Restful API for Android using ASP.NET and SQL

Server Part 1

(2)

APIs in android.

In this tutorial I’ll show you how to create a restful API for android (JSON based API) application using ASP.net and Microsoft SQL Server Database. You can use MySQL or other databases instead of SQL server. I’ll use SQL server because it is easy to use with ASP.net (Both are Microsoft Products). I’ll try to make this tutorial simple and clear to understand. If you ﬁnd any issue please feel free to ask. You can use the comment section to communicate with me.

Demo Video

[quote_box_center]

This tutorial is quite long; to make it simple I divided it to two parts. In this tutorial (Part 1) I’ll cover the Creation of the API using ASP.net and SQL Server. In Restful API for Android Part 2 I’ll cover how to implement this API in

Android Application. [/quote_box_center]

Overview Of the tutorial

This tutorial will show you the basic implementation of the restful API. I’ll show you how to connect android application with SQL server through a restful API. In android application users able create a user account and login to the account through the restful API. Application will retrieve department details from SQL Server database and display in a listview.

Step 1: Download Resources

Download and extract the resource ﬁles need to implement the restful API from below download button.

(3)

I’ll use C# as the primary programming language in Visual Studio 2012. I will use .NET Framework 4.5 to demonstrate this tutorial. This is also work with .NET Framework 3.5 and .NET Framework 4.

[/quote_box_center]

Step 2: Creating a Project and Initialization

Open Visual Studio and create an “ASP.NET Empty Web Application” project. Project name is “JSONWebAPI”. Follow below steps to create the project.

Go to “File=>New” Click “Project” or Press “Ctrl+Shift+N” 1.

From the New Project window select Web category from left panel 2.

Then Select “ASP.NET Empty Web Application” from center panel 3.

Give Name as “JSONWebAPI” 4.

Select project “Location” from Browse (Or keep the default) 5.

Make sure “Create directory for solution” is checked 6.

Click “OK” button 7.

Create New Project

Now you need to add those “dll” ﬁles in “JsonServices (0.3.4)” folder in downloaded resource to the .NET Web Application. Follow below steps to add dll ﬁle to the project.

(4)

Right click on “JSONWebAPI” project in “Solution Explorer” 1.

From the menu click on “Add Reference” item to open the “Reference Manager”

2.

Click “Browse” from the left panel 3.

Then click “Browse” button at bottom of “Reference Manager” 4.

Then go to the location where you extract the downloaded Resource Files 5.

Then select all three “.dll” ﬁles and click “Add” button 6.

Then click “OK” button (Make sure to checked all three ﬁle) 7.

Add DLL Files as Reference

Now you need to add a “Generic Handler” to your “Solution Explore”. Follow below steps.

Go to “PROJECT” menu in Visual Studio 2012 then click “Add New Item” (Ctrl+Shift+A)

1.

From “Add New Item” window select “Web” category from left panel 2.

From center panel select “Generic Handler” 3.

Keep the default Name “Handler1.ashx” 4.

Then click “Add” button 5.

Now create three classes namely “ServiceAPI.cs”, “IServiceAPI.cs” and “DBConnect.cs”. Follow below steps to create a new class

(5)

Go to “PROJECT” menu then click “Add New Item” 1.

From left panel select “Code” category 2.

Select “Class” item from center panel 3.

Give the class name as “ServiceAPI.cs” 4.

Click “Add” button 5.

Repeat the same instruction to create “IServiceAPI.cs” and “DBConnect.cs” class.

Step 3: SQL Server Database Creation and

Manipulation

Now we jump from visual studio to SQL server. I’ll not go in-depth about SQL Server. I’ll only show you basic stuﬀ you need to know in order to complete this tutorial. If you are beginner then follow all the instruction carefully.

Firstly open the SQL Server Management Studio and connect to the server. Please keep the server name in your mind. Later you need that Server Name to connect to SQL Server from API. In my case it is “AHAMEDISHAK”

Connect To SQL Server

(6)

part.

1. Create a New Database

To create a new database called “AndroidAppDB” follow the below steps Select “New Database” from right clicking “Database” folder in “Object Explore”

1.

In “New Database” window give the Database Name as “AndroidAppDB” 2.

Then click “OK” button to create the database. 3.

2. Create Database Tables

Before create tables create a “New Query” file and select the correct database. In this case “AndroidAppDB”. If you’re a beginner keep this in mind because before doing any updates to a database we need to select database first. It is very important step you need to follow every time you update the database through a Query file.

Select Correct Database

Now “Execute” below sql queries to create table. I’ll create two tables.

SQL Queries to Create Tables and Insert Data 1 2 3 4 5 6

---Table 1: UserDetails (To store user

information)---CCRREEAATTEE TTAABBLLEE UserDetails (

id IINNTT IDENTITY,

firstName VVAARRCCHHAARR(50),

(7)

Step 4: Connect and Con�gure the SQL

Server Database to JSONWebAPI

Now come back to Visual Studio. Next part of the tutorials is to connect the newly created database to our API project. By doing this we can

programmatically retrieve and update information in database. To configure the connection open the “Web.config” file in Solution Explorer. Update your file by adding below configuration lines. Please add below lines between

<conﬁguration> and </conﬁguration>

Above conﬁguration code is used to connect SQL Server database in my PC. So this will not work for your API until you change the “Data Source” value to your “Server Name”. Simply delete the “AHAMEDISHAK” value and use your SQL “Server Name” there. “Catalog” value should be the database name. If you need to connect diﬀerent database give the correct Database Name there.

7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 userName VVAARRCCHHAARR(50), ppaasssswwoorrdd VVAARRCCHHAARR(50), );

---Table 2: Dept (To store Department

information)---CCRREEAATTEE TTAABBLLEE Dept(

nnoo IINNTT,

nnaammee VVAARRCCHHAARR(50),

PPRRIIMMAARRYY KKEEYY (nnoo) );

---Insert data to Dept

Table---IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (1,'Accounting'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (2,'Marketing');

IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (3,'Information Technology'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (4,'Networking');

IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (5,'Management'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (6,'Medical'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (7,'Electronics'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (8,'Finance'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (9,'Engineering'); IINNSSEERRTT IINNTTOO Dept VVAALLUUEESS (10,'Defense');

Web.config updates 1

(8)

“ConString” is the name of the “Connection String”. If you need to connect more than one database at same time you need to create a new “Connection String” with diﬀerent name and conﬁgurations.

Next Open the “DBConnect.cs” class. This class is used to connect with the database. Update your class according to below code. Don’t forget to add correct header ﬁles. Otherwise it will show you errors.

Now connecting and conﬁguration part is over.

Step 5: Let’s Code

Next we need to configure the API interface which is used to handle the communication between android application and API. To configure the API interface open the “Handler1.ashx” file. Delete the example codes given in the file. Update the “Handler1.ashx” according to below code. Use correct header files in your code.

DBConnect.cs 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 uussiinngg System.Configuration; uussiinngg System.Data.SqlClient; nnaammeessppaaccee JSONWebAPI { ///

/// This class is used to connect to sql server database ///

ppuubblliicc ccllaassss DBConnect

{

pprriivvaattee ssttaattiicc SqlConnection NewCon;

pprriivvaattee ssttaattiicc ssttrriinngg conStr = ConfigurationManager.ConnectionS

ppuubblliicc ssttaattiicc SqlConnection getConnection()

{

NewCon = nneeww SqlConnection(conStr);

rreettuurrnn NewCon; } ppuubblliicc DBConnect() { } } }

(9)

Now you need to do some small changes to “IServiceAPI.cs” and

“ServiceAPI.cs” class. “IServiceAPI.cs” should be an interface instead of a class. To change the “IServiceAPI.cs” to an interface open the ﬁle and change the class keyword to interface. Next “ServiceAPI.cs” class should extend the “IServiceAPI.cs” interface. See below codes to if you didn’t understand.

[quote_box_center]

Interface is similar to class ﬁle. Main diﬀerent is we only declare methods in

Handler1.ashx 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 uussiinngg JsonServices; uussiinngg JsonServices.Web; nnaammeessppaaccee JSONWebAPI {

ppuubblliicc ccllaassss Handler1 : JsonHandler

{

ppuubblliicc Handler1() {

tthhiiss.service.Name = "JSONWebAPI";

tthhiiss.service.Description = "JSON API for android appliation

InterfaceConfiguration IConfig = nneeww InterfaceConfiguration

tthhiiss.service.Interfaces.Add(IConfig); } } } IServiceAPI.cs 1 2 3 4 5 6 7 nnaammeessppaaccee JSONWebAPI {

ppuubblliicc iinntteerrffaaccee IServiceAPI

{ } } ServiceAPI.cs 1 2 3 4 5 6 7 nnaammeessppaaccee JSONWebAPI {

ppuubblliicc ccllaassss ServiceAPI : IServiceAPI

{ } }

(10)

interface. We will not implement any method in interface. Interface is extends by a class. Class which extends the interface should implement all the methods in interface.

[/quote_box_center]

Now open the” IServiceAPI.cs” interface ﬁle. Add following method declarations according to below code.

Next we need to implement declared method in “ServiceAPI.cs”. Open the “ServiceAPI.cs” class. Update the class according to below code. It is important make all methods “public”.

IServiceAPI.cs 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 uussiinngg System.Data; nnaammeessppaaccee JSONWebAPI { ///

/// This interface declare the methods need to be implement. ///

ppuubblliicc iinntteerrffaaccee IServiceAPI {

vvooiidd CreateNewAccount(ssttrriinngg firstName, ssttrriinngg DataTable GetUserDetails(ssttrriinngg userName);

bbooooll UserAuthentication(ssttrriinngg userName, ssttrriinngg DataTable GetDepartmentDetails(); } } ServiceAPI.cs 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 uussiinngg System; uussiinngg System.Data; uussiinngg System.Data.SqlClient; nnaammeessppaaccee JSONWebAPI {

ppuubblliicc ccllaassss ServiceAPI : IServiceAPI

{ SqlConnection dbConnection; ppuubblliicc ServiceAPI() { dbConnection = DBConnect.getConnection(); }

ppuubblliicc vvooiidd CreateNewAccount(ssttrriinngg firstName,

{

(11)

20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 { dbConnection.Open(); }

ssttrriinngg query = "INSERT INTO UserDetails VALUES ('"

SqlCommand command = nneeww SqlCommand(query, dbConnection);

command.ExecuteNonQuery();

dbConnection.Close();

}

ppuubblliicc DataTable GetUserDetails(ssttrriinngg userName)

{

DataTable userDetailsTable = nneeww DataTable();

userDetailsTable.Columns.Add(nneeww DataColumn(

iiff (dbConnection.State.ToString() == "Closed"

{

dbConnection.Open();

}

ssttrriinngg query = "SELECT firstName,lastName FROM UserDetails

SqlDataReader reader = command.ExecuteReader();

iiff (reader.HasRows) { wwhhiillee (reader.Read()) { userDetailsTable.Rows.Add(reader[ } } reader.Close(); dbConnection.Close(); rreettuurrnn userDetailsTable; }

ppuubblliicc bbooooll UserAuthentication(ssttrriinngg userName,

{

bbooooll auth= ffaallssee;

{

}

ssttrriinngg query = "SELECT id FROM UserDetails WHERE userName=

iiff (reader.HasRows)

(12)

We have completed the restful API in ASP.NET

Step 6: Run Restful API

In this step you can run the API in a browser. Use the Play (Run) button in menu bar to run the API or go to “DEBUG” menu and click “Start Debugging” or press F5 from the keyboard. At ﬁrst you will see a web page like below. This is because you are not pointing to the correct page of the API.

78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 auth = ttrruuee; } reader.Close(); dbConnection.Close(); rreettuurrnn auth; }

ppuubblliicc DataTable GetDepartmentDetails()

{

DataTable deptTable = nneeww DataTable();

deptTable.Columns.Add("no", ttyyppeeooff(String));

deptTable.Columns.Add("name", ttyyppeeooff(String));

{

}

ssttrriinngg query = "SELECT no,name FROM Dept;"

iiff (reader.HasRows)

{

wwhhiillee (reader.Read())

{

deptTable.Rows.Add(reader["no"], reader[

} } reader.Close(); dbConnection.Close(); rreettuurrnn deptTable; } } }

(13)

To solve this problem simply add “/Handler1.ashx” to the end of the URL. In my case complete URL is “http://localhost:7541/Handler1.ashx”.

“Handler1.ashx” is the handler of the API. If you used any other name for the handler in your API use that name instead of “Handler1”.Then you will see below webpage

API Main Page

As you can see in the output you can use this restful API with diﬀerent

technologies. But I’ll only cover how to use this in android environment. To use this restful API with Android you need to download the client interface ﬁle from

(14)

Server. To do that enter “?ANDROID” to end of the URL in browser. In my case URL is “http://localhost:7541/Handler1.ashx?ANDROID”. Then it will pop up a “Save As” dialog box to save the ﬁle in your PC. Change the ﬁle name to “APIClient.zip”.

Save the Client Interface

Step 7: Restful API for Android Part 2

tutorial

In Restful API for Android Part 2 tutorial I’ll show you how to use this restful API in a client android application. Subscribe to the website through your email or our social media pages in Google Plus, Facebook, Feed Burner and YouTube

to get instant notiﬁcation about new tutorials and videos. See the video demonstration of the tutorial how to do this tutorial from start to end. You can download the source code of this tutorial below. If you have any questions related to this tutorial feel free to ask. Comment section is open for you to communicate with me.

Share this tutorial with your friends and give a support to my website. Thank you very much for visiting TuteCentral.

(15)

Video Demonstration

Downloads

Download Sample Code

Enter your email address to subscribe to this blog and receive notiﬁcations of new posts by email.

Email Address

Subscribe