How To Send Data From Android To SQL Server Using RESTful API

Introduction

Most of the Android applications use an API to send and receive data from server to client. API is very useful when applications need to send or receive data.

In this tutorial I will show you how to create a Restful JSON based API for Android application using ASP.NET and Microsoft SQL Server database.

Overview of the tutorial

This tutorial shows you the basic implementation of the RESTful API and will let you know ow to create a Restful API in Visual Studio with ASP.NET using C#.

How to connect android application with SQL Server Database

In this tutorial I am using Visual Studio 2015 (you can use any version) and Android 1.5.1 version with minimum SDK API 18: Android 2.2 (Froyo).

First we create an API

Step I:

Download required resources:

Download three JSON service DLL files from the link....

Step II:

Open Visual Studio and create ASP.NET Empty Web Application project.

For this:

Go to File, then New. Click Project. From new project window select web category. Then select ASP.NET Empty Web Application and give it a name. Select project location from Browse. Make sure Create Directory for Solution is checked. Click Ok.

Now add DLL files in the .Net web application.

1. Right click on the project in Solution Explorer
2. Form the menu, click on Add Reference to open the Reference Manager
3. Click on Browse from left panel
4. Then click browse button at bottom of Reference Manager
5. Go to location where save the download files
6. Select three .dll files(which you have is download) and click on add button
7. Click ok

Now you need to add a handler class. For this,

1. Go to project menu then click Add New Item.
2. From Add New Item, select web category from left panel.
3. From center select Generic Handler.
4. Give it a name or left it default and click on Add button.

Now create three new classes. To add class:

1. Go to project menu and click on Add New Item.
2. From the code category select class.
3. Give class name as SAPI.cs.
4. Click Add button.

In the same way add two more classes named ISAPI and DBConnect.

Step III:

SQL Server Database Creation

Open SQL Server Management Studio and connect with server.

Create a database:

1. Right click on Database folder in Object Explorer and select new database.
2. Give database a name (I have given serverdb) and click OK.

Now create tables. To create table:

1. Click on created database and click on New Query.
2. SQLQuery window will open.
3. Now execute the following SQL Query to create table,
   
   Sql query to create table
   

   CREATE TABLE UserData (
   Name VARCHAR(50),
   userName VARCHAR(50),
   password VARCHAR(50),
   PhoneNumber VARCHAR(50),
   CNIC VARCHAR(50),

Step IV:

Make API and Connect Database with this and go back to visual studio.

Now we programmatically save information in database. To configure a connection open web.config file in Solution Explorer. Add the following line between <configuratio> and </configuration> tag

<connectionStrings>
    <add name="ConString" connectionString="Data Source=DESKTOP-AU9BNOB;Initial Catalog=serverProjectdb;Integrated Security=True" providerName="System.Data.SqlCliesnt" />
</connectionStrings>

In these lines change data source value to your Server Name. In the initial catalog change the database name with yours.

Now open DBConnect.cs class. Copy the following code into it and change the namespace with your project name.

using System.Configuration;
using System.Data.SqlClient;

namespace TestProjectAPI
{
    /// This class is used to connect to sql server database
    public class DBConnect
    {
        private static SqlConnection NewCon;
        private static stringconStr = ConfigurationManager.ConnectionStrings["ConString"].ConnectionString;
        public static SqlConnection getConnection()
        {
            NewCon = new SqlConnection(conStr);
            return NewCon;
        }
        public DBConnect()
        {

        }
    }
}

Step V:

Configuring the API

To configure API interface open Handler.ashx file and copy the following code into it. Use correct namespace in your code.

using JsonServices;
using JsonServices.Web;

namespace TestProjectAPI
{
    public classHandler1: JsonHandler
    {
        public Handler1()
        {
            this.service.Name = " TestProjectAPI ";
            this.service.Description = "JSON API for android appliation";
            InterfaceConfiguration IConfig = new InterfaceConfiguration("RestAPI", typeof(ISAPI), typeof(SAPI));
            this.service.Interfaces.Add(IConfig);
        }
    }
}

Now we need to change ISAPI.cs class to an interface class. To do this open ISAPI.cs and the class keyword to interface. And extend the SAPI.cs class with this Interface ISAPI.cs.

 

Here's the code to modify both classes.

 

ISAPI.cs

namespace TestProjectAPI
{
    public interface ISAPI
    {

    }
}

 SAPI.cs

namespace TestProjectAPI
{
    public interface SAPI: ISAPI
    {

    }
}

Now open ISAPI.cs and copy the following code. You can change methods according to your requirements.

using System.Data;

namespace TestProjectAPI
{
    /// This interface declare the methods need to be implement.
    public interface ISAPI
    {
        void CreateNewAccount(string Name, string userName, string password, string PhoneNumber, string CNIC);
    }
}

Now open SAPI.cs and add the following code.

using System;
using System.Data;
using System.Data.SqlClient;

namespace TestProjectAPI
{
    public classSAPI: ISAPI

   {
        SqlConnection dbConnection;
        public ServiceAPI()
        {
            dbConnection = DBConnect.getConnection();
        }
        public void CreateNewAccount(string Name, string userName, string password, string PhoneNumber, string CNIC) {
            if (dbConnection.State.ToString() == "Closed") {
                dbConnection.Open();
            }

            string query = "INSERT INTO UserDetails VALUES ('" + Name + "','" + userName + "','" + password + "','" + PhoneNumber + "','" + CNIC + "');";

            SqlCommand command = new SqlCommand(query, dbConnection);
            command.ExecuteNonQuery();

            dbConnection.Close();
        }
    }

Now we have successful code API.

Step VI:

Now run the API in your browser. At first the page gives you the error.



To solve this problem, simply add “/Handler1.ashx” to the end of URL. In my case complete URL is “localhost:49702/Handler.ashx”. If you use another name of Handler class then add that name instead of Handler1.ashx.

After adding this, if you see below type of screen it means your API is working.



Now at the end of URL add “?ANDROID” and complete URL becomes “localhost:49702/Handler.ashx?ANDROID”.

Press enter and it will prompt you to save a zipped file.

Now extract that zip file and start coding with Android Studio.

Step I:

Creating Android project.

Name the project Navigation

 

Only one activity which is Main Activity.

Step II:

Open extracted file, then copy and paste the RestAPI.java file into your project. Change the package value with your package name.

Step 3:

Add the following permission into AndroidMenifest.xml

<uses-permissionandroid:name="android.permission.INTERNET"/>

Step 4:

Open the activity_main.xml file and add the following code into it.

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android" xmlns:tools="http://schemas.android.com/tools" android:layout_width="match_parent" android:layout_height="match_parent" android:paddingBottom="@dimen/activity_vertical_margin" android:paddingLeft="@dimen/activity_horizontal_margin" android:paddingRight="@dimen/activity_horizontal_margin" android:paddingTop="@dimen/activity_vertical_margin" tools:context="com.example.zubariaashraf.navigation.CreateUserActivity" android:background="@drawable/imag">

    <TextView android:id="@+id/textView1" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="@string/create_user_account" android:textAppearance="?android:attr/textAppearanceLarge" android:layout_alignParentTop="true" android:layout_centerHorizontal="true" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="textPersonName" android:ems="10" android:id="@+id/name" android:layout_below="@+id/textView1" android:layout_alignParentRight="true" android:layout_alignParentEnd="true" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@+id/uname" android:layout_below="@+id/name" android:layout_alignRight="@+id/name" android:layout_alignEnd="@+id/name" android:layout_alignLeft="@+id/name" android:layout_alignStart="@+id/name" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="textPassword" android:ems="10" android:id="@+id/upassword" android:layout_below="@+id/uname" android:layout_alignRight="@+id/uname" android:layout_alignEnd="@+id/uname" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="textPassword" android:ems="10" android:id="@+id/uRepassword" android:layout_below="@+id/upassword" android:layout_alignRight="@+id/upassword" android:layout_alignEnd="@+id/upassword" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="phone" android:ems="10" android:id="@+id/phonenum" android:layout_below="@+id/uRepassword" android:layout_alignRight="@+id/uRepassword" android:layout_alignEnd="@+id/uRepassword" />

    <EditText android:layout_width="wrap_content" android:layout_height="wrap_content" android:inputType="number" android:ems="10" android:id="@+id/cnic" android:layout_below="@+id/phonenum" android:layout_alignParentRight="true" android:layout_alignParentEnd="true" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Name:" android:id="@+id/textView" android:layout_alignParentLeft="true" android:layout_above="@+id/uname" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="User Name:" android:id="@+id/textView5" android:layout_above="@+id/upassword" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Password:" android:id="@+id/textView6" android:layout_alignBottom="@+id/upassword" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Re-Password:" android:id="@+id/textView7" android:layout_above="@+id/phonenum" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="Phone #:" android:id="@+id/textView8" android:layout_alignBottom="@+id/phonenum" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="CNIC:" android:id="@+id/textView9" android:layout_alignBottom="@+id/cnic" android:layout_alignParentLeft="true" android:layout_alignParentStart="true" />

    <Button android:id="@+id/btn_createuser" android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="@string/create_user_account" android:layout_alignParentBottom="true" android:layout_toRightOf="@+id/textView8" android:layout_toEndOf="@+id/textView8" android:layout_marginBottom="42dp" />

    <TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:id="@+id/viewid" android:layout_above="@+id/btn_createuser" android:layout_alignRight="@+id/cnic" android:layout_alignEnd="@+id/cnic" />

    <!-- <TextView
android:id="@+id/textView3"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignLeft="@+id/textView2"
android:layout_below="@+id/textView2"
android:layout_marginTop="22dp"
android:text="@string/lastname"
android:textAppearance="?android:attr/textAppearanceMedium" />

<TextView
android:id="@+id/textView4"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignRight="@+id/textView3"
android:layout_below="@+id/textView3"
android:layout_marginTop="20dp"
android:text="@string/username"
android:textAppearance="?android:attr/textAppearanceMedium" />

<TextView
android:id="@+id/textView5"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignRight="@+id/textView4"
android:layout_below="@+id/textView4"
android:layout_marginTop="20dp"
android:text="@string/password"
android:textAppearance="?android:attr/textAppearanceMedium" />

<TextView
android:id="@+id/textView2"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/firstname"
android:textAppearance="?android:attr/textAppearanceMedium"
android:layout_below="@+id/textView1"
android:layout_alignParentLeft="true"
android:layout_alignParentStart="true" />

<EditText
android:id="@+id/et_fisrtname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView2"
android:layout_alignBottom="@+id/textView2"
android:layout_alignParentRight="true"
android:ems="10"
android:layout_toRightOf="@+id/textView6"
android:layout_toEndOf="@+id/textView6">

<requestFocus />
</EditText>

<EditText
android:id="@+id/et_lastname"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView3"
android:layout_alignBottom="@+id/textView3"
android:layout_alignLeft="@+id/et_fisrtname"
android:layout_alignRight="@+id/et_fisrtname"
android:ems="10" />

<EditText
android:id="@+id/et_cu_username"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView4"
android:layout_alignBottom="@+id/textView4"
android:layout_alignLeft="@+id/et_lastname"
android:layout_alignRight="@+id/et_lastname"
android:ems="10" />

<EditText
android:id="@+id/et_cu_password"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_alignBaseline="@+id/textView5"
android:layout_alignBottom="@+id/textView5"
android:layout_alignLeft="@+id/et_cu_username"
android:layout_alignRight="@+id/et_cu_username"
android:ems="10" />

<Button
android:id="@+id/btn_createuser"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="@string/create_user_account"
android:layout_alignParentBottom="true"
android:layout_centerHorizontal="true" />

<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:inputType="textPassword"
android:ems="10"
android:id="@+id/editText"
android:layout_below="@+id/et_cu_password"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
android:layout_alignLeft="@+id/et_cu_password"
android:layout_alignStart="@+id/et_cu_password" />

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Re-password:"
android:id="@+id/textView"
android:layout_alignBottom="@+id/editText"
android:layout_alignLeft="@+id/textView5"
android:layout_alignStart="@+id/textView5" />

<EditText
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:inputType="phone"
android:ems="10"
android:id="@+id/editText2"
android:layout_below="@+id/editText"
android:layout_alignParentRight="true"
android:layout_alignParentEnd="true"
android:layout_alignLeft="@+id/editText"
android:layout_alignStart="@+id/editText" />

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="Phone #:"
android:id="@+id/textView6"
android:layout_alignBottom="@+id/editText2"
android:layout_alignLeft="@+id/textView"
android:layout_alignStart="@+id/textView" />

<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:textAppearance="?android:attr/textAppearanceMedium"
android:text="CNIC:"
android:id="@+id/textView7"
android:layout_below="@+id/editText2"
android:layout_alignLeft="@+id/textView4"
android:layout_alignStart="@+id/textView4"
android:layout_alignParentStart="false" />-->



</RelativeLayout>

Step 5

Add two more classes named

UserDetailsTable.java and JSONParser.java.
UserDetailsTable.java class map database objects to java objects.
JSONParser.java class used to parse JSONObjects

Step 6

Now open UserDetailsTable.java and add the following code into it.

package com.example.zubariaashraf.navigation;

import android.content.Intent;

/**
 * Created by Zubaria Ashraf on 8/2/2016.
 */
public class UserDetailsTable
{
    String Name, userName, password, PhoneNumber, CNIC;
    int id;

    public UserDetailsTable(String Name, String userName,
        String password, String PhoneNumber, String CNIC)
    {
        super();
        this.id = id;
        this.Name = Name;
        this.userName = userName;
        this.password = password;
        this.PhoneNumber = PhoneNumber;
        this.CNIC = CNIC;
    }

    public UserDetailsTable()
    {
        super();
        this.id = 0;
        this.Name = null;
        this.userName = null;
        this.password = null;
        this.PhoneNumber = null;
        this.CNIC = null;


    }

    public int getId()
    {
        return id;
    }
    public void setId(int id)
    {
        this.id = id;
    }

    public String getName()
    {
        return Name;
    }

    public void setName(String Name)
    {
        this.Name = Name;
    }

    public String getUserName()
    {
        return userName;
    }

    public void setUserName(String userName)
    {
        this.userName = userName;
    }

    public String getPassword()
    {
        return password;
    }

    public void setPassword(String password)
    {
        this.password = password;
    }

    public String getPhoneNumber()
    {
        return PhoneNumber;
    }

    public void setPhoneNumber(String PhoneNumber)
    {
        this.PhoneNumber = PhoneNumber;
    }

    public String getCNIC()
    {
        return CNIC;
    }

    public void setCNIC(String CNIC)
    {
        this.CNIC = CNIC;
    }

}

Now open JSONParser.java and add the following code into it. You can modify this class according to your requirements.

package com.example.zubariaashraf.navigation;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;

import android.annotation.SuppressLint;
import android.util.Log;

/**
 * Created by Zubaria Ashraf on 8/2/2016.
 */
public class JSONParser
{

    public JSONParser()
    {
        super();
    }
}

Now open Main Activity.java and add the following code into it.

package com.example.zubariaashraf.navigation;

import android.content.Context;
import android.os.AsyncTask;
import android.os.Bundle;
import android.app.Activity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.support.v4.app.NavUtils;
import android.annotation.TargetApi;
import android.content.Intent;
import android.os.Build;
import android.widget.TextView;
import android.widget.Toast;

import org.json.JSONObject;

import java.sql.SQLException;

public class CreateUserActivity extends Activity
{

    EditText etName, etUsername, etPassword, rPassword, pNumber, uCnic;
    Button btnCreateUser;
    Context context;
    boolean value;
    String username;

    @Override
    protected void onCreate(Bundle savedInstanceState)
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_create_user);
        context = this;
        Show the Up button in the action bar.
        setupActionBar();

        etName = (EditText) findViewById(R.id.name);
        etUsername = (EditText) findViewById(R.id.uname);
        etPassword = (EditText) findViewById(R.id.upassword);
        rPassword = (EditText) findViewById(R.id.uRepassword);
        pNumber = (EditText) findViewById(R.id.phonenum);
        uCnic = (EditText) findViewById(R.id.cnic);


        btnCreateUser = (Button) findViewById(R.id.btn_createuser);


        btnCreateUser.setOnClickListener(
            new View.OnClickListener()
          {

                @Override
                public void onClick(View v)
                {
                    // TODO Auto-generated method stub
                    String firstname, password, rePassword, Pnumber, ucnic;
                    firstname = etName.getText().toString();
                    username = etUsername.getText().toString();
                    password = etPassword.getText().toString();
                    rePassword = rPassword.getText().toString();
                    Pnumber = pNumber.getText().toString();
                    ucnic = uCnic.getText().toString();

                    UserDetailsTable userDetail = new UserDetailsTable(firstname, username, password, Pnumber, ucnic);
                    new AsyncCreateUser().execute(userDetail);

                }
            });
    }

    ////create user account

    protected class AsyncCreateUser extends
    AsyncTask < UserDetailsTable, Void, Void >
    {

        @Override
        protected Void doInBackground(UserDetailsTable...params)
        {

            RestAPI api = new RestAPI();
            try
            {

                api.CreateNewAccount(params[0].getName(),
                    params[0].getUserName(),
                    params[0].getPassword(),
                    params[0].getPhoneNumber(),
                    params[0].getCNIC());

            } catch (Exception e)
            {
                // TODO Auto-generated catch block
                Log.d("AsyncCreateUser", e.getMessage());

            }
            return null;
        }

        @Override
        protected void onPostExecute(Void result)
        {
            store(username);
            Intent i = new Intent(CreateUserActivity.this, LoginActivity.class);
            startActivity(i);
        }

    }


    @Override
    public boolean onOptionsItemSelected(MenuItem item)
    {
        switch (item.getItemId())
        {
            case android.R.id.home:

                NavUtils.navigateUpFromSameTask(this);
                return true;
        }
        return super.onOptionsItemSelected(item);
    }

}

Step 7

Now run the application

To run this, the application should know the correct URL of the API. So to add this open the RestAPI class and here you will see an attribute called urlString with "http://DESKTOP-AU9BNOB/Handler1.ashx" URL as in my RestAPI.

So to run this application on emulator you need to host your API on local server. But if you want to run application on real android device then you have to host API in public hosting site.

Run the application and enjoy.

After hosting API on local or public hosting server change the URL from the RestAPI.

 

Read more articles on Android:

• Using Android Flashlight With A Widget
• Android Studio And Git Configuration - Day Four