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

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


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

Last edited:
Click to expand...
hi erel
thanks for reply, I tried this, but I am getting Internal server error, but it is working fine when i paste the url with query in browser

Code: Copy 
#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.

Private quality= "quality" As String
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")
    Panel1.SetLayout(0,0,100%x,100%y)
    ListView1.SetLayout(0,30dip,100%x,100%y-30dip)
    fetchdata
'    Dim sqltxt As String
'    If dept="Department: Quality" Then  sqltxt  ="SELECT * FROM [GRAMAPATHA].[dbo].[TrInspectionSheduleHeader] WHERE  IcMonitor =" & id & " order by [DateOfVisit]  desc"
End Sub


Sub fetchdata
    ProgressDialogShow("Fetching list of works")
    ExecuteRemoteQuery("http://89.238.162.147/gpm/query.aspx", quality)
  
  
End Sub

Sub ExecuteRemoteQuery(Query As String, jobname As String)
    Dim job As HttpJob
    Dim sqltxt As String
    id = Main.EmployeeID
    dept=Main.department
    If dept="Department: Quality" Then  sqltxt  ="SELECT * FROM [GRAMAPATHA].[dbo].[TrInspectionSheduleHeader] WHERE  IcMonitor =" & id & " order by [DateOfVisit]  desc"
  
    job.Initialize(jobname, Me)
    job.PostString("http://89.238.162.147/gpm/query.aspx", sqltxt)
End Sub
Sub Activity_Resume

End Sub

Sub JobDone(Job As HttpJob)
      
    ProgressDialogHide
    If Job.Success Then
    Dim res As String
        res = Job.GetString
        Log("Response from server: " & res)
        Dim parser As JSONParser
        parser.Initialize(res)
        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
  
    Else
        ToastMessageShow("Error: " & Job.ErrorMessage, True)
    End If
    Job.Release
End Sub
Sub Activity_Pause (UserClosed As Boolean)

End Sub

[{"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"}]
Click to expand...
 
I think I am getting timeout error

** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (get_data1) Create, isFirst = true **
** Activity (get_data1) Resume **
** Service (httputils2service) Create **
** Service (httputils2service) Start **
Error occurred. Query=83
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()

at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
Click to expand...
 
Hi erel
I checked again, now i am getting log file like this
** Activity (main) Pause, UserClosed = false **
** Activity (main) Resume **
** Activity (main) Pause, UserClosed = false **
** Activity (get_data) Create, isFirst = false **
Starting Job: Job1
** Activity (get_data) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
Error. Url=http://89.238.162.147/gpm/query.aspx Message=Internal Server Error
Error occurred. Query=83
System.Data.SqlClient.SqlException (0x80131904): Incorrect syntax near '83'.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at GP_Mobile.query.Page_Load(Object sender, EventArgs e) in H:\gp_mobile\GP_Mobile\GP_Mobile\query.aspx.vb:line 45
** Service (httputilsservice) Destroy **
PackageAdded: package:com.ubercab
PackageAdded: package:flipboard.app
** Activity (get_data) Pause, UserClosed = false **
PackageAdded: package:com.evernote
** Activity (get_data) Resume **
** Activity (get_data) Pause, UserClosed = false **
PackageAdded: package:com.google.android.music
PackageAdded: package:com.facebook.orca
Click to expand...

but I am getting correct result, when i copy and paste the query in browser, url is like this

http://89.238.162.147/gpm/query.aspx?query=SELECT * FROM [GRAMAPATHA].[dbo].[TrInspectionSheduleHeader] WHERE IcMonitor =2411 order by [DateOfVisit] desc
 
Hello Erel, I get error when I run my app.

just I changed the url and table name

Code: Copy 
B4A version: 6.00 BETA #1
Parsing code.    (0.01s)
Compiling code.    (0.20s)
   
ObfuscatorMap.txt file created in Objects folder.
Compiling layouts code.    (0.00s)
Generating R file.    (0.44s)
Compiling generated Java code.    Error
B4A line: 98
Log(Response.GetString(\
javac 1.7.0_25
src\anywheresoftware\b4a\samples\sqlserver\httputilsservice.java:153: error: cannot access ParseException
anywheresoftware.b4a.keywords.Common.Log(_response.GetString("UTF8"));
                                                            ^
  class file for org.apache.http.ParseException not found
 
Hello Erel Now is ok but small problem

when I use this code work fine >
Code: Copy 
    Private ServerUrl As String = "http://hreeef-001-site1.ctempurl.com/test1.aspx?query=select * from tbl1"


but like this does not work
Private ServerUrl As String = "http://hreeef-001-site1.ctempurl.com/test1.aspx"

this all the code
Code: Copy 
Sub Process_Globals
    Private ServerUrl As String = "http://hreeef-001-site1.ctempurl.com/test1.aspx?query=select * from tbl1"
End Sub

Sub Globals

    Private ListView1 As ListView
End Sub

Sub Activity_Create(FirstTime As Boolean)
    Activity.LoadLayout("1")
 
    Dim job As HttpJob
    job.Initialize("Job1", Me)
    job.PostString(ServerUrl , "")
End Sub

Sub Activity_Resume
End Sub


Sub Activity_Pause (UserClosed As Boolean)
 
End Sub

Sub JobDone (Job As HttpJob)
    If Job.Success Then
        Dim parser As JSONParser
        Dim response As String = Job.GetString
        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("id")) 'log the values of col1 and col2
            Log("col2=" & m.Get("fname"))
            Log("col3=" & m.Get("address"))
         
            ListView1.AddTwoLines2(m.Get("id") , m.Get("fname") , m.Get("address"))
         
        Next
    End If
    Job.Release
End Sub

and thanks alot
 

Attachments

Last edited:
Thanks Erel , It works fine when I wrote like this

job.PostString(ServerUrl & "?query=select * from tbl1","")
 
My teacher Erel , sorry about many questions

select and insert are fine

but delete and update are not working

Code: Copy 
job.PostString(ServerUrl & "?query=delete from tbl1 where id='" & EditText1.Text & "'" , "")

job.PostString(ServerUrl & "?query=update tbl1 set id='" & EditText1.Text & "',fname='" & EditText2.Text & "',address='" & EditText3.Text & "' where id='" & EditText1.Text & "'" , "")

this delete all records work fine

job.PostString(ServerUrl & "?query=delete from tbl1" , "")

id = text
 
Last edited:
Status
Not open for further replies.

Similar Threads

aeric
  • Article Article
B4J Library [B4X] [SQL] MiniORMUtils
2 3
Replies
45
Views
22K
aeric
aeric
aeric
  • Article Article
Share My Creation (PHP/MySQL/API) User Login App
4 5 6
Replies
108
Views
102K
aeric
aeric
Bladimir Silva Toro
  • Article Article
Android Tutorial Tutorial of connecting Android to MS SQL Server using jRDC2
2
Replies
30
Views
31K
josejad
josejad
Erel
  • Locked
  • Article Article
B4A Library JdbcSQL - Directly connect to remote databases
2
Replies
23
Views
79K
Erel
Erel
Erel
  • Locked
  • Article Article
Android Tutorial Connect Android to MySQL Database Tutorial
30 31 32
Replies
633
Views
1M
Oke
O
Back
Top