Retatrget every SQL statement before its execution

Question

I need some help with my C# application.

Nowadays it works fine but one of the new requirements from customer is to implement underlying Database (MS SQL or Oracle) using any user schema selectable before program starts.

Inside of my application there are around 150 DataCommand objects.

So I need to catch every SQL expression inside of my application before it is executed and change target object(s) location. For example to change:

SELECT APPLICATION_NAME FROM **MY_SCHEMA**.APPLICATIONS

to

SELECT APPLICATION_NAME FROM **NEW_SCHEMA**.APPLICATIONS

As I see the solution of this problem - is to inject some code into every IDbCommand inside of my application, but can't imagine the point of start.

Any suggestions please.

You could include it in app.config either as the whole connection string or the schema as an appsetting. — Jaybird
– Jaybird, Commented Dec 18, 2018 at 8:21
This is the one part of solution. But how I can do replacement inside of ALL DataCommand objects without touching everyone? — Alexander
– Alexander, Commented Dec 18, 2018 at 8:29

Alexander · Accepted Answer · 2020-12-23 07:56:17Z

0

Today's workaround:

New schema owner is read from config file:

System.Configuration.AppSettingsReader configurationAppSettings = new System.Configuration.AppSettingsReader();
String strSchemaOwner = ((string)(configurationAppSettings.GetValue("DatabaseOwner", typeof(string))));

Every form have a list of Database Commands (by analogue of Form Controls) which is filled with Command objects.

On FormLoad event I pass this array to the method who do replace everyone command's text:

foreach(IDbCommand pCommand in arrCollection)
{
    pCommand.CommandText = pCommand.CommandText.Replace("MY_SCHEMA", strSchemaOwner);
}

answered Dec 23, 2020 at 7:56

Alexander

1,3471 gold badge17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Retatrget every SQL statement before its execution

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related