Modify TableAdapter Commands at Runtime

Question

Is it possible to modify the SELECT, UPDATE, DELETE, and INSERT commands for a tableadapter at runtime? What I want to do is set the commands for the tableadapter based on the connection type (SQL Server or Access) the user selects when the application opens.

Basically, I want the tableadapter names to remain the same so I don't have to put if statements in everywhere I call tableadapter.fill or .update methods. Instead, I want to put an if statement in after the connection string is chosen and modify the SELECT, UPDATE, DELETE, and INSERT commands based on what the user selects. Something like below

If ConnectionString  = Access Then
    tableadapter.selectcommand="SELECT...FROM AccessDbase"
Else
    tableadapter.selectcommand="SELECT...FROM SQLDbase"
End if

What is the best way to do this without having to create two separate applications (one to connect to Access and on for SQL Server)?

Many thanks in advance.

score 0 · Answer 1 · answered Jan 29 '21 at 17:24

You can modify the query directly by updating the CommandText:

daTableAdapter.Adapter.UpdateCommand.CommandText = sql_query;

To update the Connection String, then you would need to update the SqlConnection of the table adapter:

daTableAdapter.Connection.ConnectionString = myConnectionString;

You can also modify the Connection of one command exclusively by using:

daTableAdapter.Adapter.UpdateCommand.Connection.ConnectionString = myConnectionString;

score -1 · Answer 2 · answered Mar 28 '12 at 06:38

you can change select insert o update command by setting adapters commands.commandtexts

If ConnectionString = Access Then
    tableadapter.SelectCommand.CommandText = "SELECT...FROM AccessDbase"
    tableadapter.InsertCommand.CommandText = "INSERT INTO AccessDbase..."
    tableadapter.UpdateCommand.CommandText = "UPDATE AccessDbase SET ..."
    tableadapter.DeleteCommand.CommandText = "DELETE FROM AccessDbase WHERE ..."
Else
    tableadapter.SelectCommand.CommandText = "SELECT...FROM SQLDbase"
    tableadapter.InsertCommand.CommandText = "INSERT INTO SQLDbase..."
    tableadapter.UpdateCommand.CommandText = "UPDATE SQLDbase SET ..."
    tableadapter.DeleteCommand.CommandText = "DELETE FROM SQLDbase WHERE ..."
End if

Modify TableAdapter Commands at Runtime

2 Answers2