shane

shane

  • NA
  • 2
  • 0

How do I load a SQL Cursor from a stored procedure into a datareader

Mar 7 2009 4:41 PM

Gday, im fairly new to C# and ado.net

I have a stored procedure in SQL Server 2005 that returns a cursor, as below;

CREATE PROCEDURE SP_GetModules

(

@ModuleCursor CURSOR VARYING OUTPUT

)

AS

SET NOCOUNT ON;

SET @ModuleCursor = CURSOR

FORWARD_ONLY STATIC FOR

SELECT uid, name, active

FROM BIM.module;

OPEN @ModuleCursor;

GO

In C# I want to get this cursors resultset and use it, so I am thinking its something like this;

Question 1) How do I load the cursor into ADO.Net Reader

Question 2) Do I need to reference index of colums, cant I use column names ?

private void LoadModules(ref SqlConnection Connection)

{

SqlCommand command = Connection.CreateCommand();

command.CommandText = "BIM.dbo.SP_GetModules";

command.CommandType = CommandType.StoredProcedure;

//How do I load the cursor into ADO.Net Reader ?

//command.Parameters.Add("@ModuleCursor", SqlDbType.Binary);

//command.Prepare();

//command.Parameters["@ModuleCursor"].Value = command.;

SqlDataReader reader = command.ExecuteReader();

while (reader.Read())

{

amodule = new Module();

//Question (2) Can I use column name instead of index number ?

//amodule.UID = reader.GetInt32(0);

//amodule.Name = reader.GetString(1);

//amodule.Active = reader.GetBoolean(2);

this.Add(amodule);

}

}


Answers (1)