Text File to DataTable

Features

This code snippet is a function that returns a DataTable by converting a delimited text file. The function also allows for the user to specify if the file’s first row is the column header text.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Data
• System.Linq
• System.Xml

Code

Private Function FileToDataTable(ByVal path As String, ByVal delimiter As Char, ByVal headers As Boolean) As DataTable
    Dim dt As New DataTable
    Dim lines() As String = IO.File.ReadAllLines(path)

    Dim x As Integer = 0
    If headers Then
        dt.Columns.AddRange((From column As String In lines(x).Split(delimiter) Select New DataColumn(column)).ToArray)
        x += 1
    Else
        dt.Columns.AddRange((From column As String In lines(x).Split(delimiter) Select New DataColumn()).ToArray)
    End If

    For index As Integer = x To lines.Length - 1
        dt.Rows.Add(lines(index).Split(delimiter))
    Next

    Return dt
End Function

Fiddle: https://dotnetfiddle.net/K5hRbg

Exceptions

If the first row in the text file has less columns than any of the following rows then a System.ArgumentException will be thrown.

 

If the incorrect delimiter is used then a System.IndexOutOfRangeException will be thrown.

If the file does not exist then a System.IO.FileNotFoundException will be thrown

Calculate Mean(Average)

Features

This code snippet is a function that returns a Double based on the sum of all the items in a collection divided by the amount of items in a collection.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Linq

Code

Friend Function CalculateAverage(ByVal collection() As Object) As Double
    Return collection.Sum(Function(item) Convert.ToDouble(item)) / collection.Length
End Function

Fiddle: https://dotnetfiddle.net/oBejPC

Exceptions

If the collection cannot successfully be converted to a Double array then a System.FormatException

Validate Login with SQL

Features

This code snippet is a function that returns a Boolean value based on if a database’s data table contains a row with a particular username and password.

References

The following namespaces/class will need to be added in order for the code to compile:

• System
• System.Data

Notes

I have included various TODO comments that are there to inform you that you will need to change certain information in the code. This is to personalize the code for your particular need.

Code

Friend Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
    Dim count As Integer = 0
    Using con As SqlClient.SqlConnection = New SqlClient.SqlConnection
        Try
            'TODO: Change "My Connection String Here" with a valid connection string
            con.ConnectionString = "My Connection String Here"

            'TODO: Change ID, username, and password to valid column names
            'TODO: Change MyTable to a valid table name
            Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("SELECT Count([ID]) FROM [MyTable] WHERE [username]=@user AND [password]=@pass", con)
                With cmd.Parameters
                    .AddWithValue("@user", username)
                    .AddWithValue("@pass", password)
                End With

                con.Open()
                count = Convert.ToInt32(cmd.ExecuteScalar())
                con.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    'Return row count is greater than 0
    Return count > 0
End Function

Fiddle: https://dotnetfiddle.net/B0GC5Y

Validate Login with Microsoft Access

Features

This code snippet is a function that returns a Boolean value based on if a database’s data table contains a row with a particular username and password.

References

The following namespaces/class will need to be added in order for the code to compile:

• System
• System.Data

Notes

I have included various TODO comments that are there to inform you that you will need to change certain information in the code. This is to personalize the code for your particular need.

Code

Friend Function ValidateLogin(ByVal username As String, ByVal password As String) As Boolean
    Dim count As Integer = 0
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection
        Try
            'TODO: Change "My Connection String Here" with a valid connection string
            con.ConnectionString = "My Connection String Here"

            'TODO: Change ID, username, and password to valid column names
            'TODO: Change MyTable to a valid table name
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT Count([ID]) FROM [MyTable] WHERE [username]=@user AND [password]=@pass", con)
                With cmd.Parameters
                    .AddWithValue("@user", username)
                    .AddWithValue("@pass", password)
                End With

                con.Open()
                count = Convert.ToInt32(cmd.ExecuteScalar())
                con.Close()
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.Message)
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    'Return row count is greater than 0
    Return count > 0
End Function

Fiddle: https://dotnetfiddle.net/gf6lCm

Convert DataTable to CSV

Features

This code snippet is two functions that converts a DataTable to a CSV file. The One difference between the two functions is that the one with the Boolean parameter named headers allows you to signify that the first row in the CSV file contains header text.

References

The following namespaces/class will need to be added in order for the code to compile:

• System
• System.Data
• System.Data.DataSetExtensions
• System.Linq
• System.Xml

Code

Friend Sub ConvertDataTableToCSV(ByVal data As DataTable, ByVal path As String)
    Dim csv As String = String.Join(Environment.NewLine, (From row As DataRow In data.Rows.Cast(Of DataRow) Select String.Join(",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select row.Item(column.ColumnName)))))
    IO.File.WriteAllText(path, csv)
End Sub

Friend Sub ConvertDataTableToCSV(ByVal data As DataTable, ByVal path As String, ByVal headers As Boolean)
    Dim csv As String = String.Empty
    If headers Then
        csv = String.Join((",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select column.Caption).ToArray) & Environment.NewLine()
    End If

    csv &= String.Join(Environment.NewLine, (From row As DataRow In data.Rows.Cast(Of DataRow) Select String.Join(",", (From column As DataColumn In data.Columns.Cast(Of DataColumn) Select row.Item(column.ColumnName)))))
    IO.File.WriteAllText(path, csv)
End Sub

Fiddle: https://dotnetfiddle.net/TOjUK3

Convert CSV File to DataTable

Features

This code snippet is two functions that returns a DataTable from a CSV file. One difference between the two functions is that the one with the Boolean parameter named header allows you to signify that the first row in the CSV file contains header text.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Data
• System.Xml

Code

Friend Function ConvertCSVToDataTable(ByVal path As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR=YES;FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", "C:\")
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & path, con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

Friend Function ConvertCSVToDataTable(ByVal path As String, ByVal header As Boolean) As DataTable
    Dim dt As DataTable = New DataTable()
    Using con As OleDb.OleDbConnection = New OleDb.OleDbConnection()
        Try
            con.ConnectionString = String.Format("Provider={0};Data Source={1};Extended Properties=""Text;HDR={2};FMT=Delimited""", "Microsoft.Jet.OLEDB.4.0", "C:\", If(header, "YES", "NO"))
            Using cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand("SELECT * FROM " & path, con)
                Using da As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter(cmd)
                    con.Open()
                    da.Fill(dt)
                    con.Close()
                End Using
            End Using
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        Finally
            If con IsNot Nothing AndAlso con.State = ConnectionState.Open Then
                con.Close()
            End If
        End Try
    End Using

    Return dt
End Function

Fiddle: https://dotnetfiddle.net/TOjUK3

Get Files by Accessed Date

Features

This code snippet is two functions that returns an array of IO.FileInfo from a directory based on the day that the file was last accessed. The difference between the two is that the one with the String parameter named filter allows you to specify the search pattern of the files.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Linq

Code

Friend Function GetFilesByAccessedDate(ByVal directory As String, ByVal creation As DateTime) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles() Where file.LastAccessTime.Date = creation.Date).ToArray()
End Function

Friend Function GetFilesByAccessedDate(ByVal directory As String, ByVal creation As DateTime, ByVal filter As String) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles(filter) Where file.LastAccessTime.Date = creation.Date).ToArray()
End Function

Fiddle: https://dotnetfiddle.net/dPS9nb

Get Files by Modified Date

Features

This code snippet is two functions that returns an array of IO.FileInfo from a directory based on the day that the file was last modified. The difference between the two is that the one with the String parameter named filter allows you to specify the search pattern of the files.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Linq

Code

Friend Function GetFilesByModifiedDate(ByVal directory As String, ByVal creation As DateTime) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles() Where file.LastWriteTime.Date = creation.Date).ToArray()
End Function

Friend Function GetFilesByModifiedDate(ByVal directory As String, ByVal creation As DateTime, ByVal filter As String) As IO.FileInfo()
    Return (From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles(filter) Where file.LastWriteTime.Date = creation.Date).ToArray()
End Function

Fiddle: https://dotnetfiddle.net/rnVPGc

Get Files by Creation Date

Features

This code snippet is two functions that returns an array of IO.FileInfo from a directory based on the day that the file was created. The difference between the two is that the one with the String parameter named filter allows you to specify the search pattern of the files.

References

The following namespaces will need to be added in order for the code to compile:

• System
• System.Linq

Code

Friend Function GetFilesByCreationDate(ByVal directory As String, ByVal creation As DateTime) As IO.FileInfo()
    Return(From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles() Where file.CreationTime.Date = creation.Date).ToArray()
End Function

Friend Function GetFilesByCreationDate(ByVal directory As String, ByVal creation As DateTime, ByVal filter As String) As IO.FileInfo()
    Return(From file As IO.FileInfo In New IO.DirectoryInfo(directory).GetFiles(filter) Where file.CreationTime.Date = creation.Date).ToArray()
End Function

Fiddle: https://dotnetfiddle.net/90LaaW