Use a specific connection with a table when there is more than one connection

visual-studio

(brian71us) #1

My schema has two database connections. Tables from the “default” connection work properly. Tables from the second (non-default) database connection report that the table name doesn’t exist. If I change the default database connection then it flips the other way. It’s like Data Abstract ignores the database connection that I’ve specified in the select statement.


(antonk) #2

Hello

You need to implement some additional server-side logic to get this working.

To be able to work with Schemas containing objects that use different connections, add the ValidateDatatableAccess, ValidateCommandExecution and BeforeUpdateData event handlers to your Service with the following code:

private void DataAbstractService_ValidateDatatableAccess(DataAbstractServiceValidateDBObjectAccessEventArgs aEA)
{
  this.AcquireCorrectConnection(aEA.Schema.FindDataset(aEA.DBObjectName));
}

private void DataAbstractService_ValidateCommandExecution(DataAbstractServiceValidateDBObjectAccessEventArgs aEA)
{
  this.AcquireCorrectConnection(aEA.Schema.Commands.FindItem(aEA.DBObjectName));
} 

private void DataAbstractService_BeforeUpdateData(DataAbstractService aSender, DataAbstractServiceUpdateDataEventArgs aEA)
{
  if (aEA.Deltas.Count > 0)
    this.AcquireCorrectConnection(aEA.Deltas[0].Schema);
}

private void AcquireCorrectConnection(RemObjects.DataAbstract.Schema.SQLSchemaElement schemaElement)
{
  if ((schemaElement == null) || (schemaElement.Statements.Count == 0))
    return;

  String lRequiredConnection = schemaElement.Statements[0].Connection;
  if (String.IsNullOrEmpty(lRequiredConnection))
    lRequiredConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.DefaultConnectionName;
  if (String.Equals(this.Connection.Name, lRequiredConnection, StringComparison.OrdinalIgnoreCase))
    return;

  // Release default connection
  IAbstractConnection lConnection = this.Connection;
  Boolean lHandleTransaction = lConnection.InTransaction;
  if (lHandleTransaction)
    this.CommitTransaction(lConnection);
  RemObjects.DataAbstract.Server.Engine.ConnectionManager.ReleaseConnection(ref lConnection);

  // Acquire new connection
  lConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireConnection(lRequiredConnection, true);
  if (lHandleTransaction)
    this.BeginTransaction(ref lConnection);
  this.Connection = lConnection;
}

Regards


(brian71us) #3

Will give this a try. Thanks!