Convert a List(of T) to datatable?

[HardyRexion]

I'm working through Murach's ado.net 4 database programming book and enjoying it. As I go through I'm trying to create a dummy programme for an industry I'm familiar with: fireplaces

The book explains about 3 tier programming instead of databinding.

Following the advice, I have a table in my SQL database named tblFireplaces. In my project I have a class that represents the table called 'Fireplace' (only 3 fields so far to keep it simple) and a class to contain all database manipulation code (Add, Edit, Delete, Update, etc) called FireplaceDB

To retrieve the rows from tblFireplaces I have a Function in FireplacesDB that returns a List(of Fireplaces) and that list is then set as the datatsource for a BindingSource of a DataGridView.

All well and good so far, however using this method causes issues with filtering & sorting in that you can't!

I could create Functions to select rows from the database using SQL but that would require a lot of unnecessary database lookups.

I've searched around and can't see any easy way to convert the list and it certainly can't be filtered as far as I know.

My idea was to get the information from my List and convert it to a DataTable and have that as the source but even that is complicated it seems.

If I want to have sorting it seems I'd have to use datasets instead. Is this the only way?

Hope I've been clear enough. Can provide the code if needed but I'm sure you see what I'm getting at

[.paul.]

You could use linq to filter your list(of Fireplaces)
Post your code if you need an example

[HardyRexion]

Excellent. Thank you.

Here is the calling Sub:

    Private Sub ResetGrid()
        Dim fireplaces As List(Of Fireplace) = FireplacesDB.GetFireplacesList
        FireplacesBindingSource.DataSource = fireplaces
    End Sub

And the GetFirePlacesList code:

Public Shared Function GetFireplacesList() As List(Of Fireplace)

        Dim FireplaceList As New List(Of Fireplace)
        Dim cnn As SqlConnection = GetSQLConnection()
        Dim strSQL As String = "SELECT * FROM tblFireplaces ORDER BY Name"
        Dim cmdFireplaces As New SqlCommand(strSQL, cnn)
        Try

            cnn.Open()
            Dim myReader As SqlDataReader = cmdFireplaces.ExecuteReader
            Dim fireplace As Fireplace

            Do While myReader.Read
                fireplace = New Fireplace
                fireplace.FireplaceID = myReader("FireplaceID")
                fireplace.Name = myReader("Name")
                fireplace.Deleted = myReader("fpDeleted")
                FireplaceList.Add(fireplace)
            Loop
            myReader.Close()

        Catch ex As Exception
            Throw ex
        Finally
            cnn.Dispose()
        End Try


        Return FireplaceList
    End Function

[TnTinMN]

Instead of converting your list to a DataTable, why not just fill and return a DataTable?

Public Shared Function GetFireplacesListV2() As DataTable
     Dim dt As New DataTable
     Using cnn As SqlConnection = GetSQLConnection()
        Dim strSQL As String = "SELECT FireplaceID, Name, fpDeleted FROM tblFireplaces ORDER BY Name"
        Using cmdFireplaces As New SqlCommand(strSQL, cnn)
           Using da As New SqlDataAdapter(cmdFireplaces)
              da.Fill(dt)
           End Using 'da
        End Using 'cmdFireplaces
     End Using 'cnn
     Return dt
 End Function

Note: I just typed this code in. It has not been tested, but it should give you the general idea.

[techgnome]

You may be thinking about this the wrong way. Don't think about filtering the list. You can't. Instead, think about filtering the view or the data source.... the BindingSource has a Filter property that you should be able to use to filter out records (or filter in records I guess it is).

-tg

[HardyRexion]

Thanks for the input, and that's what I ask myself, and what I would have written off the cuff, but working through the book it really emphasises the 3 tier solution using objects but to me this seems to create problems such as mine.

[HardyRexion]

You may be thinking about this the wrong way. Don't think about filtering the list. You can't. Instead, think about filtering the view or the data source.... the BindingSource has a Filter property that you should be able to use to filter out records (or filter in records I guess it is).

-tg

As the datasource is the list, any filtering simply doesn't happen. That was my first port of call as I have a filter working elsewhere but that uses a datatable similar to what TnTinMN has written. Doing it that way and all is good.

[techgnome]

HUh... I'd swear I'd done that before...

At any rate according to this:
http://stackoverflow.com/questions/1...omclassobjects
and this:
http://stackoverflow.com/questions/1...-source-filter

If you use a BindingList(Of T) it should work. Apparently the List(Of T) doesn't implement the IBindingListViw needed by the DataSource/DGV to properly support the filtering.

Never mind, scratch that... I guess I didn't read that well enough... BindingList(Of T) doesn't implement the interface after all. Well shoot.

-tg

[TnTinMN]

Never mind, scratch that... I guess I didn't read that well enough... BindingList(Of T) doesn't implement the interface after all. Well shoot.

Yeah, the standard BindingList(Of T) seems like a class that someone forgot to finish. You can find several examples of a SortableBindingList, but this one is the only one that I have seen that also implements IBindingListView to support filtering.

Implementing multi-column filtering on the IBindingListView

Note: The article says that it that it includes a VB.Net version and it does, but you need to look in the VS2005 folder for it.