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();
}
private void AddNewRow()
{
SqlTransaction transaction = _connection.BeginTransaction();
SqlCommand cmd = new SqlCommand()
{
CommandText = "INSERT INTO [MyPharmacyDB].[dbo].[Prescriptions] ( [CustomerName], [PESEL], [PrescriptionNumber])" +
"VALUES( @CustomerName, @PESEL, @PrescriptionNumber);" +
"SELECT SCOPE_IDENTITY(); ",
CommandType = CommandType.Text,
Connection = _connection,
Transaction = transaction
};
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(para1);
cmd.Parameters.Add(para2);
cmd.Parameters.Add(para3);
try
{
ID = Convert.ToInt32(cmd.ExecuteScalar());
transaction.Commit();
OnSuccesAction?.Invoke($"[Prescriptions] - Pomyślnie dodano rekord. ID = {ID}");
}
catch (Exception)
{
//Console.WriteLine(ex.Message, ex.StackTrace);
transaction.Rollback();
}
finally
{
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();
}
}
private void AddNewRow()
{
if (ID == 0) //Dodaj nowy rekord.
{
SqlTransaction transaction = _connection.BeginTransaction();
SqlCommand cmd = new SqlCommand()
{
CommandText = "INSERT INTO [MyPharmacyDB].[dbo].[Orders] ( [PrescriptionID], [MedicineID], [Date], [Amount])" +
"VALUES( @PrescriptionID, @MedicineID, @Date, @Amount);" +
"SELECT SCOPE_IDENTITY(); ",
CommandType = CommandType.Text,
Connection = _connection,
Transaction = transaction
};
SqlParameter parameterPrescriptionID = new SqlParameter()
{
ParameterName = "@PrescriptionID",
Value = _prescription.ID,
DbType = DbType.Int32
};
SqlParameter parameterMedicineID = new SqlParameter()
{
ParameterName = "@MedicineID",
Value = _medicine.ID,
DbType = DbType.Int32
};
SqlParameter parameterDate = new SqlParameter()
{
ParameterName = "@Date",
Value = _date,
DbType = DbType.String
};
SqlParameter parameterAmount = new SqlParameter()
{
ParameterName = "@Amount",
Value = _amount,
DbType = DbType.Int32
};
cmd.Parameters.Add(parameterPrescriptionID);
cmd.Parameters.Add(parameterMedicineID);
cmd.Parameters.Add(parameterDate);
cmd.Parameters.Add(parameterAmount);
try
{
ID = Convert.ToInt32(cmd.ExecuteScalar());
transaction.Commit();
OnSuccesAction?.Invoke($"[Orders] - Pomyślnie dodano rekord. ID = {ID}");
}
catch (Exception)
{
//Console.WriteLine(ex.Message, ex.StackTrace);
transaction.Rollback();
}
finally
{
Close();
}
}
else
{
UpdateRow();
}
}
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();
}
}
private void AddNewRow()
{
SqlTransaction transaction = _connection.BeginTransaction();
SqlCommand cmd = new SqlCommand()
{
CommandText = "INSERT INTO [MyPharmacyDB].[dbo].[Medicines] ( [Name], [Manufacturer], [Price], [Amount], [WithPrescription])" +
"VALUES( @Name, @Manufacturer, @Price, @Amount ,@WithPrescription);" +
"SELECT SCOPE_IDENTITY(); ",
CommandType = CommandType.Text,
Connection = _connection,
Transaction = transaction
};
SqlParameter Name = new SqlParameter()
{
ParameterName = "@Name",
Value = _name,
DbType = DbType.String
};
SqlParameter Manufacturer = new SqlParameter()
{
ParameterName = "@Manufacturer",
Value = _manufacturer,
DbType = DbType.String
};
SqlParameter Price = new SqlParameter()
{
ParameterName = "@Price",
Value = _price,
DbType = DbType.Decimal
};
SqlParameter Amount = new SqlParameter()
{
ParameterName = "@Amount",
Value = _amount,
DbType = DbType.Int32
};
SqlParameter WithPrescription = new SqlParameter()
{
ParameterName = "@WithPrescription",
Value = _withPrescription,
DbType = DbType.Boolean
};
cmd.Parameters.Add(Name);
cmd.Parameters.Add(Manufacturer);
cmd.Parameters.Add(Price);
cmd.Parameters.Add(Amount);
cmd.Parameters.Add(WithPrescription);
try
{
ID = Convert.ToInt32(cmd.ExecuteScalar());
transaction.Commit();
OnSuccesAction?.Invoke($"[Medicines] - Pomyślnie dodano rekord. ID = {ID}");
}
catch (Exception)
{
////Console.WriteLine(ex.Message, ex.StackTrace);
transaction.Rollback();
}
finally
{
Close();
}
}
