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.....partia lly.

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.