Copying a row from one table to another using LINQ and C#

Question

I have two tables that are pretty much exact clones of one another (identical columns, just different columns set as primary keys). Basically the second table is just for keeping a history of the first table. What I need to do is, when a user updates a record in table 1 I need to insert the original copy of that record into table 2.

I am using a LinqDataSource object and utilizing the LinqDataSource_Updating(object sender, LinqDataSourceUpdateEventArgs e) event so I have access to e.OriginalObject and that will be perfect for inserting the original row in table 2. My problem is that I don't want to have to set every property manually because there are about 50 of them, so I want to use Reflection but am not sure how to properly go about it.

Consider the following code:

INSTRUMENT_DATA_SHEET _original = (INSTRUMENT_DATA_SHEET)e.OriginalObject; INSTRUMENT_DATA_SHEET_HISTORY _history = new INSTRUMENT_DATA_SHEET_HISTORY();

How can I go about copying all of the _original's property values to _history's? I have tried using the solution from this question, however it isn't working for me. It throws the error:

Property DATE has an incompatible type in E_and_I.INSTRUMENT_DATA_SHEET_HISTORY

My guess is that it's because the DATE column is part of the primary key in table 2, but not table 1. As I said, the only difference between the two tables are the primary keys. Here they are for your reference:

Primary Keys

Have you considered doing this using SQL triggers instead of in code? — Rudism
– Rudism, Commented Aug 8, 2012 at 16:07

Erik Philips · Accepted Answer · 2012-08-08 16:48:28Z

1

The problem I see is that your History type Date field is DateTime and your Original one is DateTime? (same problem with REV in History, it can't be null). You'll have to decide what happens if there is a null DateTime of Date in your original version. Then you should be able to modify Skeets code (oh dear!) to handle specifically these fields differently then the rest of the fields.

answered Aug 8, 2012 at 16:48

Erik Philips

54.9k11 gold badges133 silver badges158 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Hoff Over a year ago

That's precisely what I'm trying to do actually - haven't figured it out yet though...

Gert Arnold Over a year ago

@Hoff I think you should make the columns in the source table not null as well, because apparently it is a requirement: you can't create a history record is one of the values is null.

Hoff Over a year ago

Agreed - gotta love getting slapped with somebody else's database (lazy & unthought out) design.

Hoff · Accepted Answer · 2012-08-08 18:36:54Z

Ok I've managed to figure it out :) Here's what I did:

INSTRUMENT_DATA_SHEET _original = (INSTRUMENT_DATA_SHEET)e.OriginalObject; INSTRUMENT_DATA_SHEET_HISTORY _history = new INSTRUMENT_DATA_SHEET_HISTORY(); foreach (PropertyInfo pi in _original.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance)) { _history.GetType().GetProperty(pi.Name).SetValue(_history, pi.GetValue(_original, null), null); }

Not very elegant but it gets the job done!

Collectives™ on Stack Overflow

Copying a row from one table to another using LINQ and C#

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related