ConectDB.cs

using System;
using System.Collections.Generic;
using System.Linq;
using System.Runtime.InteropServices;
using System.Text;
using System.Threading.Tasks;
using static System.Runtime.InteropServices.JavaScript.JSType;
using System.Data.SqlClient;
using System.Data;
using Microsoft.Data.SqlClient;
//using Microsoft.Data.SqlClient.SqlException;



namespace Manager
{
    class ConectDB
    {

        public int InsertDataCategory(string ConnectionString)
        {
            string query = "INSERT INTO Categories(CategoryName)" +
                "VALUES(@CategoryName)";
            int rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(ConnectionString))
                while (true)
                {
                    Console.WriteLine("Insert category Name");
                    string CategoryName = Console.ReadLine();

                    using (SqlCommand cmd = new SqlCommand(query, cn))
                    {
                        
                        cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 20).Value= CategoryName;

                        cn.Open();
                        rowsAffected += cmd.ExecuteNonQuery();
                        cn.Close();
                    }
                    Console.WriteLine("Do you want to add another category? Type y/n");
                    string? result = Console.ReadLine();
                    if (result.Equals("n"))
                    {
                        if (rowsAffected == 1)
                        {
                            Console.WriteLine("1 row affected");
                        }
                        else
                        {
                            Console.WriteLine(rowsAffected + " rows affected");
                        }
                        break;
                    }
                }

            return rowsAffected;
        }
        public int InsertDataProduct(string ConnectionString)
        {
            string query = "INSERT INTO Products(CategoryID, ProductName,ProductDescription,price,ImgPath)" +
                "VALUES(@CategoryID, @ProductName, @ProductDescription, @price, @ImgPath)";
            int rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(ConnectionString))
                while (true)
                {
                    Console.WriteLine("Insert category Name");
                    string CategoryName = Console.ReadLine();
                    Console.WriteLine("Insert Product Name");
                    string ProductName = Console.ReadLine();
                    Console.WriteLine("Insert Product Description");
                    string ProductDescription = Console.ReadLine();
                    Console.WriteLine("Insert price");
                    float price = float.Parse(Console.ReadLine());
                    Console.WriteLine("Insert ImgPath");
                    string ImgPath = Console.ReadLine();
                 
                    int CategoryID = getCategoryId(ConnectionString, CategoryName);
                    if(CategoryID == -1)
                    {
                        Console.WriteLine("Error getting Category ID.");
                        return -1;
                    }
                    else if (CategoryID == -2)
                    {
                        InsertDataCategory(ConnectionString);
                    }
                    using (SqlCommand cmd = new SqlCommand(query, cn))
                    {
                        cmd.Parameters.Add("@CategoryID", SqlDbType.Int).Value=CategoryID;
                        cmd.Parameters.Add("@ProductName", SqlDbType.VarChar, 20).Value=ProductName;
                        cmd.Parameters.Add("@ProductDescription", SqlDbType.VarChar, 20).Value=ProductDescription;
                        cmd.Parameters.Add("@price", SqlDbType.Float).Value=price;
                        cmd.Parameters.Add("@ImgPath", SqlDbType.VarChar, 50).Value=ImgPath;
                        cn.Open();
                        rowsAffected += cmd.ExecuteNonQuery();
                        cn.Close();
                    }
                    Console.WriteLine("Do you want to add another product? Type y/n");
                    string? result = Console.ReadLine();
                    if (result.Equals("n"))
                    {
                        if (rowsAffected == 1)
                        {
                            Console.WriteLine("1 row affected");
                        }
                        else
                        {
                            Console.WriteLine(rowsAffected + " rows affected");
                        }
                        break;
                    }
                }

            return rowsAffected;
        }

        private int getCategoryId(string ConnectionString, string categoryName)
        {
            string query = "SELECT ID FROM Categories WHERE CategoryName = @CategoryName";
            int rowsAffected = 0;
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    cmd.Parameters.Add("@CategoryName", SqlDbType.VarChar, 20).Value= categoryName;
                    try
                    {
                        cn.Open();
                        SqlDataReader reader = cmd.ExecuteReader();
                        if (reader.Read())
                        {
                            int categoryId = reader.GetInt32(0);
                            return categoryId;
                        }
                        else
                        {
                            Console.WriteLine("Category not found.");
                            return -2;
                        }
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine("Error: " + ex.Message);
                        return -1;
                    }
                    finally
                    {
                        cn.Close();
                    }

                }

            }


        }
        public void printCategories(string ConnectionString)
        {
            string query = "select * from Categories";
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    try
                    {
                        cn.Open();
                        SqlDataReader r = cmd.ExecuteReader();
                        while (r.Read()) {
                            Console.WriteLine("\t{0}\t{1}",
                                r[0], r[1]);
                        }
                        r.Close();
                    }
                    catch(SqlException ex) 
                    {
                        Console.WriteLine(ex.Message);
                    }
                    Console.ReadLine();
                }
            }
        }

        public void printProducts(string ConnectionString)
        {
            string query = "select * from Products";
            using (SqlConnection cn = new SqlConnection(ConnectionString))
            {
                using (SqlCommand cmd = new SqlCommand(query, cn))
                {
                    try
                    {
                        cn.Open();
                        SqlDataReader r = cmd.ExecuteReader();
                        while (r.Read())
                        {
                            Console.WriteLine("\t{0}\t{1}\t{2}\t{3}\t{4}\t{5}",
                                r[0], r[1], r[2], r[3], r[4], r[5]);
                        }
                        r.Close();
                    }
                    catch (SqlException ex)
                    {
                        Console.WriteLine(ex.Message);
                    }
                    Console.ReadLine();
                }
            }
        }

    }
}