public static List <Interview> GetInterviewSorted(string columnName) // SORTS THE DATA IN THE DATABASE { List <Interview> interviewList = new List <Interview>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT InterviewID, PositionID, CompanyID, ContactID, " + "DateTime, AdditionalNotes FROM dbo.Interview " + "ORDER BY CASE WHEN @ColumnName = 'InterviewID' THEN InterviewID END ASC, " + "CASE WHEN @ColumnName = 'PositionID' THEN PositionID END ASC, " + "CASE WHEN @ColumnName = 'CompanyID' THEN CompanyID END ASC, " + "CASE WHEN @ColumnName = 'ContactID' THEN ContactID END ASC, " + "CASE WHEN @ColumnName = 'DateTime' THEN DateTime END ASC, " + "CASE WHEN @ColumnName = 'AdditionalNotes' THEN AdditionalNotes END ASC;"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("InterviewID"), cNOrd = reader.GetOrdinal("PositionID"), cAOrd = reader.GetOrdinal("CompanyID"), cford = reader.GetOrdinal("ContactID"), cCOrd = reader.GetOrdinal("DateTime"), cSOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Interview interv = new Interview(); interv.InterviewID = reader.GetInt32(cIDOrd); interv.PositionID = reader.GetInt32(cNOrd); interv.CompanyID = reader.GetInt32(cAOrd); interv.ContactID = reader.GetInt32(cford); interv.DateTimeInterview = reader.GetDateTime(cCOrd); if (reader["AdditionalNotes"].Equals(DBNull.Value)) { interv.AdditionalNotes = ""; } else { interv.AdditionalNotes = reader.GetString(cSOrd); } interviewList.Add(interv); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(interviewList); }
public static List <Interview> GetInterview() // USED TO POPULATE THE DATA GRID TABLE ALSO USED TO REFRESH THE DATA GRID { List <Interview> interviewList = new List <Interview>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT InterviewID, PositionID, CompanyID, ContactID, " + "DateTime, AdditionalNotes FROM dbo.Interview"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("InterviewID"), cNOrd = reader.GetOrdinal("PositionID"), cAOrd = reader.GetOrdinal("CompanyID"), cford = reader.GetOrdinal("ContactID"), cCOrd = reader.GetOrdinal("DateTime"), cSOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Interview interv = new Interview(); interv.InterviewID = reader.GetInt32(cIDOrd); interv.PositionID = reader.GetInt32(cNOrd); interv.CompanyID = reader.GetInt32(cAOrd); interv.ContactID = reader.GetInt32(cford); interv.DateTimeInterview = reader.GetDateTime(cCOrd); if (reader["AdditionalNotes"].Equals(DBNull.Value)) { interv.AdditionalNotes = ""; } else { interv.AdditionalNotes = reader.GetString(cSOrd); } interviewList.Add(interv); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(interviewList); }
public static Interview GetInterviewByRow(int interviewID) // GETS ONE ROW AT A TIME FROM THE DATABASE { Interview interview = new Interview(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT InterviewID, PositionID, CompanyID, ContactID, " + "DateTime, AdditionalNotes FROM dbo.Interview WHERE InterviewID = @InterviewID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@InterviewID", interviewID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("InterviewID"), cNOrd = reader.GetOrdinal("PositionID"), cAOrd = reader.GetOrdinal("CompanyID"), cford = reader.GetOrdinal("ContactID"), cCOrd = reader.GetOrdinal("DateTime"), cSOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { interview.InterviewID = reader.GetInt32(cIDOrd); interview.PositionID = reader.GetInt32(cNOrd); interview.CompanyID = reader.GetInt32(cAOrd); interview.ContactID = reader.GetInt32(cford); interview.DateTimeInterview = reader.GetDateTime(cCOrd); if (reader["AdditionalNotes"].Equals(DBNull.Value)) { interview.AdditionalNotes = ""; } else { interview.AdditionalNotes = reader.GetString(cSOrd); } } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(interview); }
public static int AddInterview(Interview interview) // ADDS A NEW ROW TO THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string insertStatement = "INSERT Interview " + "(PositionID, CompanyID, ContactID, " + "DateTime, AdditionalNotes) " + "VALUES (@PositionID, @CompanyID, @ContactID, " + "@DateTime, @AdditionalNotes);"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@PositionID", interview.PositionID); insertCommand.Parameters.AddWithValue("@CompanyID", interview.CompanyID); insertCommand.Parameters.AddWithValue("@ContactID", interview.ContactID); insertCommand.Parameters.AddWithValue("@DateTime", interview.DateTimeInterview); insertCommand.Parameters["@DateTime"].SqlDbType = SqlDbType.DateTime; if (interview.AdditionalNotes == null) { insertCommand.Parameters.AddWithValue("@AdditionalNotes", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@AdditionalNotes", interview.AdditionalNotes); } try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Interview') FROM Interview"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int vendorID = Convert.ToInt32(selectCommand.ExecuteScalar()); return(vendorID); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } }
public static List <Interview> GetInterviewFiltered(string columnName, string columnfilter) // PUTS A FILTER TO THE DATABASE { int filtered = 0; DateTime dateFilter; List <Interview> interviewList = new List <Interview>(); SqlConnection connection = PRG299DB.GetConnection(); /* * When you pass any column name and filter which matches to any records and per column name, it will return those records. * When column name matches and no record matches as per column name it fallback to last ELSE part so it won't return any records as expected. * In one special case when you don't mention any column name i.e. @ColumnName = '' then all rows will be returned as you didn't want to filter. */ string selectStatement = "SELECT InterviewID, PositionID, CompanyID, ContactID," + "DateTime, AdditionalNotes FROM dbo.Interview " + "WHERE CASE WHEN @ColumnName = 'InterviewID' AND InterviewID = @Filter THEN 1 " + "WHEN @ColumnName = 'PositionID' AND PositionID = @Filter THEN 1 " + "WHEN @ColumnName = 'CompanyID' AND CompanyID = @Filter THEN 1 " + "WHEN @ColumnName = 'ContactID' AND ContactID = @Filter THEN 1 " + "WHEN @ColumnName = 'DateTime' AND CASE WHEN ISDATE(@Filter) = 1 THEN CONVERT(DATETIME, @Filter, 101) ELSE NULL END = DateTime THEN 1 " + "WHEN @ColumnName = 'AdditionalNotes' AND AdditionalNotes LIKE '%' + @Filter + '%' THEN 1 WHEN @ColumnName = '' THEN 1 ELSE 0 END = 1"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); if (columnName == "InterviewID" || columnName == "PositionID" || columnName == "CompanyID" || columnName == "ContactID") { int.TryParse(columnfilter, out filtered); selectCommand.Parameters.AddWithValue("@Filter", filtered); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.Int; } else if (columnName == "DateTimeInterview") { if (DateTime.TryParse(columnfilter, out dateFilter)) { selectCommand.Parameters.AddWithValue("@Filter", dateFilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.DateTime; } else { } } else { selectCommand.Parameters.AddWithValue("@Filter", columnfilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.VarChar; } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("InterviewID"), cNOrd = reader.GetOrdinal("PositionID"), cAOrd = reader.GetOrdinal("CompanyID"), cford = reader.GetOrdinal("ContactID"), cCOrd = reader.GetOrdinal("DateTime"), cSOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Interview interv = new Interview(); interv.InterviewID = reader.GetInt32(cIDOrd); interv.PositionID = reader.GetInt32(cNOrd); interv.CompanyID = reader.GetInt32(cAOrd); interv.ContactID = reader.GetInt32(cford); interv.DateTimeInterview = reader.GetDateTime(cCOrd); if (reader["AdditionalNotes"].Equals(DBNull.Value)) { interv.AdditionalNotes = ""; } else { interv.AdditionalNotes = reader.GetString(cSOrd); } interviewList.Add(interv); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(interviewList); }
public static bool UpdateInterview(Interview oldInterview, Interview newInterview) // MODIFIES THE DATABASE A ROW AT A TIME { SqlConnection connection = PRG299DB.GetConnection(); string updateStatement = "UPDATE Interview SET " + "PositionID = @NewPositionID, " + "CompanyID = @NewCompanyID, " + "ContactID = @NewContactID, " + "DateTime = @NewDateTime, " + "AdditionalNotes = @NewAdditionalNotes " + "WHERE InterviewID = @OldInterviewID " + "AND PositionID = @OldPositionID " + "AND CompanyID = @OldCompanyID " + "AND ContactID = @OldContactID " + "AND DateTime = @OldDateTime " + "AND (AdditionalNotes = @OldAdditionalNotes " + "OR AdditionalNotes IS NULL AND @OldAdditionalNotes IS NULL)"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewPositionID", newInterview.PositionID); updateCommand.Parameters.AddWithValue("@NewCompanyID", newInterview.CompanyID); updateCommand.Parameters.AddWithValue("@NewContactID", newInterview.ContactID); updateCommand.Parameters.AddWithValue("@NewDateTime", newInterview.DateTimeInterview); if (newInterview.AdditionalNotes == "") { updateCommand.Parameters.AddWithValue("@NewAdditionalNotes", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewAdditionalNotes", newInterview.AdditionalNotes); } updateCommand.Parameters.AddWithValue("@OldInterviewID", oldInterview.InterviewID); updateCommand.Parameters.AddWithValue("@OldPositionID", oldInterview.PositionID); updateCommand.Parameters.AddWithValue("@OldCompanyID", oldInterview.CompanyID); updateCommand.Parameters.AddWithValue("@OldContactID", oldInterview.ContactID); updateCommand.Parameters.AddWithValue("@OldDateTime", oldInterview.DateTimeInterview); if (oldInterview.AdditionalNotes == "") { updateCommand.Parameters.AddWithValue("@OldAdditionalNotes", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldAdditionalNotes", oldInterview.AdditionalNotes); } try { connection.Open(); int count = updateCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } }