Can I parametrize a PART of a TableAdapter's table names?

Question

Hi,

I am trying to access a Dynamics NAV database using ADO.NET, and I want to parametrize parts of the SQL queries. The problem with this is that on the SQL database, Dynamics NAV tables are named "Company Name$Table Name" (e.g. "Cronus AG$Customer"), and I would like to parametrize ONLY the Company Name part of the table name.

Is this possible?

Thanks in advance

Arne

Answer 1

Hi Arne,

Thanks for your post and welcome to MSDN forums.

Does one company have only one table ? If one comapny has more tables, and you would like to parametrize only the company name part of the table name, there would be some errors because one company name would map to many tables in the NAV tables. Could you please explain more or provide some sample codes ? Thanks.

Best regards,
Alex Liang

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 2

Hi Alex,

sorry I never got back to this after asking, I was given different tasks shortly thereafter.

To answer your question: Dynamics NAV has a database structure that supports multiple companies. So from NAV's point of view, you can select one of multiple customers, which always have the same tables such as Contacts , Sales , Seminars , etc. However, from the DB's point of view, those customers are separate, each customer having its own tables. Basically, with two customers (CRONUS and AdventureWorks), the tables would look as follows:

• dbo.AdventureWorks$Contacts
• dbo.AdventureWorks$Sales
• dbo.AdventureWorks$Seminars
• dbo.AdventureWorks$...
• dbo.CRONUS$Contacts
• dbo.CRONUS$Sales
• dbo.CRONUS$Seminars
• dbo.CRONUS$...

I solved the problem in the meantime, but not in a very elegant way. Since the project I was working on required access to only one of many different customers in the database, I simply created synonyms for that customer's tables, such as "dbo.Seminar " for "dbo.CRONUS$Seminar " etc. This way, I can write a customer agnostic DataSet as long as those synonyms exist on the database.

For obvious reasons, I am not very happy with this solution. I would like to put the customer name (AdventureWorks, CRONUS,...) into a config file and pass it to my DataSet, creating the actual table name from a concatenation of the customer name variable and the NAV table name. Something along those lines:

SELECT * FROM ("dbo." + $customerName + "$Contacts")

Unfortunately, ADO.NET does not allow me to use variables in SQL statements.

My question is, what would be the best approach to parameterize the company part of the table names?

Thanks for your help

Arne