Connecting to Database - potatoscript/csharp GitHub Wiki

🥔 Connecting to Databases in C# 🥔

📚 What is a Database?

A database is like a potato storage room where you keep all your delicious potatoes sorted by type, size, and freshness. 🥔📦

When you need a potato, you go into the storage room, find it, and bring it out. Similarly, when you need information (data), you go to the database, look for the information, and retrieve it.

🥔 Why Use a Database?

✅ Store data efficiently – Like storing many types of potatoes neatly.
✅ Retrieve data quickly – Find the exact potato (or data) you need!
✅ Update data easily – Add, modify, or delete potatoes without hassle.

🗄️ Types of Databases in C#

In C#, you can connect to different types of databases:

Database Type	Description
🥔 SQL Server	A powerful, enterprise-level database used in many companies.
🍟 MySQL	Open-source, widely used for websites and applications.
🥔 SQLite	Lightweight and portable, perfect for small applications.
🥔 PostgreSQL	Advanced open-source database with powerful features.
🍠 Oracle	Enterprise-level, widely used in large organizations.

🎯 How to Connect to a Database in C#

To connect to a database, we need to follow these steps:

📝 Step 1: Add Required Namespaces

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

📝 Step 2: Define the Connection String

A connection string is like the key to your potato storage room. It tells your program where the database is and how to access it.

🥔 Connection String Format:

string connectionString = "Server=localhost;Database=PotatoDB;User Id=root;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.

📚 Connecting to Different Databases in C#

🎯 1. Connect to SQL Server

To connect to a SQL Server database, use the SqlConnection class.

🧠 Example:

using System;
using System.Data.SqlClient;

class Program
{
    static void Main()
    {
        // Connection string for SQL Server
        string connectionString = @"Server=localhost;Database=PotatoDB;User Id=sa;Password=potato123;";
        
        // Create a SQL connection
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                // Open connection
                connection.Open();
                Console.WriteLine("🥔 Connection to SQL Server successful!");

                // Query to get all potatoes
                string query = "SELECT Name, Type, PricePerKg FROM Potatoes";
                
                // Create SQL command
                SqlCommand command = new SqlCommand(query, connection);
                
                // Execute query and read data
                using (SqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"🥔 Name: {reader["Name"]}, Type: {reader["Type"]}, Price: {reader["PricePerKg"]} per kg");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

🥔 Connection to SQL Server successful!
🥔 Name: Golden Potato, Type: Yukon Gold, Price: 3.5 per kg
🥔 Name: Red Potato, Type: Red Bliss, Price: 3.0 per kg

🎯 2. Connect to MySQL

To connect to MySQL, use the MySqlConnection class.

🧠 Example:

using System;
using MySql.Data.MySqlClient;

class Program
{
    static void Main()
    {
        // Connection string for MySQL
        string connectionString = "Server=localhost;Database=PotatoDB;User Id=root;Password=potato123;";
        
        // Create a MySQL connection
        using (MySqlConnection connection = new MySqlConnection(connectionString))
        {
            try
            {
                // Open connection
                connection.Open();
                Console.WriteLine("🥔 Connection to MySQL successful!");

                // Query to get all potatoes
                string query = "SELECT Name, Type, PricePerKg FROM Potatoes";
                
                // Create MySQL command
                MySqlCommand command = new MySqlCommand(query, connection);
                
                // Execute query and read data
                using (MySqlDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"🥔 Name: {reader["Name"]}, Type: {reader["Type"]}, Price: {reader["PricePerKg"]} per kg");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

🥔 Connection to MySQL successful!
🥔 Name: Sweet Potato, Type: Japanese Yam, Price: 4.0 per kg

🎯 3. Connect to SQLite

To connect to a SQLite database, use the SQLiteConnection class.

🧠 Example:

using System;
using System.Data.SQLite;

class Program
{
    static void Main()
    {
        // Connection string for SQLite
        string connectionString = "Data Source=PotatoDB.sqlite;Version=3;";
        
        // Create a SQLite connection
        using (SQLiteConnection connection = new SQLiteConnection(connectionString))
        {
            try
            {
                // Open connection
                connection.Open();
                Console.WriteLine("🥔 Connection to SQLite successful!");

                // Query to get all potatoes
                string query = "SELECT Name, Type, PricePerKg FROM Potatoes";
                
                // Create SQLite command
                SQLiteCommand command = new SQLiteCommand(query, connection);
                
                // Execute query and read data
                using (SQLiteDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        Console.WriteLine($"🥔 Name: {reader["Name"]}, Type: {reader["Type"]}, Price: {reader["PricePerKg"]} per kg");
                    }
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

🥔 Connection to SQLite successful!
🥔 Name: Russet Potato, Type: Russet, Price: 2.8 per kg

🎯 Inserting Data into Databases

We can also insert data (add a new potato!) into the database.

🧠 Example (Insert into SQL Server):

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 SQL Server!");

                // Insert query
                string query = "INSERT INTO Potatoes (Name, Type, PricePerKg) VALUES ('Purple Potato', 'Purple Majesty', 5.0)";
                
                // Create SQL command
                SqlCommand command = new SqlCommand(query, connection);
                
                // Execute query
                int rowsAffected = command.ExecuteNonQuery();
                Console.WriteLine($"✅ {rowsAffected} row(s) inserted successfully!");
            }
            catch (Exception ex)
            {
                Console.WriteLine($"⚠️ Error: {ex.Message}");
            }
        }
    }
}

🎁 Output:

🥔 Connected to SQL Server!
✅ 1 row(s) inserted successfully!

📝 Updating Data in Databases

You can update the details of a potato if it’s getting old! 🥔😅

🧠 Example (Update in SQL Server):

string query = "UPDATE Potatoes SET PricePerKg = 4.5 WHERE Name = 'Golden Potato'";

📝 Deleting Data from Databases

Sometimes, bad potatoes need to go! 🥔💀

🧠 Example (Delete from SQL Server):

string query = "DELETE FROM Potatoes WHERE Name = 'Red Potato'";