Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Imports MySql.Data.MySqlClient
- Public Class DBManager
- Private connectionString = "server=localhost;user id=root;password=;database=block3m4;"
- Private connect As MySqlConnection
- Public Sub New()
- Try
- Me.connect = New MySqlConnection(connectionString)
- Me.connect.Open()
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- End Sub
- Public Sub testConnection()
- If Me.connect.State = ConnectionState.Open Then
- MsgBox("I'm wide open, baby!")
- Else
- MsgBox("I'm NOT very open")
- End If
- End Sub
- Public Function getAllUsers()
- Dim query As String = "SELECT * FROM users WHERE status = 1"
- Dim dataset As New DataSet
- Try
- Dim cmd As New MySqlCommand(query, Me.connect)
- Dim adapter As New MySqlDataAdapter(cmd)
- adapter.Fill(dataset, "users")
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- Return dataset.Tables("users").Rows
- End Function
- Public Function getAllUsersList() As List(Of UserBean)
- Dim query As String = "SELECT * FROM users WHERE status = 1"
- Dim dataset As New DataSet
- Dim userlist As New List(Of UserBean)
- Try
- Dim cmd As New MySqlCommand(query, Me.connect)
- Dim adapter As New MySqlDataAdapter(cmd)
- adapter.Fill(dataset, "users")
- For Each row As DataRow In dataset.Tables("users").Rows
- userlist.Add(New UserBean(row("id"), row("fname"), row("lname"), row("age"), row("gender"), row("status")))
- Next
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- Return userlist
- End Function
- Public Function getSingleUser(ByVal id As Integer) As UserBean
- Dim query As String = "SELECT * FROM users WHERE id = " & id
- Dim user = New UserBean()
- Try
- Dim cmd As New MySqlCommand(query, Me.connect)
- Dim reader As MySqlDataReader = cmd.ExecuteReader()
- If reader.Read() Then
- user.Id = reader.GetInt16(0)
- user.Firstname = reader.GetString(1)
- user.Lastname = reader.GetString(2)
- user.Age = reader.GetInt16(3)
- user.Gender = reader.GetString(4)
- user.Status = reader.GetBoolean(5)
- End If
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- Return user
- End Function
- Public Sub addUser(ByVal user As UserBean)
- Dim cmd As New MySqlCommand
- Try
- cmd.Connection = Me.connect
- cmd.CommandText = "INSERT INTO users VALUES(DEFAULT, @fname, @lname, @age, @gender, DEFAULT)"
- cmd.Parameters.AddWithValue("@fname", user.Firstname)
- cmd.Parameters.AddWithValue("@lname", user.Lastname)
- cmd.Parameters.AddWithValue("@age", user.Age)
- cmd.Parameters.AddWithValue("@gender", user.Gender)
- Dim rowAffected = cmd.ExecuteNonQuery()
- MsgBox("Row Affected : " & rowAffected)
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- End Sub
- Public Sub editUser(ByVal user As UserBean)
- Dim cmd As New MySqlCommand
- Try
- cmd.Connection = Me.connect
- cmd.CommandText = "UPDATE users SET fname = @fname, lname = @lname, age = @age, gender = @gender WHERE id = @id"
- cmd.Parameters.AddWithValue("@fname", user.Firstname)
- cmd.Parameters.AddWithValue("@lname", user.Lastname)
- cmd.Parameters.AddWithValue("@age", user.Age)
- cmd.Parameters.AddWithValue("@gender", user.Gender)
- cmd.Parameters.AddWithValue("@id", user.Id)
- Dim rowAffected = cmd.ExecuteNonQuery()
- MsgBox("Row Affected : " & rowAffected)
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- End Sub
- Public Sub deleteUser(ByVal id As Integer)
- Dim cmd As New MySqlCommand
- Try
- cmd.Connection = Me.connect
- cmd.CommandText = "UPDATE users SET status = 0 WHERE id = @id"
- cmd.Parameters.AddWithValue("@id", id)
- Dim rowAffected = cmd.ExecuteNonQuery()
- MsgBox("Row Affected : " & rowAffected)
- Catch ex As Exception
- MsgBox(ex.Message, MsgBoxStyle.Critical, "Connection Failed")
- End Try
- End Sub
- End Class
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement