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