Virtual Private Database

[suhman]

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]

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]

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]

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]

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]

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();
        }
    }
}