public int ADD(Product model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Products_ADD", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@productname", model.ProductName);
cmd.Parameters.AddWithValue("@productprice", model.Price);
cmd.Parameters.AddWithValue("@quantity", model.Quantity);
cmd.Parameters.AddWithValue("@insertby", model.Insertby);
cmd.Parameters.AddWithValue("@insertdate", model.InserDate);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Modify(Employess model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Employess_Edit", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@employeeid", model.ID);
cmd.Parameters.AddWithValue("@name", model.Name);
cmd.Parameters.AddWithValue("@lastname", model.LastName);
cmd.Parameters.AddWithValue("@personalid", model.PersonalID);
cmd.Parameters.AddWithValue("@address", model.Adress);
cmd.Parameters.AddWithValue("@birthday", model.Birthday);
cmd.Parameters.AddWithValue("@email", model.Email);
cmd.Parameters.AddWithValue("@phonenumber", model.PhoneNumber);
cmd.Parameters.AddWithValue("@username", model.Username);
cmd.Parameters.AddWithValue("@password", model.Password);
cmd.Parameters.AddWithValue("@salary", model.Salary);
cmd.Parameters.AddWithValue("@updateby", model.UpdatedBy);
cmd.Parameters.AddWithValue("@updatedate", model.UpdateDate);
cmd.Parameters.AddWithValue("@updateno", model.LastUpdate);
var rowaffectd = cmd.ExecuteNonQuery();
return(rowaffectd);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Modify(Product model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Product_Edit", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@productid", model.ProductID);
cmd.Parameters.AddWithValue("@productname", model.ProductName);
cmd.Parameters.AddWithValue("@productprice", model.Price);
cmd.Parameters.AddWithValue("@productquantity", model.Quantity);
cmd.Parameters.AddWithValue("@updateby", model.UpdatedBy);
cmd.Parameters.AddWithValue("@updatedate", model.UpdateDate);
cmd.Parameters.AddWithValue("@updateno", model.LastUpdate);
int rowaffectd = cmd.ExecuteNonQuery();
return(rowaffectd);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public List <Computer> GetAll()
{
try
{
List <Computer> computers = null;
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "GetALL_Computers", CommandType.StoredProcedure))
{
computers = new List <Computer>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Computer computer = ToObject(reader);
computers.Add(computer);
}
}
}
}
return(computers);
}
catch (Exception e)
{
return(null);
}
}
public int GetTotalOrders(int billID)
{
try
{
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "GetTotalPriceOrders", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@billID", billID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
if (reader.Read())
{
StaticClass.TotalPrice = double.Parse(reader["shuma"].ToString());
}
}
}
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public List <Bill> Get(int id)
{
List <Bill> billr = null;
try
{
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "GetBill", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@computerID", id);
using (SqlDataReader reader = cmd.ExecuteReader())
{
billr = new List <Bill>();
while (reader.Read())
{
Bill bill = ToObject(reader);
billr.Add(bill);
}
}
}
}
return(billr);
}
catch (Exception e)
{
return(null);
}
}
public int GetBillId(Bill model)
{
try
{
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "GetBillID", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@computerId", model.ComputerId);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
if (reader.Read())
{
StaticClass.BillID = int.Parse(reader["BillID"].ToString());
}
}
}
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Modify(Computer model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Computers_Update", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@computerid", model.ComputerID);
cmd.Parameters.AddWithValue("@partid", model.PartID);
cmd.Parameters.AddWithValue("@priceperhour", model.PricePerHour);
cmd.Parameters.AddWithValue("@updateby", model.UpdatedBy);
cmd.Parameters.AddWithValue("@updatedate", model.UpdateDate);
cmd.Parameters.AddWithValue("@updateno", model.LastUpdate);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public Orders GetByName(int name)
{
Orders order = null;
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "GetByIdOrder", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@id", name);
//cmd.Parameters.AddWithValue("@name", name);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
order = new Orders();
if (reader.Read())
{
StaticClass.ID = int.Parse(reader["ProductID"].ToString());
StaticClass.Price = double.Parse(reader["ProductPrice"].ToString());
}
}
}
}
}
return(order);
}
catch (Exception e)
{
return(null);
}
}
public List <Product> GetAll()
{
try
{
List <Product> ProductResult = null;
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd =
SQLfunctions.Command(conn, cmdText: "Product_GETALL", CommandType.StoredProcedure))
{
ProductResult = new List <Product>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Product product = ToObject(reader);
ProductResult.Add(product);
}
}
}
}
return(ProductResult);
}
catch (Exception e)
{
return(null);
}
}
public List <Bill> GetAll()
{
try
{
List <Bill> billResult = null;
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "GETALL_Bill", CommandType.StoredProcedure))
{
billResult = new List <Bill>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Bill bill = ToObject(reader);
billResult.Add(bill);
}
}
}
return(billResult);
}
}
catch (Exception e)
{
return(null);
}
}
public int ADD(ComputerParts model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "ADD_ComputerParts", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@case", model.ComputerCase);
cmd.Parameters.AddWithValue("@mouse", model.ComputerMouse);
cmd.Parameters.AddWithValue("@keyboard", model.ComputerKeyboard);
cmd.Parameters.AddWithValue("@headsets", model.ComputerHeadSet);
cmd.Parameters.AddWithValue("@monitor", model.ComputerMonitor);
cmd.Parameters.AddWithValue("@mousepad", model.ComputerMousePad);
cmd.Parameters.AddWithValue("@processor", model.ComputerCPU);
cmd.Parameters.AddWithValue("@graphiccard", model.ComputerGraphicCard);
cmd.Parameters.AddWithValue("@motherboard", model.ComputerMotherboard);
cmd.Parameters.AddWithValue("@ram", model.ComputerRAM);
cmd.Parameters.AddWithValue("@ssd", model.ComputerSSD);
cmd.Parameters.AddWithValue("@hdd", model.ComputerHDD);
cmd.Parameters.AddWithValue("@psu", model.ComputerPSU);
cmd.Parameters.AddWithValue("@cooler", model.ComputerCooler);
cmd.Parameters.AddWithValue("@insertby", model.Insertby);
cmd.Parameters.AddWithValue("@insertdate", model.InserDate);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int ADD(Clients model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Clients_Insert", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@name", model.Name);
cmd.Parameters.AddWithValue("@lastname", model.LastName);
cmd.Parameters.AddWithValue("@personalid", model.PersonalID);
cmd.Parameters.AddWithValue("@address", model.Address);
cmd.Parameters.AddWithValue("@birthday", model.Birthday);
cmd.Parameters.AddWithValue("@email", model.Email);
cmd.Parameters.AddWithValue("@username", model.UserName);
cmd.Parameters.AddWithValue("@password", model.Password);
cmd.Parameters.AddWithValue("@balance", model.Balance);
cmd.Parameters.AddWithValue("@phonenumber", model.PhoneNumber);
cmd.Parameters.AddWithValue("@insertby", model.Insertby);
cmd.Parameters.AddWithValue("@insertdate", model.InserDate);
//cmd.Parameters.AddWithValue("@isactive", model.IsActive);
//cmd.Parameters.AddWithValue("@isguest", model.IsGuest);
int rowaffect = cmd.ExecuteNonQuery();
return(rowaffect);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public List <Clients> GetAll()
{
try
{
List <Clients> clientsResult = null;
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Clients_GetALL", CommandType.StoredProcedure))
{
clientsResult = new List <Clients>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Clients clients = ToObject(reader);
clientsResult.Add(clients);
}
}
}
}
return(clientsResult);
}
catch (Exception e)
{
return(null);
}
}
public List <Orders> GetAll()
{
try
{
List <Orders> orders = null;
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd =
SQLfunctions.Command(conn, cmdText: "GetAll_Order", CommandType.StoredProcedure))
{
orders = new List <Orders>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Orders order = ToObject(reader);
orders.Add(order);
}
}
}
}
return(orders);
}
catch (Exception e)
{
return(null);
}
}
public int ADD(Bill model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
//AddBill procedure and BillAdd with endTime
using (var cmd = SQLfunctions.Command(con, cmdText: "AddBill", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@employee", model.EmployeeUsername);
cmd.Parameters.AddWithValue("@computerID", model.ComputerId);
cmd.Parameters.AddWithValue("@client", model.ClientUsername);
cmd.Parameters.AddWithValue("@startTime", model.StartTime);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public List <Employess> GetAll()
{
try
{
List <Employess> EmployessResult = null;
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "Employess_GetALL", CommandType.StoredProcedure))
{
EmployessResult = new List <Employess>();
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Employess employess = ToObject(reader);
EmployessResult.Add(employess);
}
}
}
}
return(EmployessResult);
}
catch (Exception e)
{
return(null);
}
}
//public string _connString = ConfigurationManager.ConnectionStrings["Game-Center"].ConnectionString;
public int ADD(Employess model)
{
try
{
using (var conn = SQLfunctions.GetConnection())
{
using (var command = SQLfunctions.Command(conn, cmdText: "UserName_Check",
cmdType: CommandType.StoredProcedure))
{
command.Parameters.AddWithValue("@username", model.Username);
var result = command.ExecuteScalar();
result = (result == DBNull.Value) ? null : result;
int res = Convert.ToInt32(result);
if (result != null)
{
//return -1;
MessageBox.Show($"This UserName Exits: {model.Username}");
}
else
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd =
SQLfunctions.Command(con, cmdText: "Add_Employess",
CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@name", model.Name);
cmd.Parameters.AddWithValue("@personalid", model.PersonalID);
cmd.Parameters.AddWithValue("@lastname", model.LastName);
cmd.Parameters.AddWithValue("@birthday", model.Birthday);
cmd.Parameters.AddWithValue("@email", model.Email);
cmd.Parameters.AddWithValue("@phonenumber", model.PhoneNumber);
cmd.Parameters.AddWithValue("@username", model.Username);
cmd.Parameters.AddWithValue("@password", model.Password);
cmd.Parameters.AddWithValue("@salary", model.Salary);
cmd.Parameters.AddWithValue("@insertBy", model.Insertby);
cmd.Parameters.AddWithValue("@insertDate", model.InserDate);
cmd.Parameters.AddWithValue("@address", model.Adress);
int RowAffected = cmd.ExecuteNonQuery();
return(RowAffected);
}
}
}
return(res);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Remove(int ID)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Delete_EmployeeByID", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("employeeid", ID);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception)
{
return(-1);
}
}
public int Remove(int ID)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Delete_ClientsByID", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@clientid", ID);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public int IsActiveFalse(Computer model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "IsActiveFalse", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@computerId", model.ComputerID);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Balance(Clients model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Balance", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@username", model.UserName);
cmd.Parameters.AddWithValue("@price", model.Price);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public Product GetByID(int ID)
{
Product product = null;
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "ProductGetByID", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("productid", ID);
using (SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
product = new Product();
if (reader.Read())
{
product.ProductID = int.Parse(reader["ProductID"].ToString());
product.ProductName = reader["ProductName"].ToString();
product.Quantity = int.Parse(reader["ProductQuantity"].ToString());
if (reader["ProductPrice"] != DBNull.Value)
{
product.Price = decimal.Parse(reader["ProductPrice"].ToString());
}
}
}
}
}
}
return(product);
}
catch (Exception e)
{
return(null);
}
}
public int GetTotal(Bill model)
{
try
{
using (var conn = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(conn, cmdText: "Total_ADD", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@billID", model.BillID);
cmd.Parameters.AddWithValue("@total", model.Total);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public Employess Login(string username, string password)
{
Employess employess = null;
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "LoginEmployee", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("username", username);
cmd.Parameters.AddWithValue("password", password);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
employess = ToObject(reader);
}
}
}
}
return(employess);
}
public int ADD(Computer model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "Add_Computers", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@partid", model.PartID);
cmd.Parameters.AddWithValue("@priceperhour", model.PricePerHour);
cmd.Parameters.AddWithValue("@insertby", model.Insertby);
cmd.Parameters.AddWithValue("@insertdate", model.InserDate);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
public int Modify(ComputerParts model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "PCparts_Edit", cmdType: CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@partid", model.PartID);
cmd.Parameters.AddWithValue("@case", model.ComputerCase);
cmd.Parameters.AddWithValue("@mouse", model.ComputerMouse);
cmd.Parameters.AddWithValue("@keyboard", model.ComputerKeyboard);
cmd.Parameters.AddWithValue("@headsets", model.ComputerHeadSet);
cmd.Parameters.AddWithValue("@monitor", model.ComputerMonitor);
cmd.Parameters.AddWithValue("@mousepad", model.ComputerMousePad);
cmd.Parameters.AddWithValue("@processor", model.ComputerCPU);
cmd.Parameters.AddWithValue("@graphiccard", model.ComputerGraphicCard);
cmd.Parameters.AddWithValue("@motherboard", model.ComputerMotherboard);
cmd.Parameters.AddWithValue("@ram", model.ComputerRAM);
cmd.Parameters.AddWithValue("@ssd", model.ComputerSSD);
cmd.Parameters.AddWithValue("@hdd", model.ComputerHDD);
cmd.Parameters.AddWithValue("@psu", model.ComputerPSU);
cmd.Parameters.AddWithValue("@cooler", model.ComputerCooler);
cmd.Parameters.AddWithValue("@updateby", model.UpdatedBy);
cmd.Parameters.AddWithValue("@updatedate", model.UpdateDate);
cmd.Parameters.AddWithValue("@updateno", model.LastUpdate);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
public int ADD(Orders model)
{
try
{
using (var con = SQLfunctions.GetConnection())
{
using (var cmd = SQLfunctions.Command(con, cmdText: "OrderAdd", CommandType.StoredProcedure))
{
cmd.Parameters.AddWithValue("@productId", model.ProductId);
cmd.Parameters.AddWithValue("@billId", model.BillId);
cmd.Parameters.AddWithValue("@quantity", model.Quantity);
cmd.Parameters.AddWithValue("@price", model.Price);
int rowaffected = cmd.ExecuteNonQuery();
return(rowaffected);
}
}
}
catch (Exception e)
{
return(-1);
}
}
