如何在C#中调用使用游标的存储过程

Question

如何使用C#用光标调用下面所示的存储过程？

ALTER PROCEDURE [dbo].[Customers_Cursor1]
AS
BEGIN
SET NOCOUNT ON;
-- DECLARE THE VARIABLES FOR HOLDING DATA.
DECLARE @CustomerId INT,
@Name VARCHAR(100),
@Country VARCHAR(100)

-- DECLARE AND SET COUNTER.
DECLARE @Counter INT
SET @Counter = 1

-- DECLARE THE CURSOR FOR A QUERY.
DECLARE PrintCustomers CURSOR READ_ONLY FOR
SELECT CustomerId, Name, Country
FROM Customers

-- OPEN CURSOR.
OPEN PrintCustomers

-- FETCH THE RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO @CustomerId, @Name, @Country

-- LOOP UNTIL RECORDS ARE AVAILABLE.
WHILE @@FETCH_STATUS = 0
BEGIN
SELECT @CustomerId, @Name, @Country
-- FETCH THE NEXT RECORD INTO THE VARIABLES.
FETCH NEXT FROM PrintCustomers INTO @CustomerId, @Name, @Country
END

-- CLOSE THE CURSOR.
CLOSE PrintCustomers
DEALLOCATE PrintCustomers
END

提前感谢

Answer 1

这应该可以工作。

在SQL 中

Exec Customers_Cursor1 <CustomerID>, <Name>, <Country>

在C#中：

using (SqlConnection Connection = new SqlConnection("YourConnectionString"))
{
SqlCommand Command = new SqlCommand("Customers_Cursor1", Connection);
Command.CommandType = CommandType.StoredProcedure;
Command.CommandTimeout = CommandTimeout;

Command.Connection.Open();
SqlDataReader dr = Command.ExecuteReader(CommandBehavior.CloseConnection);
DataTable MyTable = new DataTable(); //Returns a Table from the Query
MyTable.Load(dr);
dr.Close();
return MyTable;
}