How to fill data in Android Gridview 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 gridview. First of all create a database in MS SQL with name MyDatabase and create following table with name “countries”

USE [mydatabase]
GO
/****** Object: Table [dbo].[Countries] Script Date: 10/04/2015 19:20:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Countries](
[ID] [int] IDENTITY(1,1) NOT NULL,
[CountryName] [nvarchar](50) NOT NULL,
[Continent] [nvarchar](50) NULL
) ON [PRIMARY]

Download Source code.

The Layout file :

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

gridiews.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="#000" android:orientation="vertical" >

<GridView android:id="@+id/gridview" android:layout_width="fill_parent" android:layout_height="fill_parent" android:gravity="center" android:numColumns="auto_fit" android:stretchMode="columnWidth" >
</GridView>

</LinearLayout>

I am going to use a custom layout for the gridview to display data. Make a new layout file named templateforgrid.xml in the layout folder.

templateforgrid.xml

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="wrap_content" android:layout_height="wrap_content" android:background="#000" android:orientation="vertical" android:padding="5dp" >

<TextView android:id="@+id/txtcountry" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="5px" android:text="COUNTRY" android:textStyle="bold" android:textColor="#ff3c3c" android:textSize="16sp" >
</TextView>

<TextView android:id="@+id/txtcontinent" android:layout_width="wrap_content" android:layout_height="wrap_content" android:layout_marginTop="5px" android:text="CONTINENT" android:textStyle="bold|italic" android:textColor="#0080ff" android:textSize="16sp" >
</TextView>

</LinearLayout>

 

Make a new activity class file in your package with name ForGrid.java (I was not getting any good name!!).

ForGrid.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.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import android.annotation.SuppressLint;
import android.os.Bundle;
import android.os.StrictMode;
import android.support.v7.app.ActionBarActivity;
import android.util.Log;
import android.widget.GridView;
import android.widget.SimpleAdapter;

public class ForGrid extends ActionBarActivity {
GridView gridview;
ArrayList<String> arrayList;

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

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

ip = "192.168.0.100";
un = "sa";
passwords = "123";
db = "MyDatabase";
gridview = (GridView) findViewById(R.id.gridview);

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

try {
connect = CONN(un, passwords, db, ip);
Statement statement = connect.createStatement();
rs = statement.executeQuery(query);
List<Map<String, String>> data = null;
data = new ArrayList<Map<String, String>>();

while (rs.next()) {
Map<String, String> datanum = new HashMap<String, String>();
datanum.put("A", rs.getString("CountryName"));
datanum.put("B", rs.getString("Continent"));

data.add(datanum);
}
String[] from = { "A", "B" };
int[] views = { R.id.txtcountry, R.id.txtcontinent };
final SimpleAdapter ADA = new SimpleAdapter(ForGrid.this,
data, R.layout.templateforgrid, from, views);
gridview.setAdapter(ADA);
} catch (SQLException e) {
e.printStackTrace();
}

}

@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=".ForGrid" 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.