CRUD Operations - potatoscript/csharp GitHub Wiki

πŸ₯” CRUD Operations (Create, Read, Update, Delete) in C# πŸ₯”

πŸ“š What is CRUD?

CRUD stands for:

  • πŸ₯” C – Create (Add new data)
  • πŸ₯” R – Read (Retrieve or view data)
  • πŸ₯” U – Update (Modify existing data)
  • πŸ₯” D – Delete (Remove data)

Imagine running a potato farm where you:

  1. Create – Add new types of potatoes to your storage. πŸ₯”πŸŒ±
  2. Read – Check which potatoes are available. πŸ“šπŸ₯”
  3. Update – Change the potato price or update its quality. πŸ› οΈπŸ₯”
  4. Delete – Remove the rotten or expired potatoes. ❌πŸ₯”

🎯 How Does CRUD Work in C#?

To perform CRUD operations in C#, we follow these steps:

  1. Connect to the database πŸ“‘
  2. Write SQL queries πŸ“„
  3. Execute SQL commands βœ…
  4. Process the results πŸ”

πŸ“ Required Setup

Before starting CRUD operations, make sure you:

βœ… Install the required database provider (SQL Server, MySQL, or SQLite).
βœ… Include necessary namespaces in your code.

using System;
using System.Data.SqlClient;  // For SQL Server
using MySql.Data.MySqlClient; // For MySQL
using System.Data.SQLite;     // For SQLite

πŸ₯” Setting Up the Database

Let’s create a table called Potatoes in the database. It will have the following fields:

Field Name Data Type Description
Id int Unique ID for each potato
Name varchar Name of the potato
Type varchar Type of the potato
PricePerKg decimal Price per kg

🎯 Create Table SQL Query:

CREATE TABLE Potatoes (
    Id INT PRIMARY KEY IDENTITY(1,1),
    Name NVARCHAR(50),
    Type NVARCHAR(50),
    PricePerKg DECIMAL(5,2)
);

🎯 Connection String

Here’s a sample connection string to connect to SQL Server:

string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";

βœ… Explanation:

  • Server – Where the database is located.
  • Database – Name of the database.
  • User Id – Username to access the database.
  • Password – Password for the user.

πŸ₯” C – Create (Insert New Data)

To add a new potato to the database, we use the INSERT SQL query.

🧠 Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("πŸ₯” Connected to the database!");

                // Insert query to add a new potato
                string query = "INSERT INTO Potatoes (Name, Type, PricePerKg) VALUES (@Name, @Type, @PricePerKg)";

                // Create command and add parameters
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@Name", "Golden Potato");
                command.Parameters.AddWithValue("@Type", "Yukon Gold");
                command.Parameters.AddWithValue("@PricePerKg", 3.5);

                // Execute query
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"βœ… {rowsAffected} row(s) inserted successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

πŸ₯” Connected to the database!
βœ… 1 row(s) inserted successfully!

πŸ“š R – Read (Retrieve Data)

To check available potatoes, we use the SELECT SQL query.

🧠 Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("πŸ₯” Connected to the database!");

                // Select query to read all potatoes
                string query = "SELECT Id, Name, Type, PricePerKg FROM Potatoes";

                // Create command
                SqlCommand command = new SqlCommand(query, connection);

                // Execute query and read data
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"πŸ₯” Id: {reader["Id"]}, Name: {reader["Name"]}, Type: {reader["Type"]}, Price: {reader["PricePerKg"]} per kg");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

πŸ₯” Connected to the database!
πŸ₯” Id: 1, Name: Golden Potato, Type: Yukon Gold, Price: 3.5 per kg

πŸ› οΈ U – Update (Modify Data)

To update the price of a potato, we use the UPDATE SQL query.

🧠 Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("πŸ₯” Connected to the database!");

                // Update query to change the price
                string query = "UPDATE Potatoes SET PricePerKg = @Price WHERE Name = @Name";

                // Create command and add parameters
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@Price", 4.0);
                command.Parameters.AddWithValue("@Name", "Golden Potato");

                // Execute query
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"βœ… {rowsAffected} row(s) updated successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

πŸ₯” Connected to the database!
βœ… 1 row(s) updated successfully!

❌ D – Delete (Remove Data)

To remove rotten potatoes from the database, we use the DELETE SQL query.

🧠 Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                Console.WriteLine("πŸ₯” Connected to the database!");

                // Delete query to remove a potato
                string query = "DELETE FROM Potatoes WHERE Name = @Name";

                // Create command and add parameters
                SqlCommand command = new SqlCommand(query, connection);
                command.Parameters.AddWithValue("@Name", "Golden Potato");

                // Execute query
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"βœ… {rowsAffected} row(s) deleted successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

πŸ₯” Connected to the database!
βœ… 1 row(s) deleted successfully!