Exploring all things software engineering and beyond...

How To: Populate a List of Objects from a DataSet Using LINQ

Many of us use a list of objects between methods, classes, layers, etc. Sometimes the origin of this generic object list is a DataSet that was populated from a SQL stored procedure. To populate the list of objects from the DataSet, one would have to iterate through each row in the designated DataTable within the DataSet and assign values to the object's properties. It's not a bad method at all, and at least we are talking about working with objects in the first place (and not transporting raw ADO.NET objects through all layers), so we are already in a good place. Here is what that typical code described above might look like:


Dim CustomerData As New DataSet
'-->Get the data from the database; omitting code for brevity

'Create a new list of objects from type Customer
Dim CustomerList As New List(Of Customer)
Dim MyCustomer As Customer = Nothing

'Iterate through each of the rows in the DataTable
For Each dr As DataRow In CustomerData.Tables(0).Rows
'Instantiate the Customer intance for the current iteration
MyCustomer = New Customer()

'Add the non null values to this object's properties
If Not IsDBNull(dr("ID")) Then MyCustomer.ID = Integer.Parse(dr("ID"))
If Not IsDBNull(dr("FirstName")) Then MyCustomer.FirstName = dr("FirstName").ToString()
If Not IsDBNull(dr("LastName")) Then MyCustomer.LastName = dr("LastName").ToString()

'Add the inidividual object to the list
CustomerList.Add(MyCustomer)
Next

'Return the list if needed
Return CustomerList
However we can use LINQ to streamline this process a bit. The 'System.Data.DataSet' extensions namespace exposes methods that allow us to work with LINQ to DataSets in .NET. The issue is that a DataSet or better a DataTable in its raw form does not implement the 'IEnumerable(Of T)' or 'IQueryable' interfaces required to work with LINQ. By calling the 'AsEnumerable' method from this namespace on the DataTable in the FROM clause of a query, we can use a DataTable as a LINQ source.

With the DataTable as the source of our LINQ query, we can now select into a list of objects (or an anonymous type for local use only as opposed to a fully qualified class). The example of code below details how to do this on a simple 'Customer' class. Notice that I check to make sure the value returned from the database is not null before assignment to the property using the VB.NET ternary 'If()' operator.


Dim CustomerData As New DataSet
'-->Get the data from the database; omitting code for brevity

'Use LINQ to query the DataTable data into the 'CustomerList' row collection
Dim CustomerCollection = From MyCustomer In CustomerData.Tables(0).AsEnumerable() _
Select New Customer With { _
.ID = If(Not IsDBNull(MyCustomer.Field(Of Integer)("ID")), MyCustomer.Field(Of Integer)("ID"), 0), _
.FirstName = If(Not IsDBNull(MyCustomer.Field(Of String)("FirstName")), MyCustomer.Field(Of String)("FirstName"), String.Empty), _
.LastName = If(Not IsDBNull(MyCustomer.Field(Of String)("LastName")), MyCustomer.Field(Of String)("LastName"), String.Empty)}

'Return the list if needed
Return CustomerCollection.ToList()
That's just a taste of how you can use the power of LINQ with some traditional ADO.NET objects in .NET. It shows how you can begin migrating some older existing .NET code with some of the newer technologies and methods available.

Resolving The: 'AsEnumerable' is not a member of 'System.Data.DataTable' Runtime Error

If you begin writing some LINQ to DataSet queries, and try to find the .AsEnumerable function exposed on a DataTable, you will see the following design time error occur:

'AsEnumerable' is not a member of 'System.Data.DataTable'

This is because this functionality exists in the System.Data.DataSetExtensions namespace. Add a reference to this .dll and the error will resolve, and this functionality will become available.

How To: Use Recursion in .NET To Delete All Files and Directories in .NET

Have you ever needed to have some code delete all of the folders and files below a given directory? Maybe the reason is for an automated cleanup that runs on schedule, or just cleaning up cache files after a given period of time. Whatever the reason, a good way to do this in .NET is to write some methods and call them recursively to delete out all files in the lowest level directory, making its way back up to the top level. And of course deleting the directories (folders) themselves after all files within are deleted.

At this point you may be saying, "Can’t I just write some code to iterate through all of the directories, and delete the directories and files in a single operation?" Well you could try it by just calling the basic overload for .Delete(), but you would quickly come across the following exception being thrown:

"The directory is not empty."

This is because the default overload of the .Delete() method of the directory object, will not let you delete it if there are any files still within that directory. Regardless, you may actually need to observe each file prior to deletion to examine its creation date, file attributes, etc. prior to deletion and this is not possible when the single delete is done on the entire directory and all of its files. Therefore we must traverse downward through all directories, deleting out all of the files within before deleting the containing directory. This can all be done using recursion and some operations from the System.IO namespace in .NET.


You may have also gone down this path, but hit another bump when you received the following exception upon deleting a file:

"Access to the path 'MyFile.doc' is denied."

This occurs because read-only files can also not be deleted; we will take care of this by changing the file attributes to not be 'Read-Only' prior to deletion. This however brings up an important last point: many times file deletion regardless of file attributes requires an elevated permission. Therefore you may need to wrap the following code using impersonation of an elevated account, or just make sure that the directories and files being deleted have the proper permissions allowed at the root level. Either solution will work fine.


I want to emphasize this entry more around how to use recursion in operations such as deleting files, but not focus too heavily on the file delete example. Recursion is a powerful and often overlooked method of writing good streamlined clean code. I find those that were formally educated in Software Engineering or Computer Science are well familiar with concepts like recursion, where as the Barnes and Noble book learned developer may have not used some of these methods before. That's perfectly fine, and hopefully this will help you understand recursion better and be able to use it in the future. In fact, if you are 100% only interested in deleting all files in a directory without needing the ability to observe each file prior to deletion then you can provide a boolean value to the 2nd parameter in the overload of the 'Delete()' method that will delete all files in a directory before deleting the directory for you all in 1 line of code shown below:


System.IO.Directory.Delete("C:\TempTest", True)
So without further ado, let's get to the code. It has quite a bit of commenting injected to help explain the process. You may want to test it out on a directory that has multiple levels and files, before using it on anything live to make sure you have your version working properly.


Public Sub TestDelete()

'Get an object repesenting the directory path below
Dim di As New DirectoryInfo("C:\MyTestDirectory")

'Traverse all of the child directors in the root; get to the lowest child
'and delte all files, working our way back up to the top. All files
'must be deleted in the directory, before the directory itself can be deleted.
For Each diChild As DirectoryInfo In di.GetDirectories()
TraverseDirectory(diChild)
Next

'Finally, clean all of the files directly in the root directory
CleanAllFilesInDirectory(di)

End Sub

'''
''' A method to traverse down through child directories until
''' we have reached the lowest level and then clean (delete) all
''' files before deleting the directory itself.
'''

'''
''' All files must be deleted in a directory prior to deleting the
''' directory itself to prevent the following exception:
''' "The directory is not empty."
'''

Private Sub TraverseDirectory(ByVal di As DirectoryInfo)

'If the current directory has more child directories, then continure
'to traverse down until we are at the lowest level. At that point all of the
'files will be deleted.
For Each diChild As DirectoryInfo In di.GetDirectories()
TraverseDirectory(diChild)
Next

'Now that we have no more child directories to traverse, delete all of the files
'in the current directory, and then delete the directory itself.
CleanAllFilesInDirectory(di)


'The containing directory can only be deleted if the directory
'is now completely empty and all files previously within
'were deleted.
If di.GetFiles().Count = 0 Then
di.Delete()
End If

End Sub

'''
''' Iterates through all files in the directory passed into
''' method and deletes them.
'''

'''
''' It may be necessary to wrap this call in impersonation or ensure parent directory
''' permissions prior, because delete permissions are not guaranteed.
'''

Private Sub CleanAllFilesInDirectory(ByVal DirectoryToClean As DirectoryInfo)

For Each fi As FileInfo In DirectoryToClean.GetFiles()
'The following code is NOT required, but shows how some logic can be wrapped
'around the deletion of files. For example, only delete files with
'a creation date older than 1 hour from the current time. If you
'always want to delete all of the files regardless, just remove
'the next 'If' statement.
If fi.CreationTime < Now.Subtract(New TimeSpan(0, 0, 1)) Then
'Read only files can not be deleted, so mark the attribute as 'IsReadOnly = False'
fi.IsReadOnly = False
fi.Delete()

'On a rare occasion, files being deleted might be slower than program execution, and upon returning
'from this call, attempting to delete the directory will throw an exception stating it is not yet
'empty, even though a fraction of a second later it actually is. Therefore the 'Optional' code below
'can stall the process just long enough to ensure the file is deleted before proceeding. The value
'can be adjusted as needed from testing and running the process repeatedly.
System.Threading.Thread.Sleep(50) '50 millisecond stall (0.05 Seconds)
End If
Next
End Sub


This code should be almost 'copy and paste' ready to run in your application. The main modification needed is to change the root directory referenced in the initial DirectoryInfo object. Also notice the optional code I added to show how you can wrap the file deletion in conditional code stating to only delete files older than 1 hour. This is not required, but shows you how the code can easily be modified.