StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » How to call stored procdure and return different type value?

How to call stored procdure and return different type value?

Raymond Lo — Thu, 16 Apr 2009 05:30:58 GMT

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.

RE: How to call stored procdure and return different type value?

Raymond Lo — Thu, 16 Apr 2009 05:30:58 GMT

Thanks Trent,

It's work.

RE: How to call stored procdure and return different type value?

Trent L. Taylor — Wed, 15 Apr 2009 10:46:38 GMT

[quote]Isn't there extra overhead by calling the sproc from within the BO?[/quote]

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.

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

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.

RE: How to call stored procdure and return different type value?

Bill Cunnien — Wed, 15 Apr 2009 10:32:29 GMT

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

RE: How to call stored procdure and return different type value?

Trent L. Taylor — Wed, 15 Apr 2009 08:41:29 GMT

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

[codesnippet]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)[/codesnippet]

Calling Outside of a BO

[codesnippet]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)[/codesnippet]

RE: How to call stored procdure and return different type value?

Raymond Lo — Wed, 15 Apr 2009 05:16:17 GMT

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()

RE: How to call stored procdure and return different type value?

Trent L. Taylor — Tue, 07 Apr 2009 09:32:03 GMT

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.

RE: How to call stored procdure and return different type value?

Raymond Lo — Tue, 07 Apr 2009 04:25:15 GMT

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,

RE: How to call stored procdure and return different type value?

Greg McGuffey — Tue, 07 Apr 2009 00:02:05 GMT

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!).

[codesnippet]'-- 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[/codesnippet]