Android using Stored Procedure – MS SQL Server

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 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://site name.com"
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();
        }
    });
}

}