[RESOLVED] Can i use 'DataObjects' in a DataSet?

[Megalith]

Hi all,

Ok heres my question, as some may know i have been working on an xml heavy application and have been extracting the xml via LINQ and populating predefined Dataobjects (defined directly from classes and added to Data Sources as type Object) now using this method i have lost a lot of functionality normally achieved with a database method (ability to sort dgv using the header columns for example) I tried adding a dataset to the project and discovered that i cannot simply drag these objects to the DataSet designer.

Any suggestions would be helpful, right now i am considering going down the route of sql CE 3.5 but this would mean rewriting a large portion of my project and tbh i'm not sure how to add LINQ to sql compact (google comes up mainly with c# examples or how to get data from sql in linq form.

Currently i have 4 Objects in my Data Sources panel and i wish to give some relational abilities to these objects.

[jmcilhinney]

LINQ to SQL works with SQL Server only, not SQL Server CE or any other database. You could presumably generate an Entity Framework data model from a SQL Server CE database and the EF is similar to L2S in many ways.

You might also consider how the DataSet functionality you mention is implemented and try implementing something similar for yourself. The sorting functionality comes from the DataView class and its IBindingList implementation. You could define your own collection class to store your DTOs and implement IBindingList yourself. As for relationships, that comes from the fact that the DataSet is a container for DataTables and DataRelations. You could define your own container class that provides the relationship "glue" in a similar way.

[Megalith]

Hi jmc thanks for the reply. Shame about the sql ce thing, would you know if this has changed with 2010 or if it is planned for Service packs? I did however spot something on google that had a workaround (http://pietschsoft.com/post/2009/01/...t-Edition.aspx), i will rule that route out though anyway due to the something like 80% code rewrite it would entail. I would prefer it ultimately too, if i didn't have to rely on any external architecture beyond the xml.

I'm always up for a challenge you have given me a lot to think about. Primarily that seeing as i have created these DTO's i may as well continue in the process and follow it through to its conclusion.

I could serialize the resulting tables with this way a lot easier than with a database if i needed persistent data, and that would open up the possibility of time graphs as 2 of my DTO's change over time, the other 2 are more settings and items.

Thanks for the pointers I'll let you know how i get on.

[jmcilhinney]

Shame about the sql ce thing, would you know if this has changed with 2010 or if it is planned for Service packs?

Not likely. About the only thing SQL Server and SQL Server CE share is the name. The underlying architecture is very different so they'd basically have to create L2S all over again.

[Megalith]

Ok dived into this this morning (just about recovered from a birthday party), I got out a recent test project i had that took a samle of xml inline and parses it with linq, what i was then doing was something like

DataGridView1.DataSource = query.ToList

My research came up with 2 methods:-
1/ use a dataTable and then CopyToDataTable

Dim Table as DataTable
Table = query.CopyToDataTable()

2/ use a dataview (as you suggested)

Dim View as DataView
View = query.AsDataView()

neither of the above worked. I checked i had 3.5 framework in advanced compile options of the projects properties, this was correct. I added an import for System.Linq and System.Data. but adding System.Data.DataViewExtensions didn't work despite it existing in the project properties references list. Not sure what i'm missing with this. my query is very simple and should work.

        Dim query = From i In Example _
                    Where i.Word <> "" _
                    Order By i.Word _
                    Select i

mousing over query says it is of type IEnumerable(Of Test). Example is a List(Of Test) which is derived via a linq query on the original inline xml.

[jmcilhinney]

There is no such thing as System.Data.DataViewExtensions. There's a System.Data.DataSetExtensions.dll assembly, which contains all the LINQ to DataSet extension methods. It contains types from the System.Data.DataSetExtensions namespace, none of which are of interest to you. It also contains members of the System.Data namespace, the most interesting being the DataRowExtensions and DataTableExtensions classes, which contain extension methods you can call on DataRows and DataTables respectively.

The AsDataView method shouldn't have worked because that is an extension method of the DataTable class, so it can only be called on a DataTable. The CopyToDataTable method, on the other hand, is an extension method of both the DataTable class and the IEnumerable(Of T) interface. That said, the documentation clearly states:

Returns a DataTable that contains copies of the DataRow objects, given an input IEnumerable(Of T) object where the generic parameter T is DataRow. (Defined by DataTableExtensions.)

The answer is exactly where it's supposed to be. I've never used the CopyToDataTable method before yet I can find this information in about 30 seconds. It frustrates me terribly that so many people simply refuse to click the Help menu.

[Megalith]

So the reason it doesn't work is because my query returns a type which isn't T? kinda suspected that. Will have another look and change my query.

[jmcilhinney]

So the reason it doesn't work is because my query returns a type which isn't T? kinda suspected that. Will have another look and change my query.

T isn't a type. It's a place-holder for a type. The reason it doesn't work is because your query returns a type that isn't DataRow.

[Megalith]

ok guess i need to readup more on this subject. I guess also i can forget the query as i have my data as a list(Of Test) so i can just use that and presumably use it's ForEach property to create datarows and add these to the dataTable or dataview? is there a method i can use that will place the entire list in in one go (like addRange?)

[jmcilhinney]

As far as I'm aware there's no way to automatically generate a DataTable from a List. You'd have to create the DataTable, create each column, then add each row.

[Megalith]

ok with my test program i have now created 2 tables within a dataset but i'm at a loss how to link my foreign key from table 2 to a primary key in table 1. What i have done works but is it the best way to do what i'm after? i will post my code

Imports ListOfList.Class1
Imports System.Linq
Imports System.Data

Public Class Form1
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim element = <xml>
                          <object>
                              <word>someword</word>
                              <number>7</number>
                              <another_word>hey</another_word>
                              <name>
                                  <age>34</age>
                                  <first_name>Theodore</first_name>
                                  <last_name>Brown</last_name>
                              </name>
                              <name>
                                  <age>78</age>
                                  <first_name>Bobby</first_name>
                                  <last_name>Dazzler</last_name>
                              </name>
                          </object>
                          <object>
                              <word>nothing&#37;20else</word>
                              <name>
                                  <age>22</age>
                                  <first_name>Reginald</first_name>
                                  <last_name>Perry</last_name>
                              </name>
                              <name>
                                  <age>78</age>
                                  <first_name>Bobby</first_name>
                                  <last_name>Dazzler</last_name>
                              </name>
                          </object>
                          <object>
                              <word>How</word>
                              <another_word>Secret</another_word>
                              <name>
                                  <age>78</age>
                                  <first_name>Bobby</first_name>
                                  <last_name>Dazzler</last_name>
                              </name>
                           </object>
                      </xml>

        Dim Example = From item In element...<object> _
                      Select Word = item.<word>.Value, _
                             Number = CInt(item.<number>.Value), _
                             AnotherWord = item.<another_word>.Value, _
                             Names = From name In item...<name> _
                             Select Age = name.<age>.First.Value, _
                             FirstName = name.<first_name>.First.Value, _
                             LastName = name.<last_name>.First.Value

        Dim table As New DataTable
        Dim tblName As New DataTable
        table = MakeTestTable()
        tblName = MakeNameTable()
        ds.Tables.Add(table)
        ds.Tables.Add(tblName)

        For Each item In Example
            Dim row As DataRow
            Dim row2 As DataRow
            row = table.NewRow()
            row("Word") = item.Word
            row("Number") = item.Number
            row("AnotherWord") = item.AnotherWord
            row("Names") = ""
            table.Rows.Add(row)
            For Each n In item.Names
                row2 = tblName.NewRow()
                row2("Word") = item.Word
                row2("Age") = n.Age
                row2("FirstName") = n.FirstName
                row2("LastName") = n.LastName
                tblName.Rows.Add(row2)
            Next
        Next
        'table = query
        DataGridView1.DataSource = ds.Tables("Test").Copy()
        DataGridView2.DataSource = ds.Tables("Names").Copy()
    End Sub

    Private Function MakeTestTable() As DataTable
        ' Create a new DataTable titled 'Names.'
        Dim testTable As DataTable = New DataTable("Test")

        ' Add three column objects to the table.
        Dim idColumn As DataColumn = New DataColumn()
        idColumn.DataType = System.Type.GetType("System.Int32")
        idColumn.ColumnName = "id"
        idColumn.AutoIncrement = True
        testTable.Columns.Add(idColumn)

        Dim wordColumn As DataColumn = New DataColumn()
        wordColumn.DataType = System.Type.GetType("System.String")
        wordColumn.ColumnName = "Word"
        wordColumn.DefaultValue = "Word"
        testTable.Columns.Add(wordColumn)

        Dim numberColumn As DataColumn = New DataColumn()
        numberColumn.DataType = Type.GetType("System.Int32")
        numberColumn.ColumnName = "Number"
        numberColumn.DefaultValue = 0
        testTable.Columns.Add(numberColumn)

        Dim anotherWordColumn As DataColumn = New DataColumn()
        anotherWordColumn.DataType = System.Type.GetType("System.String")
        anotherWordColumn.ColumnName = "AnotherWord"
        testTable.Columns.Add(anotherWordColumn)

        Dim namesColumn As DataColumn = New DataColumn()
        namesColumn.DataType = System.Type.GetType("System.String")
        namesColumn.ColumnName = "Names"
        testTable.Columns.Add(namesColumn)

        ' Create an array for DataColumn objects.
        Dim keys(0) As DataColumn
        keys(0) = idColumn
        testTable.PrimaryKey = keys

        ' Return the new DataTable.
        MakeTestTable = testTable
    End Function

    Private Function MakeNameTable() As DataTable
        ' Create a new DataTable titled 'Names.'
        Dim nameTable As DataTable = New DataTable("Names")

        ' Add three column objects to the table.
        Dim idColumn As DataColumn = New DataColumn()
        idColumn.DataType = System.Type.GetType("System.Int32")
        idColumn.ColumnName = "id"
        idColumn.AutoIncrement = True
        nameTable.Columns.Add(idColumn)

        Dim wordColumn As DataColumn = New DataColumn()
        wordColumn.DataType = System.Type.GetType("System.String")
        wordColumn.ColumnName = "Word"
        nameTable.Columns.Add(wordColumn)

        Dim ageColumn As DataColumn = New DataColumn()
        ageColumn.DataType = System.Type.GetType("System.Int32")
        ageColumn.ColumnName = "Age"
        ageColumn.DefaultValue = 0
        nameTable.Columns.Add(ageColumn)

        Dim fNameColumn As DataColumn = New DataColumn()
        fNameColumn.DataType = Type.GetType("System.String")
        fNameColumn.ColumnName = "FirstName"
        fNameColumn.DefaultValue = ""
        nameTable.Columns.Add(fNameColumn)

        Dim lNameColumn As DataColumn = New DataColumn()
        lNameColumn.DataType = System.Type.GetType("System.String")
        lNameColumn.ColumnName = "LastName"
        nameTable.Columns.Add(lNameColumn)

        ' Create an array for DataColumn objects.
        Dim keys(0) As DataColumn
        keys(0) = idColumn
        nameTable.PrimaryKey = keys

        ' Return the new DataTable.
        MakeNameTable = nameTable
    End Function

End Class

Module globals
    Friend ds As DataSet = New DataSet
End Module

[Megalith]

this is the class

Imports System
Imports System.Collections.Generic
Public Class Class1
    Public Class Test
        Private _word As String
        Public Property Word() As String
            Get
                Return _word
            End Get
            Set(ByVal value As String)
                _word = value
            End Set
        End Property

        Private _number As Integer
        Public Property Number() As Integer
            Get
                Return _number
            End Get
            Set(ByVal value As Integer)
                _number = value
            End Set
        End Property

        Private _anotherWord As String
        Public Property AnotherWord() As String
            Get
                Return _anotherWord
            End Get
            Set(ByVal value As String)
                _anotherWord = value
            End Set
        End Property

        Private _names As New List(Of TestList)
        Public Property Names() As List(Of TestList)
            Get
                Return _names
            End Get
            Set(ByVal value As List(Of TestList))
                _names = value
            End Set
        End Property
    End Class

    Public Class TestList
        Private _word As String
        Public Property Word() As String
            Get
                Return _word
            End Get
            Set(ByVal value As String)
                _word = value
            End Set
        End Property

        Private _age As Integer
        Public Property Age() As Integer
            Get
                Return _age
            End Get
            Set(ByVal value As Integer)
                _age = value
            End Set
        End Property

         Private _firstName As String
        Public Property FirstName() As String
            Get
                Return _firstName
            End Get
            Set(ByVal value As String)
                _firstName = value
            End Set
        End Property

        Private _lastName As String
        Public Property LastName() As String
            Get
                Return _lastName
            End Get
            Set(ByVal value As String)
                _lastName = value
            End Set
        End Property
    End Class
End Class

[Megalith]

Ok got some info but it still doesn't do what i expected it to do. I have added a foreign key constraint after i have loaded the 2 tables onto the dataset

        Dim table As New DataTable
        Dim tblName As New DataTable
        table = MakeTestTable()
        tblName = MakeNameTable()
        ds.Tables.Add(table)
        ds.Tables.Add(tblName)
        Dim fk As ForeignKeyConstraint
        fk = New ForeignKeyConstraint("fk", ds.Tables("Test").Columns("Word"), ds.Tables("Names").Columns("Word"))
        ds.Tables("Names").Constraints.Add(fk)

i expected that when i click a row in dgv2 that the line in dgv1 would reflect the foreign key

[jmcilhinney]

If you want relations to be honoured across controls then you have to bind your data accordingly. Follow the CodeBank link in my signature and check out my thread on master/detail data-binding.

[Megalith]

Ah great, ty very much, working exactly right now. The example in the codebank has allowed me to improve a large portion of my testcode.

The final issue i have i guess is keeping this data persistent so i can update only when the data has changed at source (or no more than 1 a day) I was thinking of serializing the dataset presumably to do this i open a file of byte and write the entire dataset to a file in the working directory? would you recommend this approach?

[jmcilhinney]

Ah great, ty very much, working exactly right now. The example in the codebank has allowed me to improve a large portion of my testcode.

The final issue i have i guess is keeping this data persistent so i can update only when the data has changed at source (or no more than 1 a day) I was thinking of serializing the dataset presumably to do this i open a file of byte and write the entire dataset to a file in the working directory? would you recommend this approach?

The DataSet has ReadXml and WriteXml methods for self-serialisation.

[Megalith]

The DataSet has ReadXml and WriteXml methods for self-serialisation.

Excellent, thanks again, i'll implement this on my main project. Thread resolved.