?
Solved

passing parameter to stored proced inside a web service

Posted on 2013-05-30
2
Medium Priority
?
561 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
[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 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 75

Accepted Solution

by:
käµfm³d   👽 earned 2000 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

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…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

777 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