Lesson 34: Database Intro in VB2022

Imports System.Data.SqlClient

Public Class DatabaseForm
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Define connection string
        Dim connString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
        
        ' Create SqlConnection object
        Using conn As New SqlConnection(connString)
            Try
                ' Open the connection
                conn.Open()
                
                ' Check connection state
                If conn.State = ConnectionState.Open Then
                    MessageBox.Show("Database connection successful!", "Connection Status", 
                                    MessageBoxButtons.OK, MessageBoxIcon.Information)
                End If
                
            Catch ex As SqlException
                MessageBox.Show("Error connecting to database: " & ex.Message, "Connection Error", 
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
End Class

Imports System.Data
Imports System.Data.SqlClient

Public Class DataForm
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Connection string
        Dim connString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
        
        ' SQL query
        Dim query As String = "SELECT ContactID, FirstName, LastName, Email FROM Contacts"
        
        ' Create objects
        Using conn As New SqlConnection(connString)
            Try
                conn.Open()
                
                ' Create DataAdapter
                Dim adapter As New SqlDataAdapter(query, conn)
                
                ' Create DataTable
                Dim dt As New DataTable()
                
                ' Fill DataTable with data
                adapter.Fill(dt)
                
                ' Bind to DataGridView
                DataGridView1.DataSource = dt
                
            Catch ex As SqlException
                MessageBox.Show("Database error: " & ex.Message, "Error", 
                                MessageBoxButtons.OK, MessageBoxIcon.Error)
            End Try
        End Using
    End Sub
End Class

Imports System.Data.SqlClient

Public Class InsertForm
    Private Sub BtnInsert_Click(sender As Object, e As EventArgs) Handles BtnInsert.Click
        Dim connString As String = "Server=localhost\SQLEXPRESS;Database=ContactsDB;Integrated Security=True;"
        Dim query As String = "INSERT INTO Contacts (FirstName, LastName, Email) VALUES (@FirstName, @LastName, @Email)"
        
        Using conn As New SqlConnection(connString)
            Using cmd As New SqlCommand(query, conn)
                ' Add parameters to prevent SQL injection
                cmd.Parameters.AddWithValue("@FirstName", txtFirstName.Text)
                cmd.Parameters.AddWithValue("@LastName", txtLastName.Text)
                cmd.Parameters.AddWithValue("@Email", txtEmail.Text)
                
                Try
                    conn.Open()
                    Dim rowsAffected = cmd.ExecuteNonQuery()
                    
                    If rowsAffected > 0 Then
                        MessageBox.Show("Record inserted successfully!", "Success", 
                                        MessageBoxButtons.OK, MessageBoxIcon.Information)
                    Else
                        MessageBox.Show("No records were inserted.", "Information", 
                                        MessageBoxButtons.OK, MessageBoxIcon.Information)
                    End If
                    
                Catch ex As SqlException
                    MessageBox.Show("Error inserting record: " & ex.Message, "Error", 
                                    MessageBoxButtons.OK, MessageBoxIcon.Error)
                End Try
            End Using
        End Using
    End Sub
End Class