July 25, 2013 at 3:13 am
Hi,
Can i get a sample webservice which can:
1. Connect to SQL Server 2008
2. Take 'Database table name' as input and return its records
Please help.
July 26, 2013 at 3:53 am
This should help:
http://www.codeproject.com/Articles/20683/Publish-SQL-Server-2005-As-Webservice
MCITP SQL 2005, MCSA SQL 2012
July 26, 2013 at 5:56 am
||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
July 26, 2013 at 6:04 am
ok, after modifying the SoapAction to the correct name, i get the xml back of the data table, but the xml doesn't contain the schema,just the data i think, so i get an error trying to get the data table to read the xml.
ok i modified the posted code above; i create a dataset on the fly, have it read the xml, and then grab the first table in it as the datatable i need to fiddle with.
the above is now working 100%.
enjoy!
Lowell
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply