how to call a sqlserver proc to get back a dataset?

12 Mar 2006 9:03 AM

Fred Exley

Here's the sqlserver procedure:

------------------------------------------------------------------------

ALTER PROCEDURE monte_monte.Proc_Ingrs
/*

(

@parameter1 int = 5,

@parameter2 datatype OUTPUT

)

*/

@dsSQLOutputCursor cursor varying OUTPUT

AS

/* SET NOCOUNT ON */

SET @dsSQLOutputCursor = CURSOR FOR

SELECT NDB_No, FDGRP_CD, DESCRIPTION, USED FROM tblIngredients WHERE FdGrp_Cd = '0400'

OPEN @dsSQLOutputCursor

RETURN

------------------------------------------------------------------------

And heres my c# attempt to call this procedure:

// SqlServer database call:

Database db = DatabaseFactory.CreateDatabase("monte_Plan9ConnectionString");

DataSet dsSQLOutputCursor = new DataSet();

DbCommand dbCommand = db.GetStoredProcCommand("Proc_Ingrs",dsSQLOutputCursor);

//db.AddInParameter(dbCommand, dsSQLOutputCursor, DbType.Int32, 7);

dsSQLOutputCursor = db.ExecuteDataSet(dbCommand);

msgBack = "Datasets were accessed successfully.";

------------------------------------------------------------------------

And nothing is retrieved. I think I need to set the cursor parameter (commented out), but what's the syntax for calling a proc to retrieve a cursor? Thanks for any ideas -Fred

12 Mar 2006 8:33 PM

Otis Mukinfus

Show quote

On Sun, 12 Mar 2006 01:03:19 -0800, "Fred Exley" <fexley***@msn.com> wrote:

>Here's the sqlserver procedure:
>
>------------------------------------------------------------------------
>
>ALTER PROCEDURE monte_monte.Proc_Ingrs
>/*
>
>(
>
>@parameter1 int = 5,
>
>@parameter2 datatype OUTPUT
>
>)
>
>*/
>
>@dsSQLOutputCursor cursor varying OUTPUT
>
>AS
>
>/* SET NOCOUNT ON */
>
>SET @dsSQLOutputCursor = CURSOR FOR
>
>SELECT NDB_No, FDGRP_CD, DESCRIPTION, USED FROM tblIngredients WHERE FdGrp_Cd = '0400'
>
>OPEN @dsSQLOutputCursor
>
>RETURN
>
>------------------------------------------------------------------------
>
>And heres my c# attempt to call this procedure:
>
>// SqlServer database call:
>
>Database db = DatabaseFactory.CreateDatabase("monte_Plan9ConnectionString");
>
>DataSet dsSQLOutputCursor = new DataSet();
>
>
>DbCommand dbCommand = db.GetStoredProcCommand("Proc_Ingrs",dsSQLOutputCursor);
>
>//db.AddInParameter(dbCommand, dsSQLOutputCursor, DbType.Int32, 7);
>
>dsSQLOutputCursor = db.ExecuteDataSet(dbCommand);
>
>msgBack = "Datasets were accessed successfully.";
>
>------------------------------------------------------------------------
>
>
>
>And nothing is retrieved. I think I need to set the cursor parameter (commented out), but what's the syntax for calling a proc to retrieve a cursor? Thanks for any ideas -Fred
>
>
>

No need to use a cursor in MS SQL Server SP unless you feel you must.

Use the same syntax you would use in any select statement. By the way, you have
the parameters declared correctly. So: SELECT * FROM myTable WHERE @myParam =
myColumn is all you need to return a resultset from MS SQL Server.

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

15 Mar 2006 12:23 AM

Fred Exley

Hey thanks, man. Works fine now:

--------------------------------------------------------------------------------------------------------------

ALTER PROCEDURE monte_monte.GetItems (@parameter1 char(1))

AS

BEGIN

IF @parameter1 = 'c'

Select * from tblIngredients where used = 'y'

ELSE

Select * from tblIngredients

END

RETURN

---------------------------------------------------------------------------
and to call it:
public GetItems(string pWhichSet) // index value 1 = common items)

{

// call a sqlserver proceure:

Database db = DatabaseFactory.CreateDatabase("monte_Plan9ConnectionString");

dsIngredients = db.ExecuteDataSet("GetItems", pWhichSet);

tblIngredients = dsIngredients.Tables[0];

....

------------------------------------------------------------------------------------------------------------------

-Fred