DevForce 2010: Intercepting user activity

Print Page | Close Window

Intercepting user activity

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce 2010
Forum Discription: For .NET 4.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=4141
Printed Date: 12-May-2026 at 9:33pm

Topic: Intercepting user activity

Posted By: pponzano
Subject: Intercepting user activity
Date Posted: 07-May-2013 at 5:13am

Hello,
we've requested an implementation on our service to log all the stuff a user do, our application uses some direct access to the DB with IdeaBlade EF and some with storedprocedure (always using IDEABLADE).... what's the best approach to log everything? our DB engineer proposed to store on a temp table the user id before and after an user completed the action and then via trigger to store data in a table... I think there's an easy way with interceptors... but don't know where to start from... can you please provide me some guidelines that covers in a single point the EF / StoredProcedure?

Thanks in advance
Paolo

Replies:

Posted By: sbelini
Date Posted: 07-May-2013 at 11:01am

Paolo,

I'd suggest doing it in the SaveInterceptor, where you'd be able to create the "audit" entities and add them to the list of entities to be saved (i.e. Added/Modified/Deleted) before calling base.SaveChanges().

The only potential issue is with Added entities with Identity columns. A search in our forum will bring up a few threads discussing this. One of them is http://www.ideablade.com/forum/forum_posts.asp?TID=2717&KW=audit&PN=1&title=auditing-inserts-with-identity-columns - http://www.ideablade.com/forum/forum_posts.asp?TID=2717&KW=audit&PN=1&title=auditing-inserts-with-identity-columns .

Posted By: pponzano
Date Posted: 07-May-2013 at 11:13pm

Hello Silvio,
this can work with entities...but what about calls made via SP? I've seen that using EntityQueryInterceptor I get SP as well... is there a way of disciminating if it's an update/save? maybe passing a parameter....

Thanks

Posted By: sbelini
Date Posted: 08-May-2013 at 9:31am

Paolo,

In the interceptor, you will not be able to differentiate a "query" SP from a "save" SP.
I see 2 possibilities:
1) Handle the auditing of stored proc saved entities inside the stored proc;
2) Rather then handling auditing in the app, do it via a DB trigger.

Kind regards,
Silvio.

Posted By: pponzano
Date Posted: 10-May-2013 at 5:19am

Silvio,
it's possible to get the SqlConnection Current IdeaBlade query run's on? If So I can make a call using it before the ExecuteQuery ?

In thay way I can :

1) set a trigger on the table I need to monitor
2)I can write to a @TMP table an entry passing the ID (via SP)
3)execute call
4)call another entry on the same connection to delete from the @TMP table the entry

Posted By: sbelini
Date Posted: 13-May-2013 at 10:30am

Paolo,

I don't understand the question.
If you are using triggers, there shouldn't be a need to involve DevForce as it could be completely handled on the DB.

Posted By: pponzano
Date Posted: 13-May-2013 at 11:38pm

Silvio, how can I pass the user guid to the DB layer? if I use trigger I can handle the update/delete of a row but how can I track that user123 has done it? it my mind if I can use the devforce sqlconnection I can

write on a temp table the userid and the sqlconnection id, using the same sqlconnection with devforce I do what I need (and I can have on a trigger the select @@SPID (so I know the connection, i can look on @TMP for the id) then after the call of devforce I can remove it from @TMP

my idea is

1)getting devforce sqlconnection
2) use a storedprocedure to insert on @TMP userid, @SPID
3)execute devorce
4)remove from @TMP the previous entry

Is this impossible?

Thanks

Posted By: pponzano
Date Posted: 14-May-2013 at 3:11am

Hello,
I've found this http://msdn.microsoft.com/en-us/library/ms189252.aspx

it's possible to set this SessionContextInformation? is enougth to set this.Context = myValue in the EntityQueryInterceptor?

Thanks

Posted By: pponzano
Date Posted: 14-May-2013 at 3:25am

It's possible to provide me a sample using this? even on pvt mail (the one I use for license)
It's quite urgent
Thanks

Posted By: stephenmcd1
Date Posted: 14-May-2013 at 8:54am

You can take a look at a post I made in another topic on this forum. We need to do something very similar to you - every time DevForce opens a SQL Connection (for querying or saving), we needed to 'mark' the connection with what user was active in the application. Then triggers and procedures can see what the current user is to perform logging, security, etc. In our case, we 'mark' the connection by setting data in the context_info property just like you seem to be thinking. We have a stored procedure that takes in a few parameters (an int and a GUID) and takes care of converting them to the format needed for context_info. We've found that setting context_info seems a lot faster than having to deal with temp tables....especially since connections will be opened/closed all the time.

Here is the post: http://www.ideablade.com/forum/forum_posts.asp?TID=3855&PID=15476&title=value-cannot-be-null-getting-ientityqueryt-when-using-usepersistentdbconnectionpersession#15476 - http://www.ideablade.com/forum/forum_posts.asp?TID=3855&PID=15476&title=value-cannot-be-null-getting-ientityqueryt-when-using-usepersistentdbconnectionpersession#15476 (also see subsequent posts that contain additional information)

Posted By: pponzano
Date Posted: 15-May-2013 at 12:50am

Hello stephenmcd1,
Thanks a lot I'll check this out and if I got any question I'll ask you for help...thanks a lot

Paolo

Posted By: pponzano
Date Posted: 15-May-2013 at 7:59am

Hello stephenmcd1,
as I wrote you on the other thread can you please provide me the implementation of ProviderRow?
I've managed to load my factory from web.config but I got the debugger continue breaking on

Factory

    public override DbConnection CreateConnection()
        {
            return new CustomDbConnection();//here
        }

which is called by CustomDbConnection:

protected override string DefaultWrappedProviderName
        {
            get { return Invariants.Test; }
        }

Why this happen? It's an huge effort for you to produce me a simple mockup project? Thanks

Posted By: stephenmcd1
Date Posted: 17-May-2013 at 5:01pm

For those following along in this thread, see my response on the other thread http://www.ideablade.com/forum/forum_posts.asp?TID=3855&PID=16338&title=value-cannot-be-null-getting-ientityqueryt-when-using-usepersistentdbconnectionpersession#16338 - here .

Print Page | Close Window