IO.System.FileInfo

20 Mar

[VB.NET] Public Class Fichier

By saadry

Public Class Fichier


This class is very similar to the IO.System.FileInfo Class but will contain added features useful for database connections and/or database provider.

Related Content:

  • Microsoft.Jet.OLEDB.4.0
  • Microsoft.ACE.OLEDB.12.0
  • SQL
  • Oracle DBF Files

Aim:

To create a Fichier Class which can be used to store functions and properties. This Fichier Class is similar to the FileInfo Class but will include various sets of functions and properties for database query.

This class retrieves the file attributes and functions to manipulate or transform the file will be included.

Main Functions

– Open / Modify / Create / Copy / Cut and Manipulate a File

– Compress / Decompress / Encrypt / Decrypt the selected File

– Detect Attributes or Set new attributes.

This class records pre-set configuration needed in order to read :

  • SQL (*.mdf) Files
  • Access (*.accdb and *.mdb)
  • Excel (*.xls and *.xlsx)
  • Saadry (*.saadry)
  • Oracle (*.dbf)

 

Properties

Index

 

Physical Information


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
[cc lang="vbnet"]
#Region "<0.1> [Physical Information]"
#Region "<Physical Information> [Target]"
    'Target
    Private _Target As String
    ''' <summary>
    ''' Define the entire filepath of the selected file
    ''' </summary>
    ''' <returns></returns>
    Public Property Target() As String
        Get
            Return _Target
        End Get
        Set(ByVal value As String)
            _Target = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [Exist]"
    'File Exist
    Private _Exist As Boolean
    ''' <summary>
    ''' Examine if the File exist
    ''' </summary>
    ''' <returns></returns>
    Public Property Exist() As Boolean
        Get
            Return _Exist
        End Get
        Set(ByVal value As Boolean)
            _Exist = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [FileName]"
    'File Name (Name + Extension)
    Private _FileName As String
    ''' <summary>
    ''' Return the File Name without Extension
    ''' </summary>
    ''' <returns></returns>
    Public Property FileName() As String
        Get
            Return _FileName
        End Get
        Set(ByVal value As String)
            _FileName = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [Name]"
    'Name
    Private _Name As String
    ''' <summary>
    ''' Return the File Name without Extension
    ''' </summary>
    ''' <returns></returns>
    Public Property Name() As String
        Get
            Return _Name
        End Get
        Set(ByVal value As String)
            _Name = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [Extension]"
    'File Extension (Extension with dot)
    Private _Extension As String
    ''' <summary>
    ''' Set or Return the Extension of the file
    ''' </summary>
    ''' <returns></returns>
    Public Property Extension() As String
        Get
            Return _Extension
        End Get
        Set(ByVal value As String)
            _Extension = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [FullName]"
    'File FullName (File Path + File Name + File Extension)
    Private _FullName As String
    ''' <summary>
    ''' Returns the Full Path of the File also used when converting the file
    ''' </summary>
    ''' <returns></returns>
    Public Property FullName() As String
        Get
            Return _FullName
        End Get
        Set(ByVal value As String)
            _FullName = value
        End Set
    End Property
#End Region
#Region "<Physical Information> [FileType]"
    'File FileType (File Path + File Name + File Extension)
    Private _FileType As String
    ''' <summary>
    ''' Returns the Full Path of the File also used when converting the file
    ''' </summary>
    ''' <returns></returns>
    Public Property FileType() As String
        Get
            Return _FileType
        End Get
        Set(ByVal value As String)
            _FileType = value
        End Set
    End Property
#End Region
#End Region

Date Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#Region "<0.2> [Date Information]"
#Region "<Date Information> [Modify]"
    'File Date Modify
    Private _Modify As DateTime
    ''' <summary>
    ''' Return the last Modified Date of the file
    ''' </summary>
    ''' <returns></returns>
    Public Property Modify() As DateTime
        Get
            Return _Modify
        End Get
        Set(ByVal value As DateTime)
            _Modify = value
        End Set
    End Property
#End Region
#Region "<Date Information> [Created]"
    'File Date Created
    Private _Created As DateTime
    ''' <summary>
    ''' Return the date of Creation of the file
    ''' </summary>
    ''' <returns></returns>
    Public Property Created() As DateTime
        Get
            Return _Created
        End Get
        Set(ByVal value As DateTime)
            _Created = value
        End Set
    End Property
#End Region
#Region "<Date Information> [Accessed]"
    'File Date Accessed
    Private _Accessed As DateTime
    ''' <summary>
    ''' Return the last accessed date of the file
    ''' </summary>
    ''' <returns></returns>
    Public Property Accessed() As DateTime
        Get
            Return _Accessed
        End Get
        Set(ByVal value As DateTime)
            _Accessed = value
        End Set
    End Property
#End Region
#End Region

Size Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
#Region "<0.3> [Size Information]"
#Region "<Size Information> [Size]"
    'File Size
    Private _Size As SizeInfo
    ''' <summary>
    ''' Return the converted Size
    ''' -Display : Returns the visual content for the reader (size + Scale)
    ''' -Valeur  : Returns the formated size
    ''' -Text    : Returns the scale (o, byt, kyt, Gyt, Tyt)
    ''' -Raw     : Returns the raw size
    ''' </summary>
    ''' <returns></returns>
    Public Property Size() As SizeInfo
        Get
            Return _Size
        End Get
        Set(ByVal value As SizeInfo)
            _Size = value
        End Set
    End Property
#End Region
#End Region

Location Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
#Region "<0.4> [Location Information]"
#Region "<Location Information> [Path]"
    'File Directory
    Private _Path As DirectoryInfo
    ''' <summary>
    ''' Returns the Directory location of the File
    ''' </summary>
    ''' <returns></returns>
    Public Property Path() As DirectoryInfo
        Get
            Return _Path
        End Get
        Set(ByVal value As DirectoryInfo)
            _Path = value
        End Set
    End Property
#End Region
#Region "<Location Information> [Parent]"
    'File Parent
    Private _Parent As DirectoryInfo
    ''' <summary>
    ''' Returns the Parent directory file location
    ''' </summary>
    ''' <returns></returns>
    Public Property Parent() As DirectoryInfo
        Get
            Return _Parent
        End Get
        Set(ByVal value As DirectoryInfo)
            _Parent = value
        End Set
    End Property
#End Region
#End Region

Data Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
#Region "<0.5> [Data Information]"
#Region "<Data Information> [Password]"
    Private _Password As String
    ''' <summary>
    ''' Set the File Password
    ''' </summary>
    ''' <returns></returns>
    Public Property Password() As String
        Get
            Return _Password
        End Get
        Set(ByVal value As String)
            _Password = value
        End Set
    End Property
#End Region  
#Region "<Data Information> [Line]"
    Private _Line As String
    ''' <summary>
    ''' Set or Get the Line of the File
    ''' </summary>
    ''' <returns></returns>
    Public Property Line() As String
        Get
            Return _Line
        End Get
        Set(ByVal value As String)
            _Line = value
        End Set
    End Property
#End Region                              
#End Region

FileType Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
#Region "<0.6> [FileType Information]"
#Region "<FileType Information> [Type]"
    Private _Type As E_FileType
    Public Property Type() As E_FileType
        Get
            Return _Type
        End Get
        Set(ByVal value As E_FileType)
            _Type = value
        End Set
    End Property
#End Region
#Region "<FileType Information> [DatabaseType]"
    Private _DatabaseType As E_DatabaseType
    ''' <summary>
    ''' Get or Set the DatabaseType of the File
    ''' </summary>
    ''' <returns></returns>
    Public Property DatabaseType() As E_DatabaseType
        Get
            Return _DatabaseType
        End Get
        Set(ByVal value As E_DatabaseType)
            _DatabaseType = value
        End Set
    End Property
#End Region                      
#End Region

Attributes Information

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
#Region "<0.7> [Attributes Information]"
#Region "<Attributes Information> [Is_Compressed]"
    Private _Is_Compressed As Boolean
    ''' <summary>
    ''' Determine if the file is compressed or not
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Compressed() As Boolean
        Get
            Return _Is_Compressed
        End Get
        Set(ByVal value As Boolean)
            _Is_Compressed = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Convertable]"
    Private _Is_Convertable As Boolean
    ''' <summary>
    ''' Determine if the file is convertable or not
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Convertable() As Boolean
        Get
            Return _Is_Convertable
        End Get
        Set(ByVal value As Boolean)
            _Is_Convertable = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Corrupted]"
    Private _Is_Corrupted As Boolean
    ''' <summary>
    ''' Determine if the file is corrupted
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Corrupted() As Boolean
        Get
            Return _Is_Corrupted
        End Get
        Set(ByVal value As Boolean)
            _Is_Corrupted = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Database]"
    Private _Is_Database As Boolean
    ''' <summary>
    ''' Determine if the file is database compatible
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Database() As Boolean
        Get
            Return _Is_Database
        End Get
        Set(ByVal value As Boolean)
            _Is_Database = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Encrypted]"
    Private _Is_Encrypted As Boolean
    ''' <summary>
    ''' Determine if the file is Encrypted
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Encrypted() As Boolean
        Get
            Return _Is_Encrypted
        End Get
        Set(ByVal value As Boolean)
            _Is_Encrypted = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Exportable]"
    Private _Is_Exportable As Boolean
    ''' <summary>
    ''' Determine if the file can be exported
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Exportable() As Boolean
        Get
            Return _Is_Exportable
        End Get
        Set(ByVal value As Boolean)
            _Is_Exportable = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Hidden]"
    Private _Is_Hidden As Boolean
    ''' <summary>
    ''' Determine if the file is Hidden
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Hidden() As Boolean
        Get
            Return _Is_Hidden
        End Get
        Set(ByVal value As Boolean)
            _Is_Hidden = value
        End Set
    End Property
#End Region
#Region "<Attributes Information> [Is_Readable]"
    Private _Is_Readable As Boolean
    ''' <summary>
    ''' Determine if the file is readable
    ''' </summary>
    ''' <returns></returns>
    Public Property Is_Readable() As Boolean
        Get
            Return _Is_Readable
        End Get
        Set(ByVal value As Boolean)
            _Is_Readable = value
        End Set
    End Property
#End Region
#End Region

Structures and Enumerations

Index


Structures

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
#Region "<1> [Structure]"
#Region "<Structure> [SizeInfo]"
    Public Structure SizeInfo
        Public Display As String
        Public Valeur As Double
        Public Text As String
        Public Scale As Size_Scale
        Public Raw As Double
    End Structure
#End Region                          
#Region "<Structure> [LineInfo]"
    Public Structure LineInfo
        Public Provider As String
        Public Data_Source As String
        Public Security As String
        Public Extension As String
        Public Line As String
    End Structure
#End Region                        
#Region "<Structure> [S_Tables]"
    Public Structure S_Tables
        Public Name As String
        Public List As ListBox
        Public LV As ListViewEx
        Public Data As DataTable
        Public DataSet As DataSet
        Public CList As List(Of TableInfo)
    End Structure
#End Region                      
#End Region    
#End Region

Enumerations

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
#Region "<2> [Enumeration]"
#Region "<Enumeration> [Size_Scale]"
    Public Enum Size_Scale
        Bytes
        Kilobyte
        Megabyte
        Gigabyte
        Terabyte
    End Enum
#End Region                        
#Region "<Enumeration> [E_FileType]"
    Public Enum E_FileType
        NotTested
        NotRecognized
        Compression
        Database
        Image
        Pcompta
        PDF
        Saadry
        Text
        Web
        Executable
        Folder
        Programmation
        Movies
        Music
        MS_Word
        MS_Publisher
        MS_Powerpoint
        MS_Outlook
        MS_OneNote
    End Enum
#End Region                        
#Region "<Enumeration> [E_DatabaseType]"
    Public Enum E_DatabaseType
        NotTested
        NotRecognized
        Accdb
        Mdb
        Dbf
        Excel
        Saadry
        SQL
    End Enum
#End Region                    
#End Region                        
#End Region

Public Subs

Index


– Public Sub New()
– Public Sub New(FilePath)
– Public Sub New(FilePath,Password)

[Public Subs]
1. Reset
2. Analyse
3. GetAttributes
4. Display

New

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
#Region "<B> [New]"
    ''' <summary>
    ''' Create an Empty Schemas of Fichier Class
    ''' </summary>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Sub New()
        Reset()
    End Sub
    Public Sub New(ByVal FilePath As String)
        Analyse(FilePath)
        Try
            GetLineInfo()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
    Public Sub New(ByVal FilePath As String,
                   ByVal Pass As String)
        Analyse(FilePath, Pass)
        Try
            GetLineInfo()

        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub
#End Region

Public Subs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
#Region "<C> [Public Subs]"
#Region "<Public Subs> [Reset]"
    ''' <summary>
    ''' Reset all variables
    ''' </summary>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Sub Reset()
        Me.Accessed = Nothing
        Me.Created = Nothing
        Me.Exist = False
        Me.Extension = Nothing
        Me.FileName = "Nom du fichier et Extension"
        Me.FullName = Nothing
        Me.Modify = Nothing
        Me.Name = "Nom du fichier"
        Me.Parent = Nothing
        Me.Password = Nothing
        Me.Path = Nothing
        Me.Size = Nothing
        Me.Target = "Lien du fichier"
    End Sub

#End Region                      
#Region "<Public Subs> [Analyse]"
    ''' <summary>
    ''' Analyse the file and collect informations
    ''' </summary>
    ''' <param name="FilePath"></param>
    ''' <param name="Pass"></param>
    ''' <authors>Saadry Dunkel Consultant </authors>
    Public Sub Analyse(ByVal FilePath As String,
                       Optional ByVal Pass As String = Nothing)
        Reset()
        '<Exception 1> : FilePath is Empty
        If FilePath = String.Empty Then
            MsgBox("<Exception 1> Warning the FilePath is empty no data will be processed!")
            Exit Sub
        End If
        '<Exception 2> : FilePath is not a File
        Dim Fi As New FileInfo(FilePath)
        If Not Fi.Exists Then
            MsgBox("<Exception 2> Warning the FilePath is not a File")
        End If
        'Storing Values
        Me.Accessed = Fi.LastAccessTimeUtc
        Me.Created = Fi.CreationTimeUtc
        Me.Exist = True
        Me.Extension = Fi.Extension
        Me.FileName = Fi.Name
        Me.FullName = FilePath
        Me.Modify = Fi.LastWriteTimeUtc
        Me.Name = IO.Path.GetFileNameWithoutExtension(Fi.FullName)
        Me.Path = Fi.Directory
        Me.Parent = IO.Directory.GetParent(FilePath)
        Me.Size = GetFileSize(FilePath)
        Me.Target = FilePath
        Me.Password = Pass
        Me.FileType = GetFileType(Me.Extension)
        GetAttributes()
    End Sub

#End Region
#Region "<Public Subs> [GetAttributes]"
    ''' <summary>
    ''' This evaluate the attributes of the selected File
    ''' </summary>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Sub GetAttributes()
        'MsgBox(Me.Target)
        Dim iAttribute As IO.FileAttributes = IO.File.GetAttributes(Me.Target)
        'Compressed
        If (iAttribute And System.IO.FileAttributes.Compressed) = FileAttributes.Compressed Then
            Me.Is_Compressed = True
        Else
            Me.Is_Compressed = False
        End If
        'Encrypted
        If (iAttribute And System.IO.FileAttributes.Encrypted) = FileAttributes.Encrypted Then
            Me.Is_Encrypted = True
        Else
            Me.Is_Encrypted = False
        End If
        'Hidden
        If (iAttribute And System.IO.FileAttributes.Hidden) = FileAttributes.Hidden Then
            Me.Is_Hidden = True
        Else
            Me.Is_Hidden = False
        End If
        'Normal
    End Sub
#End Region
#Region "<Public Subs> [Display]"
    ''' <summary>
    ''' Display File information in a Form
    ''' </summary>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Sub Display()
        Dim FileX As New iFile
        FileX.Txt_Extension.Text = Me.Extension
        FileX.Txt_Name.Text = Me.Name
        FileX.L_Accessed.Text = Me.Accessed.ToShortDateString & " | " & Me.Accessed.ToLongTimeString
        FileX.L_Created.Text = Me.Created.ToShortDateString & " | " & Me.Created.ToLongTimeString
        FileX.L_Modify.Text = Me.Modify.ToShortDateString & " | " & Me.Modify.ToLongTimeString
        FileX.CB_FileType.Text = Me.FileType
        FileX.L_Sizeinfo.Text = Me.Size.Display
        FileX.Txt_Password.Text = Me.Password
        FileX.Is_Compressed.Value = Me.Is_Compressed
        FileX.Is_Encrypted.Value = Me.Is_Encrypted
        FileX.Is_Hidden.Value = Me.Is_Hidden
        FileX.Show()
    End Sub
#End Region
#End Region

Functions

Index


GetFileSize()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
#Region "<Functions>Size> [GetFileSize()]"
    ''' <summary>
    ''' Collect Size Information
    ''' </summary>
    ''' <param name="FilePath"></param>
    ''' <returns></returns>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Function GetFileSize(ByVal FilePath As String) As SizeInfo
        Dim Result As New SizeInfo

        Dim EstimatedSize As ULong = My.Computer.FileSystem.GetFileInfo(FilePath).Length
        Result.Raw = EstimatedSize
        Dim DoubleBytes As Double = Nothing
        Dim SizeType As Int32 = -1
        Select Case EstimatedSize
            Case Is >= 1099511627776
                DoubleBytes = CDbl(EstimatedSize / 1099511627776) 'TB
                Result.Text = " To"
                Result.Valeur = FormatNumber(DoubleBytes, 2)
                Result.Display = Result.Valeur & Result.Text
                Result.Scale = Size_Scale.Terabyte
            Case 1073741824 To 1099511627775
                DoubleBytes = CDbl(EstimatedSize / 1073741824) 'GB
                Result.Text = " Go"
                Result.Valeur = FormatNumber(DoubleBytes, 2)
                Result.Display = Result.Valeur & Result.Text
                Result.Scale = Size_Scale.Gigabyte
            Case 1048576 To 1073741823
                DoubleBytes = CDbl(EstimatedSize / 1048576) 'MB
                Result.Text = " Mo"
                Result.Valeur = FormatNumber(DoubleBytes, 2)
                Result.Display = Result.Valeur & Result.Text
                Result.Scale = Size_Scale.Megabyte
            Case 1024 To 1048575
                DoubleBytes = CDbl(EstimatedSize / 1024) 'KB
                Result.Text = " Ko"
                Result.Valeur = FormatNumber(DoubleBytes, 2)
                Result.Display = Result.Valeur & Result.Text
                Result.Scale = Size_Scale.Kilobyte
            Case 0 To 1023
                DoubleBytes = EstimatedSize ' bytes
                Result.Text = " O"
                Result.Valeur = FormatNumber(DoubleBytes, 2)
                Result.Display = Result.Valeur & Result.Text
                Result.Scale = Size_Scale.Bytes
        End Select
        Return Result
    End Function
#End Region

ListTables ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
#Region "<Functions>DatabaseInfo> [ListTables()]"
    ''' <summary>
    ''' This functions list the tables from a given database
    ''' </summary>
    ''' <example>
    ''' ListTables().List to display As Listbox Item
    ''' ListTables().LV to display As ListviewEx Item
    ''' ListTables().Data to display As DataTable Item
    ''' ListTables().DataSet to display As DataSet Item
    ''' ListTables().CList to display As List of TableInfo Item
    ''' <seealso cref="TableInfo"/>
    ''' </example>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Function ListTables() As S_Tables
        Dim Result As New S_Tables
        'Formating
        Result.CList = New List(Of TableInfo)
        Result.Data = New DataTable
        Result.List = New ListBox
        Result.List.Dock = DockStyle.Fill
        Result.LV = New ListViewEx
        Dim Row As New DataGridViewRow
        Dim Td As New TableInfo
        If DatabaseType = E_DatabaseType.Accdb Or DatabaseType = E_DatabaseType.Saadry Then
            Using con As New OleDbConnection(GetLineInfo.Line)
                Try
                    Dim restrictions() As String = New String(3) {}
                    restrictions(3) = "Table"
                    con.Open()
                    Result.Data = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                                                              New Object() {Nothing, Nothing, Nothing, "TABLE"})
                    For i = 0 To Result.Data.Rows.Count - 1 'Step i + 1
                        Result.List.DisplayMember = "TABLE_NAME"
                        Result.List.DataSource = Result.Data
                        Td = New TableInfo(Me, Result.Data.Rows(i)!TABLE_TYPE.ToString)
                        Result.CList.Add(Td)
                        Dim item As New ListViewItem(Result.Data.Rows(i)!TABLE_NAME.ToString)
                        item.SubItems.Add(Result.Data.Rows(i)!TABLE_TYPE.ToString)
                        Result.LV.Items.Add(item)
                    Next
                Catch ex As Exception
                    MsgBox(ex.Message)
                    If ex.Message.Contains("Mot de Passe") Then
                        MyPass = InputBox("Définir un mot de passe", "Verouillé")
                    End If
                Finally
                    con.Close()
                    con.Dispose()
                End Try
            End Using
        ElseIf DatabaseType = E_DatabaseType.Mdb Then
            Using con As New OleDbConnection(GetLineInfo.Line)
                Try
                    con.Open()

                    Result.Data = con.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables,
                                                                  New Object() {Nothing, Nothing, Nothing, Nothing})
                    For i = 0 To Result.Data.Rows.Count - 1
                        If Result.Data.Rows(i)!TABLE_TYPE.ToString = "TABLE" Then
                            Result.List.Items.Add(Result.Data.Rows(i)!TABLE_TYPE.ToString)
                            Dim item As New ListViewItem(Result.Data.Rows(i)!TABLE_NAME.ToString)
                            item.SubItems.Add(Result.Data.Rows(i)!TABLE_TYPE.ToString)
                            Result.LV.Items.Add(item)
                            Td = New TableInfo(Me, Result.Data.Rows(i)!TABLE_TYPE.ToString)
                            Result.CList.Add(Td)
                        End If
                    Next
                Catch ex As Exception
                    MessageBox.Show(ex.Message.ToString(), "Data Load Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
                Finally
                    con.Close()
                    con.Dispose()
                End Try
            End Using
        ElseIf DatabaseType = E_DatabaseType.Dbf Then
            Result.List.Items.Add(Target)
        ElseIf DatabaseType = E_DatabaseType.SQL Then
            Dim Cmd As SqlDataAdapter
            Dim Str As String = "SELECT * FROM sys.tables" '"SELECT * FROM INFORMATION_SCHEMA.tables ORDER BY TABLE_NAME"
            Dim Lsql As New List(Of String)
            Using con As New SqlConnection(GetLineInfo.Line)
                Try
                    con.Open()
                    Cmd = New SqlDataAdapter(Str, con)
                    Cmd.Fill(Result.DataSet)

                Catch ex As Exception
                Finally
                    con.Close()
                    con.Dispose()
                End Try
            End Using
            For Each table As DataTable In Result.DataSet.Tables
                For Each row1 As DataRow In table.Rows
                    'Only fetch tables (ommit views)
                    If row1.ItemArray(3).ToString = "BASE TABLE" Then
                        For Each col As DataColumn In table.Columns
                            Dim item As New ListViewItem() 'Result.Data.Rows(i)!TABLE_NAME.ToString)
                            '
                            If col.ToString() = "TABLE_NAME" Then
                                Lsql.Add(row1(col).ToString())
                                Result.List.Items.Add(row1(col).ToString())
                                item.Text = row1(col).ToString()
                                Td = New TableInfo(Me, row1(col).ToString())
                                Result.CList.Add(Td)
                                '    Exit For
                            End If
                            If col.ToString() = "TABLE_TYPE" Then
                                item.SubItems.Add(row1(col).ToString)
                            End If
                            Result.LV.Items.Add(item)
                        Next
                    End If
                Next
            Next
        End If
        Return Result
    End Function
#End Region

GetLineInfo ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
#Region "<Functions>DatabaseInfo> [GetLineInfo()]"
    ''' <summary>
    ''' This Functions Sets the Line information of a Selected File
    ''' </summary>
    ''' <returns></returns>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Function GetLineInfo() As LineInfo
        Dim Result As New LineInfo
        'Catch Exceptions
        '<Exception 1>
        If Exist = False Or Target = Nothing Then
            Return Result
            Exit Function
        End If
        'Runtime Variables
        Dim StringBuilder As New OleDbConnectionStringBuilder
        Dim SetPassword As String = "Jet OLEDB:Database Password"
        Dim Provider As String = ""
        Dim DataSource As String = ""
        Dim Ext As String = Me.Extension.ToLower
        Dim FileName As String = Name.ToUpper
        'Examining
        If Ext.Contains(".accdb") Then
#Region "<Access Accdb>"
            Me.FileType = E_FileType.Database
            Me.DatabaseType = E_DatabaseType.Accdb
            Me.Is_Database = True
            With StringBuilder
                .DataSource = Target
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            End With
            If Me.Password <> Nothing Then
                StringBuilder.Add(SetPassword, Me.Password)
            End If
            Line = StringBuilder.ConnectionString
#End Region
        ElseIf Ext.Contains(".dbf") Then
#Region "<Oracle Dbf>"
            Me.FileType = E_FileType.Database
            Me.DatabaseType = E_DatabaseType.Dbf
            Me.Is_Database = True
            Provider = "Microsoft.Jet.OLEDB.4.0"
            With StringBuilder
                .DataSource = IO.Path.GetDirectoryName(Target)
                .Provider = Provider
            End With
            StringBuilder.Add("Extended Properties", "dBase III")
            Result.Extension = "Extended Properties,dBase III"

            Me.Line = StringBuilder.ConnectionString
            Return Result
#End Region
        ElseIf Ext.Contains(".mdf") Then
#Region "<SQL Mdf>"
            Me.FileType = E_FileType.Database
            Me.DatabaseType = E_DatabaseType.SQL
            Me.Is_Database = True
            DataSource = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename="
            If Name = "MySql" Then
                Me.Line = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" & Target & ";Integrated Security=True"
            Else
                Me.Line = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" & Target & ";Integrated Security=True"
            End If
            Result.Data_Source = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=" & Target
            Result.Security = "Integrated Security=True"
#End Region
        ElseIf Ext.Contains(".saadry") Or Ext.Contains(".saadry_compta") Then
#Region "<Saadry>"
            Me.FileType = E_FileType.Database
            Me.DatabaseType = E_DatabaseType.Saadry
            Me.Is_Database = True
            If Name = "Gestions" Then
                Me.Password = My.Settings.Key
            End If
            With StringBuilder
                .DataSource = Target
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            End With
            If Password <> Nothing Then
                StringBuilder.Add(SetPassword, Me.Password)
            End If
            Me.Line = StringBuilder.ConnectionString
            Result.Data_Source = "Data Source=" & Target

#End Region
        ElseIf Ext.Contains(".xls") Or Ext.Contains(".xlsx") Then
#Region "<Excel Xls>"
            Me.FileType = E_FileType.Database
            Me.DatabaseType = E_DatabaseType.Excel
            Me.Is_Database = True
            DataSource = Target
            Provider = "Microsoft.Jet.OLEDB.4.0"
            With StringBuilder
                .DataSource = DataSource
                .Provider = Provider
            End With
            If Me.Password <> Nothing Then
                StringBuilder.Add(SetPassword, Me.Password)
            End If
            StringBuilder.Add("Extended Properties", "Excel 12.0")
            Result.Extension = "Extended Properties,Excel 12.0"

            Me.Line = StringBuilder.ConnectionString
#End Region
        Else
            Me.DatabaseType = E_DatabaseType.NotRecognized
            Me.Is_Database = False
        End If
        'Reporting Result
        Result.Data_Source = Me.Target
        Result.Line = Me.Line
        Return Result
    End Function '20/03/2018 16h10-16h49
#End Region

Lookup ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
#Region "<Functions>DatabaseInfo> [Lookup()] As String"
    ''' <summary>
    ''' This function search a single value given defined parameters
    ''' Lookup(Lookup_Value   |As String,
    '''        TableName      |As String,
    '''        Variable_Index |As String,
    '''        Where_Value_Is |As String)
    ''' </summary>
    ''' <param name="Lookup_Value">The column where the value should be</param>
    ''' <param name="TableName">The name of the table</param>
    ''' <param name="Variable_Index">The name of the indexed column</param>
    ''' <param name="Where_Value_Is">The value of the index</param>
    ''' <returns>As String</returns>
    ''' <authors>Saadry Dunkel Consultant</authors>
    Public Function Lookup(ByVal Lookup_Value As String,
                       ByVal TableName As String,
                       ByVal Variable_Index As String,
                       ByVal Where_Value_Is As String) As String
        Dim Result As String = Nothing
        Dim String_Command As String = Nothing
        If Variable_Index = Nothing And Where_Value_Is = Nothing Then
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName
        Else
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName & " WHERE " & Variable_Index & "='" & Where_Value_Is & "'"
        End If
        Using con As New OleDbConnection(GetLineInfo.Line)
            Using Cmd As New OleDbCommand(String_Command, con)
                con.Open()
                Try
                    Result = Cmd.ExecuteScalar().ToString
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                con.Close()
                con.Dispose()
            End Using
        End Using
        Return Result
    End Function 'Transformed to be tested
#End Region

List_LookupX ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#Region "<Functions>DatabaseInfo> [List_LookupX()] As ListviewEX"
    ''' <summary>
    ''' This function search a value given defined parameters
    ''' List_LookupX(Lookup_Value   |As String,
    '''              TableName      |As String,
    '''              Variable_Index |As String,
    '''              Where_Value_Is |As String)
    ''' </summary>
    ''' <param name="Lookup_Value">The column where the value should be</param>
    ''' <param name="TableName">The name of the table</param>
    ''' <param name="Variable_Index">The name of the indexed column</param>
    ''' <param name="Where_Value_Is">The value of the index</param>
    ''' <authors>Saadry Dunkel Consultant</authors>
    ''' <returns>As ListviewEx</returns>
    Public Function List_LookupX(ByVal Lookup_Value As String,
                       ByVal TableName As String,
                       Optional ByVal Variable_Index As String = "",
                       Optional ByVal Where_Value_Is As String = "") As ListViewEx
        Dim Result As New ListViewEx
        Dim String_Command As String = Nothing
        If Variable_Index = Nothing And Where_Value_Is = Nothing Then
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName
        Else
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName & " WHERE " & Variable_Index & "='" & Where_Value_Is & "'"
        End If
        Dim Reader As OleDbDataReader
        Dim Data As New DataTable
        Using con As New OleDbConnection(GetLineInfo.Line)
            Using Cmd As New OleDbCommand(String_Command, con)
                con.Open()
                Try
                    Reader = Cmd.ExecuteReader
                    While Reader.Read
                        ' Result.Items.Add(Reader(0).ToString)
                        Result.Items.Add(Reader.GetString(0))
                    End While
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                con.Close()
                con.Dispose()
            End Using
        End Using
        Return Result
    End Function 'Transformed to be tested

#End Region

List_Lookup ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#Region "<Functions>DatabaseInfo> [List_Lookup()] As ListView"
    ''' <summary>
    ''' This function search a value given defined parameters
    ''' List_Lookup(Lookup_Value   |As String,
    '''              TableName      |As String,
    '''              Variable_Index |As String,
    '''              Where_Value_Is |As String)
    ''' </summary>
    ''' <param name="Lookup_Value">The column where the value should be</param>
    ''' <param name="TableName">The name of the table</param>
    ''' <param name="Variable_Index">The name of the indexed column</param>
    ''' <param name="Where_Value_Is">The value of the index</param>
    ''' <authors>Saadry Dunkel Consultant</authors>
    ''' <returns>As ListView</returns>
    Public Function List_Lookup(ByVal Lookup_Value As String,
                       ByVal TableName As String,
                       Optional ByVal Variable_Index As String = "",
                       Optional ByVal Where_Value_Is As String = "") As ListView
        Dim Result As New ListView
        Dim String_Command As String = Nothing
        If Variable_Index = Nothing And Where_Value_Is = Nothing Then
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName
        Else
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName & " WHERE " & Variable_Index & "='" & Where_Value_Is & "'"
        End If
        Dim Reader As OleDbDataReader
        Dim Data As New DataTable
        Using con As New OleDbConnection(GetLineInfo.Line)
            Using Cmd As New OleDbCommand(String_Command, con)
                con.Open()
                Try
                    Reader = Cmd.ExecuteReader
                    While Reader.Read
                        ' Result.Items.Add(Reader(0).ToString)
                        Result.Items.Add(Reader.GetString(0))
                    End While
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                con.Close()
                con.Dispose()
            End Using
        End Using
        Return Result
    End Function 'Transformed to be tested

#End Region

Search ()

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
#Region "<Functions>DatabaseInfo> [Search()] As ListBox"
    ''' <summary>
    ''' This function search a value given defined parameters
    ''' List_Lookup(Lookup_Value   |As String,
    '''              TableName      |As String,
    '''              Variable_Index |As String,
    '''              Where_Value_Is |As String)
    ''' </summary>
    ''' <param name="Lookup_Value">The column where the value should be</param>
    ''' <param name="TableName">The name of the table</param>
    ''' <param name="Variable_Index">The name of the indexed column</param>
    ''' <param name="Where_Value_Is">The value of the index</param>
    ''' <authors>Saadry Dunkel Consultant</authors>
    ''' <returns>As ListBox</returns>
    Public Function Search(ByVal Lookup_Value As String,
                       ByVal TableName As String,
                       Optional ByVal Variable_Index As String = "",
                       Optional ByVal Where_Value_Is As String = "") As ListBox
        Dim Result As New ListBox
        Dim String_Command As String = Nothing
        If Variable_Index = Nothing And Where_Value_Is = Nothing Then
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName
        Else
            String_Command = "SELECT " & Lookup_Value & " FROM " & TableName & " WHERE " & Variable_Index & "='" & Where_Value_Is & "'"
        End If
        Dim Reader As OleDbDataReader
        Dim Data As New DataTable
        Using con As New OleDbConnection(GetLineInfo.Line)
            Using Cmd As New OleDbCommand(String_Command, con)
                con.Open()
                Try
                    Reader = Cmd.ExecuteReader
                    While Reader.Read
                        ' Result.Items.Add(Reader(0).ToString)
                        Result.Items.Add(Reader.GetString(0))
                    End While
                Catch ex As Exception
                    MsgBox(ex.Message)
                End Try
                con.Close()
                con.Dispose()
            End Using
        End Using
        Return Result
    End Function 'Transformed to be tested

#End Region

Telephone

+41 (079) 5111570

Postal address

Saadry Dunkel
2 avenue des Amazones
1224 Chênes-Bougeries

Contact Us




All fields are required.


Sending...

Close contact form
UA-29636309-1