StrataFrame Forum » StrataFrame Application Framework - V1 » WinForms (How do I?) » Using DataBasics to execute a non-bo-bound stored procedure

Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 09 Jun 2009 11:07:12 GMT

Hi,

I am wondering if someone could provide a sample code as how I can use

DataBasics.DataSources[""].ExecuteStoredProcedure(..)

My sproc takes 2 varchar input parameters and returns an integer value:

@HWID VARCHAR(250),

@ComputerName VARCHAR(250),

@iKassenId int OUTPUT

i want to execute the sproc and get the Result integer.

Thanks !

RE: Using DataBasics to execute a non-bo-bound stored procedure

Greg McGuffey — Tue, 09 Jun 2009 11:07:12 GMT

LOL! Glad you found a solution to your issue though!

RE: Using DataBasics to execute a non-bo-bound stored procedure

Alex Luyando — Tue, 09 Jun 2009 08:48:05 GMT

[quote][b]Greg McGuffey (08/26/2008)[/b][hr]I'm wondering if the problem is that you are using Return instead of Select. My understanding is that Return is used to return a status code, while you'd use Select to return a record set, which ExecuteScalar will then retrieve data from. [/quote]

How I wish I had read this about two hours earlier! LOL

Very handy to know.. Thanks!

RE: Using DataBasics to execute a non-bo-bound stored procedure

Greg McGuffey — Wed, 27 Aug 2008 11:10:01 GMT

Glad you got it working and that I was of help! :D

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Wed, 27 Aug 2008 02:49:56 GMT

Just letting you know it does indeed work now using SELECT instead of RETURN.

Thanks again to Greg for pointing this out. :)

RE: Using DataBasics to execute a non-bo-bound stored procedure

Dustin Taylor — Tue, 26 Aug 2008 11:06:52 GMT

Yep, greg nailed it. Even though it is a scalar, you still want to return a recordset, hence the need for a SELECT.

RETURN will exit the code loop immediately. It can return an integer, but that is typically used to show success or failure and isn't what you are looking for in this case.

RE: Using DataBasics to execute a non-bo-bound stored procedure

Michel Levy — Tue, 26 Aug 2008 11:02:36 GMT

Philipp,

Using a BO is for a maintenance purpose (All the business code is in the BO, all the connections against SQL server run from a BO to DL)

And the intermediate SP (or execute as.. revert) is for a security purpose: I never expose a SP directly in the AppUser schema.

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 10:16:33 GMT

hi,

thanks. that seems like it could be the problem. i wasnt aware of the use of select instead of return.

i will try changing it when i get back to work tommorow.

RE: Using DataBasics to execute a non-bo-bound stored procedure

Greg McGuffey — Tue, 26 Aug 2008 10:06:08 GMT

I'm wondering if the problem is that you are using Return instead of Select. My understanding is that Return is used to return a status code, while you'd use Select to return a record set, which ExecuteScalar will then retrieve data from. So change:

[codesnippet]RETURN @iKassenId[/codesnippet]

to

[codesnippet]Select @iKassenId[/codesnippet]

and see if that works. I'm guessing that since you don't select anything, either null or DdNull.Value is being returned from execute scalar.

You might also want to check for conditions like this in code. Something like:

[codesnippet]object result = DataBasics.DataSources[""].ExecuteStoredProcedure("spK_KassenAnmeldung", DbCommandExecutionType.ExecuteScalar, spParams);
if result != null && result != DbNull.Value
{
return (int)result;
}
else
{
// return default or throw an exception
return 0
}[/codesnippet]

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 09:24:59 GMT

Hi Dustin,

this is my sproc:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

ALTER PROCEDURE [dbo].[spK_KassenAnmeldung]

@HWID VARCHAR(250),

@ComputerName VARCHAR(250)

DECLARE @iKassenId int

SET @iKassenId = ( SELECT TOP 1 ID FROM tbKassen WHERE

HWID = @HWID AND ComputerName = @ComputerName );

IF ( @iKassenId IS NULL )

BEGIN

DECLARE @iKassenNr int;

SET @iKassenNr = ( SELECT count(*) FROM tbKassen );

INSERT INTO tbKassen (Bezeichnung, Zyklus, HWID, ComputerName)

VALUES ('Kasse'+LTRIM(STR(@iKassenNr)) , 0, @HWID, @ComputerName);

SET @iKassenId = SCOPE_IDENTITY();

END

RETURN @iKassenId

RE: Using DataBasics to execute a non-bo-bound stored procedure

Dustin Taylor — Tue, 26 Aug 2008 09:24:01 GMT

Your code looks good. What is in your stored procedure?

I just made a test that comes accross fine using the StrataFrame sample. Here is my SF code:

[codesnippet]Dim spParams As System.Data.Common.DbParameter() = {New System.Data.SqlClient.SqlParameter}
spParams(0).ParameterName = "@cust_pk"
spParams(0).Value = "1"
Dim test As String = MicroFour.StrataFrame.Data.DataBasics.DataSources("").ExecuteStoredProcedure("GetCustomerLastName", MicroFour.StrataFrame.Data.DbCommandExecutionType.ExecuteScalar, spParams)[/codesnippet]

And here is the alter for my stored procedure:

[codesnippet]set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetCustomerLastName]
-- Add the parameters for the stored procedure here
@cust_pk int = 1
AS
BEGIN
-- Insert statements for procedure here
SELECT cust_LastName FROM Customers WHERE cust_pk = @cust_pk
END[/codesnippet]

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 09:20:53 GMT

HI Michel,

thanks for you reply. i dont know i it runs using a bo, and for this particular function i dont really want to use a bo.

i dont quite understand what you mean by:

[quote](you need to grant exec in an intermediate SP, or execute as ..., and revert after execution)[/quote]

From the Help-Document, the ExecuteStoredProcedure function is capable of doing what i want. i dont understand why i would need to use a bo for it. ?

RE: Using DataBasics to execute a non-bo-bound stored procedure

Michel Levy — Tue, 26 Aug 2008 09:09:44 GMT

Hi Phillip,

Does your procedure run fine (and returns a nice integer value) when executed from within a BO?

And why not using a BO?
I've a lot of SP and Scalar functions on a SQL database, all in dbo schema, and I use any of my BOs (no one connecting on dbo schema) to run theses SP and functions (you need to grant exec in an intermediate SP, or execute as ..., and revert after execution)

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 08:51:00 GMT

Shorter Code, but same error:

private static int KassenAnmeldung()

{

return (int)DataBasics.DataSources[""].ExecuteStoredProcedure("spK_KassenAnmeldung", DbCommandExecutionType.ExecuteScalar, new System.Data.SqlClient.SqlParameter("@HWID", "123123123"),new System.Data.SqlClient.SqlParameter("@ComputerName", "Computer9"));

}

For some reason the Object returned by executestoredprocedure has a value of NULL.

Executing the same sproc on the sql management studio with the same parameters returns a nice integer value.

i dont get it ?

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 06:38:25 GMT

heres the complete code:

private static int KassenAnmeldung()

{

System.Data.Common.DbParameter[] spParams = new System.Data.SqlClient.SqlParameter[2];

spParams[0] = new System.Data.SqlClient.SqlParameter();

spParams[1] = new System.Data.SqlClient.SqlParameter();

spParams[0].ParameterName = "@HWID";

spParams[1].ParameterName = "@ComputerName";

spParams[0].Value = "123123123";

spParams[1].Value = "Computer1";

return (int)DataBasics.DataSources[""].ExecuteStoredProcedure("spK_KassenAnmeldung", DbCommandExecutionType.ExecuteScalar, spParams);

}

the stored procedure itself works. i tested it within sql management studio:

DECLARE @i int

EXEC @i= spK_KassenAnmeldung '123123123','Computer9';

select @i

RE: Using DataBasics to execute a non-bo-bound stored procedure

Philipp Guntermann — Tue, 26 Aug 2008 05:59:04 GMT

i tried it like that and get a NULLREFERENCE exception:

int iResult = 0;

System.Data.Common.DbParameter[] spParams = new System.Data.SqlClient.SqlParameter[2];

spParams[0].Value = "123123123";

spParams[1].Value = "Computer1";

iResult = (int)DataBasics.DataSources[""].ExecuteStoredProcedure("spK_KassenAnmeldung", DbCommandExecutionType.ExecuteScalar, spParams);