Solved

How do I reorder the columns in a data Table

Posted on 2007-03-25
5
1,836 Views
Last Modified: 2007-12-19
Hello Experts,

I would like to refer to the question which i raised yesterday. As in the previous question, the task I would like to accomplish is to reorder the columns in a data table before exporting to CSV file. All the data rows in the columns of object dtCargoList are already populated with data and the columns names are reformatted as in the "Select Case" statement.  The order in which the columns exported to the CSV file appeared is the not the order desired. For example, I would like the order of Columns in the CSC file as the one below;

Order: [ShipName], [ShipType], [Berth,InDate/Time],[OutDateTime] etc.. as appeared exactly in the Select Case Statement.

The code I used for this is mentioned below for clarity.

Can you pls suggest an efficient way to finish this. Thanks.



 'Public Function to export Find Cargo search results to a csv file
        Public Function ExportFindCargoSearchResults(ByRef sTempFilePath As String, _
                                                     ByRef dtCargoList As DataTable, _
                                                     ByVal sFileName As String) As Data.ResultData

            Dim oResultData As New Data.ResultData
            Dim oFileWriter As StreamWriter

            Try

                Dim sheader As String
                Dim sbody As String
                Dim srecord As String
                Dim sformattedColumnName As String
                Dim drRow As DataRow

                'For each Datacolumn in the DataTable
                For Each col As DataColumn In dtCargoList.Columns


                    'Initially set formattted column name to empty string
                    sformattedColumnName = String.Empty

                    'Change the headers to match the screen information

                    Select Case col.ColumnName

                        Case "Ship_name"
                            sformattedColumnName = "Ship Name"
                            'sHeaderColumnShipname = "Ship Name"

                        Case "Ship_type"
                            sformattedColumnName = "Ship Type"
                            'sHeaderColumnShipType = "Ship Type"

                        Case "Berth_abbreviation"
                            sformattedColumnName = "Berth"
                            'sHeaderColumnBerth = "Berth"

                        Case "In_datetime"
                            sformattedColumnName = "In"
                            'sHeaderColumnInDateTime = "In"

                        Case "Out_datetime"
                            sformattedColumnName = "Out"
                            'sHeaderColumnOutDateTime = "Out"

                        Case "Cargo_agent_short_name"
                            sformattedColumnName = "Cargo Agent"
                            'sHeaderColumnCargoAgent = "Cargo Agent"

                        Case "Principle_agent_short_name"
                            sformattedColumnName = "Principal Agent"
                            'sHeaderColumnPrincipalAgent = "Principal Agent"

                        Case "Handling_type_description"
                            sformattedColumnName = "Handling Type"
                            'sHeaderColumnHandlingType = "Handling Type"

                        Case "Storage_id"
                            sformattedColumnName = "Storage ID"
                            'sHeaderColumnStorageID = "Storage ID"

                        Case "Storage_device_type_description"
                            sformattedColumnName = "Storage Device"
                            'sHeaderColumnStorageDevice = "Storage Device"

                        Case "TimeRestrictedCargo"
                            sformattedColumnName = "Time Restricted"
                            'sHeaderColumnTimeRestricted = "Time Restricted"

                        Case "Unno"
                            sformattedColumnName = "UNNo"
                            'sHeaderColumnUNNo = "UNNo"

                        Case "Class"
                            sformattedColumnName = "Class"
                            'sHeaderColumnClass = "Class"

                        Case "Subrisk"
                            sformattedColumnName = "Subrisk"
                            'sHeaderColumnSubrisk = "Subrisk"

                        Case "Pg"
                            sformattedColumnName = "PG"
                            'sHeaderColumnPackagingGroup = "PG"

                        Case "Proper_shipping_name"
                            sformattedColumnName = "PSN"
                            'sHeaderColumnProperShippingName = "PSN"

                        Case "Quantity"
                            sformattedColumnName = "Qty"
                            'sHeaderColumnQuantity = "Qty"

                        Case "Packaging_type_code"
                            sformattedColumnName = "PT"
                            'sHeaderColumnPackagingTypeCode = "PT"
                    End Select

                    'Add the column to the header and include the comma

                    sheader = sheader & Chr(34) & sformattedColumnName & Chr(34) & ","

                Next

                sheader = sheader.Substring(0, sheader.Length - 1)
               
                'For each row in the datatable
                For Each row As DataRow In dtCargoList.Rows
                    'Create an array
                    Dim arr() As Object = row.ItemArray()

                    For i As Integer = 0 To arr.Length - 1
                        If arr(i).ToString().IndexOf(",") > 0 Then
                            srecord = srecord & Chr(34) & _
                            arr(i).ToString() & Chr(34) & ","
                        Else
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    Next
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""
                Next

                Dim sCSVtext As String = sheader & vbCrLf & sbody

                Dim sFullFilePath = sTempFilePath & sFileName

                'Write CSV text to file
                'Check if message and file path are both provided
                If Not (sCSVtext Is Nothing AndAlso sFullFilePath Is Nothing) Then

                    'check if file exists
                    If File.Exists(sFullFilePath) Then
                        'add file path
                        oFileWriter = File.AppendText(sFullFilePath)
                    Else
                        'create new file and add file path
                        oFileWriter = File.CreateText(sFullFilePath)
                    End If
                    'Write text to file, flush then close the file
                    oFileWriter.Write(sCSVtext)
                    oFileWriter.Flush()
                    oFileWriter.Close()
                    sCSVtext = String.Empty

                End If ' customer log file and message is not null


                'Set the return result to success
                oResultData.ReturnResult = Data.ResultData.ReturnResultType.Success

                'Catch the exception and set the return result and errors
            Catch ex As Exception
                oResultData.ReturnResult = Data.ResultData.ReturnResultType.SystemError
                oResultData.ErrorMessage &= "Error ocurred while trying to export Cargo details"
                oResultData.ErrorException = ex

            End Try

            'Return the result data
            Return oResultData

        End Function
0
Comment
Question by:LukeSayaw
  • 2
  • 2
5 Comments
 
LVL 15

Expert Comment

by:igor_alpha
ID: 18790512
Hi LukeSayaw,
You can use SetOrdinal() method of DataColumn.
It changes the ordinal or position of the DataColumn to the specified ordinal or position. If ordinal is less than 0 or greater than the existing number of columns ? 1 (greater than the ordinal of the last column) then an Invalid ArgumentException is thrown.

DataColumn.SetOrdinal Method
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.setordinal.aspx

For example:
datatable.Columns("Col1").SetOrdinal(1)
0
 

Author Comment

by:LukeSayaw
ID: 18790638
The method "SetOrdinal" is only available in .NET Framework 2.0 or 3. I am using .NET Framewrok 1.1 only. So the method is not applicable unfortunately.

At the moment I am trying another approach: Copying the data to a new DataTable and re-order the column indexes. That might work but not sure.

If u have a better efficient approach to it, pls let me know immediately.
Thanks
0
 
LVL 15

Expert Comment

by:igor_alpha
ID: 18790728
Sorry, you didn't said anything about .net 1.1

You will have to code a method to spin through the data and build a copy with the columns in the desired order.

DataSet dsNew = new DataSet();
DataTable dt = new DataTable("MyTableName");
DataColumn OldCol;

// Swapping columns 0 and 1
OldCol = MyDataSet.Tables[0].Columns[1];
dt.Columns.Add(new DataColumn(OldCol.ColumnName, OldCol.DataType));
OldCol = MyDataSet.Tables[0].Columns[0];
dt.Columns.Add(new DataColumn(OldCol.ColumnName, OldCol.DataType));

dsNew.Tables.Add(dt);
0
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18791502
You don't actually need to re-order columns in a datatable.  You could simply create a "translation array" and use it in building your output string for the CSV file.  On these lines

   Dim translate(dtCargoList.Columns.Count - 1) As Integer
   translate(0) = <existing column number that you want to be first in output>
   translate(1) = <existing column number that you want to be second in output>
   'etc

Then in this

                    For i As Integer = 0 To arr.Length - 1
                        If arr(i).ToString().IndexOf(",") > 0 Then
                            srecord = srecord & Chr(34) & _
                            arr(i).ToString() & Chr(34) & ","
                        Else
                            srecord = srecord & arr(i).ToString() & ","
                        End If
                    Next
                    sbody = sbody & srecord.Substring(0, srecord.Length - 1) & vbCrLf
                    srecord = ""
                Next

replace every occurrence of arr(i) with arr(translate(i))

Roger
0
 

Author Comment

by:LukeSayaw
ID: 18791941
I prefer the second approach to the first one. I will use the second approach in the application and hope that it will work. I will let u know if it works tomorrow.

Many Thanks
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now