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);}
}
Vijaya KadiyalaPosted Mar 11, 2009, 12:06 PM
Hi
Why do you want do return with Cursor!! are you from oralce background?? you can simple write one SQL in Procedure and call that procedure in your ado.net.
Thanks -- Vijaya Kadiyala