Android using Stored Procedure – MS SQL Server

Android Listview can be used to display information to the user in different formats. You can customize Android Listview according to your needs depending upon your application usage. Lately I was also puzzled on using Stored procedure made in MS SQL Server database in Android application. I have already shown how to use MS SQL Server data to use in Android Application in my previous posts. If you have not seen my previous post please visit it once, as it contains the jar library information which you will have to add to make this application work.

Below is the code of the application which uses Stored procedure made in MS SQL Server to fetch the Listview data.

SQL Script :

CREATE TABLE [dbo].[countries](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [CountryName] [nvarchar](50) NOT NULL
) ON [PRIMARY]

insert into countries values ('India')
insert into countries values ('Spain')
insert into countries values ('New Zealand')
insert into countries values ('Italy')
insert into countries values ('Istanbul')
insert into countries values ('USA')
insert into countries values ('Australia')
insert into countries values ('Brazil')




create procedure viewAllCountries
as
(
select * from countries
)
exec viewAllCountries

create procedure viewCountry
@id nvarchar(50)
as
(
select * from countries where Id=@id
)

exec viewCountry '1'

Create a new Android application project in Eclipse or Android studio (I’m using Eclipse, Android studio can also be used ). Create a new xml layout file with name

countries.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:background="#c0392b"
 android:orientation="vertical"
 android:weightSum="10" >

 <EditText
 android:id="@+id/edtid"
 android:layout_width="match_parent"
 android:layout_height="0dp"
 android:layout_weight="1"
 android:hint="ENTER ID HERE"
 android:padding="2dp"
 android:textColor="#fff"
 android:textColorHint="#fff"
 android:textSize="19sp" />

 <TextView
 android:id="@+id/lblheader"
 android:layout_width="match_parent"
 android:layout_height="0dp"
 android:layout_weight="1"
 android:padding="10dp"
 android:text="COUNTRIES : "
 android:textColor="#fff"
 android:textSize="15sp" />

 <ListView
 android:id="@+id/lstcountry"
 android:layout_width="match_parent"
 android:layout_height="0dp"
 android:layout_weight="7"
 android:background="#fff"
 android:divider="#252525"
 android:dividerHeight="1dp" >
 </ListView>

 <LinearLayout
 android:layout_width="match_parent"
 android:layout_height="0dp"
 android:layout_weight="1"
 android:orientation="horizontal"
 android:padding="5dp" >

 <Button
 android:id="@+id/btnview"
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_marginLeft="10dp"
 android:layout_marginRight="10dp"
 android:layout_weight="1"
 android:background="#fff"
 android:text="VIEW"
 android:textColor="#c0392b"
 android:textSize="20sp" />

 <Button
 android:id="@+id/btnviewall"
 android:layout_width="0dp"
 android:layout_height="wrap_content"
 android:layout_marginLeft="10dp"
 android:layout_marginRight="10dp"
 android:layout_weight="1"
 android:background="#fff"
 android:text="VIEW ALL"
 android:textColor="#c0392b"
 android:textSize="20sp" />
 </LinearLayout>

</LinearLayout>

How to execute stored procedure from Android :

You have to create a PreparedStatement variable and assign the stored procedure name to this variable and later execute this preparedstatement using the ResultSet. If resultset returns a non-empty value, use your logic to make up your application.

PreparedStatement statement = connect.prepareStatement(“EXEC viewAllCountries”);

final ArrayList list = new ArrayList();

rs = statement.executeQuery();

while (rs.next()) {

}

Create a new class file in your project with name LoadCountries.java and edit it as following :

LoadCountries.java

package com.hitesh.mssqlapp;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import android.annotation.SuppressLint;
import android.graphics.Typeface;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.view.View;
import android.widget.AdapterView;
import android.widget.AdapterView.OnItemClickListener;
import android.widget.ArrayAdapter;
import android.widget.Button;
import android.widget.EditText;
import android.widget.ListView;
import android.widget.TextView;
import android.widget.Toast;
public class LoadCountries extends ActionBarActivity {
	TextView lblheader;
	Typeface font;
	Button btnviewall,btnview;
	ListView lstcountry;
	EditText edtid;
	/*********** CONNECTION DATABASE VARIABLES **************/

	String usernameS;
	String datets;
	String call="192.168.0.100", db="mydatabase", un="sa", passwords="123";
	Connection connect;
	ResultSet rs;
	@SuppressLint("NewApi")
	private Connection CONN(String _user, String _pass, String _DB,
			String _server) {
		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;
	}
	@Override
	protected void onCreate(Bundle savedInstanceState) {
		super.onCreate(savedInstanceState);
		setContentView(R.layout.countries);
		lblheader = (TextView) findViewById(R.id.lblheader);
		lstcountry = (ListView) findViewById(R.id.lstcountry);
		btnviewall = (Button) findViewById(R.id.btnviewall);
		btnview = (Button) findViewById(R.id.btnview);
		edtid = (EditText) findViewById(R.id.edtid);
		/************* CONNECTION DATABASE VARIABLES ***************/
		
		connect = CONN(un, passwords, db, call);
		btnviewall.setOnClickListener(new View.OnClickListener() {
			@Override
			public void onClick(View v) {
				
				try {
				
					PreparedStatement statement = connect.prepareStatement("EXEC viewAllCountries");
					final ArrayList list = new ArrayList();
					rs = statement.executeQuery();
					while (rs.next()) {
					list.add(rs.getString("CountryName"));
					}
					ArrayAdapter adapter = new ArrayAdapter(LoadCountries.this,
					        android.R.layout.simple_list_item_1, list);
					
					lstcountry.setAdapter(adapter);
				} catch (SQLException e) {
					Toast.makeText(LoadCountries.this, e.getMessage().toString(),
							Toast.LENGTH_LONG).show();
				}
			}
		});
		
		btnview.setOnClickListener(new View.OnClickListener() {
			@Override
			public void onClick(View v) {
				
				try {
				
					PreparedStatement statement = connect.prepareStatement("EXEC viewCountry '"+edtid.getText().toString()+"'");
					final ArrayList list = new ArrayList();
					rs = statement.executeQuery();
					while (rs.next()) {
					list.add(rs.getString("CountryName"));
					}
					ArrayAdapter adapter = new ArrayAdapter(LoadCountries.this,
					        android.R.layout.simple_list_item_1, list);
					
					lstcountry.setAdapter(adapter);
				} catch (SQLException e) {
					Toast.makeText(LoadCountries.this, e.getMessage().toString(),
							Toast.LENGTH_LONG).show();
				}
			}
		});
		lstcountry.setOnItemClickListener(new OnItemClickListener() {
			@Override
			public void onItemClick(AdapterView<?> parent, View view,
					int position, long id) {
				// TODO Auto-generated method stub
				
				String item = lstcountry.getItemAtPosition(position).toString();
			    Toast.makeText(LoadCountries.this, item + " selected", Toast.LENGTH_LONG).show();
			}
		});
	}
}

Do you have any questions or queries on this post…please comment below and let me know what you think about this code.

If you like my work please like my facebook page .