Using Oracle/SQLConnectionstringBuilder in the same call

Question

I am trying to create a new object of the OracleConnectionStringBuilder or the SqlConnectionStringBuilder depending on the database providor sent to the function.

Can't figure out how to do that in the shortest(and readable) manner possible.

I start with:

var isIntegratedSecuritySet = string.IsNullOrEmpty(UserId) || string.IsNullOrEmpty(Password);
var connBuilder = new SqlConnectionStringBuilder()
    {
        DataSource = DataSource,
        UserID = UserId,
        Password = Password,
        InitialCatalog = InitialCatalog,
        ConnectTimeout = 15,
        IntegratedSecurity = isIntegratedSecuritySet
    };

    return connBuilder.ConnectionString;
}

But now Oracle doesn't have IntegratedSecurity, it has PersistSecurityInfo.

So I try this:

var isIntegratedSecuritySet = string.IsNullOrEmpty(UserId) || string.IsNullOrEmpty(Password);
var connBuilder = new DbConnectionStringBuilder();

try
{
    if (Providor.Equals(DatabaseProvidors.Oracle))
    {
        connBuilder = new OracleConnectionStringBuilder()
        {

            PersistSecurityInfo = isIntegratedSecuritySet
        };
    }
    else if (Providor.Equals(DatabaseProvidors.SqlServer))
    {
        connBuilder = new SqlConnectionStringBuilder()
        {
            IntegratedSecurity = isIntegratedSecuritySet
        };
    }

But now I need to cast the properties to one or the other

((SqlConnectionStringBuilder)connBuilder).DataSource = DataSource;

etc..

What can I do to be able to do that without having to explicitly define both in full form like this:

var isIntegratedSecuritySet = string.IsNullOrEmpty(UserId) || string.IsNullOrEmpty(Password);

try
{
    if (Providor.Equals(DatabaseProvidors.Oracle))
    {
        var connBuilder = new OracleConnectionStringBuilder()
        {
            DataSource = DataSource,
            UserID = UserId,
            Password = Password,
            ConnectionTimeout = 15,
            PersistSecurityInfo = isIntegratedSecuritySet
        };

        //if (InitialCatalog != null)
        //    connBuilder.InitialCatalog = InitialCatalog;
        return connBuilder.ConnectionString;
    }
    else if (Providor.Equals(DatabaseProvidors.SqlServer))
    {
        var connBuilder = new SqlConnectionStringBuilder()
        {
            DataSource = DataSource,
            UserID = UserId,
            Password = Password,
            ConnectTimeout = 15,
            IntegratedSecurity = isIntegratedSecuritySet
        };

        if (InitialCatalog != null)
            connBuilder.InitialCatalog = InitialCatalog;

        return connBuilder.ConnectionString;
    }
}

Wernfried Domscheit · Accepted Answer · 2016-03-11 13:47:32Z

2

Why not using the generic DbConnectionStringBuilder?

var connBuilder = new System.Data.Common.DbConnectionStringBuilder();

connBuilder.Add("Data Source", DataSource);
connBuilder.Add("User Id", UserId);
connBuilder.Add("Password", Password);

if (Providor.Equals(DatabaseProvidors.Oracle)) {
   connBuilder.Add("Persist Security Info", isIntegratedSecuritySet);
   connBuilder.Add("Connection Timeout", ConnectionTimeout);
} else if (Providor.Equals(DatabaseProvidors.SqlServer)) {
   connBuilder.Add("Integrated Securiry", isIntegratedSecuritySet);
   connBuilder.Add("Connect Timeout", ConnectionTimeout); // Not sure about this property
}

edited Mar 11, 2016 at 13:47

answered Mar 11, 2016 at 11:58

Wernfried Domscheit

60.5k10 gold badges93 silver badges132 bronze badges

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

5 Comments

Dan Guzman Over a year ago

Also note that PersistSecurityInfo and IntegratedSecuriry have different meanings. Both System.Data.SqlClient and System.Data.OracleClient support both. The collection key includes a space between the words ("Persist Security Info" and "Integrated Securiry").

AngelicCore Over a year ago

@Good idea - I will try that Wernfried I was using the generic version in my code above but I wasn't doing the .Add bit - was using its properties directly, which the generic lacks

AngelicCore Over a year ago

@DanGuzman That's true I glanced over the names, my mistake. Was mainly focused on the issue rather than the actual properties.

Wernfried Domscheit Over a year ago

Corrected the parameter names.

AngelicCore Over a year ago

@WernfriedDomscheit How do I know the names of the "strings"? It sounds like I have to guess all of them as they don't appear in intellisense as they aren't properties - or even in msdn documentations.

Collectives™ on Stack Overflow

Using Oracle/SQLConnectionstringBuilder in the same call

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related