Tutorial Connect Android to MS SQL Server Tutorial

Discussion in 'Tutorials & Examples' started by Erel, Dec 11, 2011.

  1. Erel

    Erel Administrator Staff Member Licensed User

    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:
    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<stringobject>> list = new List<Dictionary<stringobject>>();
                        
    while (rdr.Read())
                        {
                            Dictionary<
    stringobject> d = new Dictionary<stringobject>(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).
    [​IMG]

    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
    [​IMG]

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

    Attached Files:

    Last edited: Aug 4, 2013
    Klaus Matle likes this.
  2. askez

    askez Member Licensed User

    asp

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

    Erel Administrator Staff Member Licensed User

    If you can access the server from ASP then you should be able to create a similar ASP script that passes the query to the server and returns the result. I'm not familiar with ASP at all so I don't really know.
     
  4. timothyrmyers

    timothyrmyers New Member Licensed User

    Undeclared variable 'httputils'

    Yes to classic asp. aspjson - JSON for ASP - Google Project Hosting

    I am getting a compile error. Any idea what is wrong? All I've done is copy it down and made no changes.

    Compiling code. Error
    Error parsing program.
    Error description: Undeclared variable 'httputils' is used before it was assigned any value.
    Occurred on line: 13
    HttpUtils.CallbackActivity = "Main"
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    This project requires HttpUtils (a code module and a service). It is included in the attached example.
     
  6. hasanaydin52

    hasanaydin52 Member Licensed User

    HttpUtils cant run in Another Activity

    HttpUtils run correct if in main activity.

    if you write same code second activity, Http Job never done.

    I cant find why this was.
     
  7. Erel

    Erel Administrator Staff Member Licensed User

    You should change this line with the name of the second activity:
    Code:
    HttpUtils.CallbackActivity = "Main"
     
  8. darkuni

    darkuni Member Licensed User

    Hi, I'm having some issues with this demo ... I'm essentially cutting and pasting code to my own project. The demo will compile - my code won't.

    The error I'm getting is:

    Error parsing program.
    Error description: Unknown type: jsonparser
    Are you missing a library reference?
    Occurred on line: 34
    Dim parser As JSONParser

    both parser and JSONParser are RED in my source code (they are not in the demo's source code).

    I looked at my source code in a text editor and the demo source code.

    In the demo source code (text) it has:

    Library1=core
    Library2=http
    Library3=json

    In mine? It only has

    Library1=core

    I have added the two HTTP modules to my project already.

    Any help? I guess I COULD just copy and paste those lines in a text editor, but I'd rather understand the big picture.

    Thank you!
     
  9. darkuni

    darkuni Member Licensed User

    I figured it out ... I had to CHECK the boxes next to HTTP and JSON in the LIBRARIES tab ...

    I'll get this yet!
     
  10. RobertJurado

    RobertJurado Member Licensed User

    I am using the HttpUtilsService and trying to change the original code so instead of sending a query statement like:

    http://192.168.1.103:81/test.aspx?query=select * from status

    now I can send tokens like this:

    http://192.168.1.103:81/test.aspx?query=ByCustomer&name=smith

    Any ideas, on how to change and add more parameters?


    UPDATE:
    Never mind... found my solution.....

    My issue was in my ASP.NET website... in order to read my new parameter names, I need to use :

    sRequestType = Request.Params["key1"];

    and not

    sRequestType = Request.QueryString["key1"];



    Robert J.
     
    Last edited: Feb 3, 2012
  11. Erel

    Erel Administrator Staff Member Licensed User

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

    zoran035 Member Licensed User

    Rep

    To complicated. IU do not belive that there no one lib
    fro MS SQL Server to connect like MySQL syntax
    Thanks
     
    Xicu likes this.
  13. GiovanniPolese

    GiovanniPolese Active Member Licensed User

    Connection refused

    I have the following scenario, very similar to the tutorial example. I have a "b4a" program connecting with either an asp or an aspx programs, which manage a database. Asp or/and aspx work fine, when called with browser, either on local machine, or on remote server. This is a fixed point. Also, the classic asp program works fine with the b4a program, only when the asp runs on remote server (didn't yet tried the aspx version). In other words the connection with the remote server, like "http://www.mysite/folder/program.asp?parameters etc." work. But, excluding this case, I always get the sadly famous "org.apache.http.conn.HttpHostConnectException:Connection to http://localhost refused" error Reason. In other words I cannot connect to local machine neither with classic asp as a counterpart or with aspx. I tried changing localhost with 127.0.0.1 and using also ports with no result, and, as i said, I am using the same string that works with the browser (even copy pasted). I don't see that simply "changing the localhost with the ipaddress" resolves, as somebody wrote. Can it be a problem of my machine (Windows XP) only?
     
  14. Erel

    Erel Administrator Staff Member Licensed User

    Are you using an emulator or a real device? The emulator can cause additional connectivity issues.

    If you are using a real device then it is most likely a firewall issue which blocks the incoming connection.
     
  15. GiovanniPolese

    GiovanniPolese Active Member Licensed User

    Connection

    Hi, thanks for reply.
    I am using the emulator. Though the final issue is working with real device connected to a remote server, I need to work with emulator and local machine during development. (Moreover, I tested with emulator and real device, with classic asp on remote site as a counterpart, and works, so if aspx will not, it will be an aspx issue, not b4a). In my XP machine, Basic4Android is among the Firewall's exceptions, but perharps the emulator (if it is another program, I don't know) should be put there too? If this is not the issue, I have no other ideas...
    Thanks anyway
    Giovanni
     
  16. Erel

    Erel Administrator Staff Member Licensed User

    The emulator is a different process. Try to temporary disable the firewall and see if it helps.
     
  17. GiovanniPolese

    GiovanniPolese Active Member Licensed User

    Connection

    No, disabling firewall has no effect...
     
  18. hdtvirl

    hdtvirl Active Member Licensed User

    Parsing error.

    Hello Erel, I have bit the bullet and gone with JSON in the web service, we have added a bit of code into our webservice to only be used by Android Devices.

    We are using the code from example the on the Tablet just to test the webservice, it all works ok if run from the command line and and returns a string containing the JSON array to the tablet, but when we run the app on the device we get a parsing error saying Runtime Exception JSON Array expected.

    We have tested the returned string using the tools on the json site and it passes,

    <?xml version="1.0" encoding="utf-8"?>
    <string xmlns="http://tempuri.org/">[{"EM_CODE":"001","EM_NAME":"Audrey"},{"EM_CODE":"002","EM_NAME":"Brian"},{"EM_CODE":"003","EM_NAME":"Paul"},{"EM_CODE":"004","EM_NAME":"Kate"},{"EM_CODE":"005","EM_NAME":"Mary"},{"EM_CODE":"006","EM_NAME":"Sean"},{"EM_CODE":"998","EM_NAME":"System Admin2"},{"EM_CODE":"999","EM_NAME":"System Admin"}]</string>


    This is the string returned by the webservice.

    Does this file look correct to you ? or is there too much in the returned string?


    Regards


    hdtvirl
     
  19. Mwinsor

    Mwinsor New Member Licensed User

    I am having the same problem.
     
  20. Erel

    Erel Administrator Staff Member Licensed User

    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.
     

Share This Page

Loading...