StrataFrame Forum

How to call stored procdure and return different type value?

http://forum.strataframe.net/Topic22665.aspx

By Raymond Lo - 4/6/2009

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.

By Greg McGuffey - 4/6/2009

Raymond,



This is very easy! The trick is to use parameters to get the return data. I'm just typing this, so the enums for CommandType, Direction and SqlDataType aren't exactly accurate (intellisense will fix 'em right up though!).



'-- Assumes that pcurcode is provided via a method argument

Using cmd As New SqlCommand()

  '-- Setup sproc

  cmd.CommandType = StoredProcedure

  cmd.CommandText = "sp_GetInfoCurrency"



  '-- Add input param

  cmd.Parameters.Add("@pcurcode",char,5)

  cmd.Parameters("@pcurcode").Value = pcurcode



  '-- Add output params

  cmd.Parameters.Add("@pcurid",int).Direction = Output

  cmd.Parameters.Add("@pcurname",nvarchar,50).Direction = Output

  cmd.Parameters.Add("@pcurrate",smallmoney).Direction = Output



  '-- Execute spoc

  Me.ExecuteNonQuery(cmd)



  '-- Get output via params

  ' Note that you might need to check for NULLs here too

  ' (I'm not here though)

  ' Vars declared outside of Using someplace....

  pcurid = CType(cmd.Parameters("@pcurid").Value, Integer)

  pcurname = Ctype(cmd.Parameters("@pcurname").Value, String)

  pcurrate = CType(cmd.Parameters("@pcurrate").Value, Decimal)

End Using
By Raymond Lo - 4/6/2009

Hi Greg McGuffey,

     Thank you very much!!!

     Is there any way through StrataFrame to call this procdure and return the value? Because I want to using the StrataFrame connections to retrieve the data.

Regards,

By Trent L. Taylor - 4/7/2009

That is what Greg is showing you.  The Me.ExecuteNonQuery is being executed on the BO, so when you pass over the SqlCommand it will update the Connection of that object through the BO without any additional coding.
By Raymond Lo - 4/14/2009

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()
By Trent L. Taylor - 4/15/2009

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

Dim cmd As New SqlCommand("dbo.sp_GetInfoCurrency")

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@pcurcode", SqlDbType.Char).Value  = me.cboCurrency.Text
Me.ExecuteNonQuery(cmd)

Calling Outside of a BO

Dim cmd As New SqlCommand("dbo.sp_GetInfoCurrency")

cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@pcurcode", SqlDbType.Char).Value  = me.cboCurrency.Text
MicroFour.StrataFrame.DataBasics.DataSources("").ExecuteNonQuery(cmd)
By Bill Cunnien - 4/15/2009

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
By Trent L. Taylor - 4/15/2009

Isn't there extra overhead by calling the sproc from within the BO?

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.

Is it possible that I lose something, though, by not going through the BO?

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.

By Raymond Lo - 4/15/2009

Thanks Trent,

It's work.