Inserting a Duplicate DataRow to a DataSet

While developing an online form, I came upon the need to insert two copies the results to a database, one for each of two options selected on the form. The only thing different between these entries would be this option, and of course, the autogenerated ID for the row’s primary key.

While I could simply call the Insert function of the TableAdapter twice, this method requires that each field be explicitly submitted as a parameter, and since the data is coming from textboxes, and there are about a dozen boxes on the page, that’s a whole lot of textbox.Text.Trim() arguments!

One alternative was to introduce a local variable for each textbox, assigning it the respective value, and only passing those locals as parameters, but being a HUGE fan of strong-typing, I would much rather use a class that maps these fields to a row, especially since the dataset I made for this Table already has one!

It was easy enough to instantiate the dataset, create the strongly-typed row and populate it with data and add it to the dataset:

var ds = new MyDataTable();

var app = ds.NewMyRow();
app.Option = OptionMenu1.SelectedValue; = Foo.Text.Trim(); = Bar.Text.Trim();

Now I can add the first copy of the row to the datatable just fine. Unfortunately, since app is a reference variable, I can’t add it again, because it already belongs to the table. And even though the Add method will take an overload of the ItemArray, since it’s passing duplicate values, I’ll still get an error because the primary key (automatically assigned as -1) is being duplicated!

ds.Rows.Add(app); // Throws Error, already in table!
ds.Rows.Add(app.ItemArray); // Throws Error, duplicate Primary Key! 

For whatever reason, DataRow does not include a Clone() method or any way to easily make a duplicate of the item. So the solution I worked out is to instead make a new instance of an object array from the DataRow.ItemArray property, setting the primary key (at index 0) to null before adding this new instance using the Add method. This forces the dataset to generate a new auto key so that the DataRow is unique. Then I can retrieve the reference to this new row (so I can have strongly-typed access to its properties) and change the option for this new row.

if (OptionMenu2.SelectedValue != "none")
    // duplicate row
var arr = app.ItemArray; arr[0] = null; ds.Rows.Add(arr); // update the option for the second row var app2 = ds.Rows[1] as MyRow; app2.Option = OptionMenu2.SelectedValue; } new MyTableAdapter().Update(ds);

Now all I have to do is call the update method on my table adapter and both rows are added from just one copy of the data. Not bad!

Editing The Datasource

Ahh it’s all coming back to me now… the headaches that is 😛

One thing that I would always forget how to do with the new 2.0 data controls is how to enable editing using the datasource. I set up the sql statements for select, insert, update, and delete, but only select would work. when I update something and save, it just reverts back…

the problem is that I forgot to set the Datakeys property for the control which I want to edit. It must be set to the primary key of the table in order to enable “out of the box” editing.

back to work