android ms sql database app

Connect Android to MS SQL Database.

In this post we will see how we can connect our Android Application to MS SQL Database server directly and perform CRUD operations. We will use JTDS.jar library for connecting with Database.

Download jtds library using this link: Jtds library.

Download Source code.

Next we will create our MS SQL Database. My Database name is CustomerDB. It is a Microsoft SQL 2014 Database.

Script:

--create database CustomerDB
--USE [CustomersDB]

CREATE TABLE [dbo].[tblUsers](
[Id] [int] IDENTITY(1,1) NOT NULL,
[UserId] [nvarchar](50) NOT NULL,
[Password] [nvarchar](50) NOT NULL,
[OnDate] [datetime] NULL DEFAULT (getdate()),
[UserRole] [nvarchar](50) NULL
)

Copy the downloaded library in your Android project’s library folder and add it as a library to your project.

Android MS SQL - Add as Library JTDS library
Android MS SQL – Add as Library JTDS library

Now let’s create the layout of our app.

Create a layout file with name signup.xml in your res > layout > folder and edit it as below:

res > layout > signup.xml:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
android:layout_width="match_parent"
android:layout_height="match_parent"
android:orientation="vertical"
android:id="@+id/lvparent"
android:padding="5dp">

<LinearLayout
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal"
android:layout_marginTop="50dp"
android:orientation="horizontal"
android:padding="5dp">

<ImageView
android:layout_width="50dp"
android:layout_height="50dp"
android:layout_gravity="center_vertical"
android:src="@drawable/user" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_gravity="center_vertical"
android:layout_marginRight="5dp"
android:fontFamily="sans-serif-black"
android:gravity="center_horizontal"
android:text="USER SIGN UP"
android:textColor="#b71540"
android:textSize="25sp" />
</LinearLayout>

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Enter Email Address"
android:textSize="16sp" />

<EditText
android:id="@+id/edtEmailAddress"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Email Address"
android:padding="5dp"
android:textColor="#2d3436" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Password"
android:textSize="16sp" />

<EditText
android:id="@+id/edtPassword"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Enter Password"
android:padding="5dp"
android:inputType="textPassword"
android:textColor="#2d3436" />

<TextView
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_marginTop="15dp"
android:fontFamily="sans-serif-condensed-medium"
android:gravity="start"
android:text="Confirm Password"
android:textSize="16sp" />

<EditText
android:id="@+id/edtConfirmPassword"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:layout_margin="2dp"
android:background="@drawable/myedittextbg"
android:fontFamily="sans-serif-condensed-medium"
android:hint="Confirm Password"
android:padding="5dp"
android:inputType="textPassword"
android:textColor="#2d3436" />

<Button
android:id="@+id/btnSignUp"
android:layout_width="wrap_content"
android:layout_height="34dp"
android:layout_gravity="center_horizontal"
android:layout_marginTop="20dp"
android:background="@drawable/mybtn"
android:fontFamily="sans-serif-condensed-medium"
android:text="SIGN UP"
android:textColor="#fff" />

<ProgressBar
android:id="@+id/pbbar"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:layout_gravity="center_horizontal" />
</LinearLayout>

Now create two drawable design files to design our buttons and edittext.

res > drawable > mybtn.xml:

<?xml version="1.0" encoding="utf-8"?>

<shape
xmlns:android="http://schemas.android.com/apk/res/android"
android:shape="rectangle"
android:padding="2dp">
<solid android:color="#b71540"/>

<corners
android:radius="5dp"/>
</shape>

res > layout > myedittextbg.xml:

<?xml version="1.0" encoding="utf-8"?>

<shape
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:shape="rectangle"
    android:padding="10dp">
    <solid android:color="#fff"/>
    <stroke android:color="#000" android:width="1dp"/>
    <corners
        android:radius="2dp"/>
</shape>

This design contains three android edittext text boxes. For getting email address, password and confirm password from users. We will add user information from our app to ms sql database.

android ms sql database app
How to connect Android app with MS SQL Database

Create a java file in your android’s project with name ConnectionHelper.java and edit it as below:

ConnectionHelper.java:

package com.app.myapplication;

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

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;

public class ConnectionHelper {


    @SuppressLint("NewApi")
    public static Connection CONN() {

        String _user = "sa";
        String _pass = "789";
        String _DB = "CustomersDB";
        String _server = "192.168.0.104";
        StrictMode.ThreadPolicy policy = new StrictMode.ThreadPolicy.Builder()
                .permitAll().build();
        StrictMode.setThreadPolicy(policy);
        Connection conn = null;
        String ConnURL = null;
        try {
            Class.forName("net.sourceforge.jtds.jdbc.Driver");
            ConnURL = "jdbc:jtds:sqlserver://" + _server + ";"
                    + "databaseName=" + _DB + ";user=" + _user + ";password="
                    + _pass + ";";
            conn = DriverManager.getConnection(ConnURL);
        } catch (SQLException se) {
            Log.e("ERRO", se.getMessage());
        } catch (ClassNotFoundException e) {
            Log.e("ERRO", e.getMessage());
        } catch (Exception e) {
            Log.e("ERRO", e.getMessage());
        }
        return conn;
    }
}

This class will return a database connection object which can be used to connect with our database and add users information.
Now create a class with name signup.java and edit it as below:

signup.java:

package com.app.myapplication;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;

import android.annotation.SuppressLint;
import android.app.Activity;
import android.app.ProgressDialog;
import android.media.tv.TvContract;
import android.os.AsyncTask;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.design.widget.Snackbar;
import android.support.v7.app.AppCompatActivity;
import android.util.Log;
import android.view.Menu;
import android.view.MenuItem;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.LinearLayout;
import android.widget.ProgressBar;
import android.widget.Spinner;
import android.widget.Toast;

public class signup extends AppCompatActivity {

    EditText edtEmailAddress, edtPassword, edtConfirmPassword;
    Button btnSignUp;
    ProgressBar progressBar;
    LinearLayout lvparent;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.signup);

        edtEmailAddress = findViewById(R.id.edtEmailAddress);
        edtPassword = findViewById(R.id.edtPassword);
        edtConfirmPassword = findViewById(R.id.edtConfirmPassword);
        btnSignUp = findViewById(R.id.btnSignUp);
        progressBar = findViewById(R.id.pbbar);
        lvparent = findViewById(R.id.lvparent);
        this.setTitle("User SignUp");

        btnSignUp.setOnClickListener(new View.OnClickListener() {
            @Override
            public void onClick(View v) {

                if (isEmpty(edtEmailAddress.getText().toString()) ||
                        isEmpty(edtPassword.getText().toString()) ||
                        isEmpty(edtConfirmPassword.getText().toString()))
                    ShowSnackBar("Please enter all fields");
                else if (!edtPassword.getText().toString().equals(edtConfirmPassword.getText().toString()))
                    ShowSnackBar("Password does not match");
                else {
                    AddUsers addUsers = new AddUsers();
                    addUsers.execute("");
                }

            }
        });
    }

    public void ShowSnackBar(String message) {
        Snackbar.make(lvparent, message, Snackbar.LENGTH_LONG)
                .setAction("CLOSE", new View.OnClickListener() {
                    @Override
                    public void onClick(View view) {

                    }
                })
                .setActionTextColor(getResources().getColor(android.R.color.holo_red_light))
                .show();
    }

    public Boolean isEmpty(String strValue) {
        if (strValue == null || strValue.trim().equals(("")))
            return true;
        else
            return false;
    }

    private class AddUsers extends AsyncTask<String, Void, String> {
        String emailId, password;


        @Override
        protected void onPreExecute() {
            super.onPreExecute();

            emailId = edtEmailAddress.getText().toString();
            password = edtPassword.getText().toString();
            progressBar.setVisibility(View.VISIBLE);
            btnSignUp.setVisibility(View.GONE);
        }

        @Override
        protected String doInBackground(String... params) {

            try {
                ConnectionHelper con = new ConnectionHelper();
                Connection connect = ConnectionHelper.CONN();

                String queryStmt = "Insert into tblUsers " +
                        " (UserId,Password,UserRole) values "
                        + "('"
                        + emailId
                        + "','"
                        + password
                        + "','User')";

                PreparedStatement preparedStatement = connect
                        .prepareStatement(queryStmt);

                preparedStatement.executeUpdate();

                preparedStatement.close();

                return "Added successfully";
            } catch (SQLException e) {
                e.printStackTrace();
                return e.getMessage().toString();
            } catch (Exception e) {
                return "Exception. Please check your code and database.";
            }
        }

        @Override
        protected void onPostExecute(String result) {

            //Toast.makeText(signup.this, result, Toast.LENGTH_SHORT).show();
            ShowSnackBar(result);
            progressBar.setVisibility(View.GONE);
            btnSignUp.setVisibility(View.VISIBLE);
            if (result.equals("Added successfully")) {
                // Clear();
            }

        }
    }

}

The AddUsers method will add the information into our database. We will first check if the information provided is correct and valid and then call AddUsers method to add data.

Download Source code.

How the connection works between Android and SQL Database in the following points:

  1. Your Laptop or Desktop where your Database is present should be in Wifi Network A.
  2. Your phone on which you are going to install and test the app should be using Wifi Network A.
  3. App will use Wifi Network A to send info to your laptop or desktop database….so a complete network is formed.
  4. Let’s say if you have your database on your laptop or desktop connected using a LAN network of network A, then your phone as to be connected to network A using the Wifi router.
  5. In short a complete network is necessary.
  6. If your SQL Database is on internet server, like Amazon AWS or any other hosting  provide then you just need an internet connection on your phone to connect your Android App to your SQL Database.

Please consider, this is not a correct way. We are making an App with security loop holes. It is necessary to use WebAPIs to connect, as it is the best option.

Please see Android MS SQL Login process for next Step.

More on Android and MS SQL :

For Connecting Android with MySQL Database, please see this post.