Dynamic connections with SnapObjects.

Marco Meoni Accepted Answer Pending Moderation

Sunday, 16 October 2022 18:38 PM UTC
SnapObjects
# 1

Hi Juan,

please allow me a few criticisms on your solution, which however is very valuable as it raises awareness of the problem of binding cachenames dynamically.

the API has code redundancy: given a resource (e.g. PersonDemo), there is one controller per each DB type, one interface/service per each DB type, all of them duplicating the code for retrieve, update, maxId, etc functions.
there might exist typos in the DataContextFactoryXXX classes: they always set configuration for SQLServer ( var options0 = new SqlServerDataContextOptions(connectionString); )
I dont see where cachenames are parameterized, the API only connects to "Manager" cachename (which is string hardcoded!), while the other 2 cachenames (PersonDemo01 and ConectionManager) are never used.
Interfaces, controllers, services, models are all named after the DB type (e.g , see "SQL" repeated in classes'name DSSQLPersonDemoController, PersonDemoSQL, IDSSQLPersonDemoService, DSSQLPersonDemoService).

This said, I thank you again for paving the way on how to handle this problem in SnapObject's DataContext object and AddDataContext function. With the help from Appeon, I attach a simpler solution that avoids the four flaws above.

Goal is to parameterise cachenames, which for example can be sent by the client as easy as in the http request's header:

restclient.SetRequestHeaders( "CacheName:"+ ls_CacheName )
restclient.Retrieve(dw_dept, "https://localhost:5001/api/Department/Retrieve")

The API wil rely on same concept of data context factory you posted

services.AddScoped<DefaultDataContext>(provider => provider.GetService<DataContextFactory>().CreateDataContextSQL())

but it accesses current HttpContext and Config to build the connection string dynamically:

public DataContextFactory(IConfiguration config, IHttpContextAccessor httpAccessor)
{
_config = config;
_httpAccessor = httpAccessor;
}

public DefaultDataContext CreateDataContextSQL()
{
string cacheName = _httpAccessor.HttpContext.Request.Headers["CacheName"].ToString();
string connectStr = _config[$"ConnectionStrings:{cacheName}"];
var options = new OdbcSqlAnywhereDataContextOptions(connectStr);
return new DefaultDataContext(options);
}

Best,

.m

Comment

There are no comments made yet.

Logan Liu @Appeon Accepted Answer Pending Moderation

Monday, 24 June 2019 07:35 AM UTC
SnapObjects
# 2

Hi Juan,

The following points may be helpful for creating your DataContext factory:

1. Pass in the connection string to construct the DataContextOption object.

E.g.:

new SqlServerDataContextOptions(connectionString)

2. Pass in DataContextOptions object to construct the DataContext.

E.g.:

    public class SampleDataContext : SqlServerDataContext
    {
        public SampleDataContext(IDataContextOptions options)
            : base(options)
        {
        }
    }

3. Create a DataContextFactory class which can create corresponding DataContext object based on the key of the connection string. Depending on your business logic, you might want to consider the database type in the DataContextFactory class.

4. Inject the DataContextFactory into the DI framework (note the lifetime), then receive and use it in each service to get DataContext.

Regards,

Logan

Comment

Logan Liu @Appeon
Monday, 24 June 2019 09:25 AM UTC

This link is also helpful:

https://stackoverflow.com/questions/36816215/dynamically-change-connection-string-in-asp-net-core

Helpful 0

Dan Harrel
Friday, 24 March 2023 18:47 PM UTC

Thank you very much, Logan. I've known for 2 years I needed to do something like this, and the time to do it came. I was successful using your example.

For the record, my database identifier (i.e. database to connect to) comes into the Web API a little differently: It comes in the HTTPPOST body on the "logon" API call. If the logon is authenticated, then the db identifier is packaged up as a JWT (JSON Web Token) Claim and encoded in the JWT. API calls subsequent to the logon pulls the database ID from the JWT. This all fits into the framework of your example.

For what it's worth, pulling the database ID out of the HTTPPOST body was a bit tricky. This happens before the body deserializes into the Controller web method parameter. You first have to invoke EnableBuffering() on the HTTP request object, read the body text, deserialize it into the appropriate object, and then reset the body "position" to 0 - otherwise ASP.NET MVC's attempt to deserialize the body into the controller parameter will fail.

I can post some sample code if anyone is interested.

(I didn't look into Marco's example because this one solved my problem)

Helpful 0

There are no comments made yet.

Juan Alejandro Lam López Accepted Answer Pending Moderation

Sunday, 23 June 2019 18:13 PM UTC
SnapObjects
# 3

You could help me to implement something similar to this

https://dzone.com/articles/dynamic-connection-string-in-net-core

or something similar that helps me manage multiple connections to different databases and change them in execution time.

Comment

There are no comments made yet.

Resolved Dynamic connections with SnapObjects.