Maximizing Database Efficiency: A Guide to Handling SQL Operations in C#

on
 



Maximizing Database Efficiency: A Guide to Handling SQL Operations in C#
Enhance Performance and Reliability with ADO.NET and SQL Server

  • Explore advanced techniques for managing SQL operations seamlessly in C#.
  • Learn how to optimize database interactions for improved application performance.
  • Discover best practices and asynchronous programming to streamline SQL transactions.
// This method asynchronously deletes all sales-related data from the database
        public static async Task DeleteAllSalesAndSaleDetailsAsync()
        {
            try
            {
                await Center.OpenConnectionAsync(); // Open a database connection asynchronously
                using (SqlTransaction transaction = Center.con.BeginTransaction()) // Begin a new SQL transaction
                {
                    try
                    {
                        // Delete all records from the SaleDetails table
                        using (SqlCommand cmdDeleteSaleDetails = new SqlCommand("DELETE FROM SaleDetails", Center.con, transaction))
                        {
                            await cmdDeleteSaleDetails.ExecuteNonQueryAsync(); // Execute the SQL command asynchronously
                        }
                        // Delete all records from the Sales table
                        using (SqlCommand cmdDeleteSales = new SqlCommand("DELETE FROM Sales", Center.con, transaction))
                        {
                            await cmdDeleteSales.ExecuteNonQueryAsync(); // Execute the SQL command asynchronously
                        }

                        // Delete all records from the LoginHistory table
                        using (SqlCommand cmdDeleteLoginHistory = new SqlCommand("DELETE FROM LoginHistory", Center.con, transaction))
                        {
                            await cmdDeleteLoginHistory.ExecuteNonQueryAsync(); // Execute the SQL command asynchronously
                        }
                        transaction.Commit(); // Commit the transaction if all commands are executed successfully
                    }
                    catch (Exception ex)
                    {
                        transaction.Rollback(); // Rollback the transaction if an error occurs during execution
                        throw new Exception("An error occurred while deleting data from the tables.", ex);
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); // Display error message in a message box
                Console.WriteLine($"An error occurred: {ex}"); // Log the error to the console
            }
            finally
            {
                Center.CloseConnection(); // Close the database connection in a finally block to ensure it always happens
            }
        }

        // This method asynchronously checks if any records exist in the specified tables
        public static async Task<bool> CheckIfRecordsExistAsync()
        {
            bool recordsExist = false;

            try
            {
                await Center.OpenConnectionAsync(); // Open a database connection asynchronously

                string query = @"SELECT COUNT(*) FROM Sales; SELECT COUNT(*) FROM SaleDetails; SELECT COUNT(*) FROM LoginHistory";

                using (SqlCommand cmdCheckRecords = new SqlCommand(query, Center.con))
                {
                    using (SqlDataReader reader = await cmdCheckRecords.ExecuteReaderAsync())
                    {
                        if (await reader.ReadAsync())
                        {
                            int salesCount = reader.GetInt32(0);
                            if (await reader.NextResultAsync() && await reader.ReadAsync())
                            {
                                int saleDetailsCount = reader.GetInt32(0);
                                if (await reader.NextResultAsync() && await reader.ReadAsync())
                                {
                                    int loginHistoryCount = reader.GetInt32(0);

                                    recordsExist = salesCount > 0 || saleDetailsCount > 0 || loginHistoryCount > 0;
                                }
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show($"Error: {ex.Message}", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); // Display error message in a message box
                Console.WriteLine($"An error occurred: {ex}"); // Log the error to the console
            }
            finally
            {
                Center.CloseConnection(); // Close the database connection in a finally block to ensure it always happens
            }

            return recordsExist; // Return the boolean flag indicating if records exist
        }

        // Event handler for the button click event to initiate the data deletion process
        private async void btnDeleteAll_Click(object sender, EventArgs e)
        {
            // Check if there are records in the tables
            bool recordsExist = await CheckIfRecordsExistAsync();

            if (!recordsExist)
            {
                // Display message if no records found
                MessageBox.Show("No records found in Sales, SaleDetails, or LoginHistory tables.", "Information", 
                    MessageBoxButtons.OK, 
                    MessageBoxIcon.Information); 
                return;
            }

            // Show confirmation dialog box
            DialogResult result = MessageBox.Show(
                "Are you sure you want to delete all data in the Sales, SaleDetails, and LoginHistory tables?",
                "Confirmation",
                MessageBoxButtons.YesNo,
                MessageBoxIcon.Warning);

            // If the user clicks Yes, proceed with the deletion
            if (result == DialogResult.Yes)
            {
                try
                {
                    // Call the method to delete all sales-related data
                    await DeleteAllSalesAndSaleDetailsAsync(); 
                    // Display success message
                    MessageBox.Show("All data in Sales, SaleDetails, and LoginHistory tables has been deleted.",
                                    "Success", MessageBoxButtons.OK, MessageBoxIcon.Information); 
                }
                catch (Exception ex)
                {
                    // Display error message if deletion fails
                    MessageBox.Show($"Error: {ex.Message}", "Error", 
                        MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

Conclusion:
Using C# with a SQL Server database offers several advantages, including robust performance, scalability, and comprehensive security features. C# provides seamless integration with SQL Server through ADO.NET libraries, enabling developers to build efficient and reliable database-driven applications. Additionally, asynchronous programming capabilities in C# facilitate responsive and non-blocking database operations, enhancing the overall user experience. With proper design and optimization, C# applications can leverage the full potential of SQL Server databases to deliver high-quality solutions for various business

สมัครสมาชิกเพจ เริ่มต้นเพียง 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