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

  • Thread starter Thread starter Fred Exley
  • Start date Start date
F

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.";
 
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
Click to expand...
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
 
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];

....
 
Back
Top