Webservice to establish connection with SQL Server

  • 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.

  • This should help:

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

    MCITP SQL 2005, MCSA SQL 2012

  • ||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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • 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


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply