Converting all the DateTime Columns to SqlDateTime Columns in a Datatable

Question

Hi,

What I want to do is basically take any generic DataTable and then Convert all the DateTime columns into SqlDateTime Columns. (i.e if the datetime column value = datetime.MinValue then Set it to SqlDateTime.Null)

Is there anyway I can do this without altering the original datatable (i.e withoutadding computed columns ) or having to parse through then entire datatable row by row?

The reason I want to do this is because I need to feed this Datatable into a SQLBulkCopy method, which would write them all together into the database. The problem with using datetime field is that it throws an error at the time of upload.

AgentX

Answer 1

Well, the best i could come up with was this,

I know it is copying the datatable row by row, I believe it can be done more elegantly with LINQ ,

any one else has an answer?

                Dim i As Long = 0
                Dim j As Long = 0

                Dim dtOut As DataTable = dt.Clone()
                For Each c As DataColumn In dtOut.Columns
                    If c.DataType.FullName = GetType(DateTime).FullName Then
                        c.DataType = GetType(SqlTypes.SqlDateTime)
                    End If
                Next

                dtOut.BeginLoadData()
                Dim drtmp As DataRow
                For Each dr As DataRow In dt.Rows
                    drtmp = dtOut.NewRow()
                    For Each dc As DataColumn In dt.Columns
                        If dc.DataType.FullName = GetType(DateTime).FullName Then
                            If dr(dc) = Date.MinValue Then
                                drtmp(dc.ColumnName) = SqlTypes.SqlDateTime.Null
                            Else
                                drtmp(dc.ColumnName) = dr(dc)
                            End If
                        Else
                            drtmp(dc.ColumnName) = dr(dc)
                        End If
                    Next
                Next
                dtOut.EndLoadData()