sql server web service

I'm using sql server 2008. I'm using the Northwind database and the Employees table.

I created a stored procedure called GetEmployees, this is the code:

CREATE PROCEDURE [dbo].[GetEmployees] 
SELECT [EmployeeID]
  FROM [Northwind].[dbo].[Employees]

Open in new window

I saw this example:


So now with Visual Studio 2010 I created a .asmx Web Service called GetEmployeesSPWebService.asmx

This is my code:

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient

' 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 GetEmployeesSPWebService
    Inherits System.Web.Services.WebService

    <WebMethod()> _
    Public Function HelloWorld() As String
       Return "Hello World"
    End Function

    <WebMethod()> _
    Public Function GetEmployeesSP(GetEmployees As String, ParameterNames As List(Of String), ParameterValues As List(Of [Object])) As DataTable
        Dim Connection As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
        Dim Reader As SqlDataReader = Nothing
        Dim SPResult As DataTable = Nothing

            Dim Command As New SqlCommand("GetEmployees", Connection)
            Command.CommandType = CommandType.StoredProcedure

            If ParameterNames IsNot Nothing Then
                For i As Integer = 0 To ParameterNames.Count - 1
                    Dim Parameter As New SqlParameter(ParameterNames(i), ParameterValues(i))
                    If Parameter.SqlDbType.Equals(SqlDbType.NVarChar) Then
                        Parameter.SqlDbType = SqlDbType.VarChar
                    End If

                    If Parameter.SqlValue Is Nothing Then
                        Parameter.SqlValue = DBNull.Value
                    End If

            End If
            Reader = Command.ExecuteReader()
            SPResult = New DataTable()

        Catch ex As Exception

            If Reader IsNot Nothing Then
            End If
        End Try
        Return SPResult
    End Function

End Class

Open in new window

The connection string in my web.config looks like this:


      <add name="NorthwindConnectionString" connectionString="Data Source=MyServer;Initial Catalog=Northwind;Integrated Security=True"
            providerName="System.Data.SqlClient" />      

When I browse my Web service in the browser I get this:

when i run my web service
Then if I click on GetEmployeesSP then i get this:

when i click on GetEmployeesSP
That doesn't look right to me, did I do something wrong in the code of GetEmployeesSPWebService.asmx ?
Who is Participating?
käµfm³d 👽Connect With a Mentor Commented:
Those are the help pages to show consumers of your service what parameters it expects. Do this:  Create a new project (it can be in the same solution if you like). Within the Solution Explorer, right-click the "References" node, and then select "Add Service Reference." In the dialog that opens, enter "http://localhost:51942/GetEmployeesSPWebService.asmx" in the "Address" field (it might be called "Location", I can't remember off the top of my head). Then click the "Go" button. You should see list box in the lower left display operations provided by your service. Enter a custom namespace in the text box at the bottom if you prefer. Click the "Ok" button.

Now, in a code file of this new project, type the following:

Dim client As New [whatever namespace was entered into the namespace box].[A class whose name ends in "SoapClient"]()
Dim dt As DataTable = client.GetEmployeesSP()

Open in new window

In other words, you are creating a new instance of a class. This class was created automatically when you added the service reference. It is found under the namespace that was entered in the bottom text box I mentioned above. The class itself will be named seemingly peculiarly, but it will end with the word "SoapClient". This is a proxy class that you will use to interact with your service. Once you have an instance of this proxy class, you can invoke the methods of your web service as if they were methods created within the class itself (like ordinary classes you create in .NET).
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
Nothing wrong with your method at first sight, but maybe you get it wrong about what a web service does.

A web service provides you with data, not with web pages. Your method returns a DataTable. It is up to you to build a page with that DataTable. This is most often done by creating a page that calls the method and binds the result with a grid control.
käµfm³d 👽Commented:
A web service provides you with data, not with web pages.
Which said "data", technically speaking, *could* itself be a web page. After all, what is a web page other than a stream of data interpreted by a browser  ; )
Jacques Bourgeois (James Burger)PresidentCommented:
Quite right kaufmed, and if i understand well your use of the * delimiter, I think we should put a little emphasis on those. Most web services return data that can be used with wathever you want to create a page, a Windows form, a WPF form, you name it. That is the may reason between web services: return data in a way that is in a standard format that can be used by any caller, be it .NET or Java, Windows Form or Web Page.

One should try not to confuse a beginner when he starts working with a new technology. Although it is not very clear in the question, it looks to me as if maqskywalker was expecting a Web page out of the DataTable that is returned by his web method. And a web method that returns a DataTable returns... a DataTable, not a web page.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.