Streamlining Database Operations: Mastering SQL Tasks in Visual Basic .NET

on




Streamlining Database Operations: Mastering SQL Tasks in Visual Basic .NET
Efficiently Manage Access Database with Visual Basic .NET

  • Dive into the world of Visual Basic .NET and its capabilities in handling SQL tasks.
  • Discover strategies for optimizing database operations and enhancing application efficiency.
  • Learn essential techniques for managing Access database seamlessly using VB .NET.

' Imports the necessary namespace for OleDb operations

    ' Imports System.Data.OleDb


    ' Connection string components for connecting to the database

    Dim Provider As String = "Provider=Microsoft.ACE.OLEDB.12.0;"

    Dim DataSource As String = "Data Source=" & Application.StartupPath & "\POSSystem.accdb;"

    Dim SecurityInfo As String = "Persist Security Info=False"

    Private ConString As String = Provider & DataSource & SecurityInfo


    ' Asynchronously deletes all records from the SaleDetails, Sales, and Login_Logs tables

    Private Async Function DeleteAllSalesAndSaleDetailsAsync(connection As OleDbConnection, transaction As OleDbTransaction) As Task

        Try

            ' Delete all records from the SaleDetails table

            Using cmdDeleteSaleDetails As New OleDbCommand("DELETE FROM SaleDetails", connection, transaction)

                Await cmdDeleteSaleDetails.ExecuteNonQueryAsync()

            End Using


            ' Delete all records from the Sales table

            Using cmdDeleteSales As New OleDbCommand("DELETE FROM Sales", connection, transaction)

                Await cmdDeleteSales.ExecuteNonQueryAsync()

            End Using


            ' Delete all records from the Login_Logs table

            Using cmdDeleteLoginLogs As New OleDbCommand("DELETE FROM Login_Logs", connection, transaction)

                Await cmdDeleteLoginLogs.ExecuteNonQueryAsync()

            End Using


            ' Commit the transaction if all deletions were successful

            transaction.Commit()

        Catch ex As Exception

            ' Rollback the transaction if an error occurs during deletion

            transaction.Rollback()

            Throw ' Rethrow the exception to be handled by the calling method

        End Try

    End Function


    ' Asynchronously checks if any records exist in the Sales, SaleDetails, or Login_Logs tables

    Public Async Function CheckIfRecordsExistAsync(connection As OleDbConnection) As Task(Of Boolean)

        Dim recordsExist As Boolean = False


        Try

            ' Check the number of records in the Sales table

            Dim salesCount As Integer = 0

            Using cmdCheckSales As New OleDbCommand("SELECT COUNT(*) FROM Sales", connection)

                salesCount = Convert.ToInt32(Await cmdCheckSales.ExecuteScalarAsync())

            End Using


            ' Check the number of records in the SaleDetails table

            Dim saleDetailsCount As Integer = 0

            Using cmdCheckSaleDetails As New OleDbCommand("SELECT COUNT(*) FROM SaleDetails", connection)

                saleDetailsCount = Convert.ToInt32(Await cmdCheckSaleDetails.ExecuteScalarAsync())

            End Using


            ' Check the number of records in the Login_Logs table

            Dim loginLogsCount As Integer = 0

            Using cmdCheckLoginLogs As New OleDbCommand("SELECT COUNT(*) FROM Login_Logs", connection)

                loginLogsCount = Convert.ToInt32(Await cmdCheckLoginLogs.ExecuteScalarAsync())

            End Using


            ' Determine if any records exist in any of the tables

            recordsExist = salesCount > 0 OrElse saleDetailsCount > 0 OrElse loginLogsCount > 0

        Catch ex As Exception

            ' Display an error message if an exception occurs during record checking

            MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

            Console.WriteLine($"CheckIfRecordsExistAsync() : An error occurred: {ex.ToString()}")

        End Try


        Return recordsExist

    End Function


    ' Event handler for the button click event to delete all records from tables

    Private Async Sub btnDeleteAll_Click(sender As Object, e As EventArgs) Handles btnDeleteAll.Click

        ' Using block ensures the connection is closed after use

        Using connection As New OleDbConnection(ConString)

            Await connection.OpenAsync() ' Open the connection asynchronously


            ' Check if there are records in the Sales, SaleDetails, or Login_Logs tables

            Dim recordsExist As Boolean = Await CheckIfRecordsExistAsync(connection)


            If Not recordsExist Then

                ' Display a message if no records are found in any of the tables

                MessageBox.Show("No records found in Sales, SaleDetails, or Login_Logs tables.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information)

                Return ' Exit the event handler

            End If


            ' Show a confirmation dialog to confirm deletion of records

            Dim result As DialogResult = MessageBox.Show(

                "Are you sure you want to delete all data in the Sales, SaleDetails, and Login_Logs tables?",

                "Confirmation",

                MessageBoxButtons.YesNo,

                MessageBoxIcon.Warning)


            ' Proceed with deletion if the user confirms

            If result = DialogResult.Yes Then

                ' Using block ensures transaction is disposed after use

                Using transaction As OleDbTransaction = connection.BeginTransaction()

                    Try

                        ' Call the asynchronous method to delete all records

                        Await DeleteAllSalesAndSaleDetailsAsync(connection, transaction)

                        ' Display a success message after deletion

                        MessageBox.Show("All data in Sales, SaleDetails, and Login_Logs tables has been deleted.", "Success", MessageBoxButtons.OK, MessageBoxIcon.Information)

                    Catch ex As Exception

                        ' Display an error message if an exception occurs during deletion

                        MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)

                    End Try

                End Using

            End If

        End Using

    End Sub


While Visual Basic .NET provides a convenient environment for developing applications, especially when paired with a Microsoft Access database, there are some limitations and considerations to keep in mind:


  1. Single-Threaded Nature: Visual Basic .NET applications typically run on a single thread, which can affect performance when dealing with long-running database operations. Asynchronous programming techniques, as demonstrated in the provided code, help mitigate this limitation by allowing tasks to run concurrently without blocking the main thread.

  3. Access Database Size: Microsoft Access databases have size limitations compared to more robust database systems like SQL Server. While suitable for small to medium-sized applications, Access may struggle to handle large volumes of data efficiently. Consider migrating to a more scalable database solution if your application's data requirements grow significantly.

  5. Security Concerns: Access databases are prone to security vulnerabilities, particularly when exposed to external threats or unauthorized access. Ensure proper authentication, authorization, and encryption mechanisms are in place to protect sensitive data. Parameterized queries, as used in the code, help prevent SQL injection attacks but should be supplemented with additional security measures.

  7. Compatibility Issues: Visual Basic .NET and Microsoft Access may encounter compatibility issues, especially when dealing with different versions of the software or operating systems. Stay updated with the latest patches and updates to address any compatibility issues and ensure smooth operation.

  9. Limited Scalability: While Access databases are suitable for small-scale applications, they may not scale well as the application grows in complexity or user base. Consider transitioning to a more robust database backend, such as SQL Server or MySQL, for improved scalability and performance.


In summary, while Visual Basic .NET provides a user-friendly platform for developing applications with Microsoft Access databases, developers should be mindful of the limitations and take appropriate measures to address them for optimal performance, security, and scalability.


สมัครสมาชิกเพจ เริ่มต้นเพียง 199 บาท!
🚀 เข้ากลุ่มปิดเพื่อรับชมวิดีโอสอนพิเศษ, คอร์สออนไลน์, สอบถามปัญหา, ดาวน์โหลดโปรเจค Source Code ต่างๆ

ค่าสมัครสมาชิก (รายเดือน)
คนที่ 1 - 50 = ฿199
คนที่ 51 - 100 = ฿249
คนที่ 101 - 150 = ฿299

✨ คนที่ 151 - 200 = ฿349
* เท่า Netflix มาตรฐาน *

🌟 คนที่ 201 เป็นต้นไป 🌟
฿419 (เท่า Netflix พรีเมียม)

🔸 ยกเลิกการเป็นสมาชิกได้ทุกเมื่อ

สนใจ inbox สอบถามอาจารย์ได้ครับ

https://www.facebook.com/CodeAMinute/


💬 พูดคุยกับผมที่...
► LINE OA : https://lin.ee/ApE56xD
► Facebook : https://www.facebook.com/CodeAMinute

📺 ติดตามช่อง YouTube
► https://www.youtube.com/@iBasskung
► https://www.youtube.com/@iBasskungAcademy
► TikTok : https://www.tiktok.com/@codeaminute

📖 ดูหลักสูตรทั้งหมด: https://bit.ly/3lXWoj2

Udemy profile : https://www.udemy.com/user/tanin-sangngam


#CodeAMinute #iBasskung #CEO #โปรแกรมเมอร์ #สอนเขียนโปรแกรม #สอนทำโปรเจกต์ #สอนทำโปรเจค 

Comments

Post a Comment