TableAdapter - updating without a key

Question

Hi,

I'm a total newbie at the .net c# business and dove in this last week creating a form application to shuffle data around for SSIS configurations. The geniuses at MS decided not to apply a key to the table I'm working with - and generating a composite key of the two candidate fields will not work due to their combined length being too long. I don't particularly want to mess with the [ssis configurations] table schema by adding an autoincrement field.

So I've been having alot of trouble getting an update from a DataGridView control to work with a TableAdapter.

I need the update statement to be update table set a = x where b = y and c = z.

Can I set the update method of the TableAdapter, and if so, how. If not, what to do?

I see this autogenerated code:

this._adapter.InsertCommand = new global::System.Data.SqlClient.SqlCommand();
            this._adapter.InsertCommand.Connection = this.Connection;
            this._adapter.InsertCommand.CommandText = "INSERT INTO [dbo].[SSIS Configurations Staging] ([ConfigurationFilter], [Configur" +
                "edValue], [PackagePath], [ConfiguredValueType]) VALUES (@ConfigurationFilter, @C" +
                "onfiguredValue, @PackagePath, @ConfiguredValueType)";

But in my form code, the UpdateCommand is not available. I'm assuming this is because the above code is a class definition which I cannot change after creating the object. I see this code has a recommendation not to be changed since it is autogenerated by VS.

Thanks for your most excellent advice.

Answer 1

From your code i assume you are using a typed Dataset with the designer.

Not having a primary key is one of the many reasons the designer will not generate Insert, Update or Delete commands. This is a limitation of the CommandBuilder.

You could use the properties window to add an Update Command to the Apdapter but I would advice against that, if you later configure your main query again it will happily throw away all your work. The same argument holds against modifying the code in any *.Designer.cs file.

Instead, doubleclick on the caption with the Adaptername. The designer will create (if necessary) the accompanying non-designer source file and put the outside of a partial class in it. Unfortunately that is how far the code-generation of the designer goes in C#, you'll have to take it from there. (Aside: The VB designer knows a few more tricks).

Edit:

You will have to provide your own Update(...) method and setup an UpdateCommand etc.

  var updateCommand = new SqlCommand();
  ...