public void ShowOrders() { Open(); var cmd = new SqlCommand("SELECT * FROM [MyPharmacyDB].[dbo].[Orders] ", _connection); // Loads the query results into the table using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader.HasRows) { OnSuccesAction.Invoke("|-ID-|-PrescriptionID-|-MedicineID-|---Date---|-Amount-|"); while (sqlReader.Read()) { OnSuccesAction.Invoke("|" + (sqlReader.GetValue(0) + "|").PadLeft(5) + (sqlReader.GetValue(1) + "|").PadLeft(17) + (sqlReader.GetValue(2) + "|").PadLeft(13) + ((sqlReader.GetValue(3) as DateTime?).Value.ToString("dd-MM-yyyy") + "|").PadLeft(11) + (sqlReader.GetValue(4) + "|").PadLeft(9) ); } } else { OnFailAction.Invoke($"[Raport] - Brak wyników do wyświetlenia."); } } Close(); }
public override void Reload() { Open(); var cmd = new SqlCommand("SELECT * FROM [MyPharmacyDB].[dbo].AmountOfSoldMedicinesInMonth ", _connection); // Loads the query results into the table using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader.HasRows) { OnSuccesAction.Invoke("|---------Nazwa-------+--Ilość---|"); while (sqlReader.Read()) { OnSuccesAction.Invoke("|" + (sqlReader.GetValue(0).ToString() + "|").PadLeft(22) + (sqlReader.GetValue(1).ToString() + "|").PadLeft(11)); } } else { OnFailAction.Invoke($"[Raport] - Brak wyników do wyświetlenia."); } } Close(); }
public void ShowMedicines() { Open(); var cmd = new SqlCommand("SELECT * FROM [MyPharmacyDB].[dbo].[Medicines] ", _connection); // Loads the query results into the table using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader.HasRows) { OnSuccesAction.Invoke("|-ID-|-------Name-------|--Manufacturer--|--Price--|-Amount-|-With-Prescription-|"); while (sqlReader.Read()) { OnSuccesAction.Invoke("|" + (sqlReader.GetValue(0) + "|").PadLeft(5) + (sqlReader.GetValue(1) + "|").PadLeft(19) + (sqlReader.GetValue(2) + "|").PadLeft(17) + (sqlReader.GetValue(3) + "|").PadLeft(10) + (sqlReader.GetValue(4) + "|").PadLeft(9) + (sqlReader.GetValue(5) + "|").PadLeft(20) ); } } else { OnFailAction.Invoke($"[Raport] - Brak wyników do wyświetlenia."); } } Close(); }
public override void Remove() { Open(); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "[MyPharmacyDB].[dbo].DeletePrescription"; cmd.CommandType = CommandType.StoredProcedure; cmd.Connection = _connection; SqlParameter para1 = new SqlParameter() { ParameterName = "@id", Value = ID, DbType = DbType.Int32, Direction = ParameterDirection.Input, }; cmd.Parameters.Add(para1); try { ID = Convert.ToInt32(cmd.ExecuteScalar()); OnSuccesAction?.Invoke($"[Prescriptions] - Pomyślnie usunięto rekord. ID = {ID}"); } catch (Exception ex) { //Console.WriteLine(ex.Message, ex.StackTrace); OnFailAction?.Invoke($"{ex.Message}"); } finally { Close(); } }
public void ShowPrescriptions() { Open(); var cmd = new SqlCommand("SELECT * FROM [MyPharmacyDB].[dbo].[Prescriptions] ", _connection); // Loads the query results into the table using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader.HasRows) { OnSuccesAction.Invoke("|-ID-|-CustomerName-|---PESEL---|-PrescriptionNumber-|"); while (sqlReader.Read()) { OnSuccesAction.Invoke("|" + (sqlReader.GetValue(0) + "|").PadLeft(5) + (sqlReader.GetValue(1) + "|").PadLeft(15) + (sqlReader.GetValue(2) + "|").PadLeft(11) + (sqlReader.GetValue(3) + "|").PadLeft(21) ); } } else { OnFailAction.Invoke($"[Raport] - Brak wyników do wyświetlenia."); } } Close(); }
public void CleanAllDataInTables() { Open(); SqlTransaction transaction = _connection.BeginTransaction(); SqlCommand cmd = new SqlCommand() { CommandText = "DELETE FROM [MyPharmacyDB].[dbo].[Medicines]", CommandType = CommandType.Text, Connection = _connection, Transaction = transaction }; SqlCommand cmd2 = new SqlCommand() { CommandText = "DELETE FROM [MyPharmacyDB].[dbo].[Orders]", CommandType = CommandType.Text, Connection = _connection, Transaction = transaction }; SqlCommand cmd3 = new SqlCommand() { CommandText = "DELETE FROM [MyPharmacyDB].[dbo].[Prescriptions]", Connection = _connection, Transaction = transaction }; try { cmd2.ExecuteNonQuery(); cmd.ExecuteNonQuery(); cmd3.ExecuteNonQuery(); transaction.Commit(); OnSuccesAction?.Invoke($"[TableCleaner] - Pomyślnie usunięto dane z [Medicines]."); OnSuccesAction?.Invoke($"[TableCleaner] - Pomyślnie usunięto dane z [Orders]."); OnSuccesAction?.Invoke($"[TableCleaner] - Pomyślnie usunięto dane z [Prescriptions]."); } catch (Exception) { OnFailAction?.Invoke("[TableCleaner] - Nie udało się usunąć rekordów."); transaction.Rollback(); } finally { Close(); } }
public override void Save() { Open(); if (ID == 0) //Dodaj nowy rekord. { AddNewRow(); } else if (ID > 0) { UpdateRow(); } else { OnFailAction?.Invoke($"Numer Id nie może być ujemny."); } }
public override void Reload() { if (ID == 0) { throw new Exception("Reload - ID nie moze być 0"); } Open(); SqlCommand cmd = new SqlCommand() { CommandText = "SELECT *" + "FROM [MyPharmacyDB].[dbo].[Orders]" + "WHERE [ID] = @id", CommandType = CommandType.Text, Connection = _connection, }; SqlParameter para1 = new SqlParameter() { ParameterName = "@id", Value = ID, DbType = DbType.Int32 }; cmd.Parameters.Add(para1); try { using (SqlDataReader sqlReader = cmd.ExecuteReader()) { if (sqlReader.HasRows) { while (sqlReader.Read()) { if (sqlReader.FieldCount == 5) { ID = Convert.ToInt32(sqlReader.GetValue(0)); _prescription = new Prescription(Convert.ToInt32(sqlReader.GetValue(1))); _medicine = new Medicine(Convert.ToInt32(sqlReader.GetValue(2))); _date = Convert.ToString(sqlReader.GetValue(3)); _amount = Convert.ToInt32(sqlReader.GetValue(4)); } else { OnFailAction?.Invoke("ZłyFieldCount."); } } } else { OnFailAction?.Invoke($"Klient o identyfikatorze {ID} nie istnieje."); throw new Exception("ID = 0, lub rekord nie istnieje."); } OnSuccesAction?.Invoke($"[Prescriptions] - Pomyślnie odświerzono rekord. ID = {ID}"); } } catch (Exception ex) { //Console.WriteLine(ex.Message, ex.StackTrace); OnFailAction?.Invoke($"{ex.Message}"); } finally { Close(); } }
private void UpdateRow() { SqlTransaction transaction = _connection.BeginTransaction(); SqlCommand cmd = new SqlCommand() { CommandText = "UPDATE [MyPharmacyDB].[dbo].[Orders]" + "SET [PrescriptionID] = @PrescriptionID," + " [MedicineID] = @MedicineID," + " [Date] = @Date," + " [Amount] = @Amount " + "WHERE ID = @id", CommandType = CommandType.Text, Connection = _connection, Transaction = transaction }; SqlParameter para = new SqlParameter() { ParameterName = "@id", Value = ID, DbType = DbType.Int32 }; SqlParameter para1 = new SqlParameter() { ParameterName = "@PrescriptionID", Value = _prescription, DbType = DbType.String }; SqlParameter para2 = new SqlParameter() { ParameterName = "@MedicineID", Value = _medicine, DbType = DbType.String }; SqlParameter para3 = new SqlParameter() { ParameterName = "@Date", Value = _date, DbType = DbType.Decimal }; SqlParameter para4 = new SqlParameter() { ParameterName = "@Amount", Value = _amount, DbType = DbType.Int32 }; cmd.Parameters.Add(para); cmd.Parameters.Add(para1); cmd.Parameters.Add(para2); cmd.Parameters.Add(para3); cmd.Parameters.Add(para4); try { cmd.ExecuteNonQuery(); transaction.Commit(); OnSuccesAction?.Invoke($"[Orders] - Pomyślnie zmodyfikowano rekord. ID = {ID}"); } catch (Exception) { //Console.WriteLine(ex.Message, ex.StackTrace); transaction.Rollback(); OnFailAction?.Invoke($"[Orders] - Nie udało się zmodyfikować rekordu. ID = {ID}"); } finally { Close(); } }
private void UpdateRow() { SqlTransaction transaction = _connection.BeginTransaction(); SqlCommand cmd = new SqlCommand() { CommandText = "UPDATE [MyPharmacyDB].[dbo].[Prescriptions]" + "SET [CustomerName] = @CustomerName," + " [PESEL] = @PESEL," + " [PrescriptionNumber] = @PrescriptionNumber " + "WHERE ID = @id", CommandType = CommandType.Text, Connection = _connection, Transaction = transaction }; SqlParameter para = new SqlParameter() { ParameterName = "@id", Value = ID, DbType = DbType.Int32 }; SqlParameter para1 = new SqlParameter() { ParameterName = "@CustomerName", Value = CustomerName, DbType = DbType.String }; SqlParameter para2 = new SqlParameter() { ParameterName = "@PESEL", Value = PESEL, DbType = DbType.String }; SqlParameter para3 = new SqlParameter() { ParameterName = "@PrescriptionNumber", Value = PrescriptionNumber, DbType = DbType.Int32 }; cmd.Parameters.Add(para); cmd.Parameters.Add(para1); cmd.Parameters.Add(para2); cmd.Parameters.Add(para3); try { ID = Convert.ToInt32(cmd.ExecuteScalar()); transaction.Commit(); OnSuccesAction?.Invoke($"[Prescriptions] - Pomyślnie zmodyfikowano rekord. ID = {ID}"); } catch (Exception) { //Console.WriteLine(ex.Message, ex.StackTrace); transaction.Rollback(); OnFailAction?.Invoke($"[Prescriptions] - Nie udało się zmodyfikować rekordu. ID = {ID}"); } finally { Close(); } }