Inserting a Duplicate DataRow to a DataSet

By in
No comments

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;
app.foo = Foo.Text.Trim();
app.bar = 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);
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!

The following two tabs change content below.

selaromdotnet

Senior Developer at iD Tech
Josh loves all things Microsoft and Windows, and develops solutions for Web, Desktop and Mobile using the .NET Framework, Azure, UWP and everything else in the Microsoft Stack. His other passion is music, and in his spare time Josh spins and produces electronic music under the name DJ SelArom. His other passion is music, and in his spare time Josh spins and produces electronic music under the name DJ SelArom.