1. Juan Alejandro Lam López
  2. SnapObjects
  3. Saturday, 22 June 2019 05:27 AM UTC

Hi all.
I have a server with more than 500 databases with the same structure and I would like to know how I can implement a connection factory with SnapObjects. And how to connect dynamically to each one these.

 

Regards

Accepted Answer
Juan Alejandro Lam López Accepted Answer Pending Moderation
  1. Monday, 8 July 2019 04:52 AM UTC
  2. SnapObjects
  3. # Permalink

Hi Logan Liu.


Thanks for the help, I was able to replicate and develop a new example and I am uploading it to the Community.

Regards

Attachments (1)
Comment
There are no comments made yet.
Marco Meoni Accepted Answer Pending Moderation
  1. Sunday, 16 October 2022 18:38 PM UTC
  2. SnapObjects
  3. # 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.

  1. 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.
  2. there might exist typos in the DataContextFactoryXXX classes: they always set configuration for SQLServer ( var options0 = new SqlServerDataContextOptions(connectionString); )
  3. 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.
  4. 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

Attachments (1)
Comment
There are no comments made yet.
Logan Liu @Appeon Accepted Answer Pending Moderation
  1. Monday, 24 June 2019 07:35 AM UTC
  2. SnapObjects
  3. # 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

Attachments (1)
Comment
  1. Dan Harrel
  2. 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)
  1. Helpful
There are no comments made yet.
Juan Alejandro Lam López Accepted Answer Pending Moderation
  1. Sunday, 23 June 2019 18:13 PM UTC
  2. SnapObjects
  3. # 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.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.