Any Examples of connecting through a web service using Ms SQL server 2008

[sacad]

Does anyone maybe have a good example that they willing to share of connecting to sql through a web service? i know there is alot of free web services you can test on and was just wondering? I did go through the tutorial of Ezel but more me its complicated since im new to this and never done this before.
What will be the best approach to do something than this because this is what ive had in mind...
1. get a host service provider that will host my service for me and supports in SQl
2.Write a web service using asp.net in a object oriented route so i can call the functions
3. Test it on a windows pc to see if data can be read from the database
4. No sleep for a week and figuring b4a out and writing a service that connects to the database.
5.Etc

can I use the web service for instance to populate all my friends on the system and then adding them so every time i logged into the system a function must run to determine the organizational structure and then populate the employees for who i can send messages?
Then can you send a email through a written application that allows the user to choose a contact email from phone?
Thank you for all the support:sign0098:

 

[stefanobusetto]

hi
i'm using a php web service as an interface
with mssql 2005 ( 2008 is the same )

here a full example
http://www.b4x.com/forum/libraries-developers-questions/17691-ms-sql.html#post101644

 

[ScarBelly]

I went with #2 and wrote a simple web service as a page that runs on our intranet that receives requests from an android device and responds with a JASON string.
The server code for the aspx page takes care of all SQL Server work and just acts as request/response server. The B4A side uses HttpUtils and HttpUtilsService.

This has proved to be fast and easy.

 

[PHB2]

Hi ScarBelly - I am doing something very similar to you, but am having trouble decoding the hex return data from the web service. It should decode to json, but instead I get some very random characters.

Would you be willing to show me the code you use when you receive the response back from the web service?

Many thanks

Mark

 

[dealsmonkey]

In a project I am working on, I do all the work on the .net server using MSSQL. I use a stored procedure on the server, convert the data to JSON and then just read it on the Android device. This data is then stored on the device in a SQLLite database.

If you want any info, just ask

 

[ScarBelly]

PHB2
Here is the Service Job Done sub. Note since I am just using the response, it a just a string. Note how the parser As JASONParser is used. I didn't invent any of this - just plaguerized from this site. Sorry, no time to cut out the non-specific parts.

Sub SvcJobDone (Job As String)
If HttpUtils.IsSuccess(PostUrl) Then
Dim response As String
Select Job

Case "Status"
'If HttpUtils.IsSuccess(PostUrl) Then
response = HttpUtils.GetString(PostUrl)
Dim strCheck As String
strCheck = response.SubString2(0,1)
listAStatus.Initialize
listAStatus.Clear
Dim HasUnAcknowledged As Boolean
HasUnAcknowledged = False
If strCheck = "x" Then 'nothing to report
'clear messages
HttpUtils.Complete = False
AlertCount = 0
Else
Dim parser As JSONParser
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.

'listAStatus.Clear

For I = 0 To rows.Size - 1
'Log("Rows #" & i)
Dim m As Map
m = rows.Get(I)
Dim newAStatus As AndonStatus
newAStatus.Initialize
newAStatus.AlertId = m.Get("AlertID")
newAStatus.Location = m.Get("Location")
newAStatus.AlertLevel = m.Get("AlertLevel")
newAStatus.AlertTime = m.Get("AlertTime")
newAStatus.AcknowledgedTime = m.Get("AcknowledgedTime")
newAStatus.AcknowledgedBy = m.Get("AcknowledgedBy")
newAStatus.Comment = m.Get("Comment")
newAStatus.ClosedTime = m.Get("ClosedTime")
newAStatus.Station = m.Get("Station")
newAStatus.IsStop = m.Get("IsStop")
newAStatus.Reply = m.Get("Reply")
newAStatus.LocationID = m.Get("LocationID")
newAStatus.StationID = m.Get("StationID")
newAStatus.AckID = m.Get("AckID")
newAStatus.CallOrder = m.Get("CallOrder")
'If list item isn't call order 1 then wait 1 minute per call level based on AlertTime
If newAStatus.CallOrder = 1 Then
listAStatus.Add(newAStatus)
Else
DateTime.DateFormat = "yyyy-MM-dd" '!IMPORTANT
Dim now As Long
now = DateTime.now

Dim NowTicks As Long
NowTicks = DateTime.DateParse(DateTime.Date(now)) + DateTime.TimeParse(DateTime.Time(now))
Dim myAlertTime As Long
myAlertTime = DateTime.DateParse(newAStatus.AlertTime.SubString2(0,newAStatus.AlertTime.IndexOf(" "))) _
+ DateTime.TimeParse(newAStatus.AlertTime.SubString(newAStatus.AlertTime.IndexOf(" ") + 1))

If newAStatus.CallOrder = 2 AND myAlertTime < NowTicks - 60000 Then
listAStatus.Add(newAStatus)
End If
If newAStatus.CallOrder = 3 AND myAlertTime < NowTicks - 120000 Then
listAStatus.Add(newAStatus)
End If
End If
If newAStatus.AcknowledgedBy = "" Then
HasUnAcknowledged = True
End If
Next

If listAStatus.Size > AlertCount AND HasUnAcknowledged Then
SoundAlert = True
tmrAlert.Enabled = True

BeepCount = 0
AlertCount = listAStatus.Size
If IsPaused(Main) Then
Notification1.SetInfo("Andon Alert", "New alert found", Main)
Notification1.AutoCancel = True
Notification1.Notify(1)
End If
End If


End If
If Not(IsPaused(Main)) Then
CallSub(Main, "ShowStatus")
End If
If listAStatus.Size = 0 Then
Notification1.Cancel(1)
End If
End Select
HttpUtils.Complete = False

If Not(IsPaused(Main)) Then
CallSub(Main, "LastPollTime")
End If

End If


'StopService("")
End Sub

 

[ScarBelly]

Yeah, DealsMonkey, that is what I do too. Just need to read which command to determine what to return.

Note to others: If you are encoding a stream on the server side, you may need to use byteconverter on the B4A side to decode.

 

[sacad]

Stored procedures for Sql server 2008

So lets say i have a login form and a user enters his/her password and click login. how would you send that value to connect through the web service to check if the user exist in the database? DO i need to write a asp.net web service(there is prob lots of tutorials on the web of how to connect to a web service that connects to my database) that use a stored procedure like

Select user_id,access_level_id from User
where Username = ? (which is the username textbox)
and userpassword = ? (will write an encrypt decrypt function that will encode and decode the password.

i have never written a web service before but know how to do stored procedures and vb code but seems no one has an example that can show me how to do this because what i want to do is after the user logs in to send messages to people for who they have authorization and also read data from my sql database the whole time. So i must populate a combo box that gets all the user for who i can send using a web service. What will be the best way to do this?
Im gonna do some tutorials this weekend to learn how to write a asp web service that connects to sql through local host then try to connect the emulator via the ip address of my pc to test if the service works. Dont know if its possible?
Thank you for all the help guys!!!