C# - With MySQL

Performing CRUD (Create, Read, Update, Delete) operations with MySQL in C# involves connecting to a MySQL database, executing SQL commands, and handling results. Here's a step-by-step guide for setting up and using MySQL in a C# application:

1. Set Up MySQL

Ensure that you have MySQL installed and running, and that you have a database and table(s) set up. You can use MySQL Workbench or another MySQL client to manage your database.

2. Install MySQL Connector for .NET

To connect to MySQL from C#, you need the MySQL Connector/NET. You can install it via NuGet Package Manager.

Using Visual Studio:

Right-click on your project in Solution Explorer.
Choose "Manage NuGet Packages."
Search for MySql.Data and install it.

Using .NET CLI:

bash
dotnet add package MySql.Data

3. Create a Basic C# Application for CRUD Operations

Below are examples demonstrating CRUD operations in a C# application using MySQL.

Create (Insert Data)

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=your_database_name;User ID=your_username;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string insertQuery = "INSERT INTO YourTable (Column1, Column2) VALUES (@Value1, @Value2)";
            using (MySqlCommand command = new MySqlCommand(insertQuery, connection))
            {
                command.Parameters.AddWithValue("@Value1", "SampleValue1");
                command.Parameters.AddWithValue("@Value2", "SampleValue2");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) inserted.");
            }
        }
    }
}

Read (Select Data)

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=your_database_name;User ID=your_username;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string selectQuery = "SELECT * FROM YourTable";
            using (MySqlCommand command = new MySqlCommand(selectQuery, connection))
            {
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        string column1Value = reader["Column1"].ToString();
                        string column2Value = reader["Column2"].ToString();
                        Console.WriteLine($"Column1: {column1Value}, Column2: {column2Value}");
                    }
                }
            }
        }
    }
}

Update (Modify Data)

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=your_database_name;User ID=your_username;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string updateQuery = "UPDATE YourTable SET Column1 = @NewValue WHERE Column2 = @Condition";
            using (MySqlCommand command = new MySqlCommand(updateQuery, connection))
            {
                command.Parameters.AddWithValue("@NewValue", "UpdatedValue");
                command.Parameters.AddWithValue("@Condition", "ConditionValue");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) updated.");
            }
        }
    }
}

Delete (Remove Data)

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=your_database_name;User ID=your_username;Password=your_password;";

        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            connection.Open();

            string deleteQuery = "DELETE FROM YourTable WHERE Column1 = @Condition";
            using (MySqlCommand command = new MySqlCommand(deleteQuery, connection))
            {
                command.Parameters.AddWithValue("@Condition", "ValueToDelete");

                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"{rowsAffected} row(s) deleted.");
            }
        }
    }
}

4. Handling Exceptions

It’s crucial to handle exceptions to manage errors gracefully. Use try-catch blocks to catch and handle exceptions.

csharp
using MySql.Data.MySqlClient;
using System;

class Program
{
    static void Main()
    {
        string connectionString = "Server=localhost;Database=your_database_name;User ID=your_username;Password=your_password;";

        try
        {
            using (MySqlConnection connection = new MySqlConnection(connectionString))
            {
                connection.Open();

                // Perform CRUD operations here

                Console.WriteLine("Operation completed successfully.");
            }
        }
        catch (MySqlException ex)
        {
            Console.WriteLine($"MySQL Error: {ex.Message}");
        }
        catch (Exception ex)
        {
            Console.WriteLine($"Error: {ex.Message}");
        }
    }
}

5. Parameterization

Always use parameterized queries to prevent SQL injection and ensure safe handling of inputs.

By following these steps, you should be able to perform CRUD operations with MySQL in your C# application effectively. If you have any more questions or need additional details, feel free to ask!

techmore.in