// 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);
}
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);
}
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);
}