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?

what do you mean insure that the database name is independent based on what you have posted there is no way that the there can be any name collision the names in the `[TestData_Instrument]` for example vs `[pftestbench].[dbo].[TestData_TestGroupInstrument]` point to two different tables.. are you having any errors or issues currently..? — MethodMan, Jan 08 '13 at 23:14
Sorry let me clarify. [TestData_TestGroupInstrument] and [TestData_Instrument] are both tables in the [pftestbench] database. So basically, I don't want [pftestbench] to appear, just like is the case for [TestData_Instrument]. — sbenderli, Jan 09 '13 at 15:34

score 1 · Answer 1 · answered Mar 10 '15 at 18:18

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.

DataSet Designer creates different CommandText for different tables

1 Answers1