public static Administrator GetByUserId(long userId) { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); Administrator administrator; string query = $"SELECT * FROM administrators WHERE user_id = @userId"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { cmd.Parameters.AddWithValue("@userId", userId); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long id = Convert.ToInt64(reader["id"]); User user = User.GetById(Convert.ToInt64(reader["user_id"])); administrator = new Administrator(id, user); dbConnection.CloseConnection(); return(administrator); } } dbConnection.CloseConnection(); return(null); }
public void CarregarGV() { if (con.OpenConnection() == true) { try { string query = "SELECT r.id, r.startDate, r.endDate, r.STATUS, u.NAME," + " s.address AS destination, s1.address AS origin FROM rent r" + " LEFT join user u ON u.id = r.userId" + " left join station s ON s.id = r.destinationId" + " left join station s1 ON s1.id = r.startStationId WHERE u.id =" + id + " order by r.id desc"; MySqlCommand cmd = new MySqlCommand(query, con.getConnection()); DataTable dataTable = new DataTable(); MySqlDataAdapter da = new MySqlDataAdapter(cmd); da.Fill(dataTable); GridView1.DataSource = dataTable; GridView1.DataBind(); con.CloseConnection(); } catch { } } else { Console.WriteLine("Connection Failed"); } }
public static Department GetById(long id) { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); string query = $"SELECT * FROM departments WHERE id = @id LIMIT 1"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { cmd.Parameters.AddWithValue("@id", id); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { string name = reader["name"].ToString(); int colIndex = reader.GetOrdinal("supervisor_id"); long supervisorId = 0; if (!reader.IsDBNull(colIndex)) { supervisorId = Convert.ToInt64(reader["supervisor_id"]); } dbConnection.CloseConnection(); return(new Department(id, name, supervisorId)); } } dbConnection.CloseConnection(); return(null); }
public static List <Department> GetAll() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); List <Department> departments = new List <Department>(); string query = "SELECT * FROM departments"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long id = Convert.ToInt64(reader["id"]); string name = reader["name"].ToString(); long supervisorId = 0; int colIndex = reader.GetOrdinal("supervisor_id"); if (!reader.IsDBNull(colIndex)) { supervisorId = Convert.ToInt64(reader["supervisor_id"]); } departments.Add(new Department(id, name, supervisorId)); } } dbConnection.CloseConnection(); return(departments); }
public void Clubs() { labelText.Text = "Club"; Type = "Club"; string query = "select clubs.name from clubs order by clubs.id"; if (_connection.OpenConnection()) { cbItems.Items.Add(""); using (var cmd = new MySqlCommand(query, DBconnection.Instance.Connection)) { MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { cbItems.Items.Add(dataReader["name"].ToString()); } } _connection.CloseConnection(); } else { return; } }
public static Dictionary <string, int> GetSoldQuantity() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); Dictionary <string, int> productNameSoldQuantity = new Dictionary <string, int>(); string query = @" SELECT SUM(od.quantity) as quantity, p.name as name FROM orders as o INNER JOIN order_details as od ON o.id = od.order_id INNER JOIN products as p ON od.product_id = p.id GROUP BY od.product_id; "; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { //cmd.Parameters.AddWithValue("@productId", productId); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { productNameSoldQuantity.Add(reader["name"].ToString(), Convert.ToInt32(reader["quantity"])); } dbConnection.CloseConnection(); return(productNameSoldQuantity); } }
public static Dictionary <string, int> GetProductRevenue() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); Dictionary <string, int> productNamerevenue = new Dictionary <string, int>(); string query = @" SELECT p.name, od.quantity*(p.selling_price-p.buying_price) as revenue FROM `order_details` as od INNER JOIN products as p ON od.product_id = p.id GROUP BY(p.id) "; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { //cmd.Parameters.AddWithValue("@productId", productId); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { productNamerevenue.Add(reader["name"].ToString(), Convert.ToInt32(reader["revenue"])); } dbConnection.CloseConnection(); return(productNamerevenue); } }
public static Department GetByName(string name) { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); string query = $"SELECT * FROM departments WHERE name = @name"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { var nameParam = cmd.Parameters.AddWithValue("@name", name); MySqlDataReader dataReader = cmd.ExecuteReader(); while (dataReader.Read()) { long id = Convert.ToInt64(dataReader["id"]); long supervisorId = 0; if (dataReader["supervisor_id"] != DBNull.Value) { supervisorId = Convert.ToInt64(dataReader["supervisor_id"]); } return(new Department(id, name, supervisorId)); } } dbConnection.CloseConnection(); return(null); }
public static Dictionary <string, int> GetProductsByDepartment() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); Dictionary <string, int> result = new Dictionary <string, int>(); string query = @" SELECT COUNT(*) as num, d.Name as name FROM products as p INNER JOIN departments as d ON p.Department_id = d.Id GROUP BY d.Name; "; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { result.Add(reader["name"].ToString(), Convert.ToInt32(reader["num"])); } } dbConnection.CloseConnection(); return(result); }
public static List <StockRequest> GetAllPendingRequests() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); List <StockRequest> stockRequests = new List <StockRequest>(); string query = "SELECT id, quantity, completed, product_id, user_id FROM stock_requests;"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { int id = Convert.ToInt32(reader["id"]); int quantity = Convert.ToInt32(reader["quantity"]); int completed = Convert.ToInt32(reader["completed"]); int product_id = Convert.ToInt32(reader["product_id"]); int user_id = Convert.ToInt32(reader["user_id"]); if (completed == 0) { stockRequests.Add(new StockRequest(id, quantity, completed, product_id, user_id)); } } dbConnection.CloseConnection(); return(stockRequests); } }
public static List <LeaveRequest> GetAll() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); List <LeaveRequest> requests = new List <LeaveRequest>(); string query = "SELECT * FROM leaves WHERE pending = 1"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long id = Convert.ToInt64(reader["id"]); int workerId = Convert.ToInt32(reader["worker_id"]); int accepted = Convert.ToInt32(reader["approved"]); int pending = Convert.ToInt32(reader["pending"]); DateTime from = DateTime.Parse(reader["from"].ToString()); DateTime to = DateTime.Parse(reader["to"].ToString()); string description = reader["description"].ToString(); requests.Add(new LeaveRequest(id, workerId, from, to, accepted, pending, description)); } } dbConnection.CloseConnection(); return(requests); }
ICollection CreateDataSource() { DataView dv = null; // Create a table to store data for the DropDownList control. DataTable dt = new DataTable(); // Define the columns of the table. dt.Columns.Add(new DataColumn("StationTextField", typeof(String))); dt.Columns.Add(new DataColumn("StationValueField", typeof(String))); // Populate the table with sample values. try { MySqlDataReader myReader = con.listStations(); while (myReader.Read()) { int id = Convert.ToInt32(myReader["id"]); string address = Convert.ToString(myReader["address"]); int numOfBikes = Convert.ToInt32(myReader["numOfBikes"]); dt.Rows.Add(CreateRow(" ____" + address + "___ Nº of bikes available: " + numOfBikes, id.ToString(), dt)); } con.CloseConnection(); // Create a DataView from the DataTable to act as the data source // for the DropDownList control. dv = new DataView(dt); return(dv); } catch (Exception ex) { Console.WriteLine(ex); return(dv); } }
public static Product GetById(int productId) { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); Product product; string query = @" SELECT products.id AS p_id, products.name AS p_name, description, buying_price, selling_price, quantity, min_quantity, departments.name AS d_name FROM products INNER JOIN departments ON products.department_id = departments.id WHERE products.id = @id "; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { cmd.Parameters.AddWithValue("@id", productId); MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long id = Convert.ToInt64(reader["p_id"]); string name = reader["p_name"].ToString(); string description = reader["description"].ToString(); double buyingPrice = Convert.ToDouble(reader["buying_price"]); double sellingPrice = Convert.ToDouble(reader["selling_price"]); int quantity = Convert.ToInt32(reader["quantity"]); int minQuantity = Convert.ToInt32(reader["min_quantity"]); string department = reader["d_name"].ToString(); product = new Product(id, name, description, buyingPrice, sellingPrice, quantity, minQuantity, department); dbConnection.CloseConnection(); return(product); } dbConnection.CloseConnection(); return(null); } }
// funkcja która zwraca pojedyńczy obiekt do wyświetlania dwuklikiem public object GetItem(int id, string table) { if (_connection.OpenConnection()) { Dictionary <string, object> par = new Dictionary <string, object>() { { "@id", id } }; if (table == "Players") { object result = _connection.GetPlayer($"select players.id ID, players.name Firstname, lastname Lastname," + $"dateofbirth Dateofbirth, position Position, height Height, weight Weight, nationality Nationality, clubs.name Club" + $" from players, clubs where players.club = clubs.id and players.id=@id", par); _connection.CloseConnection(); return(result); } else if (table == "Clubs") { object result = _connection.GetClub($"select clubs.id ID, clubs.name Club, clubs.city City," + $" clubs.founded Founded, clubs.active League from clubs where clubs.id=@id", par); _connection.CloseConnection(); return(result); } else if (table == "Kits") { object result = _connection.GetKit($"select kits.id ID, kits.home Homekit, kits.away Awaykit," + $"kits.clubcolours Clubcolours, clubs.name Club from kits, clubs where kits.club = clubs.id and kits.id=@id", par); _connection.CloseConnection(); return(result); } else if (table == "Coaches") { object result = _connection.GetCoach($"select coaches.id ID, coaches.name Firstname, coaches.lastname Lastname," + $"coaches.dateofbirth Dateofbirth, coaches.nationality Nationality, clubs.name Club from coaches, clubs where coaches.club = clubs.id and coaches.id=@id", par); _connection.CloseConnection(); return(result); } else if (table == "Stadiums") { object result = _connection.GetStadium($"SELECT stadiums.id ID, stadiums.name Name, stadiums.city City, " + $"stadiums.capacity Capacity, stadiums.buildyear YearOfBuilt, clubs.name Club FROM stadiums " + $"JOIN clubs_has_stadiums JOIN clubs WHERE clubs_id_clubs = clubs.id " + $"AND stadiums_id_stadiums = stadiums.id and stadiums.id = @id ", par); _connection.CloseConnection(); return(result); } return(new object()); } else { return(new object()); } }
public void CompleteStockRequest() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); string query = "UPDATE stock_requests SET completed = 1 WHERE id = @id;"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { cmd.Parameters.AddWithValue("@id", this.Id); cmd.ExecuteNonQuery(); } dbConnection.CloseConnection(); }
public static long GetTheNewestDepartmentId() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); long Id = 0; string query = "SELECT id FROM `departments` ORDER BY ID DESC LIMIT 1"; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Id = Convert.ToInt64(reader["id"]); } } dbConnection.CloseConnection(); return(Id); }
public void checkCredentials() { Label1.Visible = false; Label1.Text = ""; string login = TextBox1.Text; string password = TextBox2.Text; if (con.OpenConnection() == true) { try { string query = "SELECT id, name FROM user WHERE login ='******' AND PASSWORD ='******'"; MySqlCommand cmd = new MySqlCommand(query, con.getConnection()); MySqlDataReader myReader; myReader = cmd.ExecuteReader(); int id = -1; string name = ""; while (myReader.Read()) { name = Convert.ToString(myReader["name"]); id = Convert.ToInt32(myReader["id"]); } con.CloseConnection(); if (id != -1) { Response.Redirect("userForm.aspx?id=" + id + "&name=" + name, false); } else { Label1.Visible = true; Label1.Text = "Something wrong. User name not found or incorrect password."; } }catch {} } else { Console.WriteLine("Connection Failed"); } }
public void Update(Model obj, bool isApproved) { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); LeaveRequest request = (LeaveRequest)obj; string query; if (isApproved) { query = $"UPDATE leaves SET pending = 0 ,approved= 1 WHERE id ={request.Id}"; } else { query = $"UPDATE leaves SET pending = 0 ,approved= 0 WHERE id ={request.Id}"; } using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { cmd.ExecuteNonQuery(); } dbConnection.CloseConnection(); }
public static List <Product> GetAll() { DBconnection dbConnection = new DBconnection(); dbConnection.OpenConnection(); List <Product> products = new List <Product>(); string query = @" SELECT products.id AS p_id, products.name AS p_name, description, buying_price, selling_price, quantity, min_quantity, departments.name AS d_name FROM products INNER JOIN departments ON products.department_id = departments.id "; using (MySqlCommand cmd = new MySqlCommand(query, dbConnection.connection)) { MySqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { long id = Convert.ToInt64(reader["p_id"]); string name = reader["p_name"].ToString(); string description = reader["description"].ToString(); double buyingPrice = Convert.ToDouble(reader["buying_price"]); double sellingPrice = Convert.ToDouble(reader["selling_price"]); int quantity = Convert.ToInt32(reader["quantity"]); int minQuantity = Convert.ToInt32(reader["min_quantity"]); string department = reader["d_name"].ToString(); products.Add(new Product(id, name, description, buyingPrice, sellingPrice, quantity, minQuantity, department)); } } dbConnection.CloseConnection(); return(products); }