ADO.NET Datatable to ITable

[HagenProbsthain]

Hello users,

is there a way to use an ADO.NET-Datatable as XY-Event-Layer ?
The challenge is the dynamically link to this table, so if the underlying datatable is changed the XY-Event-Layer also changes. My problem is to find out, how I make the QueryInterface between Datatable and ITable like:

Dim pTable As ITable
Dim pDatatable As System.Data.DataTable
... fill the table ...
pTable = pDatatable

Any other suggestions are also welcome.

I found examples to convert ITable to Datatable and vice versa, but I don't want to ceate temporary data. I also found ESRI.ArcGIS.Utility.Converter::ToDataSet to convert an ESRI-Recordset to ADO-Datatable, but where is something like ESRI.ArcGIS.Utility.Converter::FromDataSet ?

I tried to make a XY-Event-Layer with IQueryName2, but  it seems the SQL-Statement (whereclause) is restricted for simple queries (i. e. no joins possible).

I found a similar question in the closed thread http://forums.esri.com/Thread.asp?c=2&f=1720&t=209789, but there is no solution.

Can help anyone ?

[JamesCrandall]

Not so much a suggestion, but a solution.  First, please have a look at the sample project I have uploaded to the code gallery, it might come in handy:

http://resources.arcgis.com/gallery/file/arcobjects-net-api/details?entryID=675318D8-1422-2418-8814-...

Second, here is the code to do what you want -- basically the reverse of the process in the sample app I uploaded.  This works for an ITable in a PGDB, so you'll have to make adjustments for other types (SDE tables will require special additions and are outside of the scope of this thread).

Just issue the ADO.NET DataTable reference and an ITableName value to this function (you'll have to set the db_path string to your own system.

Good luck!

james

Private Function ConvDT_To_ITab(ByVal inDataTable As DataTable, ByVal inITableName As String) As ITable

        Try

            '**Open the PGDB for temp layers
            Dim db_path As String = "C:\yourpathname\yourPGDBname.mdb"


            Dim pWS As IWorkspace
            Dim pFWS As IFeatureWorkspace
            Dim pWorkspaceFactory As IWorkspaceFactory
            pWorkspaceFactory = New ESRI.ArcGIS.DataSourcesGDB.AccessWorkspaceFactory
            pFWS = pWorkspaceFactory.OpenFromFile(db_path, 0)
            pWS = pFWS

            ' Set up a simple fields collection
            Dim pFields As ESRI.ArcGIS.Geodatabase.IFields
            Dim pFieldsEdit As ESRI.ArcGIS.Geodatabase.IFieldsEdit
            pFields = New ESRI.ArcGIS.Geodatabase.Fields
            pFieldsEdit = New ESRI.ArcGIS.Geodatabase.Fields
            pFieldsEdit = pFields

            Dim pField As ESRI.ArcGIS.Geodatabase.IField
            Dim pFieldEdit As ESRI.ArcGIS.Geodatabase.IFieldEdit
            pField = New ESRI.ArcGIS.Geodatabase.Field
            pFieldEdit = pField

            '' create the object id field
            pField = New ESRI.ArcGIS.Geodatabase.Field
            pFieldEdit = pField
            pFieldEdit.Name_2 = "OBJECTID"
            pFieldEdit.Type_2 = esriFieldType.esriFieldTypeOID
            pFieldsEdit.AddField(pField)

            For i = 0 To inDataTable.Columns.Count - 1
                pField = New ESRI.ArcGIS.Geodatabase.Field
                pFieldEdit = pField
                With pFieldEdit 'set all the properties according to the columns in the recordset
                    .Length_2 = inDataTable.Columns(i).MaxLength
                    .Name_2 = inDataTable.Columns(i).ColumnName
                    .Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeInteger
                End With

                Dim ColName As String = inDataTable.Columns(i).ColumnName.ToString
                Dim dtColType As String = inDataTable.Columns(i).DataType.ToString

                If inDataTable.Columns(i).DataType.FullName.ToString = "System.Int32" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeInteger
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.String" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeString
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.Double" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDouble
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.Decimal" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDouble
                ElseIf inDataTable.Columns(i).DataType.FullName.ToString = "System.DateTime" Then
                    pFieldEdit.Type_2 = ESRI.ArcGIS.Geodatabase.esriFieldType.esriFieldTypeDate
                End If

                pFieldsEdit.AddField(pField)
            Next i


            '**Check for existing FeatClass and delete if present
            Dim pExistingDs As ESRI.ArcGIS.Geodatabase.IDataset
            Dim pEnumDataset As IEnumDataset
            pEnumDataset = pWS.Datasets(esriDatasetType.esriDTTable)

            pExistingDs = pEnumDataset.Next
            Do Until pExistingDs Is Nothing
                If pExistingDs.Name = inITableName Then
                    If pExistingDs.CanDelete Then
                        pExistingDs.Delete()
                    End If
                End If
                pExistingDs = pEnumDataset.Next
            Loop

            Dim UID_FEATURE As String = "esriGeodatabase.Object"
            '** create a UID for simple features
            Dim pUID As ESRI.ArcGIS.esriSystem.UID
            pUID = New ESRI.ArcGIS.esriSystem.UID
            pUID.Value = UID_FEATURE

            ''** Create new FeatClass in PGDB
            Dim newITable As ITable = pFWS.CreateTable(inITableName, _
                                             pFields, _
                                             pUID, _
                                             Nothing, Nothing)

            
            Dim pCur As ICursor
            Dim pRowBuf As IRowBuffer
            pRowBuf = newITable.CreateRowBuffer
            pCur = newITable.Insert(True)

            Dim dtCol As DataColumn
            Dim dtColname As String
            Dim row As DataRow

            For i = 0 To inDataTable.Rows.Count - 1
                row = inDataTable.Rows(i)
                If Not IsDBNull(row.Item(0)) Then
                    '***add the rest of the attributes
                    For Each dtCol In inDataTable.Columns
                        dtColname = dtCol.ColumnName.ToString
                        Dim dtColType As String = dtCol.DataType.ToString

                        If Not IsDBNull(row.Item(dtColname)) Then
                            pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = row.Item(dtColname)
                        Else
                            If dtCol.DataType.FullName.ToString = "System.Int32" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CInt(0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.String" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CStr("NA")
                            ElseIf dtCol.DataType.FullName.ToString = "System.Double" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDbl(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.Decimal" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDec(0.0)
                            ElseIf dtCol.DataType.FullName.ToString = "System.DateTime" Then
                                pRowBuf.Value(pRowBuf.Fields.FindField(dtColname)) = CDate(Now())
                            End If
                        End If
                    Next
                    pCur.InsertRow(pRowBuf)
                Else
                End If
            Next
            pCur.Flush()

                    Return newITable

        Catch ex As Exception
            MsgBox(ex.ToString)
            Return Nothing
        End Try
    End Function

[HagenProbsthain]

Hello James,

thank you for your answer.
But I believe, you really didn't understand my problem:

I don't want to convert the ADO-Datatable to an ESRI-Table, I thought there is a possiblity to use the Datatable directly thru QueryInterface.
If the user changes the location (xcoord, ycoord) in the DBMS-Table, each time a conversion is required to reflect the changes in the XY-Event-Layer - and this takes a lot of time .
But if there is no other solution, then I have to take the conversion.

[JamesCrandall]

I don't want to convert the ADO-Datatable to an ESRI-Table, I thought there is a possiblity to use the Datatable directly thru QueryInterface.

Sorry I misunderstood -- have a look at the thread title you wrote and I think you'll see why I misundertood 😉

That'd sure would be nice!  Please let me know if you find a way to accomplish this.

I found this in the old forums that shows there used to be a ToDataset utility (ESRI.ArcGIS.Utility.Converter namespace) in 9.1 but was moved to ESRI.ArcGIS.Server.WebControls.Converter so this is not avail to Desktop apps.

http://forums.esri.com/Thread.asp?c=93&f=993&t=256271&mc=7#msgid786105

The way I deal with this now is to just build and re-build a Point FeatureClass with the conversion code (or something close) I provided.  That is, instead of processing the ADO.NET DataTable into an ITable, I just generate a new point FeatureClass from it.  Haven't tried it with an X/Y Event Layer.

This approach has worked pretty darn well --- as long as the filling of the DataTable is quick, which is accomplished by way of utilizing my own StoredProcedures that deal with the SqlDataAdapter.  Once that is complete, the actual ArcObjects to build the point FeatureClass is what generates some overhead.

Good luck!

[HagenProbsthain]

Hello James,

thank you for your answers. Now I have a program, that works:
I make a ADO-Datatable with a SQL-Query.
Then I convert the ADO-Datatable into ITable and from this a made a XY-Event-Layer.
To update the Layer I refill the ADO-Datatable, then I delete all Rows in the ITable and transfer the ADO-Datatable in this ITable again, so the reference between XYEventLayer and ITable is the same.
It requires at this moment, that the structure has not been changed. Also I only tested with 30 points. But I'm confident that the performance is acceptable.

Again, many thanks for all. 🙂

HPr

[JamesCrandall]

Hello James,

thank you for your answers. Now I have a program, that works:
I make a ADO-Datatable with a SQL-Query.
Then I convert the ADO-Datatable into ITable and from this a made a XY-Event-Layer.
To update the Layer I refill the ADO-Datatable, then I delete all Rows in the ITable and transfer the ADO-Datatable in this ITable again, so the reference between XYEventLayer and ITable is the same.
It requires at this moment, that the structure has not been changed. Also I only tested with 30 points. But I'm confident that the performance is acceptable.

Again, many thanks for all. 🙂

Great to hear, Hagen!  Were you able to use the code I provided?

[RameshSivaraman]

Thanks for the code jamesfreddyc... best example I've found.

[JamesCrandall]

Thanks for the code jamesfreddyc... best example I've found.

Thank you very much for the compliment! Glad you could use it.