Android to Sql server Connection

Android cannot directly connect to the database server. Instead we will use HttpUtils to connect to a simple ASP.NET script that will issue the query and return the result as a JSON string

Steps to connect android application to sql server :
1. Create an asp.net script which connects to our sql server database and return the result as a JSON format.
Ex. Create a “LoginApp” website application, In this the Default.aspx file is as,

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Data;
using System.Web.Script.Serialization;
using System.Runtime.Serialization.Json;
using System.Text;
using System.Web.Services;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
String nm = Request.QueryString["name"];
String pwd = Request.QueryString["password"];
String query = "select * from UserLogin where name='" + nm + "' and password='" + pwd+"'";
SqlConnection con = new SqlConnection(getConnection());
SqlCommand cmd = new SqlCommand(query, con);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
string str=makejsonoftable(dt);
Response.Write(str);
}
private string getConnection()
{
string connStr =
System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
return connStr;
}
string makejsonoftable(DataTable table)
{
StringBuilder sb = new StringBuilder();
foreach (DataRow dr in table.Rows)
{
if (sb.Length != 0)
sb.Append(",");
sb.Append("{");
StringBuilder sb2 = new StringBuilder();
foreach (DataColumn col in table.Columns)
{
string fieldname = col.ColumnName;
string fieldvalue = dr[fieldname].ToString();
if (sb2.Length != 0)
sb2.Append(",");
sb2.Append(string.Format("{0}:\"{1}\"", fieldname, fieldvalue));
}
sb.Append(sb2.ToString());
sb.Append("}");
}
sb.Insert(0, "[");
sb.Append("]");
return sb.ToString();
}
}

2. Create Android application “LoginApplication” as,

import java.io.BufferedReader;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.ArrayList;
import org.apache.http.HttpEntity;
import org.apache.http.HttpResponse;
import org.apache.http.NameValuePair;
import org.apache.http.client.HttpClient;
import org.apache.http.client.entity.UrlEncodedFormEntity;
import org.apache.http.client.methods.HttpPost;
import org.apache.http.impl.client.DefaultHttpClient;
import org.apache.http.message.BasicNameValuePair;
import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import android.app.Activity;
import android.content.Intent;
import android.net.ParseException;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;
import android.widget.TextView;
import android.widget.Toast;
public class LoginApplicationActivity extends Activity
{
JSONArray jArray;
String result = null;
InputStream is = null;
StringBuilder sb=null;
public static final String LOG_TAG="HI..";
EditText un,pw;
TextView error;
Button ok;
int flag=0;
public static String name;
@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
un=(EditText)findViewById(R.id.et_un);
pw=(EditText)findViewById(R.id.et_pw);
ok=(Button)findViewById(R.id.btn_login);
error=(TextView)findViewById(R.id.tv_error);

ok.setOnClickListener(new View.OnClickListener()
{
public void onClick(final View v)
{
// create a new thread for httppost request
new Thread()
{
public void run()
{
ArrayList nameValuePairs = new ArrayList ();
//http post
try
{
String nm=un.getText().toString();
String pwd=pw.getText().toString();
HttpClient httpclient = new DefaultHttpClient();
//provide the values to asp.net script through query string
String qs="http://192.168.2.22/LoginApp/Default.aspx?name="+nm+"&password="+pwd;
HttpPost httppost = new HttpPost(qs);
httppost.setEntity(new UrlEncodedFormEntity(nameValuePairs));
HttpResponse response = httpclient.execute(httppost);
HttpEntity entity = response.getEntity();
is = entity.getContent();
}
catch(Exception e)
{ Log.e("log_tag", "Error in http connection"+e.toString()); }
//convert response to string
try
{
BufferedReader reader = new BufferedReader(new InputStreamReader(is,"iso-8859-1"),8);
sb = new StringBuilder();
sb.append(reader.readLine() + "\n");
String line="0";
while ((line = reader.readLine()) != null){
sb.append(line + "\n");
}
is.close();
result=sb.toString();
}catch(Exception e){
Log.e("log_tag", "Error converting result "+e.toString());
}

try
{
jArray = new JSONArray(result);
JSONObject json_data=null;
for(int i=0;i<jArray.length();i++)
{
flag=1;
json_data = jArray.getJSONObject(i);
name=json_data.getString("name");
String pwd=json_data.getString("password");
// get values from database and show on logcat view
//for invalid user, the values are null
Log.v(LOG_TAG,"name :"+name);
Log.v(LOG_TAG,"password :"+pwd);
}
if(flag==0)
{
//create new thread derived from “runOnUiThread” class to connect with View
// bcz simple Thread class can’t access View
LoginApplicationActivity.this.runOnUiThread(new Runnable()
{
public void run() {
error.setText("Invalid User...");
//Toast.makeText(LoginApplicationActivity.this, "Invalid
Login",Toast.LENGTH_LONG).show();
}
});
}
else{
Log.v("LOG-TAG","valid");
LoginApplicationActivity.this.runOnUiThread(new Runnable() {
public void run() {
error.setText("Login successful....");
// Toast.makeText(LoginApplicationActivity.this, "Login Successful",
Toast.LENGTH_LONG).show();
//for valid user, start another activity “ULogin” which says ‘hello’ to user..
Intent myIntent = new Intent(v.getContext(), Ulogin.class);
startActivityForResult(myIntent, 0);
}
});
}
}
catch(JSONException e1){
Toast.makeText(getBaseContext(), "No food Found" ,Toast.LENGTH_LONG).show();
} catch (ParseException e1) {
e1.printStackTrace();
}
}
}.start();
}
});
}

3. .Create a new activity named “Ulogin”,which starts when user is loged in ,

import android.app.Activity;
import android.content.Intent;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.TextView;
public class Ulogin extends Activity
{
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState)
{
super.onCreate(savedInstanceState);
setContentView(R.layout.login);
Button back = (Button)findViewById(R.id.btn_back);
TextView msg=(TextView) findViewById(R.id.tv_show);
msg.setText("Hello, " + LoginApplicationActivity.name);
back.setOnClickListener(new View.OnClickListener()
{
public void onClick(View view)
{
Intent myIntent = new Intent(view.getContext(),
LoginApplicationActivity.class);
startActivityForResult(myIntent, 0);
}
});
}
}

4. Gives Internet access permission in AndroidManifest.xml file as,

Also add new activity in AndroidManifest.xml file in category ,