Android Question Retrieve data from SQL Server

[Sofiya]

I have a database table with several columns, and i want to show only the data of one column in my local-host via Visual C#.net web API. How i do this.

 

[MarkusR]

maybe your phone/tablet can also act as http server?
or you can send data to web api via okhttputils2 library.
or a c# desktop app act as mini server and transport/receives data via tcp socket. (local-host sounds like your pc at home)
or copy sqllite db to pc and look into with db browser for sql lite.

i guess your target is copy data from mobil to pc and this web server provide table view in web, true?

this are also a good template
https://www.b4x.com/android/forum/t...ce-desktop-and-web-reports.37254/#post-219539

 

[Lee Gillie CCP]

My approach has been to make a Windows Service via Visual Studio. My work is in VB here, but it is pretty close to C#. I make remote method calls via my own https server from the mobile. I use JSON for serialization. I pass serialized method calls via remote web method calls.

Imports System.ComponentModel
Imports Newtonsoft.Json.Linq

<Serializable()> _
Public Class RemoteMethodCall
    Private m_Method As String
    Private m_Parameters As List(Of Parameter)
    Private m_ReturnType As String

    Public Property Method() As String
        Get
            Return m_Method
        End Get
        Set(value As String)
            m_Method = value
        End Set
    End Property

    Public Property Parameters() As List(Of Parameter)
        Get
            Return m_Parameters
        End Get
        Set(value As List(Of Parameter))
            m_Parameters = value
        End Set
    End Property

    Public Property ReturnType() As String
        Get
            Return m_ReturnType
        End Get
        Set(value As String)
            m_ReturnType = value
        End Set
    End Property

    Public Function GetParamArray() As Object()
        Dim params As New List(Of Object)()
        'Parameter par = default(Parameter);
        For Each par As Parameter In Parameters
            Dim targetType As Type = TypeResolver.Resolve(par.Type)
            Dim valueType As Type = par.Value.[GetType]()
            If targetType.Equals(valueType) Then
                params.Add(par.Value)
            ElseIf par.Type = valueType.Name Then
                params.Add(par.Value)
            Else
                Dim typeConverter As TypeConverter = TypeDescriptor.GetConverter(targetType)
                If typeConverter.CanConvertFrom(valueType) Then
                    Dim v As [Object] = typeConverter.ConvertFrom(par.Value)
                    params.Add(v)
                Else
                    If par.Value.GetType.Name = "JObject" Then
                        params.Add(CType(par.Value, Newtonsoft.Json.Linq.JObject).ToObject(targetType))
                    Else
                        params.Add(par.Value)
                    End If
                End If
            End If
        Next
        Return params.ToArray()
    End Function

    Public Sub New()
        m_Method = ""
        m_Parameters = New List(Of Parameter)
        m_ReturnType = ""
    End Sub

    Public Sub New(methodName As String, returnType As String, Pars As List(Of Parameter))
        m_Method = methodName
        m_ReturnType = returnType
        m_Parameters = Pars
    End Sub
End Class

Imports System.Collections.Generic
Imports System.Linq
Imports System.Text
Imports System.Threading.Tasks

<Serializable()> _
Public Class Parameter
    Private m_Name As String
    Private m_Type As String
    Private m_Value As Object

    Public Property Name() As String
        Get
            Return m_Name
        End Get
        Set(value As String)
            m_Name = value
        End Set
    End Property

    Public Property Type() As String
        Get
            Return m_Type
        End Get
        Set(value As String)
            m_Type = value
        End Set
    End Property

    Public Property Value() As Object
        Get
            Return m_Value
        End Get
        Set(value As Object)
            m_Value = value
        End Set
    End Property

End Class

The Windows service provides a transparent proxy which is triggered by the method name, and then I have written proxy implementations which actually do the SQL work via System.Data.SQLClient SQLCommand.

Transparent proxies are supported via .NET, VB, C#, and even Java, but not B4A. When it comes to serialization/deserialization here I translate all data for serialization/deserialization with maps. And then I have a B4A generator that makes wrapper classes supporting this as well. I am working on my own Java library (intended to be called from B4A) for this, but with the dynamic nature of transparent proxies I have found it difficult to translate to the B4A object model.

Since I do this a lot, I created a code generator driven by SQL metadata which wraps common table access routines, and serializable wrappers for each table. It can create similar code in VB, C#, and Java. It creates common routines for create, get, search, delete, and update are generated. Tooled for the job I can create nearly all server side support for say 20 tables, in less than an hour. Sometimes I write custom routines that perform functions beyond simple table wrapping.

For example, a transparent proxy implementation (generated code)

    Public Function Create_OCPBlacklist(rec As OCPBlacklist) As Integer Implements ICloudBroker.Create_OCPBlacklist
        Dim cn As SqlConnection = GetOCPSQLConnection()
        Dim ret As Integer
        SyncLock cn
            ret = Database.Create_OCPBlacklist(cn, rec)
        End SyncLock
        RaiseEvent OCPBlacklist_Updated(TypeOfCRUDChange.Create, rec)
        Return ret
    End Function

And then a Database object implements all SQL, for example this (also generated code)

    Public Function Create_OCPBlacklist(ByVal cn As SqlConnection, ByVal rec As OCPBlacklist, Optional ByVal myTrans As SqlTransaction = Nothing) As Int32
        SyncLock cn
            Dim SQL As String
            Dim cmd As SqlCommand

            rec.Pseudokey = 0

            SQL = "INSERT INTO OCPBlacklist (" & _
                    " BlacklistSubnet, Author, Comments, Disabled, Created," & _
                    " LastUsed, UseCount " & _
                ") OUTPUT INSERTED.Pseudokey VALUES (" & _
                    "@BlacklistSubnet,@Author,@Comments,@Disabled,@Created," & _
                    "@LastUsed,@UseCount " & _
                ")"

            cmd = New SqlCommand(SQL, cn)

            cmd.Parameters.Add("@Pseudokey",SqlDbType.int).Direction = ParameterDirection.ReturnValue
            cmd.Parameters.Add("@BlacklistSubnet",SqlDbType.varchar,255).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@Author",SqlDbType.varchar,100).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@Comments",SqlDbType.varchar,255).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@Disabled",SqlDbType.bit).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@Created",SqlDbType.datetime).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@LastUsed",SqlDbType.datetime).Direction = ParameterDirection.Input
            cmd.Parameters.Add("@UseCount",SqlDbType.int).Direction = ParameterDirection.Input

            cmd.Parameters("@BlacklistSubnet").Value = rec.BlacklistSubnet

            If rec.Author = String.Empty Then
                cmd.Parameters("@Author").Value = DBNull.Value
            Else
                cmd.Parameters("@Author").Value = rec.Author
            End If

            If rec.Comments = String.Empty Then
                cmd.Parameters("@Comments").Value = DBNull.Value
            Else
                cmd.Parameters("@Comments").Value = rec.Comments
            End If

            cmd.Parameters("@Disabled").Value = rec.Disabled

            If rec.Created = DateTime.MinValue Then
                cmd.Parameters("@Created").Value = DBNull.Value
            Else
                cmd.Parameters("@Created").Value = rec.Created
            End If

            If rec.LastUsed = DateTime.MinValue Then
                cmd.Parameters("@LastUsed").Value = DBNull.Value
            Else
                cmd.Parameters("@LastUsed").Value = rec.LastUsed
            End If

            If rec.UseCount = 0 Then
                cmd.Parameters("@UseCount").Value = DBNull.Value
            Else
                cmd.Parameters("@UseCount").Value = rec.UseCount
            End If

            If Not myTrans Is Nothing Then
                cmd.Transaction = myTrans
            End If

            Dim retVal As Object = cmd.ExecuteScalar()
            rec.Pseudokey = CType(retVal,Int32)

            cmd.Dispose()
            cmd = Nothing

        End SyncLock

        Return rec.Pseudokey

    End Function

Perhaps way more answer than you are seeking, but this is the path I have chosen, and it is working well for us. Perhaps there are glimpses of ideas you see here that can help you see where you want to go?

I tried using the Web API components for IIS but I found many difficult to diagnose issues with it, and way less control.

 

[Lee Gillie CCP]

A more modest, hacky, quick way to the end would be to create an ASPX page for IIS which performs the query, and displays the result as the page body, perhaps as a JSON string, and then do an HTTPJOB request to that specific page from B4A, and then parse/deserialize/etc. that' page's body to values you can use in B4A. If your needs go beyond this one simple query, you may find this quick way does not scale well.

 

[Peter Simpson]

You should start of by asking this question in the correct forum.

Hint: B4J...