Parameterize the schema in a strongly-typed ADO.NET TableAdapter

Question

Hey all,

I'm trying to build a strongly-typed dataset in ADO.Net and am having a little trouble with one aspect of the TableAdapters.

My query looks like

SELECT *
FROM testdict.ModuleVariable
WHERE Module = ?

My problem revolves around the testdict part. We use several different schemas to access our data (because of a multiplexed Sybase IQ instance). How can I parameterize the schema portion of this query?

I've tried:

SELECT *
FROM ?.ModuleVariable
WHERE Module = ?

but to no avail. My current mindset is that I may have to inherit the TableAdapter and parameterize the schema manually but I am hoping there is a nicer solution!

Thanks in advance

Answer 1

First of all you won't be able to achieve this in design-time by simply adding a select query. You cannot parametrize the schema.

However there's a solution for this.Here's how you can do that.

1.) Drag and drop the table into your typed-dataset designer which will create a typed-data-table for the table and the table-adapter to access database table. table adapter is aware of the schema of the data-table.

2.) Now create a stored-procedure in your database that takes two arguments. one is the schema of the table [?.ModuleVariable] and other would be your where clause or maybe anytihng you may want as criteria. You can create overloads of this as you wish. This stored-procedure will then construct the sql query based on the arguments and execute it on the database. This will return the result-set to calling table-adapter.

3.) From the design-view Add a method to table-adapter which will fetch results from the stored-procedure. Make sure that the schema of results exactly meets the schema of the associated data-table.

4.) Now from the code you can create an instance of the table adapter and call the method which in turn will call your stored-procedure and return you with the data-table filled in with results.

Have fun!