Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Webservice to establish connection with SQL Server Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 3:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, October 23, 2013 11:07 PM
Points: 4, Visits: 19
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.
Post #1477406
Posted Friday, July 26, 2013 3:53 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 8:09 AM
Points: 316, Visits: 592
This should help:

http://www.codeproject.com/Articles/20683/Publish-SQL-Server-2005-As-Webservice


MCITP SQL 2005, MCSA SQL 2012
Post #1477925
Posted Friday, July 26, 2013 5:56 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
||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:
ObscureWebService

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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477954
Posted Friday, July 26, 2013 6:04 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:20 AM
Points: 12,927, Visits: 32,332
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1477955
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse