Help - Auto Interface Implementation - SQL Attribute Schema change at run time.

[keerthirajap]

Hi Team,

I need help/advice on passing the schema name of table at run time in Auto Interface Implementation SQL Attribute. Since Dev and Production are in same server database.

Attribute parameter only concat the constant values. But some how my schema needs to be changed base on Config file.

[Sql(@"SELECT TOP 1 column1 FROM " + schemaAtRunTime + "TableName")]
Task<string> GetValueMethod();

Eg in Dev :

[Sql(@"SELECT TOP 1 column1 FROM " + "dev."   + "TableName")]
Task<string> GetValueMethod();

Eg in Prod :

[Sql(@"SELECT TOP 1 column1 FROM " + "Prd."   + "TableName")]
Task<string> GetValueMethod();

Any shorcuts, idea or help will be appreciated.

• Dotnet version: [dotnet472]
• Database: [SQL Server]
• Library version: [6.2.9]

[jonwagner]

Somewhere in the postgres adapter there is code for dynamically switching schemas. I forget exactly how we did it but it may be useful.

On Nov 6, 2019, at 5:55 AM, keerthiraja1988 notifications@github.com wrote:



Hi Team,

I need help/advice on passing the schema name of table at run time in Auto Interface Implementation SQL Attribute. Since Dev and Production are in same server database.

Attribute parameter only concat the constant values. But some how my schema needs to be changed base on Config file.

[Sql(@"SELECT TOP 1 column1 FROM " + schemaAtRunTime + "TableName")] Task GetValueMethod();

Eg in Dev :

[Sql(@"SELECT TOP 1 column1 FROM " + "dev." + "TableName")] Task GetValueMethod();

Eg in Prod :

[Sql(@"SELECT TOP 1 column1 FROM " + "Prd." + "TableName")] Task GetValueMethod();

Any shorcuts, idea or help will be appreciated.

• Dotnet version: [dotnet472]
• Database: [SQL Server]
• Library version: [6.2.9]

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHubhttps://github.com/jonwagner/Insight.Database/issues/409?email_source=notifications&email_token=AAMTO5HGKY5EPWUNLCJHUILQSKWBVA5CNFSM4JJT2KU2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HXGQ64A, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAMTO5HJYACXAPG6YQUYJT3QSKWBVANCNFSM4JJT2KUQ.

[Jaxelr]

I had a similar issue on a client a few years back using the auto implement feature for a large repo that i had no time to rewrite all that implementation. I simply used a symbol on compilation-time (im aware this is a hacky solution), heres a small repro:

    class Program
    {

        static void Main(string[] args)
        {
            var conn = new SqlConnection("Server=.;database=master;Trusted_Connection=true;");
            SqlInsightDbProvider.RegisterProvider();

            var imp = conn.As<IHelloRepo>();

            var res = imp.GetParent();

            Console.WriteLine(res.Name);
            Console.ReadLine();
        }

    }

    public interface IHelloRepo
    {
#if DEBUG
        const string schema = "dev.";
#else
        const string schema = "prod.";
#endif

        [Sql("SELECT * FROM " + schema + "table1")]
        Poco GetParent();
    }

    public class Poco
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Edit: doing this from a config is a challenge since the attribute has to be included as part of the compilation by definition. There are clever ways to work around it see:(https://stackoverflow.com/questions/51269/change-attributes-parameter-at-runtime)

[keerthirajap]

Somewhere in the postgres adapter there is code for dynamically switching schemas. I forget exactly how we did it but it may be useful. On Nov 6, 2019, at 5:55 AM, keerthiraja1988 notifications@github.com wrote:  Hi Team, I need help/advice on passing the schema name of table at run time in Auto Interface Implementation SQL Attribute. Since Dev and Production are in same server database. Attribute parameter only concat the constant values. But some how my schema needs to be changed base on Config file. [Sql(@"SELECT TOP 1 column1 FROM " + schemaAtRunTime + "TableName")] Task GetValueMethod(); Eg in Dev : [Sql(@"SELECT TOP 1 column1 FROM " + "dev." + "TableName")] Task GetValueMethod(); Eg in Prod : [Sql(@"SELECT TOP 1 column1 FROM " + "Prd." + "TableName")] Task GetValueMethod(); Any shorcuts, idea or help will be appreciated. Dotnet version: [dotnet472] Database: [SQL Server] * Library version: [6.2.9] — You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub<#409?email_source=notifications&email_token=AAMTO5HGKY5EPWUNLCJHUILQSKWBVA5CNFSM4JJT2KU2YY3PNVWWK3TUL52HS4DFUVEXG43VMWVGG33NNVSW45C7NFSM4HXGQ64A>, or unsubscribehttps://github.com/notifications/unsubscribe-auth/AAMTO5HJYACXAPG6YQUYJT3QSKWBVANCNFSM4JJT2KUQ.

@jonwagner

Is it possible to pass the sql query as a parameter to Method as given below, as we pass Special Parameters int? commandTimeout & CancellationToken? cancellationToken.

Task GetBeerByType(SqlQuery sql, CancellationToken? cancellationToken);

It will be nice to have that feature where we can run dynamic queries. Anyway developer need to take care of model binding.

We can raise it as new feature request

[keerthirajap]

I had a similar issue on a client a few years back using the auto implement feature for a large repo that i had no time to rewrite all that implementation. I simply used a symbol on compilation-time (im aware this is a hacky solution), heres a small repro:

    class Program
    {

        static void Main(string[] args)
        {
            var conn = new SqlConnection("Server=.;database=master;Trusted_Connection=true;");
            SqlInsightDbProvider.RegisterProvider();

            var imp = conn.As<IHelloRepo>();

            var res = imp.GetParent();

            Console.WriteLine(res.Name);
            Console.ReadLine();
        }

    }

    public interface IHelloRepo
    {
#if DEBUG
        const string schema = "dev.";
#else
        const string schema = "prod.";
#endif

        [Sql("SELECT * FROM " + schema + "table1")]
        Poco GetParent();
    }

    public class Poco
    {
        public int Id { get; set; }
        public string Name { get; set; }
    }

Edit: doing this from a config is a challenge since the attribute has to be included as part of the compilation by definition. There are clever ways to work around it see:(https://stackoverflow.com/questions/51269/change-attributes-parameter-at-runtime)

@Jaxelr

#if DEBUG
        const string schema = "dev.";
#else
        const string schema = "prod.";
#endif

I am to end up here. But it was huge risk when we handover the application to support team.

I am not expert in coding. Can you please help me following this link.

https://stackoverflow.com/questions/51269/change-attributes-parameter-at-runtime

[ThisNoName]

Can't you create different user and give each a default schema? Then run your DEV/PROD under different credential, assuming you didn't also split schema by region/feature/team, each with dev/test/qa/prod variant ...

[keerthirajap]

Can't you create different user and give each a default schema? Then run your DEV/PROD under different credential, assuming you didn't also split schema by region/feature/team, each with dev/test/qa/prod variant ...

Current project environment it not possible.

[Jaxelr]

@keerthiraja1988 after looking into it a bit, found that whats plausible (based on the article) is to modify the attribute instance object, but not the specific attribute instance tied to the interface. Which is not what you are looking for, just for reference heres a snippet:

void Main()
{
    Custom[] attrs = (Custom[])typeof(MyClass).
    GetCustomAttributes(typeof(Custom), false);

    Console.WriteLine(attrs[0]);

    attrs[0] = new Custom("Hello Attribute World");
    attrs[0].MoreData = "SomeData";

    Console.WriteLine(attrs[0]);
}

public class Custom : Attribute
{
    public string MyData;
    public string MoreData;
    public Custom(string MyDataIn)
    { MyData = MyDataIn; }
}

[Custom("Hello Attribute World")]
public class MyClass
{ }

I think @ThisNoName solution would be the way to go, but since you mentioned it isn't plausible, i don't see any other viable alternative, aside from the hacky workaround.

The ConnectionWithSchema postgres implementation wont work also since you already mentioned it wasnt plausible to tied different users to different schemas.

[keerthirajap]

Thank you for your effort

Finally I resolved this with c# Preprocessor directives, and this help can be closed

Created Constance Class as below

public static class Constants
    {
#if DEBUG
        public const string Db2Environment = "T";
#else
    public const string Db2Environment = "P";
#endif
}

Setup Mulitple Web.Config(s) with Environment Schema

Web.Config
<add key="DB2Environment" value="T" /> 

Web.UAT.config
<add key="DB2Environment" value="T"   xdt:Transform="Replace" xdt:Locator="Match(key)" />

Web.Prod.config
<add key="DB2Environment" value="P"   xdt:Transform="Replace" xdt:Locator="Match(key)" />

Implement Insight in HomeController

HomeController.cs

 public class HomeController : AsyncController
    {       

        private readonly System.Data.Odbc.OdbcConnection _db2Connection;
        private readonly string _db2Environment;
        private readonly IBeerRepository _beerRepository;

        public HomeController()
                {
            OdbcInsightDbProvider.RegisterProvider();

            this._db2Connection = new System.Data.Odbc.OdbcConnection("Driver ={ IBM DB2 ODBC DRIVER }; Hostname etc...");
            this._db2Environment = ConfigurationManager.AppSettings["DB2Environment"];

            if (Constants.Db2Environment != this._db2Environment)
            {
                throw new Exception("Db2 Environment Constant " + Constants.Db2Environment + " is not equal Web.Config " + this._db2Environment + ".|| Build With T for Debug/Dev/UAT and P for Prod ");
            }

            _beerRepository = this._db2Connection.As<IBeerRepository>();
        }
}

IBeerRepository.cs

    public interface IBeerRepository
    {
        [Sql("select * from  MBR" + Constants.Db2Environment + "01.Table1")]
        Task<MemberInfo> GetTable1();

        [Sql("select * from  SFG" + Constants.Db2Environment + "03.Table2")]
        Task<MemberInfo> GetTable2();
    }

If application is not build with Proper environment before release, throw error message at startup. Then support person needs to re-build and much safer now.

**_Server Error in '/' Application. Db2 Environment Constant P is not equal Web.Config T.|| Build With T for Debug/Dev/UAT and P for Prod Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Exception: Db2 Environment Constant P is not equal Web.Config T.|| Build With T for Debug/Dev/UAT and P for Prod

Source Error:

Line 31: if (Constants.Db2Environment != this._db2Environment) Line 32: { Line 33: throw new Exception("Db2 Environment Constant " + Constants.Db2Environment + " is not equal Web.Config " + this.db2Environment + ".|| Build With T for Debug/Dev/UAT and P for Prod "); Line 34: } Line 35: }**

[Jaxelr]

Great! Closing issue then.