-
Jul 4th, 2014, 05:53 PM
#1
Thread Starter
Junior Member
Convert a List(of T) to datatable?
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
-
Jul 4th, 2014, 06:11 PM
#2
Re: Convert a List(of T) to datatable?
You could use linq to filter your list(of Fireplaces)
Post your code if you need an example
- Coding Examples:
- Features:
- Online Games:
- Compiled Games:
-
Jul 4th, 2014, 07:57 PM
#3
Thread Starter
Junior Member
Re: Convert a List(of T) to datatable?
Excellent. Thank you.
Here is the calling Sub:
Code:
Private Sub ResetGrid()
Dim fireplaces As List(Of Fireplace) = FireplacesDB.GetFireplacesList
FireplacesBindingSource.DataSource = fireplaces
End Sub
And the GetFirePlacesList code:
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
-
Jul 4th, 2014, 09:53 PM
#4
Re: Convert a List(of T) to datatable?
Instead of converting your list to a DataTable, why not just fill and return a DataTable?
VB.Net Code:
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.
-
Jul 4th, 2014, 10:58 PM
#5
Re: Convert a List(of T) to datatable?
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
-
Jul 4th, 2014, 11:02 PM
#6
Thread Starter
Junior Member
Re: Convert a List(of T) to datatable?
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.
-
Jul 4th, 2014, 11:22 PM
#7
Thread Starter
Junior Member
Re: Convert a List(of T) to datatable?
Originally Posted by 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
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.
-
Jul 5th, 2014, 08:35 AM
#8
Re: Convert a List(of T) to datatable?
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
-
Jul 5th, 2014, 09:37 PM
#9
Re: Convert a List(of T) to datatable?
Originally Posted by techgnome
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.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|