Select Distinct on DataSet with Vb.Net

Functions below allow to do a "Select Distinct" query over a DataSet.

FieldNames is a string array with the name of field for the select distinct input.

Public Function SelectDistinct(ByVal SourceTable As DataTable, _
    ByVal ParamArray FieldNames() As String) As DataTable

    Dim lastValues() As Object
    Dim newTable As DataTable

    If FieldNames Is Nothing OrElse FieldNames.Length = 0 Then
        Throw New ArgumentNullException("FieldNames")
    End If

    lastValues = New Object(FieldNames.Length - 1) {}
    newTable = New DataTable

    For Each field As String In FieldNames
        newTable.Columns.Add(field, SourceTable.Columns(field).DataType)
    Next

    For Each Row As DataRow In SourceTable.Select("", String.Join(", ", FieldNames))
        If Not fieldValuesAreEqual(lastValues, Row, FieldNames) Then
            newTable.Rows.Add(createRowClone(Row, newTable.NewRow(), FieldNames))
            setLastValues(lastValues, Row, FieldNames)
        End If
    Next

    Return newTable
End Function



Private Function fieldValuesAreEqual(ByVal lastValues() As Object, _
    ByVal currentRow As DataRow, ByVal fieldNames() As String) As Boolean

    Dim areEqual As Boolean = True

    For i As Integer = 0 To fieldNames.Length - 1
        If lastValues(i) Is Nothing OrElse Not lastValues(i).Equals(currentRow(fieldNames(i))) Then
            areEqual = False
            Exit For
        End If
    Next

    Return areEqual
End Function



Private Function createRowClone(ByVal sourceRow As DataRow, _
    ByVal newRow As DataRow, ByVal fieldNames() As String) As DataRow

    For Each field As String In fieldNames
        newRow(field) = sourceRow(field)
    Next

    Return newRow
End Function



Private Shared Sub setLastValues(ByVal lastValues() As Object, _
    ByVal sourceRow As DataRow, ByVal fieldNames() As String)

    For i As Integer = 0 To fieldNames.Length - 1
        lastValues(i) = sourceRow(fieldNames(i))
    Next
End Sub

Unzipping a zip file with subfolder with SharpZipLib and VB.net

Unzipping routine using ICSharpCode (IC#Code) released open source dll, called SharpZipLib (#ZipLib), downloadable here


Imports ICSharpCode.SharpZipLib.Zip
......
If File.Exists(fileName) Then

    destPath = fileName.Substring(0, fileName.Length - 4)

    If Not Directory.Exists(destPath) Then
        Directory.CreateDirectory(destPath)
    Else
        For Each s As String In Directory.GetFiles(destPath)
            File.Delete(s)
        Next
    End If

    Dim inStream As New ZipInputStream(File.OpenRead(fileName))
    Dim outStream As FileStream
    Dim entry As ZipEntry
    Dim buff(2047) As Byte
    Dim bytes As Integer

    Do While True
        Try
            entry = inStream.GetNextEntry()
            If entry Is Nothing Then
                Exit Do
            End If

            If entry.Name.Last() = "/" Then
                Directory.CreateDirectory(destPath & "\" & _
                entry.Name.Replace("/", "\"))
            Else
                Try
                    outStream = File.Create(destPath & _
                    "\" & entry.Name, 2048)
                    Do While True
                        bytes = inStream.Read(buff, 0, 2048)
                        If bytes = 0 Then
                            Exit Do
                        End If
                        outStream.Write(buff, 0, bytes)
                    Loop
                    outStream.Close()
                Catch
                End Try
            End If
        Catch ex As ZipException
            rtn += ex.Message & vbCrLf
        End Try
    Loop
    inStream.Close()
Else
    rtn = "File '" & fileName & "' not found."
End If

Recursive copy of Files and Folders with VB.Net

It seems so banal, but sometimes the copy of files and folders conteined into a folder can undermine a developer, maybe for its banality.
For this reason, here you have an example of recoursive copy of files and folders.

Private Sub Copy(ByVal SourcePath As String, _
            ByVal DestinationPath As String)
    Dim Files() As String

    If DestinationPath.Chars(DestinationPath.Length - 1) <>
            Path.DirectorySeparatorChar Then
        DestinationPath += Path.DirectorySeparatorChar
    End If

    If Not Directory.Exists(DestinationPath) Then
        Directory.CreateDirectory(DestinationPath)
    End If

    Files = Directory.GetFileSystemEntries(SourcePath)

    Dim Element As String

    For Each Element In Files
        'Sub folders
        If Directory.Exists(Element) Then
            Copy(Element, DestinationPath +
                Path.GetFileName(Element))
            ' File in folder
        Else
            File.Copy(Element, DestinationPath +
                Path.GetFileName(Element), True)
        End If
    Next Element
End Sub

Assign a variable from a Select result with SQL Server

Sometimes will be useful saving a Select query result in a variable, perhaps to reuse it inside another query.
The command to do that is very simple:

DECLARE @variable AS datatype

SELECT @variable = Filed FROM Table WHERE Condition

We only have to comply with certain conditions:

• datatype must be of the same datatype of Field
• if the query return more than 1 row, the variable contains the last row value
• is not possible to use TOP clause
  

Update from Select with SQLServer

Suppose to be in this situation: we have to update some database records starting from query result. There are 2 solutions: we can manual update datas (it will be a problem with thousands of records) or we can use a T-Sql script to do exatcly what we want. Then only condition is that the starting query must have a field on wich we can make a join on.
The script code is below:

UPDATE T
    SET T.Field1a = Q.Field2a,
    T.Fieldb = Q.Field2b
FROM
    (SELECT FieldID, Field2a, Field2b
    FROM Table WHERE Condition) Q
INNER JOIN TableToUpdate T
    ON T.FieldID = Q.FieldID


Where T is the "Table-To-Update" alias and Q is the Select query alias.
The Join clause do the update exactly like we do an "Update ... Where".