当前位置: 动力学知识库 > 问答 > 编程问答 >

c# - How to handle inline table-valued function that returns a table using ado.net

问题描述:

I have implemented Inline table valued function in sql. I am trying to read the table values returned from Inline table valued function in C# using ado.net. Please help. I am attaching code here.

Sql function:-

CREATE FUNCTION fun

(

@lname varchar(50)

)

RETURNS TABLE

AS

RETURN

(

select fp.accntname,ld.BU,fp.salesop,idt.isdormant from legacy_system as ls

inner join fourth_page as fp on ls.accountname=fp.accountname

inner join linked as ld on fp.productid=ld.productid

inner join isdormant as idt on idt.productid=ld.productid

inner join legacy_system as l on ls.productid=idt.productid

where l.legacyname in(@lname)

)

GO

C# code:-

 [HttpPost]

public void call_stored_procedure(List<string> lyname)

{

Console.WriteLine(lyname);

string dogCsv = string.Join(",", lyname.Select(i => "'" + i + "'"));

Console.WriteLine(dogCsv);

using (SqlConnection connection = new SqlConnection("data source=.; database=Srivatsava; integrated security=SSPI"))

{

connection.Open();

using (SqlCommand command = connection.CreateCommand())

{

command.CommandText = "select dbo.fun(@lname)";

command.Parameters.AddWithValue("@lname", dogCsv);

command.CommandType = CommandType.Text;

String s=command.ExecuteScalar().ToString();

Console.WriteLine(s);

connection.Close();

}

}

}

网友答案:

All the below link are related to your question .. it's already solved.. so please before post any question .. please look it already has answer/solved or not ..

Calling SQL Defined function in C#

How can I call a SQL function in C#?

Calling SQL Functions directly from C#

网友答案:

Table valued function returns a table so to read the result your commandtext should be

command.CommandText = "select * from dbo.fun(@lname)";

Now you are using ExecuteScalar() function to execute your command .

From MSDN - ExecuteScalar()

Executes the query, and returns the first column of the first row in the result set returned by the query. Additional columns or rows are ignored.

So even though your function is returning recordset with multiple columns , only the first column of first row s returned.

If you need to read full recordset returned by the table valued function , you need to use ExecuteReader() function.

ExecuteReader()

Sends the CommandText to the Connection and builds a SqlDataReader.

分享给朋友:
您可能感兴趣的文章:
随机阅读: