如何从asp.net用游标参数调用存储过程? [英] how to call stored procedure with cursor parameter from asp.net?

问题描述

我在sql server中用光标更改输出参数创建了一个过程
如何从asp.net页访问此过程并将数据绑定到gridview

例如:
创建proc sp_getdata @mycur游标变化的输出
如
开始
为emp中的select *设置@ mycur = cursor static 打开@mycur
结束

执行:
声明@mydatacur游标
exec sp_jemp_getdata @ mycur = @ mydatacur输出
从@mydatacur获取下一个
而@@ fetch_status = 0
开始
从@mydatacur获取下一个
end

i created one procedure in sql server with cursor varying output parameter
how to access this procedure from asp.net page and bind data to gridview

ex:
create proc sp_getdata @mycur cursor varying output
as
begin
set @mycur=cursor static for select * from emp
open @mycur
end

execution:
declare @mydatacur cursor
exec sp_jemp_getdata @mycur=@mydatacur output
fetch next from @mydatacur
while @@fetch_status=0
begin
fetch next from @mydatacur
end

推荐答案

那并不是您应该如何使用游标.我不确定这是否还能奏效.您应该编写一个使用光标填充表的proc.

That''s not really how you''re supposed to use cursors. I''m not sure this can even work. You should write a proc that uses the cursor to fill a table.

尝试以下代码来调用您的SP.然后，您可以轻松地将结果集绑定到您的gridview.只需执行gv.DataSource = DataSet，然后执行gv.Bind()

Try the below code to call your SP. Then you can easily bind the result set to your gridview. Just do a gv.DataSource = DataSet and then gv.Bind()

SqlConnection lSQLConn = null;
SqlCommand lSQLCmd = new SqlCommand();
//Declare a DataAdapter and a DataSet
SqlDataAdapter lDA = new SqlDataAdapter();
DataSet lDS = new DataSet();
 
//...Execution section
 
// create and open a connection object
lSQLConn = new SqlConnection(connStr);
lSQLConn.Open();
//The CommandType must be StoredProcedure if we are using an ExecuteScalar
lSQLCmd.CommandType = CommandType.StoredProcedure;
lSQLCmd.CommandText = "sp_YourSPName"; 
lSQLCmd.Parameters.Add(new SqlParameter("@Parm1", aParm1));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm2", aParm2));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm3", aParm3));
lSQLCmd.Parameters.Add(new SqlParameter("@Parm4", aParm4));
 
lSQLCmd.Connection = lSQLConn;
//Fill the DataAdapter with a SelectCommand
lDA.SelectCommand = lSQLCmd;
lDA.Fill(lDS);

要查看完整的解释，请访问:
从WCF服务方法C#执行存储过程

To see a full explanation go to:
Executing a Stored Procedure from a WCF Service Method C#

这篇关于如何从asp.net用游标参数调用存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！