Dear All,

      I have created a stored procdure name sp_GetInfoCurrency  and the codeing as bellow:

CREATE PROCEDURE [dbo].[sp_GetInfoCurrency]
 @pcurcode nchar(5),
 @pcurid int output,
 @pcurname nvarchar(50) output,
 @pcurrate smallmoney output
AS
BEGIN
 SET NOCOUNT ON;

 select @pcurid=curid, @pcurname=curname, @pcurrate=currate from currency where curcode=@pcurcode
 if @pcurid is null
 begin
  set @pcurid=0
  set @pcurname=''
  set @pcurrate=0
 end
END

         My question is, How to call the stored procdure through StrataFrame, Is there any method or fuctions easy to do this?
         Please provide the sample code.

Thanks.

Dear  Greg /Trent ,

       I can not apply the coding to my Queuies.vb  success (all functions in this file), It alway show : Me.ExecuteNonQuery(cmd) --don't accept any parameters. I want to know , Is this code must apply in the BO as a BO method ?

      I only want to call my stored procedure and return the value (string or integer). My new coding as bellow but still not work. The error show that "miss the parameter @pcurcode"  I don't know where is wrong.  Can you help me check?

        Dim cmd As New SqlCommand()
        Dim conn As New SqlConnection(MicroFour.StrataFrame.Data.DataBasics.DataSources("").ConnectionString)
        conn.Open()
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@pcurcode", SqlDbType.Char, 5)

        cmd.Parameters("@pcurcode").Value = Me.cboCurrency.Text
        cmd = New SqlClient.SqlCommand("sp_GetInfoCurrency", conn)
        cmd.ExecuteNonQuery()

Raymond, I see a number of minor issues here, but the biggest "question mark" that I have is that is where you are trying to execute this query and why you aren't going through a business object.  Even if you wanted to execute this outside of a BO, you still never need to set the connection in SF, it will handle this for you.

In regards to some issues, you were creating two commands, one of which did not specify the SPROC.  Since you were reusing the same variable, the code at the top was unnecessary.  Then when you create the second SqlCommand, the variable will no longer exist.  Below would be a better example of how to execute this. 

Calling from within a BO

Calling Outside of a BO

Isn't there extra overhead by calling the sproc from within the BO? I would assume that the entire BO needs to be instantiated before the code can run the sproc; however, in the call from outside the BO, that is never the case. Is it possible that I lose something, though, by not going through the BO?



I have used both ways of executing an sproc...just wondering which is the cleaner way.



Thanks,

Bill

Not really, no.  I would say that 80% of all SPROCs that we execute are through a BO...if not more.  I am being conservative.  Most times you are using a SPROC to load or update data.  Especially ini the case of retrieving result sets, you would have fewer steps by doing this within the BO itself.

Well, if you are trying to retrieve result sets, then this is going to be more overhead as now you have to get the result sets into BOs.  When we write report data sources, we may have as many as 20 result sets (or even more) in some cases.  In this scenario, we will use the FillMultipleDataTables method on the BusinessLayer.  This way we can populate all of the BOs at the same time in one foul swoop.  This is significantly faster when pulling back multiple result sets.