Android Tutorial Connect Android to MS SQL Server Tutorial

Status
Not open for further replies.
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:
B4X:
<%@ 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).
SS-2011-12-11_16.03.27.png


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).
B4X:
http://www.example.com/test1.aspx?query=select * from table_1

SS-2011-12-11_16.11.33.png


Now for Basic4android program:
B4X:
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:
B4X:
** 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 **
 

Attachments

  • MsSQL.zip
    6.3 KB · Views: 2,779
Last edited:

Juzer Hussain

Active Member
Licensed User
Hi Erel,

I m trying to read a datatable (2000 records, 22 columns, max size 200 varchar) thru GetString().It takes sometime in downloading and When I loop thru records it stops at a line saying ArrayOutOfBounds. I suspect there is some limit to it. Is there any other better method of doing this.

Pls guide me.

Thanks
Juzer
 

Juzer Hussain

Active Member
Licensed User
Hi Erel,

Continuing with above......
I m using Download2 function and in Sub JobDone GetString is returning value in response(string) variable
The line where error is coming is
If m.Get("FType")="CS" Then
gClassSection(k)=m.Get("SubCategory")
k=k+1
End If
Error msg is
java.lang.ArrayIndexOutOfBoundsException: length=0; index=0

Pls guide me.

Thanks
Juzer
 

Juzer Hussain

Active Member
Licensed User
RESOLVED

Sorry array declaration was the problem in gClassSection() (it should be gClassSection(n)) where n is some number

Juzer
 

Eduardo Bardales

Member
Licensed User
Hi I´ve got this Web service, run Ok


**************************************************
This is the code in VB2010 for connect to sql server database
********************************************
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports Microsoft.SqlServer
Imports System.Data
Imports System.Data.SqlClient

<System.Web.Services.WebService(Namespace:="http://192.168.1.39:2272/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class SqlService
Inherits System.Web.Services.WebService

<WebMethod()> _
Public Sub InsertaRegistro(ByVal CadenaInsercion As String) ' As String
Try
Dim CadConeccion As String = "Server=VICTOR\SQLEXPRESS;Database=pruebawebservice;user Id=sa; Password=Siesac2016;"
Dim Con As SqlConnection = New SqlConnection(CadConeccion)
Dim Comm As SqlCommand = New SqlCommand(CadenaInsercion, Con)
Con.Open()
Comm.ExecuteNonQuery()
Con.Close()
Catch err As Exception
End Try
End Sub
End Class
*********************************************
I need to send data from B4A textedits to the webservice.
How can I do it ?
I have this code in b4a
**********************************************
Dim ServerUrl as string
ServerUrl = "http://192.168.1.39:2272/SqlService.asmx?op=InsertaRegistro"
job1.PostString(ServerUrl, "INSERT INTO tRegistro (Equipo, FechaHora) VALUES('" & txtEquipo & "'" & ",'" & txtFechaHora & "'"))
**********************************************
But appear this message
Error: org.apache.http.conn.ConnectTimeoutException: Connect to /192.168.1.39:2272 timed out

**********************************************
Helpme please!!
 

Sytek

Active Member
Licensed User
Hello, in this tutorial or example anyone can acces the server right?; Knowing the correct link I mean. or how can I encrypt and decrypt the link?

Thank's in advance
 

Sytek

Active Member
Licensed User
Yes.

You can obfuscate the link string (see the obfuscation tutorial).

Switch to RDC for a better solution.

Hello Erel
Thank's for the reply.
I Know about RDC; But I'm testing this one.

See... this is a First post Example link.
http://www.example.com/test1.aspx?query=select * from table_1
Now it returns a json string and anybody can read the retrieved query (via web browser)
What I need is to encrypt the link like this
http://www.example.com/test1.aspx?query=abcdef=rfttdrertt (not base64 'cause that's too easy to decode)
...and in the site part decrypt the query string,

Thank's in advance.
 

Helmut S

Member
Licensed User
Hello everyone,

I tried to run the Android example but could not even compile it. Just got the compiler message:

B4A version: 5.50
Parsing code. (0.01s)
Compiling code. (0.03s)
Compiling layouts code. (0.00s)
Generating R file. (0.35s)
Compiling debugger engine code. (0.93s)
Compiling generated Java code. Error
B4A line: 24
If init = False Then Process_Globals
javac 1.7.0_76
src\anywheresoftware\b4a\samples\sqlserver\httputilsservice.java:325: error: cannot find symbol
_process_globals();};
^
symbol: method _process_globals()
location: class httputilsservice
Can anybody help me to solve the problem? Thanks in advance,

Helmut
 

BarryW

Active 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:
B4X:
<%@ 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).
SS-2011-12-11_16.03.27.png


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).
B4X:
http://www.example.com/test1.aspx?query=select * from table_1

SS-2011-12-11_16.11.33.png


Now for Basic4android program:
B4X:
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:
B4X:
** 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 **

How to run the ASP Code to be access by android...
 

SCIS

Active Member
Licensed User
What do i need to chance in the ASP.NET code to let it work for my mssql server and b4a project?
and where do i need to place this file so b4a could find it?
 

SCIS

Active Member
Licensed User
You need to change the username and password.

This script should be hosted in a IIS server.

So i don't need to chance the datasource or query? I just need to fill the username and password of my database in the code.

The server is hosted at school, do i need to send my aspx file to my mentor and ask him to put this script on the IIS server?

Thanks
 

mkvidyashankar

Active Member
Licensed User
I am using the same code with asp.net web page.
I am getting results from web page
but not getting in the phone. here is the code

B4X:
Region  Activity Attributes
    #FullScreen: False
    #IncludeTitle: True
#End Region

Sub Process_Globals
    'These global variables will be declared once when the application starts.
    'These variables can be accessed from all modules.
    Dim ServerUrl As String
    ServerUrl = "http://89.238.162.147/gpm/query.aspx"
    Private kvs As KeyValueStore
End Sub

Sub Globals
    'These global variables will be redeclared each time the activity is created.
    'These variables can only be accessed from this module.

    Private Panel1 As Panel
    Private ListView1 As ListView
    Dim id As Int
    Dim dept As String
End Sub

Sub Activity_Create(FirstTime As Boolean)
    'Do not forget to load the layout file created with the visual designer. For example:
    'Activity.LoadLayout("Layout1")
Activity.LoadLayout("Layout1")
    If FirstTime Then
        HttpUtils.CallbackActivity = "get_data"
        HttpUtils.CallbackJobDoneSub = "JobDone"
            kvs.Initialize(File.DirDefaultExternal, "datastore")
    End If
      
            id = kvs.GetSimple("Employeeid")
            dept=Main.department
'            user=    kvs.GetSimple("username") ' FirstName,MiddleName,LastName
'            department =kvs.GetSimple("department") 'IcDepartment  
'            designation=kvs.GetSimple("designation")
'            address=kvs.GetSimple("Address") ' Address1,Address2,Address3
'            email=kvs.GetSimple("email") 'Email
'            phone=kvs.GetSimple("phone") 'PhoneNo
        Dim sqltxt As String
        If dept="Department: Quality" Then  sqltxt  ="SELECT * FROM [GRAMAPATHA].[dbo].[TrInspectionSheduleHeader] WHERE  IcMonitor =" & id & " order by [DateOfVisit]  desc"
        HttpUtils.PostString("Job1", ServerUrl, sqltxt)
      
    'HttpUtils.PostString("Job1", ServerUrl, "SELECT * FROM [GRAMAPATHA].[dbo].[TrInspectionSheduleHeader] WHERE IcDivision=3 order by [DateOfVisit]  desc")
    Panel1.SetLayout(0,0,100%x,100%y)
    ListView1.SetLayout(0,30dip,100%x,100%y-30dip)
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)
            ListView1.AddTwoLines(m.Get("MonitorId"),m.Get("DivisionId"))
            Log("col1=" & m.Get("MonitorId")) 'log the values of col1 and col2
            Log("col2=" & m.Get("DivisionId"))
        Next
    End If
    HttpUtils.Complete = False
End Sub

where is the error? output from the webpage is here

[{"MonitorId":"2417","DivisionId":"24","Packageno":"77","workno":"1375"},{"MonitorId":"2417","DivisionId":"24","Packageno":"77","workno":"1376"},{"MonitorId":"2417","DivisionId":"24","Packageno":"77","workno":"1393"},{"MonitorId":"2417","DivisionId":"24","Packageno":"77","workno":"1394"},{"MonitorId":"2417","DivisionId":"29","Packageno":"145","workno":"2219"},{"MonitorId":"2417","DivisionId":"29","Packageno":"145","workno":"2238"},{"MonitorId":"2417","DivisionId":"29","Packageno":"145","workno":"2239"},{"MonitorId":"2417","DivisionId":"29","Packageno":"145","workno":"2241"},{"MonitorId":"2417","DivisionId":"29","Packageno":"147","workno":"2258"},{"MonitorId":"2417","DivisionId":"29","Packageno":"147","workno":"2260"},{"MonitorId":"2417","DivisionId":"29","Packageno":"147","workno":"2262"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1811"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1812"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1813"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1814"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1815"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1816"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1817"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1818"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1819"},{"MonitorId":"2417","DivisionId":"20","Packageno":"91","workno":"1820"},{"MonitorId":"2417","DivisionId":"15","Packageno":"233","workno":"2755"},{"MonitorId":"2417","DivisionId":"15","Packageno":"233","workno":"3241"},{"MonitorId":"2417","DivisionId":"15","Packageno":"233","workno":"3243"},{"MonitorId":"2417","DivisionId":"15","Packageno":"233","workno":"3245"},{"MonitorId":"2417","DivisionId":"15","Packageno":"233","workno":"3247"},{"MonitorId":"2417","DivisionId":"4","Packageno":"439","workno":"1830"},{"MonitorId":"2417","DivisionId":"4","Packageno":"439","workno":"1838"},{"MonitorId":"2417","DivisionId":"4","Packageno":"439","workno":"1868"},{"MonitorId":"2417","DivisionId":"4","Packageno":"440","workno":"1866"},{"MonitorId":"2417","DivisionId":"4","Packageno":"440","workno":"1867"},{"MonitorId":"2417","DivisionId":"2","Packageno":"256","workno":"1243"},{"MonitorId":"2417","DivisionId":"2","Packageno":"256","workno":"1250"}]
 
Last edited:
Status
Not open for further replies.
Top