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