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]

JohnCody use one of the many c# to vb-net converters that are out there on the net, this will only give you the jist of what going on, you will have a little work yourself on the vb side of things to get it working. The are examples of using JSON within vb.net webservices also. Stackoverflow is a good place to start.

Regards

BOB

 

[McJaegs]

So when I try to connect to my database I get the error

"System.Data.SqlClient.SqlException: Cannot open database "databasename" requested by the login. The login failed."

I am using the exact same login credentials that I use to log into the server and access that database.

Does anyone know what my issue is?

 

[Erel]

I guess that your database name is not: databasename.

 

[McJaegs]

I guess that your database name is not: databasename.

No its not, I just changed it here. The error actually says the name of my database.

 

[Erel]

Try to run the ASP page from the browser. You can pass the query by adding ?query="select ...".
You should see the same error message. However it will be easier to debug the issue.

 

[McJaegs]

Try to run the ASP page from the browser. You can pass the query by adding ?query="select ...".
You should see the same error message. However it will be easier to debug the issue.

Yes, that is where I am seeing the error from. I haven't even tried to get it to run through the app I am making yet. I am first trying to see if it will even access the database through the browser.

 

[PCBorges]

Building your sample Android app.

I am trying to simulate your app.
When I create a new Android project I am presented with the following activity code:

package basic.fourPCC.project;

import android.app.Activity;
import android.os.Bundle;

public class Basic4pcc_DBProjectActivity extends Activity {
/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.main);
}

}

Can you please inform where do I insert your code provided on "Now for Basic4android program:" ?

The server JSON side is already working as required.

Thanks
Paulo Borges

 

[Erel]

This forum is for programs written in Basic4android not Java.

 

[oduvaldoOliveira]

Multiple calls to the WebService

How to run multiple calls to the WebService using routines Http and Http Utils Utils Service, being my MSSQL Database? Using the examples of the topic "Android Connect to MS SQL Server Tutorial".

Sorry my English, I am Brazilian and my English is pretty weak ...

 

[Erel]

HttpUtils can handle a single job at a time. Each job can include any number of requests (URLs).

 

[arp]

Empty Query

I'm trying to get this working - I have problem similar to some already mentioned, but cannot work out the answer.

B4A code snippet..

Sub Process_Globals
Dim ServerUrl As String
ServerUrl = "http://192.168.0.15/b4aserver/sqlserver.aspx"
End Sub

Sub Activity_Create(FirstTime As Boolean)
If FirstTime Then
HttpUtils.CallbackActivity = "Main"
HttpUtils.CallbackJobDoneSub = "JobDone"
End If

HttpUtils.PostString("Job1", ServerUrl, "select * from vSalesCustomers")
End Sub

Log output...

** Activity (main) Resume **
** Service (httputilsservice) Create **
** Service (httputilsservice) Start **
Error. Url=http://192.168.0.15/b4aserver/sqlserver.aspx Message=Internal Server Error
Error occurred. Query=
System.InvalidOperationException: ExecuteReader: CommandText property has not been initialized

at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, 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 ASP.b4aserver_sqlserver_aspx.Page_Load(Object sender, EventArgs ec) in c:\inetpub\wwwroot\B4AServer\sqlserver.aspx:line 26
finished Job1
** Service (httputilsservice) Destroy **

From what I can gather, the InputStream for the ASPX request is empty.

Any assistance would be greatly appreciated, thanks.

 

[arp]

Should mention that it works through the browser.

 

[Erel]

Try passing the query as a GET parameter:

HttpUtils.PostString("Job1", ServerUrl & "?query=select * from vSalesCustomers", "")

 

[arp]

Thanks Erel,

Unfortunately that didn't fix the problem. With spaces in the select statement the program errors with "java.lang.IllegalArgumentException: Illegal character in query at index 57: http://192.168.0.15/b4aserver/sqlserver.aspx?query=select * from vSalesCustomers order by name".

So I replaced the spaces with %20, which allows the code to run to completion, however, the result of HttpUtils.IsSuccess is false.

Ah, OK, need to change the URL parameter in the JobDone code to match the full URL passed in the Post call.

Making progress again, thanks Erel.

 

[Gearcam]

Will Android ever be able to connect direct to MS SQL ?

I want a direct way to connect to a MS SQL databse stored on a remote site hosted by "discountasp.net"
Is theire any way to connect to this ?

Currenley i have some win 6 phones that can connect to it with a program written in VS2008

I want to move this over to Android !!!!

Steve

 

[Gearcam]

ok going to use this method

I am going to test this method

I dont have many issues with the basic4android stuf

But i do with the ASP.NET script i have no idea how to make this run is it compiled in visual studio as described here

Creating a Windows Phone 7 Application Consuming Data Using a WCF Service

i know its win mob 7 stuff but the services look the sameish

Is there an example or a download of Erels script ?

Thanks

 

[Erel]

As the ASP.Net is a script you do not need to compile it. Just grab it from the first post: http://www.b4x.com/forum/basic4andr...6-connect-android-ms-sql-server-tutorial.html

 

[askez]

what is the 'rows = parser.NextArray' problem solution?
my web service is running OK (i get a good printput using http)
and it's not firewall (the device give the same)

anyone?

 

[Erel]

Check the logs and see the response you get from the server.

 

[Gearcam]

Compilation Error

As the ASP.Net is a script you do not need to compile it. Just grab it from the first post: http://www.b4x.com/forum/basic4andr...6-connect-android-ms-sql-server-tutorial.html

Erel

I have made the aspx file and uploaded it to my website
Changed the login info for the database
Added the BiN folder and System.Web.Extensions.dll it took a couple of different ones to make it run

what about Initial Catalog ????


When i try to run it from a webpage i get


Error occurred. Query=
System.Data.SqlClient.SqlException: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Connect(ServerInfo serverInfo, SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 timerExpire, Boolean encrypt, Boolean trustServerCert, Boolean integratedSecurity, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at ASP.test_aspx.Page_Load(Object sender, EventArgs ec) in xxxxxxxxxxxxxxxxx




Thanks