CODEWITHSUNDEEP
c#oracle-databasedevartdotconnect
Felix Castor
Felix Castor

Reputation: 1675

Change Schema Name at Runtime in data context

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?

Upvotes: 1

Views: 1771

Answers (3)

Devart
Devart

Reputation: 121922

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.

Upvotes: 0

Felix Castor
Felix Castor

Reputation: 1675

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;

Upvotes: 1

Kacper
Kacper

Reputation: 4818

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.

Upvotes: 1

Related Questions