Virtual Private Database

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
suhman
Posts: 5
Joined: Tue 15 Jul 2014 15:12

Virtual Private Database

Post by suhman » Tue 15 Jul 2014 21:05

Hi,

Do you have any samples on using Virtual Private Database in Entity framework Code First approach?

http://docs.oracle.com/cd/E16655_01/net ... m#DBSEG007

MariiaI
Devart Team
Posts: 1472
Joined: Mon 13 Feb 2012 08:17

Re: Virtual Private Database

Post by MariiaI » Fri 18 Jul 2014 11:23

There is no such sample at the moment. We will investigate the question and notify you about the results as soon as possible.
Have you encountered any particular difficulties when employing Virtual Private Database with dotConnect for Oracle?

suhman
Posts: 5
Joined: Tue 15 Jul 2014 15:12

Re: Virtual Private Database

Post by suhman » Fri 18 Jul 2014 12:50

As of now, we have a store proc that apply the VPD Key of the client in the connection context. While reviewing your component, I tried executing the existing store proc before executing the any linq queries and it seems working fine. Oracle automatically applies the VPD key on the queries executed by Entity Framework. I am looking for a better way to apply the VPD key in the database context instead of executing the store proc.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Virtual Private Database

Post by Shalex » Mon 21 Jul 2014 14:22

There are two ways:
1) you can specify a command that should be executed immediately after establishing the connection via the Initialization Command connection string parameter
2) handle the OracleConnection.StateChange event and call your stored procedure each time when connection becomes ConnectionState.Open.

suhman
Posts: 5
Joined: Tue 15 Jul 2014 15:12

Re: Virtual Private Database

Post by suhman » Mon 21 Jul 2014 16:18

I can't use the 1st way because I get VPD Key only after connecting the database. I tried the #2 way as below.

Code: Select all

 void Connection_StateChange(object sender, System.Data.StateChangeEventArgs e)
        {
            if (e.CurrentState == System.Data.ConnectionState.Open && e.OriginalState == System.Data.ConnectionState.Closed)
                ObjectContext.Context.Database.ExecuteSqlCommand("BEGIN sp_vpd('ABCD1234'); END;");
        }
I get an error message
The context cannot be used while the model is being created. This exception may be thrown if the context is used inside the OnModelCreating method or if the same context instance is accessed by multiple threads concurrently. Note that instance members of DbContext and related classes are not guaranteed to be thread safe.

Shalex
Site Admin
Posts: 8245
Joined: Thu 14 Aug 2008 12:44

Re: Virtual Private Database

Post by Shalex » Thu 24 Jul 2014 08:37

Try this code:

Code: Select all

class Program
{
    static void Main(string[] args)
    {
        OracleMonitor monitor = new OracleMonitor() { IsActive = true };
        using(var context = new MyDbContext.MyEntities()){
            context.Database.Connection.StateChange += conn_StateChange;
            var depts = context.DEPTs.ToList();
            // ...
        }
    }

    static void conn_StateChange(object sender, System.Data.StateChangeEventArgs e)
    {
        if (e.CurrentState == System.Data.ConnectionState.Open && e.OriginalState == System.Data.ConnectionState.Closed) { 
            var cmd = (sender as OracleConnection).CreateCommand();
            cmd.CommandText = "BEGIN sp_vpd('ABCD1234'); END;";
            cmd.ExecuteNonQuery();
        }
    }
}

Post Reply