Solved

passing parameter to stored proced inside a web service

Posted on 2013-05-30
2
546 Views
Last Modified: 2013-05-30
Hi experts,

I'm using Visual Studio 2010 and VB in a Web Application and SQL Server 2008.
I'm using the Employees table of the Northwind Database.

I have a stored procedure called GetEmployeeDetailsByID that uses 1 parameter called @EmployeeID.  

The code for the stored procedure is this:

CREATE PROCEDURE [dbo].[GetEmployeeDetailsByID] 
	@EmployeeID int = 0
AS
BEGIN
	SET NOCOUNT ON;
	SELECT FirstName, LastName, BirthDate, City, Country 
	FROM Employees WHERE EmployeeID=@EmployeeID
END

Open in new window


When I execute the Stored Procedure and enter @EmployeeID = 1 then the stored procedure returns the following:

stored procedure result
I read this article, in this example their stored procedure performs an insert statement.
http://www.c-sharpcorner.com/UploadFile/718fc8/using-stored-procedure-in-web-service/

I'm trying do them same type of thing as in the example but in my stored procedure it is doing a select statement.

See I want to write a .asmx Web Service that will call this stored procedure and pass the parameter into it and execute it.

I have a Web Service called EmployeeByIDWebService.asmx

My code looks like this:

Imports System
Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
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 EmployeeByIDWebService
    Inherits System.Web.Services.WebService

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

    Dim constring As String = "Data Source=MYCOMPUTER\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True"

    Dim conn As SqlConnection
    Dim comm As SqlCommand

    <WebMethod(Description:="Simple Example")> _
    Public Function data(EmployeeID As Integer) As String
        conn = New SqlConnection(constring)
        conn.Open()

        comm = New SqlCommand()
        comm.Connection = conn
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        Try
            'Executes a Transact-SQL statement against the connection and returns the number of rows affected.
            comm.ExecuteNonQuery()

        Catch ex As Exception
            Throw ex
        Finally
            conn.Close()
            conn.Dispose()
        End Try

        Return

    End Function


End Class

Open in new window


I'm not sure if the syntax is correct,? Is this correct?  
In the Return statement what do I put?

When I run this Web Service in the browser I should get a text box similar to this one (this pic is from the example in the link above) where I enter the EmployeeID parameter and then when I click the Invoke parameter my web service should return my result.

viewing the web service in the browser
0
Comment
Question by:maqskywalker
2 Comments
 
LVL 1

Author Comment

by:maqskywalker
ID: 39208630
I revised my Web Service called EmployeeByIDWebService.asmx this is my code:

Imports System
Imports System.Collections.Generic
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
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 EmployeeByIDWebService
    Inherits System.Web.Services.WebService

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

    Dim constring As String = "Data Source=FCOE-DPN1TR1;Initial Catalog=Northwind;Integrated Security=True"

    Dim conn As SqlConnection
    Dim comm As SqlCommand

    <WebMethod(Description:="Simple Example")> _
    Public Function data(EmployeeID As Integer) As String
        conn = New SqlConnection(constring)
        conn.Open()

        comm = New SqlCommand()
        comm.Connection = conn
        comm.CommandType = System.Data.CommandType.StoredProcedure
        comm.CommandText = "GetEmployeeDetailsByID"
        comm.Parameters.AddWithValue("@EmployeeID", EmployeeID)

        Dim dtTable As New DataTable

        Try
            'Executes a Transact-SQL statement against the connection and returns the number of rows affected.
            comm.ExecuteNonQuery()

            dtTable.Load(comm.ExecuteReader())

        Catch ex As Exception
            Throw ex
        Finally
            conn.Close()
            conn.Dispose()
        End Try

        Return dtTable(0)(0)

    End Function

End Class

Open in new window


When I run it I get this:
when i run my service in web browser
Then if i click on the link for my method called data, i get this:

my web service when i run in browser
When I type in an EmployeeID value of 1 and click invoke I get this:

result for when I enter a parameter and invoke my data method
So Nancy is the FirstName column of the result returned by my stored procedure.

Is there a better way to write this VB code that I wrote in this example to return also the LastName, BirthDate, City, Country in the xml.
What I mean is, can I just return the table that is returned by my stored procedure as a DataTable or DataSet? How would I change my syntax of my Web Service to do that?
0
 
LVL 74

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 39208654
You certainly wouldn't call ExecuteNonQuery if you are intending on returning data from the database. That method is reserved for queries that don't return data:  INSERT, UPDATE, DELETE, etc. Since your query is returning more than one column, even though only one row, you will want to use the ExecuteReader method. Since your web method is returning a String you will want to work out how you are going to represent these columns as one string...or change your return type. Working with what you currently have, you could modify your code to the following to view a result in your test app:

...

	Try
            'Executes a Transact-SQL statement against the connection and returns the number of rows affected.
            Dim reader As SqlDataReader = comm.ExecuteReader()
            
            If reader.Read() Then ' Used If since you only have 1 row being returned.
                                  '  If you select more than one row, then you'll want a loop instead
            	Dim queryResult As String = reader("FirstName").ToString() + " " + reader("LastName").ToString() + " | " + reader("BirthDate").ToString() + " | " + reader("City").ToString() + " | " + reader(Country).ToString()
            	
            	Return queryResult
            End If

        Catch ex As Exception
	            Throw ex
	        Finally
	            conn.Close()
	            conn.Dispose()
	        End Try
	
        Return String.Empty

...

Open in new window

0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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: …

708 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

15 Experts available now in Live!

Get 1:1 Help Now