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.
CREATE FUNCTION fun
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)
public void call_stored_procedure(List<string> lyname)
string dogCsv = string.Join(",", lyname.Select(i => "'" + i + "'"));
using (SqlConnection connection = new SqlConnection("data source=.; database=Srivatsava; integrated security=SSPI"))
using (SqlCommand command = connection.CreateCommand())
command.CommandText = "select dbo.fun(@lname)";
command.CommandType = CommandType.Text;
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.
Sends the CommandText to the Connection and builds a SqlDataReader.