?
Solved

How do I reorder the columns in a data Table

Posted on 2007-03-25
5
Medium Priority
?
1,980 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

ASP.Net to Oracle Connectivity Recently I had to develop an ASP.NET application connecting to an Oracle database.As I am doing it first time ,I had to solve several problems. This article will help to such developers  to develop an ASP.NET client…
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 …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…
Suggested Courses

777 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