Why would you ever want to change a default connection in a TableAdapter...
... or for that matter, the connectTimeout? I mean, that private setting that it gets created with in the VS2005 editor (typically localhost - but in theory, whatever you set it to when you generated the code in the IDE) ought to be enough for you, along with the 640K of memory in your box ;)
Seriously, I love not writing DAL code. I love dragging a stored proc into the data schema pane, and not writing parameter binding code. I HATE parameter binding code - just always seemed like the kind of thing that ought to be generated. So when I first started generating c# stored proc stubs in VS2005 - I was esctatic!
Of course, the downside comes when your very elegant solution goes to QA (isn't that always when the downside occurs??? I digress...) Most places (at least the ones that do not develop on the production instance - hopefully the one where YOU work) - have dev, QA, and production, which might require, i dunno, dynamically setting the connection string (kinda the reason you keep it in app.config and not hardcode it). I thought the thing would be smart enough to read my connection string tucked nicely into my app.config file, just the way you are supposed to. I mean, the rest of the process seemed very magical, why not this piece as well?
Of course, that ain't how it works. Each TableAdapter appears to have a local copy of it's connection object, and it encapsulates it so that you can't change it easy in code. Now, I am all for encapsulation of detail, but wow! This would normally be cool, but because there is no other way to dynamically assign a connection to the adapter that I could find (even in the constructors) - it seems impossible to do a dynamic connection. So - what to do? Go back to creating the connection, command, and adapters manually? Nah - there must be a better way:
namespace Foo.FooTableAdapters
{
public partial class QueriesTableAdapter
{
public string ConnectionString
{
get{
if (CommandCollection.Length > 0)
return (CommandCollection[0].Connection.ConnectionString);
else
return null;
}
set {
foreach (IDbCommand command in CommandCollection)
command.Connection.ConnectionString = value;
}
}
public void SetCommandTimeout(int timeout)
{
foreach (IDbCommand command in CommandCollection)
command.CommandTimeout = timeout;
}
}
}
Foo.FooTableAdapters
{
public partial class QueriesTableAdapter
{
public string ConnectionString
{
get{
if (CommandCollection.Length > 0)
return (CommandCollection[0].Connection.ConnectionString);
else
return null;
}
set {
foreach (IDbCommand command in CommandCollection)
command.Connection.ConnectionString = value;
}
}
public void SetCommandTimeout(int timeout)
{
foreach (IDbCommand command in CommandCollection)
command.CommandTimeout = timeout;
}
}
}
The solution works by adding two properties to the table adapter that aggregate connection properties that - get set for each command in the QueriesTableAdapter. This works most of the time (it is pretty infrequent to have different connections for different queries - but not implausable - if you need that, your code will be a little “fancier“). It also works by taking advantage of the fact that table adapters are defined as partial classes, allowing you to change or add other properties.
Of course, this solution seems like a lot of code for exposing two properties. And I am sure there is a more elegant solution than doing this for each table adapter (i.e. perhaps doing this as a partial class from the base class of TableAdapter somehow - or some other elegant solution). But this works, and to boot, doesn't take a lot of code, and is reasonably easy to understand. That said, YMMV as for being future proof - it takes advantage of what might be a loophole - the fact that the generated table adapter is a partial class - but hopefully, if that ever changes, the problem of dynamically setting the connection will be fixed.