Dynamically choose DataAbstract connection based on RO call

Hi,

I would like to define DA schemas but select a Connection per RO service call.

RemObjects.DataAbstract.RemoteDataAdapter.Fill but this does not let me choose the connection.
Based on some client parameters I want to select DB name, tablename and other settings which are unknown at design time.

With AcquireConnection=true the service constructor is called and then it takes a predefined connection without allowing me to create a specific connection based on client specific parameters

With AcquireConnection=false I do not succeed into getting Fill to work.
Both this.Connection=RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireConnection(conname,true);
and
this.Connection=RemObjects.DataAbstract.Server.Engine.ConnectionManager.AcquireNewConnection(connstr,true);
appear to give a connection but in the end I get ‘An exception occurred on the server: No connection was acquired by RemObjectsService’

FYI:I also added
RemObjects.DataAbstract.Server.Engine.ConnectionManager.AddDefinition(conname,connstr,false);

Are there working examples of AcquireConnection=false?
Maybe I should write my own GetData and Binary2DataSet function to achieve this? Are there samples of this?

RemObjects.DataAbstract.RemoteDataAdapter.Fill but this does not let me choose the connection.
For security reasons client apps should not have direct access to the underlying database.

There are several scenarios where a connection should be selected dynamically:

  1. A required connection is set on user log in
  2. A connection should be switched based on per-request basis (the same user needs to query more than one database).

This is the default DataService definition for further reference:

	public class DataService : RemObjects.DataAbstract.Server.DataAbstractService
	{
		public DataService()
		{
			this.InitializeComponent();
		}

		// Required for designer support
		private void InitializeComponent()
		{
			RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
			dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
			this.AcquireConnection = true;
			this.ServiceDataStreamer = dataStreamer;
			this.ServiceSchemaName = "NetCoreApplication3Dataset";
		}
	}

The simplest solution can be used when a connection name is set during user login process:

	public class DataService : RemObjects.DataAbstract.Server.DataAbstractService
	{
		public DataService()
		{
			this.InitializeComponent();
		}

		private void InitializeComponent()
		{
			RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
			dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
			this.AcquireConnection = true;
			this.ServiceDataStreamer = dataStreamer;
			this.ServiceSchemaName = "NetCoreApplication3Dataset";

			this.BeforeAcquireConnection += DataService_BeforeAcquireConnection;
		}

		private void DataService_BeforeAcquireConnection(DataAbstractService sender, BeforeAcquireConnectionEventArgs e)
		{
			e.ConnectionName = (string) this.Session["ConnectionName"];
		}
     }

In this code sample an event handler is set to one of the DataService events. The BeforeAcquireConnection event is raised when the connection is acquired by the data service.

As you can see, in the event handler implementation the desired connection name is retrieved from the user session.

More complex approach that needs to be used when a connection string needs to be used and that connection string is not registered in the ConnectionManager.

	public class DataService : RemObjects.DataAbstract.Server.DataAbstractService
	{
		public DataService()
		{
			this.InitializeComponent();
		}

		private void InitializeComponent()
		{
			RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
			dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
			this.AcquireConnection = true;
			this.ServiceDataStreamer = dataStreamer;
			this.ServiceSchemaName = "NetCoreApplication3Dataset";
		}


		protected override IAbstractConnection GetConnection()
		{
			var connectionString = (string) this.Session["ConnectionString"];

			return new BaseConnection("<<>>", connectionString, true);
		}

		protected override void ReleaseConnection(IAbstractConnection connection)
		{
			connection.Close();
		}

		protected override void DropConnection(IAbstractConnection connection)
		{
			connection.Close();
		}
	}

Here a set of 3 methods is redefined:
The GetConnection method acquires connection string from user session and then creates a new connection object.

The ReleaseConnection and DropConnection methods ensure that the connection is released properly on service deactivation.

Thanks for the feedback. Two questions

  1. When would you use AcquireConnection=false ? How does this setup work?
  2. Did you already provide an example of ‘A connection should be switched based on per-request basis (the same user needs to query more than one database).’ ? Your complex example still uses (static) session data to choose a connectionstring.

So what if the user needs to be able to switch between several connections without relogin?

The most obvious solution would be to create a set of service method that would set corresponding session value.
However this approach should be used carefully to ensure that race conditions never occur. The most obvious sample of such race condition is a situation when data is accessed for the same user from several threads and each thread needs to use its own connection. In this case it is not possible to set connection via user session as it will be used for requests from different threads.

Take a look at this data access code:

			Console.WriteLine("Press ENTER to access data!");
			Console.ReadLine();


			var dataModule = new DataModule();

			dataModule.DataAdapter.RemoteService.ServiceName = "DataService.PCTrade-SQLite";

			dataModule.LogOn("test", "test");
			var query = from x in dataModule.DataAdapter.GetTable<Orders>() select x;
			var data = query.ToList();
			Console.WriteLine("Count: " + data.Count);

			Console.ReadLine();

It is a very simple data request code with a singe non-usual code line where the ServiceName is set. Additional data is passed to the server via service name suffix. Let’s take a look hot to use this data:

	public class DataService : RemObjects.DataAbstract.Server.DataAbstractService, IMessageAwareService
	{
		public DataService()
		{
			this.InitializeComponent();
		}

		private void InitializeComponent()
		{
			RemObjects.DataAbstract.Bin2DataStreamer dataStreamer;
			dataStreamer = new RemObjects.DataAbstract.Bin2DataStreamer();
			this.AcquireConnection = false;
			this.ServiceDataStreamer = dataStreamer;
			this.ServiceSchemaName = "NetCoreApplication3Dataset";
		}

		public void OnServiceActivated(IMessage message)
		{
			var requestName = message.InterfaceName;

			var pos = requestName.IndexOf('.');

			this.ConnectionName = (pos > 1) ? requestName.Substring(pos + 1) : "";
			this.AcquireConnection = true;
			this.Connection = this.GetConnection();
		}
	}

The IMessageAwareService allows to access additional data about the incoming service request. This allows to ;extract additional data from the service request and to use this data to acquire requested data connection.

EDIT: Logged an issue to add this topic into documentation.

@fvancrae

Thanks, logged as bugs://85931

Where does your code example (see below) come from?
Do you have a fully working example for this?
This looks like client code which has access to a class Orders. How/where does it get defined?
I am starting a project and want to use the best architecture for an n-tier environment.
The classic DA schemas that are created in the RO server for strongly typed classes are generated are not flexible enough. Client side (or a tier in the middle) da schema definitions allow faster development and easier deployment. Are there any samples for this?

The point of that code was this code line where an extended data service name is set and the desired connection name is passed to the server:

DataAdapter.RemoteService.ServiceName = "DataService.PCTrade-SQLite";

The rest of the client code is usual data access via DA LINQ (DA LINQ (.NET)) and is not that relevant.

What are the planned server and client platforms (.NET, Delphi? something else)?

Not sure what you do mean here? What is the ideal scenario you want to achieve?

I understand the RemoteService.ServiceName solution, this is an additional question. I will ask it in a separate request