Solved

sql server web service

Posted on 2013-05-24
4
728 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

Automating Your MSP Business

The road to profitability.
Delivering superior services is key to ensuring customer satisfaction and the consequent long-term relationships that enable MSPs to lock in predictable, recurring revenue. What's the best way to deliver superior service? One word: automation.

Question has a verified solution.

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

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 …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
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…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

617 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