title: What is wrong with my SQL function that it always returns null when I try to retrieve its value in C#?
body: <p>What is wrong with my SQL function that it always returns null when I try to retrieve its value in C#? The function works pretty solid in the SQL designer but when I try to run it using C# it fails.</p>
  
  <p>This is my function definition. First the Table Valued Function:</p>
  
  <pre><code>ALTER FUNCTION dbo.FGetNumberOfChikensInThisDate
      (
      @Date nvarchar(12),
      @IDSource bigint
      )
  RETURNS Table 
  AS
      RETURN SELECT     SUM(NumberOfChicken) AS Number of Chickens  
         FROM            tblChickens
             WHERE        (SourceID= @IDSource) AND (ReservedDate = @Date)
  </code></pre>
  
  <p>And this is the Scalar Valued Function :</p>
  
  <pre><code>ALTER FUNCTION dbo.FSGetNumberOfChikensInThisDate
      (
      @Date nvarchar(12),
      @IDSource bigint
      )
  RETURNS bigint
  AS
   BEGIN
    DECLARE  @ret bigint;
     SELECT      @ret=  SUM(NumberOfChicken) 
         FROM            tblChickens
            WHERE        (ReserveDate = @Date) AND (SourceID= @IDSource)
  RETURN @ret;
  END;
  </code></pre>
  
  <p>I use these two methods to create the SQL command string for these functions and pass their parameters and execute them from C#: For table based functions I use:</p>
  
  <pre><code>public static DataTable ExecuteSqlFunction(string functionName, string[] Functionparamers)
          {
              SqlDataAdapter reader = new SqlDataAdapter();
              DataTable table = new DataTable();
              string query = "select * from " + functionName + "(";
              int index = 0;
              foreach (string item in Functionparamers)//{0},{0}
              {
                  query += String.Format("{0}", item);
                  query += ++index &gt;= 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();
              return table;
  
          }
  </code></pre>
  
  <p>and using it like this : </p>
  
  <pre><code>ExecuteSqlFunction("dbo.FGetNumberOfChikensInThisDate",new string[] { Date, API.CurrentSourceID });
  </code></pre>
  
  <p>will create this string : </p>
  
  <blockquote>
    <p>select * from dbo.FGetNumberOfChikensInThisDate(1391/12/01,4) //the date is in Persian</p>
  </blockquote>
  
  <p>The returning DataTable object has one row but when I write </p>
  
  <pre><code>dataTable.Rows[0][0].ToString();
  </code></pre>
  
  <p>I get a </p>
  
  <blockquote>
    <p>""</p>
  </blockquote>
  
  <p>string while when I run this SQL command on the SQL it runs just fine! (Actually I try to execute the SQL command of this function inside SQL designer and i get the results just fine but when i try to run it using c# it just acts like this). </p>
  
  <p>And for the scalar valued function is use the same former method with a slight change at the end:</p>
  
  <pre><code> public static object ExecuteScalarSqlFunction(string functionName, string[] FunctionParameters)
          {
  
              string query = "select * from " + functionName + "(";
              int index = 0;
              object result;
              foreach (string item in FunctionParameters)//{0},{0}
              {
                  query += String.Format("{0}", item);
                  query += ++index &gt;= FunctionParameters.Length ? String.Format(")", item) : ",";
              }
  
              if (connection.State != ConnectionState.Open)
              { connection.Open(); }
  
              cmd = new SqlCommand(query, connection);
              result = cmd.ExecuteScalar();
              connection.Close();
              return result;
          }
  </code></pre>
  
  <p>Calling this function like this:</p>
  
  <pre><code>ExecuteScalarSqlFunction("dbo.FSGetNumberOfChikensInThisDate",new string[] { Date, API.CurrentSourceID });
  </code></pre>
  
  <p>Will create this SQL string: </p>
  
  <blockquote>
    <p>"select * from dbo.FSGetNumberOfChikensInThisDate(1391/12/01,4)"</p>
  </blockquote>
  
  <p>and when I get to the </p>
  
  <pre><code>result = cmd.ExecuteScalar(); 
  </code></pre>
  
  <p>section it generates an exception saying:</p>
  
  <blockquote>
    <p>Invalid object name 'dbo.FSGetNumberOfChikensInThisDate'.
    while it does exists ! and is there!.</p>
  </blockquote>
  
  <p>The ExecuteSqlFunction() is working for the rest of my functions but these are exceptions which fail. The ExecuteScalarFunction is not tested since I don't know if I have missed anything in it but I'm sure the ExecuteSqlFunction which deals with table valued SQL functions is OK so it must be something else that I am missing here.</p>