Skip to main content

How to fill data in Android Gridview using MS SQL Database

In my previous posts I explained How to connect Android with MS SQL DatabaseSimple 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]

The Layout file :

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

gridviews.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>