Web service to call sql stored procedure

[barmatt80]

I don't know if this is the right part of the forum. But.... I have been working all night trying to create a web service to call a stored procedure in sql server 2008. The stored procedure calls a linked server to a db2 database that accepts 1 integer and returns 6 variables.

The end result would be publish the web service to our sharepoint servers. This is not a problem.

I am just trying to wrap my head around it. I know how to call the stored procedure using vb.net in a desktop app, just having problems transitioning to a web service to call that stored procedure.

Most searching finds trying to call a web service from a stored procedure, but I want to do the other way around, web service to call stored procedure.

Suggestions? Thanks for the help.

[PRR]

There is no difference in calling a stored procedure from web app or web service or for that matter windows service... The procedure is same .... You need connection string , command object and other regular things....

[barmatt80]

well i got the webservice to work.....partially.

I can pass my input variable,but I only get one variable back. I tried a class or a structure, but no go.

My input is an integer (intEmployeeID) and my outputs are string(strFName), string(strLName), integer (intAnnual), integer (intSick) and a couple other.

Suggestions on this?

Thanks.

[PRR]

Use SqlParameters with Direction as Output for outparameters

SqlParameter myOutputParameter = new SqlParameter("@One", SqlDbType.Int);
                myOutputParameter.Direction = ParameterDirection.Output;
 

Calling SQL Server stored procedures in ASP.NET

Retrieving Scalar Data from Stored Procedure Scott Mitchell

[Frinavale]

@barmatt80
You had the right idea using a class to pass the data back.... Why didn't the class work?

What did you attempt to do here?

You should have made a class ...maybe called EmployeeInfo ...that could be used to pass the information back to the client application.

For example (are you using C#?) your class would have looked something like:

public class EmployeeInfo{
  string _strFName;
  string _strLName;
  integer _annual;
  integer _sickDays;
} 

And your web method would look something like:

[WebMethod()]
public EmployeeInfo GetEmployeeInfo( (int employeeID))
{
  //This method calls the database and retrieves the employee information.
  //It creates and populates the EmployeeInfo Object that it passes
  //back to the client application
}

The GetEmployeeInfo web method returns an EmployeeInfo Object that contains the multiple output data that you need to pass back to the client application. This data is passed back as an Object, so is easy to use in the client application.

I might be giving you an answer that isn't even relevant to your problem though....

Are you having problems passing the data retrieved by your web service (web method) to the client application?

Or are you having problems retrieving the data from the database?

[barmatt80]

thanks for all the help. finavale I attempted the class again, just could not get it to work.

The ultimate goal is deploy this to our sharepoint servers for use with infopath forms. When i try the class, infopath recognizes the class(the fields defined within the class) as query fields not data fields.

But this is what I got.

<WebMethod(Description:="TEST: leave info")> _
    Public Function LeaveInfo(ByVal intEmployeeID As Integer)
 
        Dim strFName As String
        Dim strLName As String
        Dim strBnftDate As String
        Dim strEmail As String
        Dim decFam As Decimal
        Dim decAnn As Decimal
        Dim decSick As Decimal
        Dim decOther As Decimal
        Dim intSqlError As Integer
 
        Dim SQLCon As SqlConnection = New SqlConnection("Connection String")
        Dim SQLCmd As New SqlCommand("Stored Procedure Name", SQLCon)
        SQLCmd.CommandType = CommandType.StoredProcedure
        Dim Parameter0 As SqlParameter = New SqlParameter("@EmID", intEmployeeID)
        Dim Parameter1 As SqlParameter = New SqlParameter("@fname", SqlDbType.VarChar, 15)
        Dim Parameter2 As SqlParameter = New SqlParameter("@lname", SqlDbType.VarChar, 30)
        Dim Parameter3 As SqlParameter = New SqlParameter("@bnftdate", SqlDbType.Char, 10)
        Dim Parameter4 As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 50)
        Dim Parameter5 As SqlParameter = New SqlParameter("@fam", SqlDbType.Decimal)
        Dim Parameter6 As SqlParameter = New SqlParameter("@ann", SqlDbType.Decimal)
        Dim Parameter7 As SqlParameter = New SqlParameter("@sick", SqlDbType.Decimal)
        Dim Parameter8 As SqlParameter = New SqlParameter("@oth", SqlDbType.Decimal)
        Dim Parameter9 As SqlParameter = New SqlParameter("@sqlerror", SqlDbType.Decimal)
 
        Parameter0.Direction = ParameterDirection.Input
        Parameter1.Direction = ParameterDirection.Output
        Parameter2.Direction = ParameterDirection.Output
        Parameter3.Direction = ParameterDirection.Output
        Parameter4.Direction = ParameterDirection.Output
        Parameter5.Direction = ParameterDirection.Output
        Parameter6.Direction = ParameterDirection.Output
        Parameter7.Direction = ParameterDirection.Output
        Parameter8.Direction = ParameterDirection.Output
        Parameter9.Direction = ParameterDirection.Output
 
        SQLCmd.Parameters.Add(Parameter0)
        SQLCmd.Parameters.Add(Parameter1)
        SQLCmd.Parameters.Add(Parameter2)
        SQLCmd.Parameters.Add(Parameter3)
        SQLCmd.Parameters.Add(Parameter4)
        SQLCmd.Parameters.Add(Parameter5)
        SQLCmd.Parameters.Add(Parameter6)
        SQLCmd.Parameters.Add(Parameter7)
        SQLCmd.Parameters.Add(Parameter8)
        SQLCmd.Parameters.Add(Parameter9)
 
        SQLCon.Open()
        SQLCmd.ExecuteNonQuery()
 
        strFName = SQLCmd.Parameters("@fname").Value
        strLName = SQLCmd.Parameters("@lname").Value
        strBnftDate = SQLCmd.Parameters("@bnftdate").Value
        strEmail = SQLCmd.Parameters("@email").Value
        decFam = SQLCmd.Parameters("@fam").Value
        decAnn = SQLCmd.Parameters("@ann").Value
        decSick = SQLCmd.Parameters("@sick").Value
        decOther = SQLCmd.Parameters("@oth").Value
        intSqlError = SQLCmd.Parameters("@sqlerror").Value
 
        'not working
        'Return EmployeeInfo

I commented out the return as it doesn't work. Connection and getting the data is no problem. As I can concat the returned values into one string or variable using something like strReturnValue = strFName & strLName&....... then Return strReturnValue .

Thanks again for the help.

[barmatt80]

I am thinking i am missing something totally simple.

Any suggestions?

thanks for the help.

I'll keep banging away on it.

[Frinavale]

I don't know why I missed your last post.
Sorry that I didn't see it before now!

Your function is missing the return type.
You have:
Public Function LeaveInfo(ByVal intEmployeeID As Integer)

But it should be:
Public Function LeaveInfo(ByVal intEmployeeID As Integer) As Employee

I'm testing a web service right now...my setup is a bit different and I'm calling it from an ASPX page but I'm having no problems passing back a custom data type back to the client app.

Is your custom data type (your Employee Object) Public?

[barmatt80]

cool, let me give it a test here in the next couple hours and i'll let you know what i find out.

been a bit busy lately.

i appreciate your help.

[Frinavale]

The other thing I noticed is that you're using the ExecuteNonQuery method. This method is used to execute SQL statements that don't return a value.

Since you are expecting a value to be returned you should be executing the ExecuteReader method. This method will return a SqlDataReader type that will contain all of the records that result from executing your stored procedure.

Please take a look at this basic article on How to use a database in your program.

If you are returning an Object, then you should be populating that object with the information retrieved from the database....I don't see that in your code either.

[barmatt80]

Oh my bad on the ExecuteNonQuery, i read somewhere where to use it if it returned one record, which is what i am looking for.

I'll make some changes later on this evening after i get off work and give it a try.

[barmatt80]

Frinavale, thank you very much for the help. I always find this place a wealth of knowledge. Least I can do is post my code that I got to work. Sorry for the delay in posting back, but i reorganized my favorites and couldn't find anything. I have since made 3 more web services and combined some processes within stored procedures rather than doing the owrk in web service.

My stored procedure had one input variable(@email0) and returned 5 variables (@fname, @lname, @email, @Leave & @ErrorID).

Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.ComponentModel
Imports System.Data
Imports System.Data.SqlClient
 
 
 
Public Structure sEmployee
Public strFName As String
Public strLName As String
Public strEmail As String
Public decLeave As Decimal
Public intErrorID As Integer
End Structure
 
<WebMethod(Description:="GET Employee info")> _ 
Public Function GetLeaveInfo(ByVal strEmail As String) As sEmployee
 
Dim sqlCon As New SqlConnection()
Dim sqlCmd As New SqlCommand()
 
Dim ObjEmployee As New sEmployee
 
sqlCon.ConnectionString = "***Connection String***"
 
If sqlCon.State = ConnectionState.Closed Then
sqlCon.Open()
End If
 
sqlCmd.CommandText = "***Stored Procedure Name***"
sqlCmd.Connection = sqlCon
sqlCmd.CommandType = CommandType.StoredProcedure
 
 
Dim Parameter0 As SqlParameter = New SqlParameter("@Email0", strEmail) 
Dim Parameter1 As SqlParameter = New SqlParameter("@fname", SqlDbType.VarChar, 30) 
Dim Parameter2 As SqlParameter = New SqlParameter("@lname", SqlDbType.VarChar, 30) 
Dim Parameter3 As SqlParameter = New SqlParameter("@email", SqlDbType.VarChar, 75) 
Dim Parameter4 As SqlParameter = New SqlParameter("@Leave", SqlDbType.Decimal)
Dim Parameter5 As SqlParameter = New SqlParameter("@errorID", SqlDbType.Int)
 
Parameter4.Precision = 10
Parameter4.Scale = 2
 
Parameter0.Direction = ParameterDirection.Input 
Parameter1.Direction = ParameterDirection.Output 
Parameter2.Direction = ParameterDirection.Output 
Parameter3.Direction = ParameterDirection.Output 
Parameter4.Direction = ParameterDirection.Output
Parameter5.Direction = ParameterDirection.Output
 
SQLCmd.Parameters.Add(Parameter0) 
SQLCmd.Parameters.Add(Parameter1) 
SQLCmd.Parameters.Add(Parameter2) 
SQLCmd.Parameters.Add(Parameter3) 
SQLCmd.Parameters.Add(Parameter4)
SQLCmd.Parameters.Add(Parameter5)
 
Dim dr As SqlDataReader
dr = sqlCmd.ExecuteReader()
 
ObjEmployee.strFName = sqlCmd.Parameters("@fname").Value
ObjEmployee.strLName = sqlCmd.Parameters("@lname").Value
ObjEmployee.strEmail = sqlCmd.Parameters("@email").Value
ObjEmployee.decLeave = sqlCmd.Parameters("@leave").Value
ObjEmployee.decLeave = sqlCmd.Parameters("@errorID").Value
 
Return ObjEmployee

[premtd]

Hi ,

I have done the same above procedure , I am getting the below error only when i execute. There are no errors when i debug.

System.Data.SqlClient.SqlException: Procedure or function ESS_fetchrecords has too many arguments specified.
at System.Data.SqlClient.SqlConnection.OnError(SqlExc eption exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnErro r(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndW arning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaDat a()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteRead er(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderT ds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader( CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(Com mandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at ESSWebService.Service1.GetEmplInfo(String strEmpId) in c:\users\premkumartd\documents\visual studio 2010\Projects\ESSWebService\ESSWebService\Service1 .asmx.vb:line 53

I used the below vb.net code

Public Structure sEmployee
 
    Public StrEmpName As String
    Public StrGender As String
    Public StrStatus As String
    Public StrEmail As String
End Structure
' 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 Service1
    Inherits System.Web.Services.WebService
 
    <WebMethod(Description:="GET Employee info")> _
    Public Function GetEmplInfo(ByVal strEmpId As String) As sEmployee
        Dim sqlCon As New SqlConnection()
        Dim sqlCmd As New SqlCommand()
        Dim ObjEmployee As New sEmployee
        sqlCon.ConnectionString = "Data Source=***;Initial Catalog=****;User ID=***;Password=****"
        If sqlCon.State = ConnectionState.Closed Then
            sqlCon.Open()
        End If
        sqlCmd.CommandText = "ESS_fetchrecords"
        sqlCmd.Connection = sqlCon
        sqlCmd.CommandType = CommandType.StoredProcedure
        Dim Parameter0 As SqlParameter = New SqlParameter("@EmpNumber", strEmpId)
        'Dim Parameter1 As SqlParameter = New SqlParameter("@EmpNo", SqlDbType.VarChar, 500)
        Dim Parameter2 As SqlParameter = New SqlParameter("@EmployeeName", SqlDbType.VarChar, 1000)
        Dim Parameter3 As SqlParameter = New SqlParameter("@Gender", SqlDbType.VarChar, 1)
        Dim Parameter4 As SqlParameter = New SqlParameter("@Status", SqlDbType.VarChar, 1)
        Dim Parameter5 As SqlParameter = New SqlParameter("@EmailID", SqlDbType.VarChar, 150)
        Parameter0.Direction = ParameterDirection.Input
        'Parameter1.Direction = ParameterDirection.Output
        Parameter2.Direction = ParameterDirection.Output
        Parameter3.Direction = ParameterDirection.Output
        Parameter4.Direction = ParameterDirection.Output
        Parameter5.Direction = ParameterDirection.Output
        sqlCmd.Parameters.Add(Parameter0)
                sqlCmd.Parameters.Add(Parameter2)
        sqlCmd.Parameters.Add(Parameter3)
        sqlCmd.Parameters.Add(Parameter4)
        sqlCmd.Parameters.Add(Parameter5)
        Dim dr As SqlDataReader
        dr = sqlCmd.ExecuteReader()
        ObjEmployee.StrEmpName = sqlCmd.Parameters("@EmployeeName").Value
        ObjEmployee.StrGender = sqlCmd.Parameters("@Gender").Value
        ObjEmployee.StrStatus = sqlCmd.Parameters("@Status").Value
        ObjEmployee.StrEmail = sqlCmd.Parameters("@EmailID").Value
        Return ObjEmployee
    End Function
 
 
 

and have the below sql stored procedure code

PROCEDURE [dbo].[ESS_fetchrecords]

@EmpNumber int

AS
BEGIN

SELECT
[EmployeeName]
,[Gender]
,[Status]
,[EmailID]
FROM [Orchid].[dbo].[Employee] where EmpNo= @EmpNumber


Please help on what mistake am doing...