DataSet Designer creates different CommandText for different tables

Question

This is an odd question, and I hope I framed it correctly. I am using the Visual Studio DataSet designer to create SQL table adapters to link my project to a SQL server. The connection string is via an ODBC entry.

All tables have default queries, like update and insert. In the dataset.Designer.cs file, these commands are auto-generated. Here's one example:

this._adapter.DeleteCommand = new global::System.Data.Odbc.OdbcCommand();
            this._adapter.DeleteCommand.Connection = this.Connection;
            this._adapter.DeleteCommand.CommandText = "DELETE FROM [pftestbench].[dbo].[TestData_TestGroupInstrument] WHERE (([TestGroup" +
                "RecordNumber] = ?) AND ([InstrumentSerial] = ?))";
            this._adapter.DeleteCommand.CommandType = global::System.Data.CommandType.Text;
            this._adapter.DeleteCommand.Parameters.Add(new global::System.Data.Odbc.OdbcParameter("Original_TestGroupRecordNumber", global::System.Data.Odbc.OdbcType.BigInt, 0, global::System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "TestGroupRecordNumber", global::System.Data.DataRowVersion.Original, false, null));
            this._adapter.DeleteCommand.Parameters.Add(new global::System.Data.Odbc.OdbcParameter("Original_InstrumentSerial", global::System.Data.Odbc.OdbcType.NVarChar, 0, global::System.Data.ParameterDirection.Input, ((byte)(0)), ((byte)(0)), "InstrumentSerial", global::System.Data.DataRowVersion.Original, false, null));

I am interested in the CommandText line:

this._adapter.DeleteCommand.CommandText = "DELETE FROM [pftestbench].[dbo].[TestData_TestGroupInstrument] WHERE (([TestGroup" +
                "RecordNumber] = ?) AND ([InstrumentSerial] = ?))";

You can see how this depends on the database name, and how it is undesirable in case I change database names. Now, all but one of the tables specify the database name. The one table that does not, has a CommandText line like this:

this._adapter.DeleteCommand.CommandText = @"DELETE FROM [TestData_Instrument] WHERE (([Serial] = ?) AND ((? = 1 AND [Model] IS NULL) OR ([Model] = ?)) AND ((? = 1 AND [CalibrationDue] IS NULL) OR ([CalibrationDue] = ?)) AND ((? = 1 AND [Trace] IS NULL) OR ([Trace] = ?)) AND ((? = 1 AND [Manufacturer] IS NULL) OR ([Manufacturer] = ?)))";

The last one is what I would want ideally, because it is Database Name independent. This is why I used an ODBC to begin with, but clearly I am not doing something right.

My question is, how can I ensure that all these entries will be database name independent?

Answer 1

Unfortunately when using the designer/query builder in the DataSet Designer, you can't prevent the database name or context from being prepended to your SQL object names.

You can manually remove them though via the wizard for each query. Right click on the query in question and select "Configure...". In the Wizard that opens, remove the [pfttestbench] and any other prepended data (such as [dbo]) WITHOUT using the Query Builder (which will add them right back in when you exit.) Once you've removed the context information, hit the finish button.

To confirm this worked, you'll want to force the *.Designer.cs file to be re-created, or you'll not see the change. To do this, right click on the .xsd file in the Solution Explorer and select "Run Custom Tool". Once that's done you can either open the *.Designer.cs file like you did initially, or view the CommandText properties of the queries you modified to confirm the fully qualified name context was removed.