Notes

using System;
using System.Collections.Generic;
using genericRepo.Models;
using Microsoft.Extensions.Configuration;
using MySql.Data.MySqlClient;

namespace genericRepo.Sql
{
public class ProductDAO : IProductDAO
{
private readonly IConfiguration _configuration;
private readonly string _connectionString;
public ProductDAO(IConfiguration configuration)
{
_configuration = configuration;
_connectionString = _configuration.GetConnectionString("DefaultConnection");
}

public List<Product> GetAllProduct(string model){
using(MySqlConnection conn = new MySqlConnection(_connectionString)){
var products = new List<Product>();

conn.Open();

string cmdText = "Select * from Products";

MySqlCommand cmd = new MySqlCommand(cmdText,conn);

var reader = cmd.ExecuteReader();

while(reader.Read()){
var product = new Product();
product.Id = Convert.ToInt32(reader["Id"]);
product.ProductName = reader["ProductName"].ToString();

products.Add(product);
}

return products;
}
}

public List<Product> GetBy(string name){
using(MySqlConnection conn = new MySqlConnection(_connectionString)){
var products = new List<Product>();

conn.Open();

// string cmdText = "Select * from Products where ProductName='"+name+"'";

string cmdText = "Select * from products where ProductName = @ProductName";

MySqlCommand cmd = new MySqlCommand(cmdText,conn);

cmd.Parameters.AddWithValue("@ProductName",name);

var reader = cmd.ExecuteReader();

while(reader.Read()){
var product = new Product();
product.Id = Convert.ToInt32(reader["Id"]);
product.ProductName = reader["ProductName"].ToString();

products.Add(product);
}

return products;
}
}


public bool InsertProduct(Product product){
using(var conn = new MySqlConnection(_connectionString)){
conn.Open();

string cmdText = "Insert into Products (ProductName) values(@ProductName)";

MySqlCommand cmd = new MySqlCommand(cmdText,conn);

cmd.Parameters.AddWithValue("@ProductName",product.ProductName);

int rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected>0){
return true;
}else{
return false;
}
}
}

public bool UpdateProduct(Product product){
using(var conn = new MySqlConnection(_connectionString)){
conn.Open();

string cmdText = "Update Products set ProductName = @ProductName where Id = @Id";

MySqlCommand cmd = new MySqlCommand(cmdText,conn);

cmd.Parameters.AddWithValue("@ProductName",product.ProductName);
cmd.Parameters.AddWithValue("@Id",product.Id);

int rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected>0){
return true;
}else{
return false;
}
}
}

public bool DeleteProduct(int id){
using(var conn = new MySqlConnection(_connectionString)){
conn.Open();

string cmdText = "Delete from Products where Id = @Id";

MySqlCommand cmd = new MySqlCommand(cmdText,conn);

cmd.Parameters.AddWithValue("@Id",id);

int rowsAffected = cmd.ExecuteNonQuery();

if(rowsAffected>0){
return true;
}else{
return false;
}
}
}

public List<Product> GetAllProduct()
{
throw new NotImplementedException();
}
}
}