// Method to retrieve all the models from the DB
public List <DbModelEmail> GetAllEmailDataFromDb()
{
List <DbModelEmail> list = new List <DbModelEmail>();
using (var connection = new MSDSC.SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=DBEmailScheduler;Trusted_Connection=True;"))
{
connection.Open();
if (connection.State.Equals(SD.ConnectionState.Open))
{
using (var command = new MSDSC.SqlCommand())
{
command.Connection = connection;
command.CommandType = SD.CommandType.Text;
command.CommandText = @"
SELECT * FROM dbo.tb_emails;
" ;
MSDSC.SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
bool IsOpened = (reader.GetInt32(2) != 0) ? true : false;
bool IsFirstEmailSent = (reader.GetInt32(3) != 0) ? true : false;
DbModelEmail dbModelEmail = new DbModelEmail(reader.GetString(1), IsOpened, IsFirstEmailSent, reader.GetInt32(4));
list.Add(dbModelEmail);
}
}
}
}
return(list);
}
private static async Task <List <dynamic> > GetRows(
string query,
Func <QC.SqlDataReader, dynamic> rowConverter)
{
var result = new List <dynamic>();
var connectionString = Environment.GetEnvironmentVariable("GIFTS_DB_ADO_NET_CONNECTION_STRING");
using (var connection = new QC.SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = query;
QC.SqlDataReader reader = await command.ExecuteReaderAsync();
while (reader.Read())
{
result.Add(rowConverter(reader));
}
}
}
return(result);
}
public static async Task TruncateTransactionalOutboxTableAsync(this MicrosoftData.SqlConnection sqlConnection)
{
//CLEAR the Table for Integration Tests to validate:
await using var sqlCmd = new MicrosoftData.SqlCommand(
SqlCommands.TruncateTransactionalOutbox,
sqlConnection
);
await sqlCmd.ExecuteNonQueryAsync().ConfigureAwait(false);
}
public static int CUD(string strsql)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
conn.Open();
SqlCommand comm = new SqlCommand(strsql, conn);
return(comm.ExecuteNonQuery());
}
}
public static int CUD_Proc(string pname, SqlParameter[] paras = null)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
SqlCommand comm = new SqlCommand(pname, conn);
comm.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
return(comm.ExecuteNonQuery());
}
}
public static DataTable GetTable_Proc(string pname, SqlParameter[] paras = null)
{
using (SqlConnection conn = new SqlConnection(strconn))
{
var dt = new DataTable();
SqlCommand comm = new SqlCommand(pname, conn);
comm.CommandType = CommandType.StoredProcedure;
if (paras != null)
{
comm.Parameters.AddRange(paras);
}
SqlDataAdapter msda = new SqlDataAdapter(comm);
msda.Fill(dt);
return(dt);
}
}
// Method to update the DB with new model parameteres on the basis of email
public void UpdateDb(DbModelEmail model)
{
using (var connection = new MSDSC.SqlConnection("Server=(localdb)\\MSSQLLocalDB;Database=DBEmailScheduler;Trusted_Connection=True;"))
{
connection.Open();
if (connection.State.Equals(SD.ConnectionState.Open))
{
using (var command = new MSDSC.SqlCommand())
{
MSDSC.SqlParameter param;
command.Connection = connection;
command.CommandType = SD.CommandType.Text;
command.CommandText = @"
UPDATE dbo.tb_emails
SET isFirstEmailSent = @isFirstEmailSent, isOpened = @isEmailOpened, remainingReminderDays = @remainingReminderDays
WHERE emailID = @emailID" ;
int IsOpened = (model.IsOpened == true) ? 1 : 0;
int IsFirstEmailSent = (model.IsFirstEmailSent == true) ? 1 : 0;
param = new MSDSC.SqlParameter("@isFirstEmailSent", SD.SqlDbType.Int);
param.Value = IsFirstEmailSent;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@isEmailOpened", SD.SqlDbType.Int);
param.Value = IsOpened;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@remainingReminderDays", SD.SqlDbType.Int);
param.Value = model.RemainingReminderDays;
command.Parameters.Add(param);
param = new MSDSC.SqlParameter("@emailID", SD.SqlDbType.NVarChar);
param.Value = model.Email;
command.Parameters.Add(param);
int affectedRows = command.ExecuteNonQuery();
System.Console.WriteLine("DB Updated! Number of rows affected : " + affectedRows);
}
}
}
}
public AssignDTO CheckSecurityCodeMatch(string securityCode)
{
AssignDTO assignDTO = new AssignDTO();
using (ConnectionString connectionString = new ConnectionString())
{
try
{
connectionString.sqlConnection.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand("sp_Doctor_CheckSecurityCode", connectionString.sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@securityCode", securityCode);
using (Microsoft.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
assignDTO = new AssignDTO
{
patientDTO = new PatientDTO {
Id = reader.GetString(0)
},
SecurityCodeMatch = true
};
}
else
{
assignDTO = new AssignDTO
{
SecurityCodeMatch = false
};
}
}
}
catch (Exception exception)
{
if (exception is InvalidCastException || exception is Microsoft.Data.SqlClient.SqlException)
{
Console.WriteLine("Error source: " + exception);
throw;
}
}
}
return(assignDTO);
}
public List <PatientDTO> GetPatientStatus(string idP)
{
List <PatientDTO> patientStatus = new List <PatientDTO>();
using (ConnectionString connectionString = new ConnectionString())
{
try
{
connectionString.sqlConnection.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand("sp_Doctor_GetMeasurements", connectionString.sqlConnection);
cmd.Parameters.AddWithValue("@idP", idP);
cmd.CommandType = CommandType.StoredProcedure;
using (Microsoft.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
PatientDTO patientDTO = new PatientDTO
{
statusDTO = new StatusDTO
{
Date = reader.GetDateTime(0),
INR = reader.GetDecimal(1)
}
};
patientStatus.Add(patientDTO);
}
}
}
}
catch (Exception exception)
{
if (exception is InvalidCastException || exception is Microsoft.Data.SqlClient.SqlException)
{
Console.WriteLine("Error source: " + exception);
throw;
}
}
}
return(patientStatus);
}
public PatientDTO GetPatientAdditionalInfo(string idP)
{
PatientDTO patientDTO = new PatientDTO();
using (ConnectionString connectionString = new ConnectionString())
{
try
{
connectionString.sqlConnection.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand("sp_Doctor_GetPatientInfo", connectionString.sqlConnection);
cmd.Parameters.AddWithValue("@idP", idP);
cmd.CommandType = CommandType.StoredProcedure;
using (Microsoft.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.HasRows)
{
while (reader.Read())
{
patientDTO = new PatientDTO
{
Firstname = reader.GetString(0),
Lastname = reader.GetString(1),
Phonenumber = reader.GetString(2),
Email = reader.GetString(3),
DateOfBirth = reader.GetDateTime(4).ToString("dd-MM-yyyy")
};
}
}
}
}
catch (Exception exception)
{
if (exception is InvalidCastException || exception is Microsoft.Data.SqlClient.SqlException)
{
Console.WriteLine("Error source: " + exception);
throw;
}
}
}
return(patientDTO);
}
private static async Task <int> ExecuteUpdate(string update, Dictionary <string, object> parameters)
{
var connectionString = Environment.GetEnvironmentVariable("GIFTS_DB_CONNECTION_STRING");
using (var connection = new QC.SqlConnection(connectionString))
{
await connection.OpenAsync();
using (var command = new QC.SqlCommand())
{
command.Connection = connection;
command.CommandType = DT.CommandType.Text;
command.CommandText = update;
foreach (var item in parameters)
{
command.Parameters.AddWithValue(item.Key, item.Value);
}
return(await command.ExecuteNonQueryAsync());
}
}
}
/// <summary>
/// Attempt to create a DbParameter using the <see cref="Microsoft.EntityFrameworkCore.Storage.RelationalTypeMapping.CreateParameter(DbCommand, string, object, bool?)"/>
/// call for the specified column name.
/// </summary>
public static DbParameter TryCreateRelationalMappingParameter(string columnName, string parameterName, object value, TableInfo tableInfo)
{
if (columnName == null)
{
return(null);
}
if (!tableInfo.ColumnToPropertyDictionary.TryGetValue(columnName, out var propertyInfo))
{
return(null);
}
try
{
var relationalTypeMapping = propertyInfo.GetRelationalTypeMapping();
using var dbCommand = new Microsoft.Data.SqlClient.SqlCommand();
return(relationalTypeMapping.CreateParameter(dbCommand, parameterName, value, propertyInfo.IsNullable));
}
catch (Exception) { }
return(null);
}
//not used in project anymore
//public List<PatientDTO> GetPatientsLinkedToDoctor(string username)
//{
// List<PatientDTO> patients = new List<PatientDTO>();
// using (ConnectionString connectionString = new ConnectionString())
// {
// try
// {
// connectionString.sqlConnection.Open();
// SqlCommand cmd = new SqlCommand("GetPatientsLinkedToDoctor", connectionString.sqlConnection);
// cmd.Parameters.AddWithValue("username", username);
// cmd.CommandType = CommandType.StoredProcedure;
// using (SqlDataReader reader = cmd.ExecuteReader())
// {
// if(reader.HasRows)
// {
// while(reader.Read())
// {
// PatientDTO patientDTO = new PatientDTO
// {
// UserId = reader.GetInt32(0),
// Firstname = reader.GetString(1),
// Lastname = reader.GetString(2)
// };
// patients.Add(patientDTO);
// }
// }
// }
// }
// catch (Exception exception)
// {
// if (exception is InvalidCastException || exception is SqlException)
// {
// Console.WriteLine("Error source: " + exception);
// throw;
// }
// }
// }
// return patients;
//}
public void RemovePatientLinkedToDoctor(string idD, string idP)
{
using (ConnectionString connectionString = new ConnectionString())
{
try
{
connectionString.sqlConnection.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand("sp_Doctor_RemovePatient", connectionString.sqlConnection);
cmd.Parameters.AddWithValue("@idD", idD);
cmd.Parameters.AddWithValue("@idP", idP);
cmd.CommandType = CommandType.StoredProcedure;
cmd.ExecuteNonQuery();
}
catch (Exception exception)
{
if (exception is InvalidCastException || exception is Microsoft.Data.SqlClient.SqlException)
{
Console.WriteLine("Error source: " + exception);
throw;
}
}
}
}
public bool CheckExistingRelationDoctorPatient(string idD, string idP)
{
AssignDTO assignDTO = new AssignDTO();
using (ConnectionString connectionString = new ConnectionString())
{
try
{
connectionString.sqlConnection.Open();
Microsoft.Data.SqlClient.SqlCommand cmd = new Microsoft.Data.SqlClient.SqlCommand("sp_Doctor_CheckExistingRelation", connectionString.sqlConnection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@idD", idD);
cmd.Parameters.AddWithValue("@idP", idP);
using (Microsoft.Data.SqlClient.SqlDataReader reader = cmd.ExecuteReader())
{
if (reader.Read())
{
assignDTO.ExistingRelation = true;
}
else
{
assignDTO.ExistingRelation = false;
}
}
}
catch (Exception exception)
{
if (exception is InvalidCastException || exception is Microsoft.Data.SqlClient.SqlException)
{
Console.WriteLine("Error source: " + exception);
throw;
}
}
}
return(assignDTO.ExistingRelation);
}
