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();
}
}
}
