Multiple connections in single DA server not working as expected

Data Abstract
,
1

Hi,

In our current project we are working with 2 databases.
I have both added them in the connections manager and tested their connection, so far so good.
Next I added a table from database A into the DA schema modeler and a stored procedure (as a command) from database B.
These are both showing up correctly, they have both 1 statement with the correct connection filled in in the schema modeler.
In the connections manager you always (?) have a default connection, in my case this was the first database added (‘A’).
When trying out the client, retrieving values from the table in DB A was working as expected.
However, executing the command failed with the message “Could not find stored procedure…” .
When setting the default connection to DB B, the situation is reversed, I cannot get any values from the table in DB A, but can execute the command (the stored procedure) on DB B.
From looking around the table/command should be using the connection defined in the statement, but it seems it is always using the default connection.
Is my thinking correct? Or am I missing something?

I am using Visual Studio 2019 and DA 10.0.0.1463.
Thanks in advance!

Kind regards,
Robin

3

Hello

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

	private void DataAbstractService_ValidateDataTableAccess(DataAbstractServiceValidateDBObjectAccessEventArgs e)
	{
		this.AcquireCorrectConnection(e.Schema.FindDataset(e.DBObjectName));
		e.Connection = this.Connection;
	}

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

	private void DataAbstractService_BeforeProcessDelta(BusinessProcessor sender, DeltaEventArgs e)
	{
		this.AcquireCorrectConnection(e.Delta.Schema);
		e.Connection = this.Connection;
	}

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

		String requiredConnection = schemaElement.Statements[0].Connection;
		if (String.IsNullOrEmpty(requiredConnection))
		{
			requiredConnection = RemObjects.DataAbstract.Server.Engine.ConnectionManager.DefaultConnectionName;
		}

		if (String.Equals(this.Connection.Name, requiredConnection, StringComparison.OrdinalIgnoreCase))
		{
			return;
		}

		// Release default connection
		IAbstractConnection connection = this.Connection;
		Boolean handleTransaction = connection.InTransaction;
		if (handleTransaction)
		{
			this.CommitTransaction(connection);
		}
		RemObjects.DataAbstract.Server.Engine.ConnectionManager.ReleaseConnection(connection);

		// Acquire new connection
		connection =  RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireConnection(requiredConnection, true);
		if (handleTransaction)
		{
			this.BeginTransaction(ref connection);
		}

		this.Connection = connection;
		this.ConnectionName = requiredConnection;
	}

This code will acquire a connection that is needed to open the required DataTable, if this connection differs from the default one.

EDIT: Updated the code snippet

4

Hi,

Thanks for the code, I can see it retrieves and sets the connection property correctly.
However in my client I get the following exception:

{“An exception occurred on the server: Invalid object name ‘IniFile’.”}

With IniFile being the table name.
In this case IniFile is a table from database B, with database A set as default in the connections manager.
When trying:

dataAdapter.GetTable<IniFile>(). …

I got the above exception, I am using the LinqRemoteDataAdapter.
The initial case is working (retrieving from DB A, executing SP on DB B) but when trying to retrieve something from a table from DB B something still goes wrong.

Thanks again for your help!
Kind regards,
Robin

5

Hmm, sorry.

Updated the initial answer to a more modern API (and retested it on DA LINQ data access as well)

Logged an issue to put this into docs

6

Thanks, logged as bugs://84067

7

Hi,

No problem!
I changed it to the code sample and can also confirm this is working as expected now!
Thanks for the assistance!

8

Hi,

Today we noticed that this code is not building anymore…
We tested versions 10.0.0.1489 and 10.0.0.1495 .

Severity Code Description Project File Line Suppression State
Error CS0246 The type or namespace name ‘DataAbstractServiceValidateDBObjectAccessEventArgs’ could not be found (are you missing a using directive or an assembly reference?)

Thanks in advance!

9

Hello

Sorry for the inconvenience.
There was a major change in this family of events. While we try to maintain backwards compatibility, it was just not possible to provide reliable non-breaking change at these code paths.

You can find more details here: https://talk.remobjects.com/t/data-abstract-for-net-vnext-new-features/18415/36

Regards

10

Hi,

thanks for the quick reply, I’ll check out the article you linked

11

This link is dead.
Where can I find this info

12

That link is a limited access one. Anyway it would not answer your question as it describes changes, not the initial HowTo.
I’ll provide you the answer and code samples in this topic: Dynamically choose DataAbstract connection based on RO call