How to filter query by login id.

This filter tutorial not working in my configuration, not sure where I did wrong.

I try both method. Both are not working.

Method 1:
Create Class1 into the App_Code

Imports MyCompany.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Public Class Class1
Implements IDataFilter

Public Sub Filter(ByVal filter As SortedDictionary(Of String, Object)) _
Implements MyCompany.Data.IDataFilter.Filter
filter.Add("UserName", Membership.GetUser())

End Sub
End Class

Controller:

dataController name="Facility"
conflictDetection="overwriteChanges"
label="Facility" xmlns="urn:schemas-codeontime-com:data-aquarium"
dataFilterType="Class1"

<![CDATA[select
"FACILITY"."FACILITY" "FACILITY",
"FACILITY"."Descr" "Descr",
"EC_UserRestrict"."UserName" "UserName"
from "dbo"."FACILITY" "FACILITY" WITH (NOLOCK)
left outer join "dbo"."EC_UserRestrict" "EC_UserRestrict" on "FACILITY"."FACILITY" = "EC_UserRestrict"."Facility" ]]>

Method 2:

dataController name="Facility"
conflictDetection="overwriteChanges"
label="Facility"
handler="ecomwms.Rules.FacilityBusinessRules"
xmlns="urn:schemas-codeontime-com:data-aquarium"

<![CDATA[select
"FACILITY"."FACILITY" "FACILITY",
"FACILITY"."Descr" "Descr",
"EC_UserRestrict"."UserName" "UserName"
from "dbo"."FACILITY" "FACILITY" WITH (NOLOCK)
left outer join "dbo"."EC_UserRestrict" "EC_UserRestrict" on "FACILITY"."FACILITY" = "EC_UserRestrict"."Facility" ]]>

-- Create ecomwms.Rules.FacilityBusinessRules
Imports ecomwms.Data
Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Linq

Namespace ecomwms.Rules

Partial Public Class FacilityBusinessRules
Inherits ecomwms.Data.BusinessRules

_
Protected ReadOnly Property Facility() As String
Get
RowFilter.Canceled = _
String.IsNullOrEmpty(RowFilter.LookupContextController)
Return Context.User.Identity.Name
End Get
End Property

End Class

End Namespace
1 person has
this question
+1
Reply
  • We suggest using the view filter as described in "Row Level Security" tutorial at http://codeontime.com/Documents/CB-Ro....

    You can also watch the tutorial on our YouTube channel at https://www.youtube.com/watch?v=T5Eg0r....

    View Filter is the official method of filtering. The upcoming Dynamic Access Control List feature will also make it possible to create custom data access control lists at runtime (no code) as well as at design time.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • I have same issue, and I use filters in the view to do this:
    ie
    grid1 filter expression:
    "PurchaseOrder"."orderStatusType" IN ('READY','COMPLETED','CLOSED')
    and contractorId = @ContractorIdFilter

    Then define a static member that does the calculation, in the businesrules class, like this:
    ////////////////////
    public int ContractorIdFilter
    {

    get
    {
    return Common.GetContractorIdForCurrentUser();

    }
    }
    ////////////////////

    inside the method GetContractorIdForCurrentUser() I do all the work in determine who the user is.

    For me, this works better than what COT suggest in the tutorials, though it depends upon your circumstances.

    Pat.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Patrick,

    Thanks for sharing, I think this is the solution I'm looking for.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • This reply was removed on 2011-04-26.
    see the change log
  • 1
    @Bali,
    Sure. This is the code for my static method. You can put this in any class you want. I made it static just so it's easier to call.
    //////////////////////////////////////////////////////
    public static int GetContractorIdForCurrentUser()
    {
    MembershipUser mu = Membership.GetUser();
    ccdvaEntities _ccdvaDB = new ccdvaEntities();

    if (mu == null)
    throw new Exception("Currently logged in user not found.");

    string aspNetUserId = mu.ProviderUserKey.ToString();

    // Now lookup mapping table - ContractorUsers to find contractorId
    ccdvaModel.ContractorUser conUser = (from cu in _ccdvaDB.ContractorUsers where cu.UserId.Equals(aspNetUserId) select cu).First();

    // if not found, throw exception
    if (conUser == null) throw new Exception( String.Format("User '{0}' must be mapped to a Contractor business.", mu.UserName));

    // return it.
    return conUser.contractorId;
    }
    //////////////////////////////////////////////////////

    You can see I have used SQL Entity Framework to query the database, though you can use plain SQL or whatever suits. The reason I will not use the COT framework is that it is slow when you are doing many hundreds of calls. In my case, this method will be called thousands of times per day, so it must be fast.

    The other 'Trick' in all this, is associating your aspnet membership users with something meaningfull (in my case, that is a 'Contractor' (which means a business/company) ).
    To do this, when I create a user, I have to manually link them to the Contractor, see the screenshot. This has been described in other COT posts (in old forum) as how to associate other information with aspnet membership users. The problem is that we cannot easily add fields to the aspnet membeship tables, so we have to do hacks like this to make it work.



    Here is another pic of my DB, showing my Contractor table. The ContractorUsers.UserId is the field that links to the aspnet membership users tables. That is the trick that makes it work.
    See:


    As an aside, when people edit the records, they assign a Contractor to a record - that is how a contractorId is assigned (and in turn filtered) on per record basis.

    I used snippets of info from the COT forum and google of course to figure all this out.. I admit its not straight-forward!

    Hope that helps a bit. :)

    Regards,

    Pat.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited

  • Definitely help, thanks a lot. My problem solved after applied your suggestion.
  • (some HTML allowed)
    How does this make you feel?
    Add Image
    I'm

    e.g. sad, anxious, confused, frustrated indifferent, undecided, unconcerned kidding, amused, unsure, silly happy, confident, thankful, excited