0

I am currently in the process of creating a SQL Server CE database application in C# and I am having some logic issues that I thought maybe someone could help with.

Objective: to be able to supply an XML file to the end user, which tells the program to create a new set of tables using the supplied structure (new tables with tmp_ prefix). Existing data then needs to be moved from the old tables to the new ones (with new structure), then the old tables need to be dropped.

I've written too much code to be able to paste it here, so I'm going to break it down into logical steps (as it is a logical issue, not a compiler issue).

  1. Get new database structure from supplied XML file, read into datatable [DONE]

  2. Dynamically concatenate a SQL query to create new table with tmp_ prefix [DONE]

  3. Compare new structure with old structure, move relevant data across [NOT DONE]

I am having problems with the logical approach to step 3. Basically I need to move data from an old structure to the new structure - ignoring old columns which do not appear in the new set of columns, and entering blank data for new columns which do not appear in the list of old columns. I have need to adhere to the new column schema, such as datatype, max length, etc etc. This is seriously making my head hurt as I'm very new to C#. Does anyone have ideas as the best way to approach this?

Thanks in advance!

Mike Baxter
  • 6,868
  • 17
  • 67
  • 115
  • There's a few ways. What you're basically looking to achieve is a comparison between structures on a column-by-column basis (if you can rely on their similarity) and transfer data over as a result of the findings from that comparison. How are you 'moving relevant data across' assuming you have the data? –  Mar 15 '13 at 15:44
  • @DeeMac I'm not moving any data across yet. I'm still struggling with the logic before I start coding it. Any attempts at coding this have fallen flat ;) My initial thought I guess would be to dynamically construct an INSERT statement based on multiple if-statements, then run the statement, and delete the old table once all the data has been moved across. – Mike Baxter Mar 15 '13 at 15:46
  • 1
    Ok, it seems you're trying to take this on as one big massive task. You've done a good job of breaking it down into 3 steps. Now break the 3rd step down even further so that your problems are more manageable. I don't mean to come across as patronising by the way. Break step 3 down into, "get structure of 1st db, get structure of 2nd, compare each column and assign all common columns to a list, use that list to determine what data from your data set is inserted into the 2nd db, insert it". Based on that - ask the relevant questions on SO for each task that you cannot complete yourself. –  Mar 15 '13 at 15:52
  • @DeeMac Do not worry about patronizing me - I am new to C#. Thanks for the pointers, I will break the problem down further and come back. – Mike Baxter Mar 15 '13 at 15:56
  • Another pointer, with tasks like this don't go too procedural - e.g. first match the tables - I usually used terms `left` and `right` (for comparing similar things). So db-s fall into `only on the left`, `only on the right` - and `matching tables`. Then for matching tables you do the same with fields. You end up breaking down the whole process into `delete`, `add` - and `same` - quite simplified, but helps. I had been doing this exact thing couple times. – NSGaga-mostly-inactive Mar 15 '13 at 16:24

0 Answers0