https://parallelcodes.com/connect-android-to-ms-sql-database-2/
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.
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.
