SHARE
TWEET

ConnectionUtilities.vb

a guest Jun 8th, 2017 80 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Imports System.Data.OleDb
  2.  
  3. ''' <license>
  4. ''' Copyright (C) 2017  Dominick Jones
  5. '''
  6. ''' This program is free software: you can redistribute it and/or modify
  7. ''' it under the terms of the GNU General Public License as published by
  8. ''' the Free Software Foundation, either version 3 of the License, or
  9. ''' any later version.
  10. '''
  11. ''' This program is distributed in the hope that it will be useful,
  12. ''' but WITHOUT ANY WARRANTY; without even the implied warranty of
  13. ''' MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
  14. ''' GNU General Public License for more details.
  15. '''
  16. ''' You should have received a copy of the GNU General Public License
  17. ''' along with this program. If not, see <a>http://www.gnu.org/licenses/"</a>.
  18. ''' </license>
  19. '''
  20. ''' <summary>
  21. ''' Connection utilities so that we can interact and connect to a database.
  22. ''' </summary>
  23. ''' <remarks></remarks>
  24. Public Class ConnectionUtilities
  25.  
  26.     'The current username logged in.
  27.     Public Shared username As String = ""
  28.  
  29.     'The provider of our database.
  30.     Const DATA_PROVIDER As String = "Provider=Microsoft.ACE.OLEDB.12.0;"
  31.  
  32.     'The source of our data.
  33.     Const DATA_SOURCE As String = "Data Source = N:\BTEC Year 2\[17] Project Planning with IT\4_Assignment\Grade Tracker UCAS\UCAS Data.accdb"
  34.  
  35.     'Represents a connection.
  36.     Shared connection As New OleDbConnection
  37.  
  38.     'Our adapter for our data.
  39.     Shared adapter As OleDbDataAdapter
  40.  
  41.     'Holds temporary data to import onto our database.
  42.     Shared dataSet As New DataSet
  43.  
  44.     'Querys to test and execute on our database.
  45.     Shared query As String
  46.  
  47.     ''' <summary>
  48.     ''' Initiates a conenction for us.
  49.     ''' </summary>
  50.     ''' <remarks></remarks>
  51.     Private Shared Sub initiateConnection()
  52.         If connection.State = ConnectionState.Open Then
  53.             Return
  54.         End If
  55.  
  56.         connection.ConnectionString = DATA_PROVIDER & DATA_SOURCE
  57.         connection.Open()
  58.     End Sub
  59.  
  60.     ''' <summary>
  61.     ''' Loads all the courses on the main form.
  62.     ''' </summary>
  63.     ''' <param name="c">The combo box to load data into.</param>
  64.     ''' <remarks></remarks>
  65.     Public Shared Sub loadCourses(ByRef c As ComboBox)
  66.         initiateConnection()
  67.  
  68.         Dim command As New OleDbCommand
  69.         command.Connection = connection
  70.         command.CommandText = "Select CourseName from tblCourses"
  71.         Dim dataReader As OleDbDataReader = command.ExecuteReader
  72.         While dataReader.Read
  73.             c.Items.Add(dataReader.Item(0))
  74.         End While
  75.         dataReader.Close()
  76.         disconnect()
  77.     End Sub
  78.  
  79.     ''' <summary>
  80.     ''' Saves the username to the database with the course they have selected.
  81.     ''' </summary>
  82.     ''' <param name="testedUsername"></param>
  83.     ''' <param name="course"></param>
  84.     ''' <remarks></remarks>
  85.     Public Shared Sub save(ByVal testedUsername As String, ByVal course As String)
  86.         initiateConnection()
  87.  
  88.         Dim commandBuilder As New OleDbCommandBuilder(adapter)
  89.  
  90.         While Not testedUsername = username
  91.             For i As Integer = 0 To dataSet.Tables("tblStudents").Rows.Count - 1
  92.                 dataSet.Tables("tblStudents").Rows(i).Item("Course") = course
  93.                 adapter.Update(dataSet, "tblStudents")
  94.             Next i
  95.         End While
  96.  
  97.         MessageBox.Show("You have saved your course, please proceed.")
  98.     End Sub
  99.  
  100.     ''' <summary>
  101.     ''' Checking if a member exists.
  102.     ''' </summary>
  103.     ''' <param name="forename">The first name to check if it exists.</param>
  104.     ''' <param name="surname">The surname to check if it exists.</param>
  105.     ''' <returns>true if it does, false if not.</returns>
  106.     ''' <remarks></remarks>
  107.     Private Shared Function memberExists(ByRef forename As String, ByRef surname As String) As Boolean
  108.         initiateConnection()
  109.         query = "Select * From tblMembers WHERE Forename = '" & forename & "' AND Surname = '" & surname & "'"
  110.         adapter = New OleDbDataAdapter(query, connection)
  111.         adapter.Fill(dataSet, "tblMembers")
  112.         If dataSet.Tables("tblMembers").Rows.Count > 0 Then
  113.             Return True
  114.         End If
  115.  
  116.         Return False
  117.         disconnect()
  118.     End Function
  119.  
  120.     ''' <summary>
  121.     ''' Logs our user in by checking the database (this is not for administrative use.)
  122.     ''' </summary>
  123.     ''' <param name="testedForename">The forename to be interrogated.</param>
  124.     ''' <param name="testedSurname">The surname to be interrogated.</param>
  125.     ''' <param name="testedPassword">The password to be interrogated.</param>
  126.     ''' <remarks></remarks>
  127.     Public Shared Sub login(ByRef testedForename As String, ByRef testedSurname As String, ByRef testedPassword As String)
  128.         Dim count As Integer = 0
  129.         If Not memberExists(testedForename, testedSurname) Then
  130.             MessageBox.Show("Please register an account.")
  131.         Else
  132.             initiateConnection()
  133.  
  134.             Dim table As String = "tblMembers"
  135.             query = "Select * From " & table
  136.             adapter = New OleDbDataAdapter(query, connection)
  137.             adapter.Fill(dataSet, table)
  138.             For index = 0 To dataSet.Tables(table).Rows.Count - 1
  139.                 If testedForename = dataSet.Tables(table).Rows(index).Item("Forename") Then
  140.                     If testedSurname = dataSet.Tables(table).Rows(index).Item("Surname") Then
  141.                         If testedPassword = dataSet.Tables(table).Rows(index).Item("Pswd") Then
  142.                             If count = 1 Then
  143.                                 Return
  144.                             End If
  145.                             MessageBox.Show("Successful login, thank you " & testedForename & " " & testedSurname & "!")
  146.                             username = testedForename & " " & testedSurname
  147.                             count += 1
  148.                         Else
  149.                             MessageBox.Show("Incorrect password.")
  150.                         End If
  151.                     End If
  152.                 End If
  153.             Next
  154.         End If
  155.         disconnect()
  156.     End Sub
  157.  
  158.     ''' <summary>
  159.     ''' Logs our user in by checking the database (this is for administrative use.)
  160.     ''' </summary>
  161.     ''' <param name="testedForename">The forename to be interrogated.</param>
  162.     ''' <param name="testedSurname">The surname to be interrogated.</param>
  163.     ''' <param name="testedPassword">The password to be interrogated.</param>
  164.     ''' <remarks></remarks>
  165.     Public Shared Sub login(ByRef testedForename As String, ByRef testedSurname As String, ByRef testedPassword As String, ByRef admin As Boolean)
  166.         Dim count As Integer = 0
  167.         If Not memberExists(testedForename, testedSurname) Then
  168.             MessageBox.Show("Please register an account.")
  169.         Else
  170.             initiateConnection()
  171.  
  172.             Dim table As String = "tblMembers"
  173.             query = "Select * From " & table
  174.             adapter = New OleDbDataAdapter(query, connection)
  175.             adapter.Fill(dataSet, table)
  176.             For index = 0 To dataSet.Tables(table).Rows.Count - 1
  177.                 If testedForename = dataSet.Tables(table).Rows(index).Item("Forename") Then
  178.                     If testedSurname = dataSet.Tables(table).Rows(index).Item("Surname") Then
  179.                         If testedPassword = dataSet.Tables(table).Rows(index).Item("Pswd") Then
  180.                             If admin = dataSet.Tables(table).Rows(index).Item("Administrator") Then
  181.                                 If count = 1 Then
  182.                                     Return
  183.                                 End If
  184.                                 MessageBox.Show("Successful login, thank you " & testedForename & " " & testedSurname & "!")
  185.                                 username = testedForename & " " & testedSurname
  186.                                 count += 1
  187.                             Else
  188.                                 MessageBox.Show("You do not have the correct permissions to do this.")
  189.                                 AccountsAdmin.Dispose()
  190.                                 Accounts.Show()
  191.                             End If
  192.                         Else
  193.                             MessageBox.Show("Incorrect password.")
  194.                         End If
  195.                     End If
  196.                 End If
  197.             Next
  198.         End If
  199.         disconnect()
  200.     End Sub
  201.  
  202.     ''' <summary>
  203.     ''' Registers our user into the database.
  204.     ''' </summary>
  205.     ''' <param name="forename">The forename to register.</param>
  206.     ''' <param name="surname">The surname to register.</param>
  207.     ''' <param name="password">The password to register.</param>
  208.     ''' <remarks></remarks>
  209.     Public Shared Sub register(ByRef forename As String, ByRef surname As String, ByRef password As String)
  210.         initiateConnection()
  211.  
  212.         Dim table As String = "tblMembers"
  213.  
  214.         query = "Select * From " & table
  215.         adapter = New OleDbDataAdapter(query, connection)
  216.         adapter.Fill(dataSet, table)
  217.  
  218.         Dim commandBuilder As New OleDbCommandBuilder(adapter)
  219.         Dim newRow As DataRow = dataSet.Tables(table).NewRow()
  220.         newRow.Item("Forename") = forename
  221.         newRow.Item("Surname") = surname
  222.         newRow.Item("Pswd") = password
  223.  
  224.         dataSet.Tables(table).Rows.Add(newRow)
  225.         adapter.Update(dataSet, "tblMembers")
  226.         MessageBox.Show("Successfully registered your account.")
  227.  
  228.         disconnect()
  229.     End Sub
  230.  
  231.     ''' <summary>
  232.     ''' Closes and disposes of our connection.
  233.     ''' </summary>
  234.     ''' <remarks></remarks>
  235.     Private Shared Sub disconnect()
  236.         connection.Close()
  237.         connection.Dispose()
  238.     End Sub
  239. End Class
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top