Database Integration - lucyberryhub/WPF.Tutorial GitHub Wiki

Title: 🛢️ Berry Database Magic 🍓

Content:

Store your berry data with SQLite! 🍓

Hello, berry enthusiasts! 🍒 Let’s explore how to store, retrieve, and manipulate your berry data using SQLite in your WPF application. This guide is as complete and juicy as a berry smoothie! 🍓

Step 1: Setting Up SQLite

🍒 Install SQLite NuGet Package
Open your project in Visual Studio and install the SQLite library:
- Go to Tools > NuGet Package Manager > Manage NuGet Packages for Solution.
- Search for System.Data.SQLite.
- Install it for your project.
🍓 Add a Reference
Ensure your project references System.Data.SQLite.
🌟 Create a Database File
- Use SQLite tools or code to create a file named berries.db.
- Place it in your project directory or application folder.

Step 2: Connecting to the Database

Add the following code to connect to the SQLite database:

using System.Data.SQLite;

string connectionString = "Data Source=berries.db;Version=3;";

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    Console.WriteLine("Berry database is connected! 🍒");
}

Step 3: Creating a Table for Berry Data

Create a table to store your berry information:

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string createTableQuery = @"
        CREATE TABLE IF NOT EXISTS Berries (
            Id INTEGER PRIMARY KEY AUTOINCREMENT,
            Name TEXT NOT NULL,
            Color TEXT NOT NULL,
            SweetnessLevel INTEGER NOT NULL
        );
    ";

    using (var command = new SQLiteCommand(createTableQuery, connection))
    {
        command.ExecuteNonQuery();
        Console.WriteLine("Berry table is ready! 🍓");
    }
}

Step 4: Adding Berries to the Database

Insert some berries into the database:

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string insertQuery = @"
        INSERT INTO Berries (Name, Color, SweetnessLevel)
        VALUES (@Name, @Color, @SweetnessLevel);
    ";

    using (var command = new SQLiteCommand(insertQuery, connection))
    {
        command.Parameters.AddWithValue("@Name", "Strawberry");
        command.Parameters.AddWithValue("@Color", "Red");
        command.Parameters.AddWithValue("@SweetnessLevel", 10);
        command.ExecuteNonQuery();
        Console.WriteLine("Berry added! 🍓");
    }
}

Step 5: Reading Berries from the Database

Retrieve all the berry data and display it:

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string selectQuery = "SELECT * FROM Berries";

    using (var command = new SQLiteCommand(selectQuery, connection))
    using (var reader = command.ExecuteReader())
    {
        while (reader.Read())
        {
            Console.WriteLine($"🍒 Id: {reader["Id"]}, Name: {reader["Name"]}, Color: {reader["Color"]}, Sweetness: {reader["SweetnessLevel"]}");
        }
    }
}

Step 6: Updating Berry Data

Modify existing berry records:

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string updateQuery = @"
        UPDATE Berries
        SET SweetnessLevel = @SweetnessLevel
        WHERE Name = @Name;
    ";

    using (var command = new SQLiteCommand(updateQuery, connection))
    {
        command.Parameters.AddWithValue("@SweetnessLevel", 12);
        command.Parameters.AddWithValue("@Name", "Strawberry");
        command.ExecuteNonQuery();
        Console.WriteLine("Berry sweetness updated! 🍓");
    }
}

Step 7: Deleting Berry Data

Remove a berry from the database:

using (var connection = new SQLiteConnection(connectionString))
{
    connection.Open();
    string deleteQuery = "DELETE FROM Berries WHERE Name = @Name";

    using (var command = new SQLiteCommand(deleteQuery, connection))
    {
        command.Parameters.AddWithValue("@Name", "Strawberry");
        command.ExecuteNonQuery();
        Console.WriteLine("Berry removed! 🍒");
    }
}

Step 8: Displaying Data in a WPF DataGrid

Add a DataGrid to your WPF application to show berry data:

<DataGrid x:Name="BerryDataGrid" AutoGenerateColumns="True" />

Populate it with data:

using System.Collections.ObjectModel;

public ObservableCollection<Berry> Berries { get; set; }

public MainWindow()
{
    InitializeComponent();
    LoadBerryData();
}

public void LoadBerryData()
{
    Berries = new ObservableCollection<Berry>();

    using (var connection = new SQLiteConnection(connectionString))
    {
        connection.Open();
        string selectQuery = "SELECT * FROM Berries";

        using (var command = new SQLiteCommand(selectQuery, connection))
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                Berries.Add(new Berry
                {
                    Id = Convert.ToInt32(reader["Id"]),
                    Name = reader["Name"].ToString(),
                    Color = reader["Color"].ToString(),
                    SweetnessLevel = Convert.ToInt32(reader["SweetnessLevel"])
                });
            }
        }
    }

    BerryDataGrid.ItemsSource = Berries;
}

Step 9: Berry-Smooth Best Practices

🍓 Always sanitize user inputs to prevent SQL injection. Use parameterized queries as shown above.
🍒 Handle exceptions gracefully to avoid crashing your app.
🌟 Use asynchronous methods for database operations to keep the UI responsive.

Bonus: Berry App Deployment

Package your app into a single .exe file for easy delivery to users. See the 🛠️ Deploying WPF Applications 🍒 tutorial for more details.

🎉 Your berry database is now ready! 🍒🍓 With these steps, you can efficiently manage all your berry-tastic data!