SHARE
TWEET

tmp-dbmanager

George_Nikou Oct 23rd, 2019 (edited) 94 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Imports MySql.Data.MySqlClient
  2.  
  3. Public Class DBManager
  4.     Private connectionString = "server=localhost;user id=root;password=;database=block3m4;"
  5.     Private connect As MySqlConnection
  6.  
  7.     Public Sub New()
  8.         Try
  9.             Me.connect = New MySqlConnection(connectionString)
  10.             Me.connect.Open()
  11.         Catch ex As Exception
  12.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  13.         End Try
  14.     End Sub
  15.  
  16.     Public Sub testConnection()
  17.         If Me.connect.State = ConnectionState.Open Then
  18.             MsgBox("I'm wide open, baby!")
  19.         Else
  20.             MsgBox("I'm NOT very open")
  21.         End If
  22.     End Sub
  23.  
  24.     Public Function getAllUsers()
  25.         Dim query As String = "SELECT * FROM users WHERE status = 1"
  26.         Dim dataset As New DataSet
  27.  
  28.         Try
  29.             Dim cmd As New MySqlCommand(query, Me.connect)
  30.             Dim adapter As New MySqlDataAdapter(cmd)
  31.  
  32.             adapter.Fill(dataset, "users")
  33.         Catch ex As Exception
  34.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  35.         End Try
  36.  
  37.         Return dataset.Tables("users").Rows
  38.     End Function
  39.  
  40.     Public Function getAllUsersList() As List(Of UserBean)
  41.         Dim query As String = "SELECT * FROM users WHERE status = 1"
  42.         Dim dataset As New DataSet
  43.         Dim userlist As New List(Of UserBean)
  44.  
  45.         Try
  46.             Dim cmd As New MySqlCommand(query, Me.connect)
  47.             Dim adapter As New MySqlDataAdapter(cmd)
  48.  
  49.             adapter.Fill(dataset, "users")
  50.  
  51.             For Each row As DataRow In dataset.Tables("users").Rows
  52.                 userlist.Add(New UserBean(row("id"), row("fname"), row("lname"), row("age"), row("gender"), row("status")))
  53.             Next
  54.         Catch ex As Exception
  55.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  56.         End Try
  57.  
  58.         Return userlist
  59.     End Function
  60.  
  61.     Public Function getSingleUser(ByVal id As Integer) As UserBean
  62.         Dim query As String = "SELECT * FROM users WHERE id = " & id
  63.         Dim user = New UserBean()
  64.  
  65.         Try
  66.             Dim cmd As New MySqlCommand(query, Me.connect)
  67.             Dim reader As MySqlDataReader = cmd.ExecuteReader()
  68.  
  69.             If reader.Read() Then
  70.                 user.Id = reader.GetInt16(0)
  71.                 user.Firstname = reader.GetString(1)
  72.                 user.Lastname = reader.GetString(2)
  73.                 user.Age = reader.GetInt16(3)
  74.                 user.Gender = reader.GetString(4)
  75.                 user.Status = reader.GetBoolean(5)
  76.             End If
  77.  
  78.         Catch ex As Exception
  79.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  80.         End Try
  81.  
  82.         Return user
  83.     End Function
  84.  
  85.     Public Sub addUser(ByVal user As UserBean)
  86.         Dim cmd As New MySqlCommand
  87.  
  88.         Try
  89.             cmd.Connection = Me.connect
  90.             cmd.CommandText = "INSERT INTO users VALUES(DEFAULT, @fname, @lname, @age, @gender, DEFAULT)"
  91.             cmd.Parameters.AddWithValue("@fname", user.Firstname)
  92.             cmd.Parameters.AddWithValue("@lname", user.Lastname)
  93.             cmd.Parameters.AddWithValue("@age", user.Age)
  94.             cmd.Parameters.AddWithValue("@gender", user.Gender)
  95.  
  96.             Dim rowAffected = cmd.ExecuteNonQuery()
  97.             MsgBox("Row Affected : " & rowAffected)
  98.         Catch ex As Exception
  99.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  100.         End Try
  101.     End Sub
  102.  
  103.     Public Sub editUser(ByVal user As UserBean)
  104.         Dim cmd As New MySqlCommand
  105.  
  106.         Try
  107.             cmd.Connection = Me.connect
  108.             cmd.CommandText = "UPDATE users SET fname = @fname, lname = @lname, age = @age, gender = @gender WHERE id = @id"
  109.             cmd.Parameters.AddWithValue("@fname", user.Firstname)
  110.             cmd.Parameters.AddWithValue("@lname", user.Lastname)
  111.             cmd.Parameters.AddWithValue("@age", user.Age)
  112.             cmd.Parameters.AddWithValue("@gender", user.Gender)
  113.             cmd.Parameters.AddWithValue("@id", user.Id)
  114.  
  115.             Dim rowAffected = cmd.ExecuteNonQuery()
  116.             MsgBox("Row Affected : " & rowAffected)
  117.         Catch ex As Exception
  118.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  119.         End Try
  120.     End Sub
  121.  
  122.     Public Sub deleteUser(ByVal id As Integer)
  123.         Dim cmd As New MySqlCommand
  124.  
  125.         Try
  126.             cmd.Connection = Me.connect
  127.             cmd.CommandText = "UPDATE users SET status = 0 WHERE id = @id"
  128.             cmd.Parameters.AddWithValue("@id", id)
  129.  
  130.             Dim rowAffected = cmd.ExecuteNonQuery()
  131.             MsgBox("Row Affected : " & rowAffected)
  132.         Catch ex As Exception
  133.             MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
  134.         End Try
  135.     End Sub
  136.  
  137.  
  138. 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