.net, SQLDataTableAdapters question

Question

I have a sql query method using SQL data table adapters in .xsd file and need to be able to dynamically change the connection string to that method in the code behind. I can't figure out how to access the methods properties from code behind.

method is IsValidDock(), simply checks database for a particular dock number and returns bool.

basically I am instantiating the query in my code behind by

Dim SQLCommands As New SQLDataTableAdapters.SQLCommands()

I thought I could get to the properties of the method by

SQLCommands.IsValidDock(). some properties. This is not working, any ideas?

I haven't worked with sql data table adapters like this before, adding functionality to previous developers code, so bear with my ignorance. Thanks

Answer 1

The better question, may be, though, why do you need to change this connection on the fly? Could you just change the definition and call it a day? Do you have multiple database servers that you are working against? The connection strings get stored in the configuration for the project, so if this is a situation where you need a different connection in your test and production environments, then you can just have two config files.

Assuming that is not a solution, read on ...

If I remember correctly, the SqlDataTableAdapter objects spat out by the XSD parser in Visual Studio are sub-classes of SqlDataAdapter, are they not (you can find out in Visual Studio by right clicking on a reference to one and choosing "Go to definition")? If so, you can just new up an instance of the generated class (there should be a generated .cs or .vb file for it in your project) and pass it a customized SqlConnection object.

There is no interface for changing the connection of a data adapter after it has been created. The adapter opens the connection when you do anything that needs it (call Fill(), Insert(), etc.) and only closed when the SqlDataAdapter is disposed.

Answer 2

TableAdapters are generated as partial classes, so you could extend that TableAdapter and add the correct initialization for the connection property of the adapter in there.

Hope this helps Jaime