Solved

sql server web service

Posted on 2013-05-24
4
708 Views
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] 
AS
SELECT [EmployeeID]
      ,[LastName]
      ,[FirstName]
      ,[Title]
      ,[BirthDate]
  FROM [Northwind].[dbo].[Employees]

Open in new window


I saw this example:

http://forums.asp.net/t/1831344.aspx/1?ASMX+Web+Service+Stored+Procedures+and+MVC+Models

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

        Try
            Connection.Open()
            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

                    Command.Parameters.Add(Parameter)
                Next
            End If
            Reader = Command.ExecuteReader()
            SPResult = New DataTable()

            SPResult.Load(Reader)
        Catch ex As Exception
            Throw
        Finally
            Connection.Close()

            If Reader IsNot Nothing Then
                Reader.Close()
            End If
        End Try
        Return SPResult
    End Function

End Class

Open in new window


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

    <connectionStrings>

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


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 ?
0
Comment
Question by:maqskywalker
[X]
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
4 Comments
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 250 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).
0
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 250 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.
0
 
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  ; )
0
 
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.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
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…

734 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