Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


sql server web service

Posted on 2013-05-24
Medium Priority
Last Modified: 2013-05-29
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:="")> _
<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 ?
Question by:maqskywalker
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 75

Accepted Solution

käµfm³d   👽 earned 1000 total points
ID: 39195931
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).
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 1000 total points
ID: 39197826
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.
LVL 75

Expert Comment

by:käµfm³d 👽
ID: 39197955
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  ; )
LVL 40
ID: 39198068
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.

Featured Post

Put Machine Learning to Work--Protect Your Clients

Machine learning means Smarter Cybersecurity™ Solutions.
As technology continues to advance, managing and analyzing massive data sets just can’t be accomplished by humans alone. It requires huge amounts of memory and storage, as well as the high-speed power of the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New Relic recently released its Synthetics product that allows for the creation of performance monitors that periodically test a site's performance. If you wish to test an interactive workflow New Relic employs Selenium WebDriverJS to run those test…
The task of choosing a web design company to build a website for your business should never be taken in a light manner. Provided the fact that your website will act as a representative to your business and will be responsible for imposing an online …
The purpose of this video is to demonstrate how to set up the WordPress backend so that each page automatically generates a Mailchimp signup form in the sidebar. This will be demonstrated using a Windows 8 PC. Tools Used are Photoshop, Awesome…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question