This tutorial is a follow up to the
previous tutorial that explained how to connect to a MySQL database using a PHP web service. This time we will use an ASP.NET web service to connect to a Microsoft SQL Server.
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.
There are several possible solutions for the web service implementation:
1. Pre-set the possible queries in the ASP script and then choose one of the queries by setting a parameter in the Http request.
2. Pass the query as-is to the database.
While the second solution is more flexible and doesn't require any changes to the ASP.NET script, it can also be more vulnerable to security attacks. For example a hacker can send a "DROP TABLE" query and erase all the data.
Making the database user a "read-only" user will protect this attack.
In this tutorial I will post the ASP.NET script that handles any query and passes it directly to the server. You can of course modify it and make it support only a set of queries.
ASP.NET code:
Code:
<%@ Page Language="C#"%>
<%@ Import Namespace="System.Collections.Generic" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Web.Script.Serialization" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<script runat="server">
protected void Page_Load(object sender, EventArgs ec)
{
using (SqlConnection cn = new SqlConnection("Database=Test1; User Id=UserName; password=Password")) //change as needed
{
using (StreamReader sr = new StreamReader(Request.InputStream, Encoding.UTF8))
{
Response.ContentType = "text/plain";
string c;
c = Request.QueryString["query"]; //for debugging with the browser
//you can set the query by adding the query parameter For ex: <a href="http://127.0.0.1/test.aspx?query=select" target="_blank">http://127.0.0.1/test.aspx?query=select</a> * from table1
if (c == null)
c = sr.ReadToEnd();
try
{
SqlCommand cmd = new SqlCommand(c, cn);
cn.Open();
SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
while (rdr.Read())
{
Dictionary<string, object> d = new Dictionary<string, object>(rdr.FieldCount);
for (int i =0;i < rdr.FieldCount;i++)
{
d[rdr.GetName(i)] = rdr.GetValue(i);
}
list.Add(d);
}
JavaScriptSerializer j = new JavaScriptSerializer();
Response.Write(j.Serialize(list.ToArray()));
} catch (Exception e)
{
Response.TrySkipIisCustomErrors = true;
Response.StatusCode = 500;
Response.Write("Error occurred. Query=" + c + "\n");
Response.Write(e.ToString());
}
Response.End();
}
}
}
</script>
This code depends on System.Web.Extensions.dll.
To use this code you should save it as a file with aspx extension and modify the connection string with your values.
You may need to put System.Web.Extensions.dll in the bin folder (you will get a compilation error when the script runs if it is required and missing).
Before trying to connect from the Basic4android program, it is recommended to test that the script is working by calling it from the browser. It will be easier to read the error message this way. To help with debugging you can send the query as a GET parameter (later it will be sent as the payload).
Code:
http://www.example.com/test1.aspx?query=select * from table_1
Now for Basic4android program:
Code:
Sub Process_Globals
Dim ServerUrl As String
ServerUrl = "http://www.example.com/test1.aspx"
End Sub
Sub Globals
End Sub
Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
HttpUtils.CallbackActivity = "Main"
HttpUtils.CallbackJobDoneSub = "JobDone"
End If
HttpUtils.PostString("Job1", ServerUrl, "SELECT col1, col2 FROM Table_1")
End Sub
Sub Activity_Resume
If HttpUtils.Complete = True Then JobDone(HttpUtils.Job)
End Sub
Sub Activity_Pause (UserClosed As Boolean)
End Sub
Sub JobDone (Job As String)
If HttpUtils.IsSuccess(ServerUrl) Then
Dim parser As JSONParser
Dim response As String
response = HttpUtils.GetString(ServerUrl)
parser.Initialize(response)
Dim rows As List
rows = parser.NextArray
'work with result
'rows is a List. Each item is a Map with the columns names as keys and the db values as the values.
For i = 0 To rows.Size - 1
Log("Rows #" & i)
Dim m As Map
m = rows.Get(i)
Log("col1=" & m.Get("col1")) 'log the values of col1 and col2
Log("col2=" & m.Get("col2"))
Next
End If
HttpUtils.Complete = False
End Sub
This is the main activity code. Calling HttpUtils.PostString sends the request. When the request completes, Sub JobDone will be executed. After parsing the response with JSON parser we get a List. Each item in the list represents a row in the results set. Each item is a Map with the column names as keys and the db values as values.
When running the example, make sure to check the logs. Errors will be posted there.
The logs after a successful run:
Code:
** Activity (main) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
Rows #0
col1=23
col2=asdasd
Rows #1
col1=12323
col2=dwqd
Rows #2
col1=0
col2=1
Rows #3
col1=0
col2=1
** Service (httputilsservice) Destroy **
Connect Android to MS SQL Server Tutorial
Linear Mode
