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
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.
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:
- Your Laptop or Desktop where your Database is present should be in Wifi Network A.
- Your phone on which you are going to install and test the app should be using Wifi Network A.
- App will use Wifi Network A to send info to your laptop or desktop database….so a complete network is formed.
- 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.
- In short a complete network is necessary.
- 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 :
-
-
- Using stored procedure in Android from MS SQL Server.
- Bind Android Spinner using MS SQL Database.
- Android GridView using MS SQL Database.
- Filling data in Android Listview using MS SQL Database.
- Storing Images in MS SQL database in Base64 string values using Android
- Retrieving Images from MS SQL Database in Android.
- Storing images using ASP.NET webservice in MS SQL database from Android
- Android Login Application using MS SQL Server and SharedPreferences
-
For Connecting Android with MySQL Database, please see this post.