How to fill Data in Android Spinner using MS SQL Database

In my previous posts I explained How to connect Android with MS SQL Database, Simple Login application in android using MS SQL Database. In this post I will be explaining how to fill Data in a spinner. First of all create a database in MS SQL with name MyDatabase and create following table with name “countries”

Download Source code.

The Layout file :

Make a xml layout file in your project named spinners.xml

spinners.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:padding="10dp" >

<LinearLayout android:layout_width="match_parent" android:layout_height="match_parent" android:orientation="horizontal" >

<TextView android:layout_width="wrap_content" android:layout_height="wrap_content" android:text="COUNTRY : " />

<Spinner android:id="@+id/spinnercountry" android:layout_width="wrap_content" android:layout_height="wrap_content" />
</LinearLayout>

</LinearLayout>

Create a new Activity class in your project named : ForSpinner.java

ForSpinner.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.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.OnItemSelectedListener;
import android.widget.ArrayAdapter;
import android.widget.Spinner;
import android.widget.Toast;

public class ForSpinner extends ActionBarActivity {
Spinner spinnercountry;

String ip, db, un, passwords;
Connection connect;
PreparedStatement stmt;
ResultSet rs;

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

ip = "192.168.0.100";
un = "sa";
passwords = "123";
db = "MyDatabase";

spinnercountry = (Spinner) findViewById(R.id.spinnercountry);

connect = CONN(un, passwords, db, ip);
String query = "select CountryName from countries";

try {
connect = CONN(un, passwords, db, ip);
stmt = connect.prepareStatement(query);
rs = stmt.executeQuery();
ArrayList<String> data = new ArrayList<String>();
while (rs.next()) {
String id = rs.getString("CountryName");
data.add(id);

}
String[] array = data.toArray(new String[0]);
ArrayAdapter NoCoreAdapter = new ArrayAdapter(this,
android.R.layout.simple_list_item_1, data);
spinnercountry.setAdapter(NoCoreAdapter);
} catch (SQLException e) {
e.printStackTrace();
}
spinnercountry.setOnItemSelectedListener(new OnItemSelectedListener() {

@Override
public void onItemSelected(AdapterView<?> parent, View view,
int position, long id) {

String name = spinnercountry.getSelectedItem().toString();
Toast.makeText(ForSpinner.this, name, Toast.LENGTH_SHORT)
.show();
}

@Override
public void onNothingSelected(AdapterView<?> parent) {

}
});

}

@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;
}

}

AndroidManifest.xml

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android" package="com.hitesh.mssqlapp" android:versionCode="1" android:versionName="1.0" >

<uses-sdk android:minSdkVersion="8" android:targetSdkVersion="19" />

<uses-permission android:name="android.permission.INTERNET" />
<uses-permission android:name="android.permission.ACCESS_NETWORK_STATE" />
<uses-permission android:name="android.permission.ACCESS_WIFI_STATE" />
<uses-permission android:name="android.permission.WRITE_EXTERNAL_STORAGE" />

<application android:allowBackup="true" android:icon="@drawable/ic_launcher" android:label="@string/app_name" android:theme="@style/AppTheme" >
<activity android:name=".ForSpinner" android:label="@string/app_name" >
<intent-filter>
<action android:name="android.intent.action.MAIN" />

<category android:name="android.intent.category.LAUNCHER" />
</intent-filter>
</activity>
</application>

</manifest>

https://www.youtube.com/watch?v=mPCz0EAlWmY

Download Source code.