//get Sizes
public static List <Size> GetAll()
{
var sqlQuery = $"SELECT * FROM sizes WHERE Deleted = False";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new Size
{
SizeId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["SizeId"])),
SizeDesc = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["Size"])),
}).ToList());
}
public static IEnumerable <Role> GetUserRoles()
{
var sqlQuery = $"SELECT * FROM Roles";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new Role
{
RoleId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["RoleId"])),
RoleDiscription = SqlHelperFunctions.NullCheck(row["RoleDiscription"])
}));
}
//get Categories
public static List <Category> GetAll()
{
var sqlQuery = $"SELECT * FROM categories WHERE Deleted = FALSE";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new Category
{
CategoryId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["CategoryId"])),
CategoryDesc = SqlHelperFunctions.NullCheck(row["CategoryDesc"]),
}).ToList());
}
//get Nicotine
public static List <Nicotine> GetAll()
{
var sqlQuery = $"SELECT * FROM nicotine WHERE Deleted = False";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new Nicotine
{
NicotineId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["NicotineId"])),
NicotineStrength = Convert.ToInt32(Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Nicotine"]))),
}).ToList());
}
//get Products
public static List <Product> GetAll()
{
var sqlQuery = $"SELECT * FROM products WHERE Deleted = False";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new Product
{
ProductId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ProductId"])),
Name = SqlHelperFunctions.NullCheck(row["Name"]),
Amount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Amount"])),
Discount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Discount"]))
}).ToList());
}
//get users
public static List <User> GetAll()
{
var sqlQuery = $"SELECT * FROM Users WHERE Deleted = False";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new User
{
UserId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["UserId"])),
RoleId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["RoleId"])),
ProfilePictureId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ProfilePictureId"])),
Name = SqlHelperFunctions.NullCheck(row["Name"]),
Surname = SqlHelperFunctions.NullCheck(row["Surname"]),
Email = SqlHelperFunctions.NullCheck(row["Email"]),
}).ToList());
}
//get Category
public static Category Get(Category category)
{
var sqlQuery = $"SELECT * FROM categories WHERE CategoryId = @CategoryId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@CategoryId", category.CategoryId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
category.CategoryId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["CategoryId"]));
category.CategoryDesc = SqlHelperFunctions.NullCheck(dt.Rows[0]["CategoryDesc"]);
}
;
return(category);
}
//get Size
public static Size Get(Size size)
{
var sqlQuery = $"SELECT * FROM sizes WHERE SizeId = @SizeId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@SizeId", size.SizeId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
size.SizeId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["SizeId"]));
size.SizeDesc = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["Size"]));
}
;
return(size);
}
//get Nicotine
public static Nicotine Get(Nicotine nicotine)
{
var sqlQuery = $"SELECT * FROM nicotine WHERE NicotineId = @NicotineId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@NicotineId", nicotine.NicotineId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
nicotine.NicotineId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["NicotineId"]));
nicotine.NicotineStrength = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["Nicotine"]));
}
;
return(nicotine);
}
//get Color
public static Color Get(Color color)
{
var sqlQuery = $"SELECT * FROM colors WHERE ColorId = @ColorId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@ColorId", color.ColorId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
color.ColorId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["ColorId"]));
color.ColorDesc = SqlHelperFunctions.NullCheck(dt.Rows[0]["Color"]);
}
;
return(color);
}
// Get Sizes
public static List <ProductSizeTieListDto> GetAllProductSizes()
{
var sqlQuery = @" SELECT pst.*, s.* FROM productsizetie pst
INNER JOIN sizes s ON s.sizeId = pst.sizeId
WHERE pst.Deleted = False
";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new ProductSizeTieListDto
{
ProductSizeTieId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ProductSizeTieId"])),
SizeId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["SizeId"])),
Size = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["Size"])),
SizeAmount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Amount"])),
SizeDiscount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Discount"])),
SizeStock = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["Stock"])),
}).ToList());
}
// Get Nicotine
public static List <ProductNicotineTieListDto> GetAllProductNicotine()
{
var sqlQuery = @" SELECT pnt.*, n.* FROM productnicotinetie pnt
INNER JOIN nicotine n ON n.nicotineId = pnt.nicotineId
WHERE pnt.Deleted = False
";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new ProductNicotineTieListDto
{
ProductNicotineTieId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ProductNicotineTieId"])),
NicotineId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["NicotineId"])),
Nicotine = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Nicotine"])),
NicotineAmount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Amount"])),
NicotineDiscount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Discount"])),
NicotineStock = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["Stock"])),
}).ToList());
}
// Get Colors
public static List <ProductColorTieListDto> GetAllProductColors()
{
var sqlQuery = @" SELECT pct.*, c.* FROM productcolortie pct
INNER JOIN colors c ON c.colorId = pct.colorId
WHERE pct.Deleted = False
";
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, null, Database.ReturnType.DataTable, false);
return(dt.AsEnumerable().Select(row =>
new ProductColorTieListDto
{
ProductColorTieId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ProductColorTieId"])),
ColorId = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["ColorId"])),
Color = SqlHelperFunctions.NullCheck(row["Color"]),
ColorAmount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Amount"])),
ColorDiscount = Convert.ToDouble(SqlHelperFunctions.NullCheck(row["Discount"])),
ColorStock = Convert.ToInt32(SqlHelperFunctions.NullCheck(row["Stock"])),
}).ToList());
}
//get user
public static User Get(User user)
{
var sqlQuery = $"SELECT * FROM Users WHERE UserId = @UserId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@UserId", user.UserId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
user.UserId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["UserId"]));
user.Name = SqlHelperFunctions.NullCheck(dt.Rows[0]["Name"]);
user.Surname = SqlHelperFunctions.NullCheck(dt.Rows[0]["Surname"]);
user.Email = SqlHelperFunctions.NullCheck(dt.Rows[0]["Email"]);
user.ProfilePictureId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["ProfilePictureId"]));
user.RoleId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["RoleId"]));
}
;
return(user);
}
//get Product
public static Product Get(Product product)
{
var sqlQuery = $"SELECT * FROM products WHERE ProductId = @ProductId";
var sqlParams = new List <MySqlParameter>()
{
new MySqlParameter("@ProductId", product.ProductId)
};
var dt = (DataTable)Db.ExecDb(sqlQuery, Database.DatabaseProcedureType.Text, sqlParams.ToArray(), Database.ReturnType.DataTable, false);
if (dt.Rows.Count > 0)
{
product.ProductId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["ProductId"]));
product.CategoryId = Convert.ToInt32(SqlHelperFunctions.NullCheck(dt.Rows[0]["CategoryId"]));
product.Name = SqlHelperFunctions.NullCheck(dt.Rows[0]["Name"]);
product.Amount = Convert.ToDouble(SqlHelperFunctions.NullCheck(dt.Rows[0]["Amount"]));
product.Discount = Convert.ToDouble(SqlHelperFunctions.NullCheck(dt.Rows[0]["Discount"]));
product.Description = SqlHelperFunctions.NullCheck(dt.Rows[0]["Description"]);
}
;
return(product);
}
