0

here is the scenario: I have a .CSV bind in a dataTable, I want to move the entire columns in the DT to match the order of my data base's columns.

Once the order is executed, I'll have a Query to insert in the DB.

I thought of looping through my DT and select all row with the same index as the column like so :

Is there a better/quicker way to do it?

PS: I'll use CSV with ~100-5000 rows.

EDIT:

My database columns are like so :

first name, last name, age, Country

and my CSV is ordered like this

age, last name, country, first name

I want the CSV's columns to match the order of the DB's

Kaval Patel
  • 670
  • 4
  • 20
FistiPaul
  • 53
  • 1
  • 11
  • 5000 rows shouldn't take that long to process. What RDBMS are you using? – SS_DBA Nov 02 '17 at 15:16
  • 4
    The question is not clear. For instance, what is `DT_test` and what is `colnum`? What has overwriting all row's values with a new value to do with changing the order of columns? Last but not least, do you have a performance issue at all? – Tim Schmelter Nov 02 '17 at 15:17
  • 1
    Can only agree with @TimSchmelter your question is hard to understand. – Rand Random Nov 02 '17 at 15:19
  • @TimSchmelter sorry if it's not clear. DT_test is just a intermediate datatable. colnum is the number an int that represent the numer of the column index – FistiPaul Nov 02 '17 at 15:22
  • @FistiPaul: if `DT_test ` is a DataTable your code doesn't even compile because a `DataTable` doesn't have an indexer property. You should really try to explain your requirement better and not show code that is not compiling as if it was working code that has a performance issue. – Tim Schmelter Nov 02 '17 at 15:24
  • @TimSchmelter sorry, i'm very bad at explaining over the web. I have edit my question, is it more understandable ? – FistiPaul Nov 02 '17 at 15:30
  • I can think of ways to re-order columns in a DataTable but doing so begs the question of why? if your table exists in DB you would just map them appropriately. If table does not exist in DB then why bother re-ordering, most modern applications don't rely on ordinal position of columns if you have a case where it does well then there are still ways of inserting and transforming on the fly rather than re-ordering Datatable – Matt Nov 02 '17 at 15:31
  • @Matt how can i map them ? – FistiPaul Nov 02 '17 at 15:40
  • @FistiPaul 1 way if you have bulk copy persmission on SQL server would be to use SqlBulkCopy and setup the ColumnMappings https://stackoverflow.com/a/17469507/5510627 – Matt Nov 02 '17 at 15:50

2 Answers2

0

It sounds like you are trying to insert using INSERT INTO [table] VALUES (...). For queries, the column order should not matter. Plus, this can be dangerous. What if in the future the database columns are changed? What if the table is dropped and then created with a different column order? Sure, most of us build Apps assuming the back-end structure is static, but things happen, and it does not take much effort to declare the columns like INSERT INTO [table] ([first name], [last name], [age], [Country]) VALUES (...). Plus this way has the benefit of being easier to read.

If you want to reorder the columns in the view layer, reorder them in the view layer, but I suggest keeping your DataTable in the order that it was read.

zambonee
  • 1,599
  • 11
  • 17
-1

At first use bulk copy https://johnnycode.com/2013/08/19/using-c-sharp-sqlbulkcopy-to-import-csv-data-sql-server/ to copy your full table in the database then do whatever you want in the database which is so much faster.

Majid Akbari
  • 200
  • 12
  • So SqlBulkCopy is the answer to everything? How does that answer the question how to reorder the DataTable columns with the DB columns(whatever that means)? – Tim Schmelter Nov 02 '17 at 15:18
  • When you copy the CSV table into a table in the database like table1 you can run this code to reorder it: 'select col1, col3, col2 into table2 from table1' – Majid Akbari Nov 02 '17 at 15:25