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
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!