[VB.NET]
[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 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 |
Recent Comments