Change Schema Name at Runtime in data context

Question

So, I'm using dotConnect for Oracle. I used the template and wizard to create a model of the database (Database first approach). We have multiple databases that a single application needs to reference and unfortunately the schema naming which contain the tables are not uniform across the other databases.

In the auto generated class in the Designer.cs file I get:

[Table(Name = @"FMC_TP.EQUIPMENT")]

But considering which database connection the schema could be:

[Table(Name = @"FMC_DEV.EQUIPMENT"]

Is there a way to change the schema for the mapping at runtime?

Kacper · Accepted Answer · 2016-12-09T18:01:48.323

1

In Oracle you can call alter session set current_schema = SCHEMA_NAME statement which set contex of your session. Then you can go without prefixing tables with schema name but this may help only if you're using same session for all statements.

edited Dec 09 '16 at 18:01

answered Dec 08 '16 at 21:16

Kacper

4,798
2
19
34

I'm using 4 different schema at the same time. So I guess I would need to change each time I create a session. It's worth a shot. – Felix Castor Dec 08 '16 at 21:18
@FelixCastor Yes if in one session you use tables from differen schemas you need to execute the command multiple times but it allows you to keep table names without schema names and dynamically set them by executing multiple `alter` – Kacper Dec 08 '16 at 21:20
Worked like a charm! – Felix Castor Dec 08 '16 at 21:28

Felix Castor · Answer 2 · 2016-12-12T14:36:31.920

In case anyone has a similar issue I will expand on Kacper's answer:

In the model file MyModel.lqml I removed the schema specifications from the table names:

<Table Name="SCHEMA.TABLE" Member="ModelTableName">

to

<Table Name="TABLE" Member="ModelTableName">

Basiscally where applicable.

In code:

MyModelDataContext mycontext = new MyModelDataContext();

mycontext.ExecuteCommand($"ALTER SESSION SET CURRENT_SCHEMA = {Schema}", new object[1]);

Then perform my query.

            var rows = from x in mycontext.ModelTableName
                            where x.COLUMN == id
                            select x;

score 0 · Answer 3 · answered Dec 15 '16 at 12:45

There is an additional way to execute a command immediately after establishing a connection: set command (or several commands) via Run Once Command (or Initialization Command) connection string parameter. For more information, refer to https://www.devart.com/dotconnect/oracle/docs/?Devart.Data.Oracle~Devart.Data.Oracle.OracleConnection~ConnectionString.html.

Change Schema Name at Runtime in data context

3 Answers3