将SQLserver中的多个记录返回到C＃代码中 [英] Returning multiple records from SQLserver into C# code

问题描述

亲爱的朋友们，

我需要你的快速帮助。我的要求是我在SQLServer中的存储过程必须返回多行，例如，选择*来自tb_employee，其中department ='Consultancy'

I need a quick help from you please. My requirement is that my stored procedure in SQLServer has to return multiple rows, for exampe, select * from tb_employee where department = 'Consultancy'

我需要将这些数据放入C＃程序中数据表。我不知道该怎么做。我尝试了以下选项

I need to get this data in a C# program into a data table. I am not sure how to do this. I tried the below option

1）在SQL存储过程中，我声明了一个游标类型输出参数并分配了上述查询。

1) In SQL stored procedure, I declared a cursor type output parameter and assigned the above query.

2）In在C＃中，我用Dbtype = Object声明了一个Parameter变量（因为我找不到CURSOR的DbType（与oracle不同）

2) In the C#, I declared a Parameter variable with Dbtype = Object (as I could not find a DbType for CURSOR (unlike oracle)

然而，我无法执行C＃代码，因为我我收到了游标不兼容数据类型的错误。

However, I am not able to execute the C# code as I am getting error that incompatible datatype for cursor.

请求你的帮助。

问候，

Rajib

推荐答案

您不需要游标来从SQL Server检索结果.SQL Server可以将结果传输到客户端，以及API等SQLClient可以使用Sqldatareader消耗结果。

You don't need a cursor to retrieve results from SQL Server. SQL Server can stream results to the client, and APIs like SQLClient can consume the results using a Sqldatareader.

由于您的最终目标是DataTable，请考虑使用SqlDataAdapter。此对象在内部使用SqlDataReader来填充数据表，其中包含由指定的SqlCommand SELECT查询。

Since your end goal is a DataTable, consider using a SqlDataAdapter. This object uses a SqlDataReader internally to fill a data table with the rows returned by the specified SqlCommand SELECT query.

var dataTable = new DataTable();
var department = "Consultancy";
using (var connection = new SqlConnection(connectionString))
using (var command = new SqlCommand(@"select * from tb_employee where department = @department;", connection))
using (var dataAdapter = new SqlDataAdapter(command))
{
    command.Parameters.Add("@department", SqlDbType.VarChar, 50).Value = department;
    dataAdapter.Fill(dataTable);
}

这篇关于将SQLserver中的多个记录返回到C＃代码中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！