Single Data Context for Multiple Databases and Environments

marhtt · Post by **marhtt** » Thu 23 Jul 2009 17:04

How I make the DataContext dynamic?
This is critical to the way our organisation works, we can't afford to create new data contexts classes for every environment we have. We have dev, uat, training and production environments, this is also replicated accross databases for the same entities (tables).
The data context file decorates the class with a Table attribute which is environment specific, e.g [Table(Name = @"0123DEV.EMPLOYEE")]; how do we modify this at runtime to provide a very dynamic class?

Is this possible with LINQ to Oracle? If it isn't then it is a major flaw as to how ORACLE invokes it's voodoo and would be detrimental to our organisation and will have to scrap the use of LINQ.

Thanks in advance!

Regards

Matthew

AndreyR · Post by **AndreyR** » Fri 24 Jul 2009 08:06

Thank you for your inquiry.
We will add the possibility to remove the schema/user name in one of the future builds.
Sorry, but no timeframe for this implementation is available.
As a temporary workaround, remove the schema name manually.

marhtt · Post by **marhtt** » Mon 27 Jul 2009 08:58

Your temporary work around won't work, remember before you can query an oracle table one needs to qualify (prefix) the the table name with the schama name, removing this will throw an exception (table does not exist).
Have you got any other suggestions?

AndreyR · Post by **AndreyR** » Mon 27 Jul 2009 15:29

I have just tried to remove the schema name and succeeded in querying a database.
The only scenario when this workaround cannot help is when one connects as user1 and needs to operate with the tables owned by user2.
In this case I recommend you to create either public (if you need this table to be visible for several users) or
user-specific synonym.
One more possible way (dynamic attribute setting) is described here:
http://geekswithblogs.net/abhijeetp/arc ... -in-c.aspx

marhtt · Post by **marhtt** » Mon 27 Jul 2009 15:59

Was your test done with Oracle? I'm not sure how you can query an Oracle DB without a schema name the database table are actually prefixed with the schema name....
Below is a simple example of what I am trying to do
public List GetContacts()
{

using (SetupDataContext ctx = new SetupDataContext(_connString))
{
List userEntity = new List();

var user =
(from c in ctx.CONTACTs
select c).ToList();

return user;
}

}
catch (Exception ex)
{
//Handle exception }
}

The above method fails if you remove the schema name in the datacontext file.
P.S. my connection string is the same user as the connection string used to create the datacontext via entity developer. I get error message ORA-00942: table or view does not exist"

AndreyR · Post by **AndreyR** » Mon 27 Jul 2009 16:08

Could you please send me (support * devart * com) the model and the generated code before and after
the workaround is applied?
Please include the DB script if possible.

marhtt · Post by **marhtt** » Tue 28 Jul 2009 15:19

Thanks for your assistance. Removing the schema name isn't a fix as I previously mentioned. Oracle tables are associated with the schema e.g. 123.CONTACT and 456.CONTACT within the database are different tables; by referring to them as CONTACT is insufficient and does throw an exception.

Perhaps there's no work around

AndreyR · Post by **AndreyR** » Wed 29 Jul 2009 11:22

I think that using public synonyms for the tables from different schemas having coinciding names can be an option.
Unfortunately, I don't see any other helpful workarounds.

AndreyR · Post by **AndreyR** » Thu 30 Jul 2009 10:16

One more suggestion.
Try to execute the following command

Code: Select all

ALTER SESSION SET CURRENT_SCHEMA = "123"

on DataContext.Connection.
This will let you use table 123.Contact.
You will have to remove Schema attributes for the necessary tables.

AndreyR · Post by **AndreyR** » Tue 10 May 2011 14:18

We have added the "Preserve schema name in storage" check box.
This functionality will be available in the nearest build of dotConnect for Oracle.

AndreyR · Post by **AndreyR** » Thu 19 May 2011 12:59

We have released the new 6.30.160 build of dotConnect for Oracle. This build can be dowloaded from here (the trial version) or from Registered Users' Area (for users with active subscription only).
For the detailed information about the improvements and fixes available in dotConnect for Oracle 6.30.160, please refer to this announcement.