sql server - What is wrong with my sql function that it always returns null when i try to retrieve its value in c#? -

sql server - What is wrong with my sql function that it always returns null when i try to retrieve its value in c#? -

what wrong sql function returns null when seek retrieve value in c# ? function works pretty solid in sql designer when seek run using c# fails. function definition : first table valued function:

alter function dbo.fgetnumberofchikensinthisdate ( @date nvarchar(12), @idsource bigint ) returns table homecoming select sum(numberofchicken) number of chickens tblchickens (sourceid= @idsource) , (reserveddate = @date)

and scalar valued function :

alter function dbo.fsgetnumberofchikensinthisdate ( @date nvarchar(12), @idsource bigint ) returns bigint begin declare @ret bigint; select @ret= sum(numberofchicken) tblchickens (reservedate = @date) , (sourceid= @idsource) homecoming @ret; end;

i utilize these 2 methods create sql command string these functions , pass parameters , execute them c#: table based functions use:

public static datatable executesqlfunction(string functionname, string[] functionparamers) { sqldataadapter reader = new sqldataadapter(); datatable table = new datatable(); string query = "select * " + functionname + "("; int index = 0; foreach (string item in functionparamers)//{0},{0} { query += string.format("{0}", item); query += ++index >= functionparamers.length ? string.format(")", item) : ","; } if (connection.state != connectionstate.open) { connection.open(); } cmd = new sqlcommand(query, connection); reader.selectcommand = cmd; reader.fill(table); connection.close(); homecoming table; }

and using :

executesqlfunction("dbo.fgetnumberofchikensinthisdate",new string[] { date, api.currentsourceid });

will create string :

select * dbo.fgetnumberofchikensinthisdate(1391/12/01,4) //the date in persian

the returning datatable object has 1 row when write

datatable.rows[0][0].tostring();

i

""

string while when run sql command on sql runs fine!(actually seek execute sql command of function within sql designer , results fine when seek run using c# acts ).

and scalar valued function utilize same former method slight alter @ end :

public static object executescalarsqlfunction(string functionname, string[] functionparameters) { string query = "select * " + functionname + "("; int index = 0; object result; foreach (string item in functionparameters)//{0},{0} { query += string.format("{0}", item); query += ++index >= functionparameters.length ? string.format(")", item) : ","; } if (connection.state != connectionstate.open) { connection.open(); } cmd = new sqlcommand(query, connection); result = cmd.executescalar(); connection.close(); homecoming result; }

calling function :

executescalarsqlfunction("dbo.fsgetnumberofchikensinthisdate",new string[] { date, api.currentsourceid });

will create sql string :

"select * dbo.fsgetnumberofchikensinthisdate(1391/12/01,4)"

and when

result = cmd.executescalar();

section generates exception saying:

invalid object name 'dbo.fsgetnumberofchikensinthisdate'. while exists ! , there!.

the executesqlfunction() working rest of functions these exceptions fail !! executescalarfunction not tested since dont know if have missed in im sure executesqlfunction deals table valued sql functions ok must else missing here. can body help me solve problem ? regards

the function fsgetnumberofchikensinthisdate returns scalar value - single result of type bigint - cannot used source of select statement. can used column source select statement however.

try sql statement instead:

select dbo.fsgetnumberofchikensinthisdate('1391/12/01', 4);

should homecoming single row single unnamed bigint column result.

c# sql-server user-defined-functions