Android Tutorial Connect Android to MS SQL Server Tutorial

A new more powerful framework is now available: Remote Database Connector.

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:

<%@ 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: http://127.0.0.1/test.aspx?query=select * 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).

http://www.example.com/test1.aspx?query=select * from table_1

Now for Basic4android program:

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:

** 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 **

 

[hdtvirl]

About the firewall issue. You should test it with a real device. It is most probably an emulator issue.

@hdtvirl, this is not a valid JSON response. Only this part is valid:

Note that this is an array. So you should call NextArray.

Erel, 'rows = parser.NextArray' this is where it stops working, it never get passed the first row and it trows an error, I have tried only passing the exact line you have above and it still crashes, with the same exception regarding the array expected.

I have used the device side example you posted for the tutorial with no changes (only the code for writing the columns to the log file)

Once again thanks for your help, it is greatly appreciated.

Regards


hdtvirl

 

[hdtvirl]

Thanks Erel, it is amazing what a nights sleep will do !!!!!.

Sorted !!

Regards


hdtvirl

 

[GiovanniPolese]

Connection

I tried with a real device and connection is accepted, so it seems to be only an emulator problem..

 

[hdtvirl]

You can can any parameters you like in the Url. Which problem did you encounter?

what would a url look like to invoke a method and them pass a select screen

I have tried it and keep getting an error backfrom the service.

eg http//anywebsite.com/webservice.asmx/anymethod select statement :-select * from table

where anymethod is the method we are trting to invoke.

so our HttpUtils.PostString command would look like this.

HttpUtils.PostString("FetchData", http//anywebsite.com/webservice.asmx/anymethod, "select * from table").

Any advise would be appreciated.

Regards

hdtvirl

 

[Erel]

You should adhere to the protocol that this specific web service uses.

 

[hdtvirl]

Erel, I have tried all of that before I sent the previous reply,

This command works from the commandline of the browser.

ServerUrl = "http://192.168.250.134/Live/CustomerWebService.asmx/JSON_RunSQLStatement?Sqlstr=Select%20*%20from%20Branches"

We have embedded this command into our application on the httputils.poststring command.
ServerUrl = "http://192.168.250.134/Live/CustomerWebService.asmx/JSON_RunSQLStatement?Sqlstr=Select * from Branches"

The web service is posting back that there is no command string.

We have also posted is as "http://192.168.250.134/Live/CustomerWebService.asmx/JSON_RunSQLStatement,Select * from Branches.

None of them work, we also have a webservice method with the sql statment embedded and that works OK.

regards


hdtvirl.

 

[Erel]

If you are sending it from the browser then it is not a POST request. It is a GET request.

 

[scarecrow]

Hi, I downloaded this example to learn how it works.
When I tried to process for the first time, I´ve got a message:

Error Java Line 275 - JSON Array expected.

Did I skip something to do before try ?

I also tried to test my on server, wich returns a valid Json streamer, but error message remais de same.

I had purchased B4A few hours ago... so I´m :sign0104::sign0104:

hehehehe
Thanks for any help

Ops... Problem resolved. I built my own webservice. Thanks anyway.

 

[lachbus]

Apologies for the following noob question, as it's really not an Android problem but more of a problem getting the extensions to run on my (old) Windows 2000 server. I upgraded it from Net 1.1 to Net 2 SP2 (3.5 doesn't work on 2000).
I then installed WSE 2.0 SP2.

I still get a compilation error in Line 6:

Line 4: <%@ Import Namespace="System.Data.SqlClient" %>
Line 5: <%@ Import Namespace="System.IO" %>
Line 6: <%@ Import Namespace="System.Web.Script.Serialization" %>

I guess the extensions are somehow not installed or enabled? There's no bin directory under C:\Inetpub\wwwroot.

As I say, it's not really Erel's or Androids thing, just my noobishness to running ASP.NET scripts. But just in case you have encounteded the same problem or have a suggestion, I'd love to hear it.

Thanks, Stephan

 

[lachbus]

OK, I figured that out:
1. I had installed WSE (Web Service ENHANCEMENTS). This is not the same as Web Service EXTENSIONS. I figured out that installing Ajax (ASPAJAXExtSetup.msi) will install the extensions.
2. It still didn't work... But now I could actually find the System.Web.Extensions.dll in the Ajax directory. So I made a bin folder as Erel says, and copied this in there. Voila! It finally works...

However, while I was digging around, I came across people bitching that copying things into BIN folders is bad practice... Well, another bridge to cross some day in the future.
Thanks for reading, Stephan

 

[bjf]

aspx connect string

Probably an idiotic question but where is the actual server to witch to connect specified?

EDIT, added info in connect string

 

[jegadk]

can this be done using ASP (not .net)?
Thanx

http://www.b4x.com/forum/basic4android-updates-questions/14397-mysql-json-asp.html#post81564


Jesper

 

[scarecrow]

Hi All,
Here is another problem.

Scenario:

I´m testing HttpUtils.PostString and it works fine when I send an url without parameters.

Before test in b4a, I sent in browser:

http://myip/example.aspx

By default this url, without any parameters, returns:

[{"Col1":"1","Col2":"Laguinho com Ilha do Marcelo Cid"},{"Col1":"11","Col2":"Lago 1 do Tioayres"},{"Col1":"12","Col2":"Laguinho do Guizilini"},{"Col1":"13","Col2":"Laguinho do Edmilson"},{"Col1":"19","Col2":"Laguinho do Fernando Almeida"},{"Col1":"20","Col2":"Laguinho do Alonso"},{"Col1":"21","Col2":"Laguinho do Luís Lavia"},{"Col1":"26","Col2":"Lago do Piccirilli"},{"Col1":"27","Col2":"Lago da Vanessa Elisa"},{"Col1":"29","Col2":"Laguinho do Groo"},{"Col1":"30","Col2":""},{"Col1":"31","Col2":"Laguinho do Silvio Orsolin"},{"Col1":"32","Col2":""},{"Col1":"33","Col2":"laguinho da Rita"},{"Col1":"34","Col2":""},{"Col1":"39","Col2":"AINDA NAO CONSTRUI. DAI TER INICIADO MINHA CONTA"},{"Col1":"40","Col2":"lago da suzana"},{"Col1":"41","Col2":""},{"Col1":"42","Col2":"Laginho do Rodrigo"},{"Col1":"43","Col2":""}]

And when I try this url on b4a, like this:

HttpUtils.PostString("", "http://myip/example.aspx", "")

It works fine too !!

Here is a partial result, in b4a log:

Rows #0
col1=1
col2=Laguinho com Ilha do Marcelo Cid
Rows #1
col1=11
col2=Lago 1 do Tioayres
Rows #2
col1=12
col2=Laguinho do Guizilini
Rows #3
col1=13
col2=Laguinho do Edmilson
Rows #4
col1=19
col2=Laguinho do Fernando Almeida

Now, problems...

Problem 1.

If I pass a parameter to a URL, like this:

HttpUtils.PostString("", "http://myip/example.aspx?query=Select * from Mylake", "")
I receive an error message:

java.lang.IllegalArgumentException: Illegal character in query at index 56:

Problem 2.

I tried the same example, but changing “ “ to %20, like this:

HttpUtils.PostString("", "http://myip/example.aspx?query=Select%20*%20from%20Mylake", "")

And had no errors message, but I doesn´t work, here is log:

Starting Job:
** Activity (main) Resume ** ** Service (httputilsservice) Create ** ** Service (httputilsservice) Start ** ** Service (httputilsservice) Destroy **

Note:

All urls work fine at browser, my aspx page recognizes and work with this 3 types of query:

http://myip/example.aspx?query=Select * from Mylake
http://myip/example.aspx?query=Select * from Mylake
http://myip/example.aspx

At all of tests in browser, there´s a response.write that sends a result mentioned above.

Any ideas to solve problem 1 and problem 2 ?

Thank you.

 

[hdtvirl]

I had exactly the same problem and Erel did give me the answer straight away

You have to include the name of the server side sql string in your request, as below.

JSON_RunSQLStatement is the medthod.

and the whole Request and paramaeters string looks like this

ServerUrl = http://192.168.xxx.xxx/xxxxxxxxx/webService.asmx/JSON_RunSQLStatement?

strSQL = "Select * from tbl_customers"

Sqlstr is the server side sql string and this is what you need to fill.

HttpUtils.PostString("Job1", ServerUrl,"Sqlstr=" & strSQL)

this did the trick for me.


Bestof Luck.

Regards


hdtvirl

 

[mo3tez]

can this be done using PHP?
thanks

 

[scarecrow]

ServerUrl = http://192.168.xxx.xxx/xxxxxxxxx/webService.asmx/JSON_RunSQLStatement?
hdtvirl

Hi hdtvirl,
Thx for answer.

I think this solution only aplies to webservices, because this didn´t work for me.

My webservice isn´t a realy webservice, it´s a VB.net to response, simulating an webservice.

But I found my initial mistake and now, this method works fine for me:

http://myip/example.aspx?query=Selec...0from Mylake

Thank you

 

[scarecrow]

can this be done using PHP?
thanks

Yes. I don´t know how to do using PHP, but I don´t see any reasons that could not work.

 

[Erel]

See this tutorial for a similar PHP script: Connect Android to MySQL Database Tutorial

 

[JohnC]

ASP.Net (VB)

Hey Erel,

Is there any chance you can create a VB version of the server ASP.NET page in the first post of this thread?

I ask because I am a VB programmer, and I don't quite know how to translate some of the C# code in your ASP.NET server page into VB for ASP.NET.

 

[Erel]

I'm sorry but I'm not really familiar with VB for ASP.Net.
Maybe someone else will be able to help you.