Change Schema Name at Runtime

Discussion of open issues, suggestions and bugs regarding LinqConnect – Devart's LINQ to SQL compatible ORM
Post Reply
justin.williams
Posts: 2
Joined: Thu 08 Dec 2016 17:02

Change Schema Name at Runtime

Post by justin.williams » Thu 08 Dec 2016 17:56

Some of my tables in my model are contained in schema that may not be named the same when pulling from other databases (and my DBA refuses to change it). All of the tables are present but I may need to access it through SCHEMA1.TABLE vs SCHEMA2.TABLE.

Right now in the auto generated code behind there is a custom attribute for each table containing SCHEMA.TABLE. For instance

Code: Select all

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


For one DB FMC_TP is fine but another it may be FMC_DEV.

Is there a way to modify this at runtime possibly by adding a setting to the app.config file?

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

Re: Change Schema Name at Runtime

Post by Shalex » Fri 09 Dec 2016 09:25

Looks like you created the LinqConnect model with the selected "Preserve schema name in storage" check box in Create Model Wizard. The ways how to remove schema name:
1) select table in Model Explorer, press F4 (Properties), remove schema name from the Source property, e.g.: "SCHEMA1.TABLE1" -> "TABLE1"
2) double click the designer surface to open Model Settings > clear selection of the "Preserve schema name in storage" option. Then right click designer surface > Regenerate Storage and Mapping

Now runtime will use a schema specified in the connection string.

justin.williams
Posts: 2
Joined: Thu 08 Dec 2016 17:02

Re: Change Schema Name at Runtime

Post by justin.williams » Mon 12 Dec 2016 16:25

This would work in the instance where I had only one schema. Unfortunately, I have four schema to reference. I did find a workaround to this issue which is similar to what you have suggested just one extra step.

http://stackoverflow.com/questions/4104 ... 7#41049247

I take off the schema portion from the mapping as you suggest. Then I pass the following command depending on which schema I need to reference:

Code: Select all

mycontext.ExecuteCommand($"ALTER SESSION SET CURRENT_SCHEMA = {Schema}", new object[1]);
It works pretty good for now.

Thanks.

Post Reply