Page 1 of 1
How to call a sql server function and use the result?
Posted: Fri Feb 26, 2016 7:25 pm
by michael.behounek
Hello Forum
I have a view called v_organisation, generated in ASP.Net Maker
Now I want to use a function with the username as a parameter.
The function returns all the records of the view v-organisation, that the logged in user is allowed to look at.
The function works fine in SSMS.
What I have until now in Page_DataRendering is:
if (IsLoggedIn()){
string PrivateSQL;
PrivateSQL="SELECT * FROM f_filter_Unit_by_UserName ('"+CurrentUserName()+"')" ;
}
Now I want to know, how to use this function.
In which server event do I place the query?
How do I get the records in the original table?
I would appreciate to get an answer with code I can use rather than a hint like "look in Google for ...."
Regards
Michael
Re: How to call a sql server function and use the result?
Posted: Sat Feb 27, 2016 10:08 am
by motfs
You can simply use Table Filter to filter the record if your View contain that field. See Table Setup -> Filter in the help file for more details.
Re: How to call a sql server function and use the result?
Posted: Mon Feb 29, 2016 7:10 pm
by michael.behounek
motfs wrote:
You can simply use Table Filter to filter the record if your View contain that field.
See Table Setup -> Filter in the help file for more details.
Thank you motfs
But it is not possible to use a filter, because the permission to see a record does not depend of one single filed.
It is a hierarchy with several units, that is, why I wrote a function to get the relevant records.
In the output of ASP.NET Maker, in the File <tablename>info.cs I can change the "SQLFrom"
public string SqlFrom {
get { return "[dbo].f_filter_Unit_by_UserName ('" + CurrentUserName() + "')"; }
}
and it works. But I cannot change the "SQLFrom" dynamically in runtime, so how can I call this function instead of using the underlying table?