||edit||: the code below, as long as you have a web server and a valid conneciton to a sql server from that web server works 100%
I found this interesting when i saw it, and I tried making one as a proof of concept, but i'm currently getting an web error 500, and have not tracked the error down yet.
the service itself seems to be working, but I have nto got it to return data yet: but this might get you 90% of the way where you want to be:
here's the linky to my service:
and this is the full code::
<%@ WebService Language="VBScript" Class="ObscureService" %>
Imports System.Data
Imports System.Data.SqlClient
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
' To allow this Web Service to be called from script, using ASP.NET AJAX, uncomment the following line.
<System.Web.Script.Services.ScriptService()> _
<System.Web.Services.WebService(Namespace:="http://tempuri.org/")> _
<System.Web.Services.WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<ToolboxItem(False)> _
Public Class ObscureService
Inherits System.Web.Services.WebService
<WebMethod()> _
Public Function GetTable(TableName As String, MaxRecords As Integer) As DataTable
Dim MyConnectionString As String = "data source={0};initial catalog ={1};user id={2};password={3};Trusted_Connection=false;Connect Timeout=30;Application Name=TableGrabber Webservice;"
MyConnectionString = String.Format(MyConnectionString, "CLE02SQL03", "MyDBName", "MyDbUser", "NotTheRealPassword")
Dim MyConn As SqlConnection = New SqlConnection(MyConnectionString)
Try
Dim tsql As String = <tsql>
SELECT {1} * FROM {0}
</tsql>
tsql = String.Format(tsql, TableName, IIf(MaxRecords = 0, "", "TOP " & MaxRecords))
MyConn.Open()
Dim mySqlCommand As New SqlCommand()
mySqlCommand.Connection = MyConn
mySqlCommand.CommandText = tsql
mySqlCommand.CommandType = CommandType.Text
Dim myDataReader As SqlDataReader
myDataReader = mySqlCommand.ExecuteReader
'must have a table name to serialize.
Dim MyDataTable As New DataTable(TableName)
MyDataTable.Load(myDataReader)
Return MyDataTable
Catch ex As Exception
Return (Nothing)
End Try
End Function
End Class
this is the code I'm trying to use to consume the web service itself, and that's where it's breaking for me:.
'http://geekswithblogs.net/marcel/archive/2007/03/26/109886.aspx
Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
Dim tsql As String = ""
tsql = "<?xml version=""1.0"" encoding=""utf-8""?> " & vbCrLf
tsql = tsql & "<soap12:Envelope xmlns:xsi=""http://www.w3.org/2001/XMLSchema-instance"" xmlns:xsd=""http://www.w3.org/2001/XMLSchema"" xmlns:soap12=""http://www.w3.org/2003/05/soap-envelope""> " & vbCrLf
tsql = tsql & " <soap12:Body> " & vbCrLf
tsql = tsql & " <GetTable xmlns=""http://tempuri.org/""> " & vbCrLf
tsql = tsql & " <TableName>{0}</TableName> " & vbCrLf
tsql = tsql & " <MaxRecords>{1}</MaxRecords> " & vbCrLf
tsql = tsql & " </GetTable> " & vbCrLf
tsql = tsql & " </soap12:Body> " & vbCrLf
tsql = tsql & "</soap12:Envelope> " & vbCrLf
'--populating the parameters i inserted into the envelope.
tsql = String.Format(tsql, "sys.tables", 20)
Dim req As HttpWebRequest = DirectCast(WebRequest.Create("http://stormrage.com/ObscureService.asmx"), HttpWebRequest)
'This must be changed to teh name of the soap function you are calling: GetTable -->> your Method name isntead
req.Headers.Add("SOAPAction", """http://tempuri.org/GetTable""")
req.ContentType = "text/xml;charset=""utf-8"""
req.Accept = "text/xml"
req.Method = "POST"
Using stm As Stream = req.GetRequestStream()
Using stmw As New StreamWriter(stm)
stmw.Write(tsql)
End Using
End Using
Try
Dim response As WebResponse = req.GetResponse()
Dim responseStream As Stream = response.GetResponseStream()
'cast the responsestream into a datatable?
Dim Results As New DataTable
Dim Results As New DataTable("Results")
Dim ds As New DataSet()
ds.ReadXml(responseStream, XmlReadMode.ReadSchema)
Results = ds.Tables(0)
' TODO: Do whatever you need with the response
Catch ex As Exception
End Try
End Sub
Lowell