Solved

sql server web service

Posted on 2013-05-24
4
620 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
  • 2
  • 2
4 Comments
 
LVL 74

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 74

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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

These instructions are based on installing Owncloud on your new raspberry pi connected with a usb HDD. What do you need Part A? A Raspberry Pi, model B. A boot SD card for the Raspberry Pi. A usb HDD An Ethernet cable to connect to the lo…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
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…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now