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: 3,226
Last edited:

MohammadNew

Active Member
Licensed User
Longtime User
You will need to modify the ASP.Net script to support commands. Why aren't you using RDC?

How to modify the ASP.Net script to support commands ?

and when I use like this

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

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

is working fine but update all records and delete all records too.

when I put where does not work and no error no anything

if you want my example to try ok tell me my teacher >

after finish this tutorial I am going to learn RDC
 
Last edited:

jsanchezc

Member
Licensed User
Longtime User
How to modify the ASP.Net script to support commands ?

and when I use like this

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

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

is working fine but update all records and delete all records too.

when I put where does not work and no error no anything

if you want my example to try ok tell me my teacher >

after finish this tutorial I am going to learn RDC

Hello, you did not write "where ..."
remember place space before where .... and put 'xxxxx' if field is string type

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

delete >
job.PostString(ServerUrl & "?query=delete from tbl1 Where fieldname='" & value & "'" , "")

best practice: tables and fields names between [ & ]:
(you 'll avoid some headache)

update >
job.PostString(ServerUrl & "?query=update [tbl1] set [id]='" & EditText1.Text & "',[fname]='" & EditText2.Text & "',[address]='" & EditText3.Text & "'" & " Where [fieldname]='" & value & "'", "")

delete >
job.PostString(ServerUrl & "?query=delete from [tbl1] Where [fieldname]='" & value & "'" , "")

Hope this help
 

MohammadNew

Active Member
Licensed User
Longtime User
Hello, you did not write "where ..."
remember place space before where .... and put 'xxxxx' if field is string type

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

delete >
job.PostString(ServerUrl & "?query=delete from tbl1 Where fieldname='" & value & "'" , "")

best practice: tables and fields names between [ & ]:
(you 'll avoid some headache)

update >
job.PostString(ServerUrl & "?query=update [tbl1] set [id]='" & EditText1.Text & "',[fname]='" & EditText2.Text & "',[address]='" & EditText3.Text & "'" & " Where [fieldname]='" & value & "'", "")

delete >
job.PostString(ServerUrl & "?query=delete from [tbl1] Where [fieldname]='" & value & "'" , "")

Hope this help

sadly does not work is the same my code
no error no delete no update
Just insert and select work fine without where
 

MohammadNew

Active Member
Licensed User
Longtime User
Jsanchezc Thanks, the problem was the table the fields was Text when I changed it to varchar work fine.

and Thanks Erel
 

Scotter

Active Member
Licensed User
I got your C#.Net example working to pull data from a database into the browser.
I even was able to get my B4A app to send SQL statements as querystrings and have that C#.Net page facilitate the transfer.
Then I decided I wanted to put the SQL statements in the C#.Net page and use a "switch/case" command to pick the SQL to be submitted to the database, based on my B4A application just sending a "command" via querystring.
Here's where I'm caught up. I'm not a C# programmer. I'm going to post this in a C# forum but also here, because some of you may be trying to do what I'm doing. So ... here's my code in hopes that someone can help me find a way to figure out the following warning that comes up in FireFox when I test this out in the browser:

"The character encoding of the plain text document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the file needs to be declared in the transfer protocol or file needs to use a byte order mark as an encoding signature."

Here's the 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)
    {
        //level 01
        using (SqlConnection conn = new SqlConnection("Database=NUTSHELL; User Id=Andy; password=xyzxyz"))
        {
                //level 02
                Response.ContentType = "text/plain";
                string s = "";
                string u = Request.QueryString["u"];
                if (u == "vJ39k84g!7S2r#wV4")
                {
                    //level 04
                    string sCmd = Request.QueryString["sCmd"];
                    string IDCategory;
                    string sCategory;
                    switch (sCmd)
                    {
                        case "GetCategoryNames":
                            s = "SELECT";
                            //s += " Id";
                            s += " name";
                            //s += ", defaultSort";
                            s += " FROM Category";
                            s += " WHERE";
                            s += " (";
                            s += " ShowOnHomePage=1";
                            s += " AND IncludeInTopMenu=1";
                            s += " AND Published=1";
                            s += " )";
                            s += " ORDER BY name";
                            //s = "SELECT name FROM Category ORDER BY name";
                            break;
                        case "GetCategoryId":
                            sCategory = Request.QueryString["sCategory"];
                            s = "SELECT";
                            s += " Id";
                            s += " FROM Category";
                            s += " WHERE";
                            s += " (";
                            s += " [name]='" + sCategory + "'";
                            s += " )";
                            break;
                    }

                    try
                    {
                        SqlCommand cmd = new SqlCommand(s, conn);
                        conn.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=" + s + "\n");
                        Response.Write(e.ToString());
                    }

                    //level 03
                }
                Response.End();
            //level 02
        }
        //level 01
    }
</script>
 
Last edited:

JOHN BAUSTIN

New Member
Hello... I used this code . But I m getting error HTTPUTils cannot be resolved to a variable. Can you please help me out..
 
Last edited by a moderator:

Infotech

Member
Licensed User
hi erel why my connection sql server is not found or not acces, if i connect with ODBC this sql server is conected, why why why


this code asp.net
<%@ 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=PASTISSS; User Id=USERSQL; 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>



and this result for asp.net

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.website1_default_aspx.Page_Load(Object sender, EventArgs ec) in c:\inetpub\wwwroot\WebSite1\Default.aspx:line 25
 

Infotech

Member
Licensed User
I still can not connect, after my computer re-install

I need a web config on asp.net

this code maybe not work and why no conection?
 

Attachments

  • My Web Config.txt
    7.9 KB · Views: 480

jatko

Member
Licensed User
Longtime User
How to use the script with MS SQL instance? I have the error in line 12

12: using (SqlConnection cn = new SqlConnection("Server=localhost\insertgt; Database=something; User Id=someone; password=something"))


I got your C#.Net example working to pull data from a database into the browser.
I even was able to get my B4A app to send SQL statements as querystrings and have that C#.Net page facilitate the transfer.
Then I decided I wanted to put the SQL statements in the C#.Net page and use a "switch/case" command to pick the SQL to be submitted to the database, based on my B4A application just sending a "command" via querystring.
Here's where I'm caught up. I'm not a C# programmer. I'm going to post this in a C# forum but also here, because some of you may be trying to do what I'm doing. So ... here's my code in hopes that someone can help me find a way to figure out the following warning that comes up in FireFox when I test this out in the browser:

"The character encoding of the plain text document was not declared. The document will render with garbled text in some browser configurations if the document contains characters from outside the US-ASCII range. The character encoding of the file needs to be declared in the transfer protocol or file needs to use a byte order mark as an encoding signature."

Here's the 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)
    {
        //level 01
        using (SqlConnection conn = new SqlConnection("Database=NUTSHELL; User Id=Andy; password=xyzxyz"))
        {
                //level 02
                Response.ContentType = "text/plain";
                string s = "";
                string u = Request.QueryString["u"];
                if (u == "vJ39k84g!7S2r#wV4")
                {
                    //level 04
                    string sCmd = Request.QueryString["sCmd"];
                    string IDCategory;
                    string sCategory;
                    switch (sCmd)
                    {
                        case "GetCategoryNames":
                            s = "SELECT";
                            //s += " Id";
                            s += " name";
                            //s += ", defaultSort";
                            s += " FROM Category";
                            s += " WHERE";
                            s += " (";
                            s += " ShowOnHomePage=1";
                            s += " AND IncludeInTopMenu=1";
                            s += " AND Published=1";
                            s += " )";
                            s += " ORDER BY name";
                            //s = "SELECT name FROM Category ORDER BY name";
                            break;
                        case "GetCategoryId":
                            sCategory = Request.QueryString["sCategory"];
                            s = "SELECT";
                            s += " Id";
                            s += " FROM Category";
                            s += " WHERE";
                            s += " (";
                            s += " [name]='" + sCategory + "'";
                            s += " )";
                            break;
                    }

                    try
                    {
                        SqlCommand cmd = new SqlCommand(s, conn);
                        conn.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=" + s + "\n");
                        Response.Write(e.ToString());
                    }

                    //level 03
                }
                Response.End();
            //level 02
        }
        //level 01
    }
</script>
 
Status
Not open for further replies.
Top