Just wondering, what is the intended support for udf's?

bjones wrote:

Just wondering, what is the intended support for udf's?

In sqlserver I can't call them from ADO.NET, so these are pretty useless in that context. THey can be handy when you are constructing a typed list, and will be considered when aggregates and expressions are added later this year.

As described in Q329497 UDF''s can be called using as a standard select statement:

Select dbo.[Function Name] (Parameters)

This means that it should be possible to provide a generated .Net wrapper for UDF's.

Especially a wrapper for Multi-statement Table-valued Functions (that accepts parameters and returns a Typed DataTable) would be great (for me that is )

I suppose one could use a stored procedure here as well, but then there is no way to define to structure of the returned table.

Perhaps the parameter code from the Stored Procedure wrapper and the Typed DataTable code from the TypedView could be combined?

Just an idea.

A UDF can return a table, that is, a table typed variable. I'm not sure how such an object can be returned to the caller if the caller is not a stored proc/other function.

This seems to be an old thread but we have started to use llblgen pro and are wondering if there are any support for table functions (udf's that return one or more resultset ).

They should appear just like views, that's how they are used and are called : select aaa from my_Func( @someparam ), except they can accept params like procs. The problem is that the designer doesn't even show them.

Is there any way to have them appear in the designer so they can be typed like the rest of the database objects.

Thanks. Chris.

these are currently not supported.

I have worked around this by modifying Custom_DataAccessAdapterTemplate.template to include the following code, this one does Scalar Functions, I have one that does ResultSets as well it just changes the ExecuteScalar line.

This function can be called directly if necessary but we generally use a code file in our data layer that doesn't get modified, i.e. 2 class scenario to provide typed scalar functions and functionality similar to a TypedList.

#region User Defined Function related routines
/// <summary>
/// Calls the specified function in the SqlServer database. If a transaction is in progress, it will participate in
/// that transaction.
/// </summary>
/// <param name="userDefinedFunctionToCall">Stored procedure to call</param>
/// <param name="parameters">array of parameters to specify</param>
/// <returns>the scalar result of the function as an object</returns>
public virtual object CallUserDefinedFunction(string userDefinedFunctionToCall, SqlParameter[] parameters)
{
    string functionCallSql = "select " + HandleCatalogNameStoredProcedure(userDefinedFunctionToCall);
    string sqlParams =  "(";
    object toReturn = null;
    SqlCommand command = new SqlCommand(string.Empty);
    command.Connection = (SqlConnection)base.GetActiveConnection();
    if(base.IsTransactionInProgress)
    {
        command.Transaction = (SqlTransaction)base.PhysicalTransaction;
    }
    command.CommandType = CommandType.Text;
    command.CommandTimeout = base.CommandTimeOut;

    try
    {
        for(int paramEntry = 0; paramEntry < parameters.Length; paramEntry++)
        {
            command.Parameters.Add(parameters[paramEntry]);
            sqlParams += parameters[paramEntry].ParameterName;
            if (paramEntry < parameters.Length - 1)
            {
                sqlParams += " ,";
            }
        }           
                
        sqlParams += ")";
                
        functionCallSql += sqlParams;

        base.OpenConnection();
        toReturn = command.ExecuteScalar();
    }
    finally
    {
        // clean up a dangling automaticly opened connection if needed.
        if(!(base.KeepConnectionOpen || base.IsTransactionInProgress))
        {
            base.CloseConnection();
        }
    }
    return toReturn;
}
#endregion

John

Thanks John!