public static Resume GetResumeByRow(int resumeID) // GETS ONE ROW AT A TIME FROM THE DATABASE { Resume resume = new Resume(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ResumeID, RSCDirectoryPath, SchoolID, " + "ClientID FROM dbo.Resume WHERE ResumeID = @ResumeID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ResumeID", resumeID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ResumeID"), cNOrd = reader.GetOrdinal("RSCDirectoryPath"), cAOrd = reader.GetOrdinal("SchoolID"), cCOrd = reader.GetOrdinal("ClientID"); while (reader.Read()) { resume.ResumeID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { resume.RSCDirectoryPath = ""; } else { resume.RSCDirectoryPath = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { resume.SchoolID = -1; } else { resume.SchoolID = reader.GetInt32(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { resume.ClientID = -1; } else { resume.ClientID = reader.GetInt32(cCOrd); } } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(resume); }
public static List <ContactPosition> GetContactPosition()// USED TO POPULATE THE DATA GRID TABLE ALSO USED TO REFRESH THE DATA GRID { List <ContactPosition> contactpositionList = new List <ContactPosition>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ContactID, PositionID FROM dbo.ContactPosition"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ContactID"), cNOrd = reader.GetOrdinal("PositionID"); while (reader.Read()) { ContactPosition conPos = new ContactPosition(); conPos.ContactID = reader.GetInt32(cIDOrd); conPos.PositionID = reader.GetInt32(cNOrd); contactpositionList.Add(conPos); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(contactpositionList); }
public static int AddContactPosition(ContactPosition contactposition)// ADDS A NEW ROW TO THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string insertStatement = "INSERT ContactPosition " + "(ContactID, PositionID) " + "VALUES (@ContactID, @PositionID);"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@ContactID", contactposition.ContactID); insertCommand.Parameters.AddWithValue("@PositionID", contactposition.PositionID); try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Contact') FROM ContactPosition"; 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 ContactPosition GetContactPositionByRow(int contactpositionID)// GETS ONE ROW AT A TIME FROM THE DATABASE { ContactPosition conpos = new ContactPosition(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ContactID, PositionID FROM dbo.ContactPosition WHERE ContactID = @ContactID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ContactID", contactpositionID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ContactID"), cNOrd = reader.GetOrdinal("PositionID"); while (reader.Read()) { conpos.ContactID = reader.GetInt32(cIDOrd); conpos.PositionID = reader.GetInt32(cNOrd); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(conpos); }
public static bool DeleteResume(int resumeID) // DELETES A ROW FROM THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string deleteStatement = "DELETE FROM Resume WHERE ResumeID = @ResumeID"; SqlCommand DeleteCommand = new SqlCommand(deleteStatement, connection); DeleteCommand.Parameters.AddWithValue("@ResumeID", resumeID); try { connection.Open(); int count = DeleteCommand.ExecuteNonQuery(); if (count > 0) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } }
public static Client GetClientByRow(int clientID) // GETS ONE ROW AT A TIME FROM THE DATABASE { Client client = new Client(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ClientID, FirstName, LastName, BirthDate, StreetName, " + "City, State, ZipCode, CellPhone FROM dbo.Client WHERE ClientID = @ClientID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ClientID", clientID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ClientID"), cFOrd = reader.GetOrdinal("FirstName"), cLOrd = reader.GetOrdinal("LastName"), cBOrd = reader.GetOrdinal("BirthDate"), cSTROrd = reader.GetOrdinal("StreetName"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("CellPhone"); while (reader.Read()) { client.ClientID = reader.GetInt32(cIDOrd); client.FirstName = reader.GetString(cFOrd); client.LastName = reader.GetString(cLOrd); client.BirthDate = reader.GetDateTime(cBOrd); client.StreetName = reader.GetString(cSTROrd); client.City = reader.GetString(cCOrd); client.State = reader.GetString(cSOrd); client.ZipCode = reader.GetString(cZCOrd); if (reader["CellPhone"].Equals(DBNull.Value)) { client.CellPhone = ""; } else { client.CellPhone = reader.GetString(cPOrd); } if (clientID == client.ClientID) { break; } } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(client); }
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 <ContactPosition> GetContactPositionFiltered(string columnName, string columnfilter)// PUTS A FILTER TO THE DATABASE { int filtered = 0; List <ContactPosition> contactpositionList = new List <ContactPosition>(); 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 ContactID, PositionID FROM dbo.ContactPosition " + "WHERE CASE WHEN @ColumnName = 'ContactID' AND ContactID = @Filter THEN 1 " + "WHEN @ColumnName = 'PositionID' AND PositionID = @Filter THEN 1 WHEN @ColumnName = '' THEN 1 ELSE 0 END = 1"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); if (columnName == "ContactID" || columnName == "PositionID") { int.TryParse(columnfilter, out filtered); selectCommand.Parameters.AddWithValue("@Filter", filtered); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.Int; } else { selectCommand.Parameters.AddWithValue("@Filter", columnfilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.VarChar; } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ContactID"), cNOrd = reader.GetOrdinal("PositionID"); while (reader.Read()) { ContactPosition conPos = new ContactPosition(); conPos.ContactID = reader.GetInt32(cIDOrd); conPos.PositionID = reader.GetInt32(cNOrd); contactpositionList.Add(conPos); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(contactpositionList); }
public static int AddResume(Resume resume)// ADDS A NEW ROW TO THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string insertStatement = "INSERT Resume " + "(RSCDirectoryPath, SchoolID, " + "ClientID) " + "VALUES (@RSCDirectoryPath, @SchoolID, " + "@ClientID);"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); if (resume.RSCDirectoryPath == null || resume.RSCDirectoryPath == "") { insertCommand.Parameters.AddWithValue("@RSCDirectoryPath", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@RSCDirectoryPath", resume.RSCDirectoryPath); } if (resume.SchoolID.ToString() == null) { insertCommand.Parameters.AddWithValue("@SchoolID", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@SchoolID", resume.SchoolID); } if (resume.ClientID.ToString() == null) { insertCommand.Parameters.AddWithValue("@ClientID", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@ClientID", resume.ClientID); } try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Resume') FROM Resume"; 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 <Client> GetClient() // USED TO POPULATE THE DATA GRID TABLE ALSO USED TO REFRESH THE DATA GRID { List <Client> clientList = new List <Client>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ClientID, FirstName, LastName, BirthDate, StreetName, " + "City, State, ZipCode, CellPhone FROM dbo.Client"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ClientID"), cFOrd = reader.GetOrdinal("FirstName"), cLOrd = reader.GetOrdinal("LastName"), cBOrd = reader.GetOrdinal("BirthDate"), cSTROrd = reader.GetOrdinal("StreetName"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("CellPhone"); while (reader.Read()) { Client clientA = new Client(); clientA.ClientID = reader.GetInt32(cIDOrd); clientA.FirstName = reader.GetString(cFOrd); clientA.LastName = reader.GetString(cLOrd); clientA.BirthDate = reader.GetDateTime(cBOrd); clientA.StreetName = reader.GetString(cSTROrd); clientA.City = reader.GetString(cCOrd); clientA.State = reader.GetString(cSOrd); clientA.ZipCode = reader.GetString(cZCOrd); if (reader["CellPhone"].Equals(DBNull.Value)) { clientA.CellPhone = ""; } else { clientA.CellPhone = reader.GetString(cPOrd); } clientList.Add(clientA); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(clientList); }
public static int AddClient(Client client) // ADDS A NEW ROW TO THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string insertStatement = "INSERT INTO Client " + "(FirstName, LastName, BirthDate, StreetName, " + "City, State, ZipCode, CellPhone) " + "VALUES (@FirstName, @LastName, @BirthDate, @StreetName, " + "@City, @State, @ZipCode, @CellPhone);"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@FirstName", client.FirstName); insertCommand.Parameters.AddWithValue("@LastName", client.LastName); insertCommand.Parameters.AddWithValue("@BirthDate", client.BirthDate); insertCommand.Parameters["@BirthDate"].SqlDbType = SqlDbType.DateTime; insertCommand.Parameters.AddWithValue("@StreetName", client.StreetName); insertCommand.Parameters.AddWithValue("@City", client.City); insertCommand.Parameters.AddWithValue("@State", client.State); insertCommand.Parameters.AddWithValue("@ZipCode", client.ZipCode); if (client.CellPhone == null || client.CellPhone == "") { insertCommand.Parameters.AddWithValue("@CellPhone", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@CellPhone", client.CellPhone); } try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Client') FROM Client"; 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> 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 bool UpdateContactPosition(ContactPosition oldContactPosition, ContactPosition newContactPosition) // MODIFIES THE DATABASE A ROW AT A TIME { SqlConnection connection = PRG299DB.GetConnection(); string updateStatement = "UPDATE ContactPosition SET " + "ContactID = @NewContactID, " + "PositionID = @NewPositionID " + "WHERE ContactID = @OldContactID " + "AND PositionID = @OldPositionID"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewContactID", newContactPosition.ContactID); updateCommand.Parameters.AddWithValue("@NewPositionID", newContactPosition.PositionID); updateCommand.Parameters.AddWithValue("@OldContactID", oldContactPosition.ContactID); updateCommand.Parameters.AddWithValue("@OldPositionID", oldContactPosition.PositionID); 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(); } }
public static List <ContactPosition> GetContactPositionSorted(string columnName) // SORTS THE DATA IN THE DATABASE { List <ContactPosition> contactpositionList = new List <ContactPosition>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ContactID, PositionID FROM dbo.ContactPosition " + "ORDER BY CASE WHEN @ColumnName = 'ContactID' THEN ContactID END ASC, " + "CASE WHEN @ColumnName = 'PositionID' THEN PositionID END ASC;"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ContactID"), cNOrd = reader.GetOrdinal("PositionID"); while (reader.Read()) { ContactPosition conPos = new ContactPosition(); conPos.ContactID = reader.GetInt32(cIDOrd); conPos.PositionID = reader.GetInt32(cNOrd); contactpositionList.Add(conPos); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(contactpositionList); }
public static List <State> GetStateList() { List <State> stateList = new List <State>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT StateCode, StateName, FirstZipCode, LastZipCode " + "FROM States " + "ORDER BY StateName"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { State state = new State(); state.StateCode = reader["StateCode"].ToString(); state.StateName = reader["StateName"].ToString(); state.FirstZipCode = (int)reader["FirstZipCode"]; state.LastZipCode = (int)reader["LastZipCode"]; stateList.Add(state); } reader.Close(); } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(stateList); }
public static List <Resume> GetResumeFiltered(string columnName, string columnfilter)// PUTS A FILTER TO THE DATABASE { int filtered = 0; List <Resume> resumeList = new List <Resume>(); 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 ResumeID, RSCDirectoryPath, SchoolID, " + "ClientID FROM dbo.Resume " + "WHERE CASE WHEN @ColumnName = 'ResumeID' AND ResumeID = @Filter THEN 1 " + "WHEN @ColumnName = 'RSCDirectoryPath' AND RSCDirectoryPath LIKE '%' + @Filter + '%' THEN 1 " + "WHERE CASE WHEN @ColumnName = 'SchoolID' AND SchoolID = @Filter THEN 1 " + "WHERE CASE WHEN @ColumnName = 'ClientID' AND ClientID = @Filter THEN 1 WHEN @ColumnName = '' THEN 1 ELSE 0 END = 1"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); if (columnName == "ResumeID" || columnName == "SchoolID" || columnName == "ClientID") { int.TryParse(columnfilter, out filtered); selectCommand.Parameters.AddWithValue("@Filter", filtered); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.Int; } else { selectCommand.Parameters.AddWithValue("@Filter", columnfilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.VarChar; } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ResumeID"), cNOrd = reader.GetOrdinal("RSCDirectoryPath"), cAOrd = reader.GetOrdinal("SchoolID"), cCOrd = reader.GetOrdinal("ClientID"); while (reader.Read()) { Resume resu = new Resume(); resu.ResumeID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { resu.RSCDirectoryPath = ""; } else { resu.RSCDirectoryPath = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { resu.SchoolID = -1; } else { resu.SchoolID = reader.GetInt32(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { resu.ClientID = -1; } else { resu.ClientID = reader.GetInt32(cCOrd); } resumeList.Add(resu); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(resumeList); }
public static List <School> GetSchoolFiltered(string columnName, string columnfilter)// PUTS A FILTER TO THE DATABASE { int filtered = 0; List <School> SchoolList = new List <School>(); 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 SchoolID, SchoolName, StreetName, " + "City, State, ZipCode, NumberOfYearsAttended, Graduated FROM dbo.School " + "WHERE CASE WHEN @ColumnName = 'SchoolID' AND SchoolID = @Filter THEN 1 " + "WHEN @ColumnName = 'SchoolName' AND SchoolName LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'StreetName' AND StreetName LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'City' AND City LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'State' AND State LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'ZipCode' AND ZipCode LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'NumberOfYearsAttended' AND NumberOfYearsAttended LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'Graduated' AND Graduated 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 == "SchoolID") { int.TryParse(columnfilter, out filtered); selectCommand.Parameters.AddWithValue("@Filter", filtered); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.Int; } else { selectCommand.Parameters.AddWithValue("@Filter", columnfilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.VarChar; } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("SchoolID"), cNOrd = reader.GetOrdinal("SchoolName"), cAOrd = reader.GetOrdinal("StreetName"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("NumberOfYearsAttended"), cEOrd = reader.GetOrdinal("Graduated"); while (reader.Read()) { School scho = new School(); scho.SchoolID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { scho.SchoolName = ""; } else { scho.SchoolName = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { scho.StreetName = ""; } else { scho.StreetName = reader.GetString(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { scho.City = ""; } else { scho.City = reader.GetString(cCOrd); } if (reader[cSOrd].Equals(DBNull.Value)) { scho.State = ""; } else { scho.State = reader.GetString(cSOrd); } if (reader[cZCOrd].Equals(DBNull.Value)) { scho.ZipCode = ""; } else { scho.ZipCode = reader.GetString(cZCOrd); } if (reader[cPOrd].Equals(DBNull.Value)) { scho.NumberOfYearsAttended = -1; } else { scho.NumberOfYearsAttended = reader.GetInt32(cPOrd); } if (reader[cEOrd].Equals(DBNull.Value)) { scho.Graduated = ""; } else { scho.Graduated = reader.GetString(cEOrd); } SchoolList.Add(scho); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(SchoolList); }
public static bool UpdateResume(Resume oldResume, Resume newResume)// MODIFIES THE DATABASE A ROW AT A TIME { SqlConnection connection = PRG299DB.GetConnection(); string updateStatement = "UPDATE Resume SET " + "RSCDirectoryPath = @NewRSCDirectoryPath, " + "SchoolID = @NewSchoolID, " + "ClientID = @NewClientID " + "WHERE ResumeID = @OldResumeID " + "AND (RSCDirectoryPath = @OldRSCDirectoryPath " + "OR RSCDirectoryPath IS NULL AND @OldRSCDirectoryPath IS NULL) " + "AND (SchoolID = @OldSchoolID " + "OR SchoolID IS NULL AND @OldSchoolID IS NULL) " + "AND (ClientID = @OldClientID " + "OR ClientID IS NULL AND @OldClientID IS NULL)"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); if (newResume.RSCDirectoryPath == "") { updateCommand.Parameters.AddWithValue("@NewRSCDirectoryPath", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewRSCDirectoryPath", newResume.RSCDirectoryPath); } if (newResume.SchoolID.ToString() == "") { updateCommand.Parameters.AddWithValue("@NewSchoolID", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewSchoolID", newResume.SchoolID); } if (newResume.ClientID.ToString() == "") { updateCommand.Parameters.AddWithValue("@NewClientID", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewClientID", newResume.ClientID); } updateCommand.Parameters.AddWithValue("@OldResumeID", oldResume.ResumeID); if (oldResume.RSCDirectoryPath == "") { updateCommand.Parameters.AddWithValue("@OldRSCDirectoryPath", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldRSCDirectoryPath", oldResume.RSCDirectoryPath); } if (oldResume.SchoolID.ToString() == "") { updateCommand.Parameters.AddWithValue("@OldSchoolID", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldSchoolID", oldResume.SchoolID); } if (oldResume.ClientID.ToString() == "") { updateCommand.Parameters.AddWithValue("@OldClientID", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldClientID", oldResume.ClientID); } 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(); } }
public static List <Resume> GetResumeSorted(string columnName) // SORTS THE DATA IN THE DATABASE { List <Resume> resumeList = new List <Resume>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ResumeID, RSCDirectoryPath, SchoolID, " + "ClientID FROM dbo.Resume " + "ORDER BY CASE WHEN @ColumnName = 'ResumeID' THEN ResumeID END ASC, " + "CASE WHEN @ColumnName = 'RSCDirectoryPath' THEN RSCDirectoryPath END ASC, " + "CASE WHEN @ColumnName = 'SchoolID' THEN SchoolID END ASC, " + "CASE WHEN @ColumnName = 'ClientID' THEN ClientID END ASC;"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ResumeID"), cNOrd = reader.GetOrdinal("RSCDirectoryPath"), cAOrd = reader.GetOrdinal("SchoolID"), cCOrd = reader.GetOrdinal("ClientID"); while (reader.Read()) { Resume resu = new Resume(); resu.ResumeID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { resu.RSCDirectoryPath = ""; } else { resu.RSCDirectoryPath = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { resu.SchoolID = -1; } else { resu.SchoolID = reader.GetInt32(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { resu.ClientID = -1; } else { resu.ClientID = reader.GetInt32(cCOrd); } resumeList.Add(resu); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(resumeList); }
public static List <School> GetSchoolSorted(string columnName)// SORTS THE DATA IN THE DATABASE { List <School> SchoolList = new List <School>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT SchoolID, SchoolName, StreetName, " + "City, State, ZipCode, NumberOfYearsAttended, Graduated FROM dbo.School " + "ORDER BY CASE WHEN @ColumnName = 'SchoolID' THEN SchoolID END ASC, " + "CASE WHEN @ColumnName = 'SchoolName' THEN SchoolName END ASC, " + "CASE WHEN @ColumnName = 'StreetName' THEN StreetName END ASC, " + "CASE WHEN @ColumnName = 'City' THEN City END ASC, " + "CASE WHEN @ColumnName = 'State' THEN State END ASC, " + "CASE WHEN @ColumnName = 'ZipCode' THEN ZipCode END ASC, " + "CASE WHEN @ColumnName = 'NumberOfYearsAttended' THEN NumberOfYearsAttended END ASC, " + "CASE WHEN @ColumnName = 'Graduated' THEN Graduated END ASC;"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@ColumnName", columnName); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("SchoolID"), cNOrd = reader.GetOrdinal("SchoolName"), cAOrd = reader.GetOrdinal("StreetName"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("NumberOfYearsAttended"), cEOrd = reader.GetOrdinal("Graduated"); while (reader.Read()) { School scho = new School(); scho.SchoolID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { scho.SchoolName = ""; } else { scho.SchoolName = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { scho.StreetName = ""; } else { scho.StreetName = reader.GetString(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { scho.City = ""; } else { scho.City = reader.GetString(cCOrd); } if (reader[cSOrd].Equals(DBNull.Value)) { scho.State = ""; } else { scho.State = reader.GetString(cSOrd); } if (reader[cZCOrd].Equals(DBNull.Value)) { scho.ZipCode = ""; } else { scho.ZipCode = reader.GetString(cZCOrd); } if (reader[cPOrd].Equals(DBNull.Value)) { scho.NumberOfYearsAttended = -1; } else { scho.NumberOfYearsAttended = reader.GetInt32(cPOrd); } if (reader[cEOrd].Equals(DBNull.Value)) { scho.Graduated = ""; } else { scho.Graduated = reader.GetString(cEOrd); } SchoolList.Add(scho); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(SchoolList); }
public static bool UpdateSchool(School oldSchool, School newSchool)// MODIFIES THE DATABASE A ROW AT A TIME { SqlConnection connection = PRG299DB.GetConnection(); string updateStatement = "UPDATE School SET " + "SchoolName = @NewSchoolName, " + "StreetName = @NewStreetName, " + "City = @NewCity, " + "State = @NewState, " + "ZipCode = @NewZipCode, " + "NumberofYearsAttended = @NewNumberofYearsAttended, " + "Graduated = @NewGraduated " + "WHERE SchoolID = @OldSchoolID " + "AND (SchoolName = @OldSchoolName " + "OR SchoolName IS NULL AND @OldSchoolName IS NULL) " + "AND (StreetName = @OldStreetName " + "OR StreetName IS NULL AND @OldStreetName IS NULL) " + "AND (City = @OldCity " + "OR City IS NULL AND @OldCity IS NULL) " + "AND (State = @OldState " + "OR State IS NULL AND @OldState IS NULL) " + "AND (ZipCode = @OldZipCode " + "OR ZipCode IS NULL AND @OldZipCode IS NULL) " + "AND (NumberofYearsAttended = @OldNumberofYearsAttended " + "OR NumberofYearsAttended IS NULL AND @OldNumberofYearsAttended IS NULL) " + "AND (Graduated = @OldGraduated " + "OR Graduated IS NULL AND @OldGraduated IS NULL)"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); if (newSchool.SchoolName == "") { updateCommand.Parameters.AddWithValue("@NewSchoolName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewSchoolName", newSchool.SchoolName); } if (newSchool.StreetName == "") { updateCommand.Parameters.AddWithValue("@NewStreetName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewStreetName", newSchool.StreetName); } if (newSchool.City == "") { updateCommand.Parameters.AddWithValue("@NewCity", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewCity", newSchool.City); } if (newSchool.State == "") { updateCommand.Parameters.AddWithValue("@NewState", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewState", newSchool.State); } if (newSchool.ZipCode == "") { updateCommand.Parameters.AddWithValue("@NewZipCode", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewZipCode", newSchool.ZipCode); } if (newSchool.NumberOfYearsAttended == -1) { updateCommand.Parameters.AddWithValue("@NewNumberOfYearsAttended", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewNumberOfYearsAttended", newSchool.NumberOfYearsAttended); } if (newSchool.Graduated == "") { updateCommand.Parameters.AddWithValue("@NewGraduated", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewGraduated", newSchool.Graduated); } updateCommand.Parameters.AddWithValue("@OldSchoolID", oldSchool.SchoolID); if (oldSchool.SchoolName == "") { updateCommand.Parameters.AddWithValue("@OldSchoolName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldSchoolName", oldSchool.SchoolName); } if (oldSchool.StreetName == "") { updateCommand.Parameters.AddWithValue("@OldStreetName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldStreetName", oldSchool.StreetName); } if (oldSchool.City == "") { updateCommand.Parameters.AddWithValue("@OldCity", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldCity", oldSchool.City); } if (oldSchool.State == "") { updateCommand.Parameters.AddWithValue("@OldState", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldState", oldSchool.State); } if (oldSchool.ZipCode == "") { updateCommand.Parameters.AddWithValue("@OldZipCode", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldZipCode", oldSchool.ZipCode); } if (oldSchool.NumberOfYearsAttended == -1) { updateCommand.Parameters.AddWithValue("@OldNumberOfYearsAttended", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldNumberOfYearsAttended", oldSchool.NumberOfYearsAttended); } if (oldSchool.Graduated == "") { updateCommand.Parameters.AddWithValue("@OldGraduated", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldGraduated", oldSchool.Graduated); } 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(); } }
public static Company GetCompanyByRow(int companyID)// GETS ONE ROW AT A TIME FROM THE DATABASE { Company company = new Company(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT CompanyID, CompanyName, BuildingName, BuildingNumber, StreetAddress, " + "City, State, ZipCode, Website, AdditionalNotes FROM dbo.Company WHERE CompanyID = @CompanyID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("@CompanyID", companyID); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("CompanyID"), cNOrd = reader.GetOrdinal("CompanyName"), cBNaOrd = reader.GetOrdinal("BuildingName"), cBNuOrd = reader.GetOrdinal("BuildingNumber"), cAOrd = reader.GetOrdinal("StreetAddress"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("Website"), cEOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { company.CompanyID = reader.GetInt32(cIDOrd); company.CompanyName = reader.GetString(cNOrd); if (reader["BuildingName"].Equals(DBNull.Value)) { company.BuildingName = ""; } else { company.BuildingName = reader.GetString(cBNaOrd); } if (reader["BuildingNumber"].Equals(DBNull.Value)) { company.BuildingNumber = ""; } else { company.BuildingNumber = reader.GetString(cBNuOrd); } company.StreetAddress = reader.GetString(cAOrd); company.City = reader.GetString(cCOrd); company.State = reader.GetString(cSOrd); company.ZipCode = reader.GetString(cZCOrd); if (reader["Website"].Equals(DBNull.Value)) { company.Website = ""; } else { company.Website = reader.GetString(cPOrd); } if (reader["AdditionalNotes"].Equals(DBNull.Value)) { company.AdditionalNotes = ""; } else { company.AdditionalNotes = reader.GetString(cEOrd); } if (companyID == company.CompanyID) { break; } } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(company); }
public static List <Resume> GetResume() // USED TO POPULATE THE DATA GRID TABLE ALSO USED TO REFRESH THE DATA GRID { List <Resume> resumeList = new List <Resume>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT ResumeID, RSCDirectoryPath, SchoolID, " + "ClientID FROM dbo.Resume"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("ResumeID"), cNOrd = reader.GetOrdinal("RSCDirectoryPath"), cAOrd = reader.GetOrdinal("SchoolID"), cCOrd = reader.GetOrdinal("ClientID"); while (reader.Read()) { Resume resu = new Resume(); resu.ResumeID = reader.GetInt32(cIDOrd); if (reader[cNOrd].Equals(DBNull.Value)) { resu.RSCDirectoryPath = ""; } else { resu.RSCDirectoryPath = reader.GetString(cNOrd); } if (reader[cAOrd].Equals(DBNull.Value)) { resu.SchoolID = -1; } else { resu.SchoolID = reader.GetInt32(cAOrd); } if (reader[cCOrd].Equals(DBNull.Value)) { resu.ClientID = -1; } else { resu.ClientID = reader.GetInt32(cCOrd); } resumeList.Add(resu); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(resumeList); }
public static List <Company> GetCompanyFiltered(string columnName, string columnfilter)// PUTS A FILTER TO THE DATABASE { int filtered = 0; List <Company> companyList = new List <Company>(); 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 CompanyID, CompanyName, BuildingName, BuildingNumber, StreetAddress, " + "City, State, ZipCode, Website, AdditionalNotes FROM dbo.Company " + "WHERE CASE WHEN @ColumnName = 'CompanyID' AND CompanyID = @Filter THEN 1 " + "WHEN @ColumnName = 'CompanyName' AND CompanyName LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'BuildingName' AND BuildingName LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'BuildingNumber' AND BuildingNumber LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'StreetAddress' AND StreetAddress LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'City' AND City LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'State' AND State LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'ZipCode' AND ZipCode LIKE '%' + @Filter + '%' THEN 1 " + "WHEN @ColumnName = 'Website' AND Website LIKE '%' + @Filter + '%' 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 == "CompanyID") { int.TryParse(columnfilter, out filtered); selectCommand.Parameters.AddWithValue("@Filter", filtered); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.Int; } else { selectCommand.Parameters.AddWithValue("@Filter", columnfilter); selectCommand.Parameters["@Filter"].SqlDbType = SqlDbType.VarChar; } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("CompanyID"), cNOrd = reader.GetOrdinal("CompanyName"), cBNaOrd = reader.GetOrdinal("BuildingName"), cBNuOrd = reader.GetOrdinal("BuildingNumber"), cAOrd = reader.GetOrdinal("StreetAddress"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("Website"), cEOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Company comp = new Company(); comp.CompanyID = reader.GetInt32(cIDOrd); comp.CompanyName = reader.GetString(cNOrd); if (reader["BuildingName"].Equals(DBNull.Value)) { comp.BuildingName = ""; } else { comp.BuildingName = reader.GetString(cBNaOrd); } if (reader["BuildingNumber"].Equals(DBNull.Value)) { comp.BuildingNumber = ""; } else { comp.BuildingNumber = reader.GetString(cBNuOrd); } comp.StreetAddress = reader.GetString(cAOrd); comp.City = reader.GetString(cCOrd); comp.State = reader.GetString(cSOrd); comp.ZipCode = reader.GetString(cZCOrd); if (reader["Website"].Equals(DBNull.Value)) { comp.Website = ""; } else { comp.Website = reader.GetString(cPOrd); } if (reader["AdditionalNotes"].Equals(DBNull.Value)) { comp.AdditionalNotes = ""; } else { comp.AdditionalNotes = reader.GetString(cEOrd); } companyList.Add(comp); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(companyList); }
public static List <Company> GetCompanySorted(string columnName) // SORTS THE DATA IN THE DATABASE { List <Company> companyList = new List <Company>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT CompanyID, CompanyName, BuildingName, BuildingNumber, StreetAddress, " + "City, State, ZipCode, Website, AdditionalNotes FROM dbo.Company " + "ORDER BY CASE WHEN @ColumnName = 'CompanyID' THEN CompanyID END ASC, " + "CASE WHEN @ColumnName = 'CompanyName' THEN CompanyName END ASC, " + "CASE WHEN @ColumnName = 'BuildingName' THEN BuildingName END ASC, " + "CASE WHEN @ColumnName = 'BuildingNumber' THEN BuildingName END ASC, " + "CASE WHEN @ColumnName = 'StreetAddress' THEN StreetAddress END ASC, " + "CASE WHEN @ColumnName = 'City' THEN City END ASC, " + "CASE WHEN @ColumnName = 'State' THEN State END ASC, " + "CASE WHEN @ColumnName = 'ZipCode' THEN ZipCode END ASC, " + "CASE WHEN @ColumnName = 'Website' THEN Website 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("CompanyID"), cNOrd = reader.GetOrdinal("CompanyName"), cBNaOrd = reader.GetOrdinal("BuildingName"), cBNuOrd = reader.GetOrdinal("BuildingNumber"), cAOrd = reader.GetOrdinal("StreetAddress"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("Website"), cEOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Company comp = new Company(); comp.CompanyID = reader.GetInt32(cIDOrd); comp.CompanyName = reader.GetString(cNOrd); if (reader["BuildingName"].Equals(DBNull.Value)) { comp.BuildingName = ""; } else { comp.BuildingName = reader.GetString(cBNaOrd); } if (reader["BuildingNumber"].Equals(DBNull.Value)) { comp.BuildingNumber = ""; } else { comp.BuildingNumber = reader.GetString(cBNuOrd); } comp.StreetAddress = reader.GetString(cAOrd); comp.City = reader.GetString(cCOrd); comp.State = reader.GetString(cSOrd); comp.ZipCode = reader.GetString(cZCOrd); if (reader["Website"].Equals(DBNull.Value)) { comp.Website = ""; } else { comp.Website = reader.GetString(cPOrd); } if (reader["AdditionalNotes"].Equals(DBNull.Value)) { comp.AdditionalNotes = ""; } else { comp.AdditionalNotes = reader.GetString(cEOrd); } companyList.Add(comp); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(companyList); }
public static bool UpdateCompany(Company oldCompany, Company newCompany)// MODIFIES THE DATABASE A ROW AT A TIME { SqlConnection connection = PRG299DB.GetConnection(); string updateStatement = "UPDATE Company SET " + "CompanyName = @NewCompanyName, " + "BuildingName = @NewBuildingName, " + "BuildingNumber = @NewBuildingNumber, " + "StreetAddress = @NewStreetAddress, " + "City = @NewCity, " + "State = @NewState, " + "ZipCode = @NewZipCode, " + "Website = @NewWebsite, " + "AdditionalNotes = @NewAdditionalNotes " + "WHERE CompanyID = @OldCompanyID " + "AND CompanyName = @OldCompanyName " + "AND (BuildingName = @OldBuildingName " + "OR BuildingName IS NULL AND @OldBuildingName IS NULL) " + "AND (BuildingNumber = @OldBuildingNumber " + "OR BuildingNumber IS NULL AND @OldBuildingNumber IS NULL) " + "AND StreetAddress = @OldStreetAddress " + "AND City = @OldCity " + "AND State = @OldState " + "AND ZipCode = @OldZipCode " + "AND (Website = @OldWebsite " + "OR Website IS NULL AND @OldWebsite IS NULL) " + "AND (AdditionalNotes = @OldAdditionalNotes " + "OR AdditionalNotes IS NULL AND @OldAdditionalNotes IS NULL)"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("@NewCompanyName", newCompany.CompanyName); if (newCompany.BuildingName == "") { updateCommand.Parameters.AddWithValue("@NewBuildingName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewBuildingName", newCompany.BuildingName); } if (newCompany.BuildingNumber == "") { updateCommand.Parameters.AddWithValue("@NewBuildingNumber", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewBuildingNumber", newCompany.BuildingNumber); } updateCommand.Parameters.AddWithValue("@NewStreetAddress", newCompany.StreetAddress); updateCommand.Parameters.AddWithValue("@NewCity", newCompany.City); updateCommand.Parameters.AddWithValue("@NewState", newCompany.State); updateCommand.Parameters.AddWithValue("@NewZipCode", newCompany.ZipCode); if (newCompany.Website == "") { updateCommand.Parameters.AddWithValue("@NewWebsite", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewWebsite", newCompany.Website); } if (newCompany.AdditionalNotes == "") { updateCommand.Parameters.AddWithValue("@NewAdditionalNotes", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@NewAdditionalNotes", newCompany.AdditionalNotes); } updateCommand.Parameters.AddWithValue("@OldCompanyID", oldCompany.CompanyID); updateCommand.Parameters.AddWithValue("@OldCompanyName", oldCompany.CompanyName); if (oldCompany.BuildingName == "") { updateCommand.Parameters.AddWithValue("@OldBuildingName", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldBuildingName", oldCompany.BuildingName); } if (oldCompany.BuildingNumber == "") { updateCommand.Parameters.AddWithValue("@OldBuildingNumber", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldBuildingNumber", oldCompany.BuildingNumber); } updateCommand.Parameters.AddWithValue("@OldStreetAddress", oldCompany.StreetAddress); updateCommand.Parameters.AddWithValue("@OldCity", oldCompany.City); updateCommand.Parameters.AddWithValue("@OldState", oldCompany.State); updateCommand.Parameters.AddWithValue("@OldZipCode", oldCompany.ZipCode); if (oldCompany.Website == "") { updateCommand.Parameters.AddWithValue("@OldWebsite", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldWebsite", oldCompany.Website); } if (oldCompany.AdditionalNotes == "") { updateCommand.Parameters.AddWithValue("@OldAdditionalNotes", DBNull.Value); } else { updateCommand.Parameters.AddWithValue("@OldAdditionalNotes", oldCompany.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(); } }
public static int AddCompany(Company company)// ADDS A NEW ROW TO THE DATABASE { SqlConnection connection = PRG299DB.GetConnection(); string insertStatement = // "SET IDENTITY_INSERT Company ON; " + "INSERT Company " + "(CompanyName, BuildingName, BuildingNumber, StreetAddress, " + "City, State, ZipCode, Website, AdditionalNotes) " + "VALUES (@CompanyName, @BuildingName, @BuildingNumber, @StreetAddress, " + "@City, @State, @ZipCode, @Website, @AdditionalNotes)"; // " SET IDENTITY_INSERT Company OFF;"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@CompanyName", company.CompanyName); if (company.BuildingName == null || company.BuildingName == "") { insertCommand.Parameters.AddWithValue("@BuildingName", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@BuildingName", company.BuildingName); } if (company.BuildingNumber == null || company.BuildingNumber == "") { insertCommand.Parameters.AddWithValue("@BuildingNumber", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@BuildingNumber", company.BuildingNumber); } insertCommand.Parameters.AddWithValue("@StreetAddress", company.StreetAddress); insertCommand.Parameters.AddWithValue("@City", company.City); insertCommand.Parameters.AddWithValue("@State", company.State); insertCommand.Parameters.AddWithValue("@ZipCode", company.ZipCode); if (company.Website == null || company.Website == "") { insertCommand.Parameters.AddWithValue("@Website", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@Website", company.Website); } if (company.AdditionalNotes == null || company.AdditionalNotes == "") { insertCommand.Parameters.AddWithValue("@AdditionalNotes", DBNull.Value); } else { insertCommand.Parameters.AddWithValue("@AdditionalNotes", company.AdditionalNotes); } try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Company') FROM Company"; 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 <Company> GetCompany()// USED TO POPULATE THE DATA GRID TABLE ALSO USED TO REFRESH THE DATA GRID { List <Company> companyList = new List <Company>(); SqlConnection connection = PRG299DB.GetConnection(); string selectStatement = "SELECT CompanyID, CompanyName, BuildingName, BuildingNumber, StreetAddress, " + "City, State, ZipCode, Website, AdditionalNotes FROM dbo.Company"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); int cIDOrd = reader.GetOrdinal("CompanyID"), cNOrd = reader.GetOrdinal("CompanyName"), cBNaOrd = reader.GetOrdinal("BuildingName"), cBNuOrd = reader.GetOrdinal("BuildingNumber"), cAOrd = reader.GetOrdinal("StreetAddress"), cCOrd = reader.GetOrdinal("City"), cSOrd = reader.GetOrdinal("State"), cZCOrd = reader.GetOrdinal("ZipCode"), cPOrd = reader.GetOrdinal("Website"), cEOrd = reader.GetOrdinal("AdditionalNotes"); while (reader.Read()) { Company comp = new Company(); comp.CompanyID = reader.GetInt32(cIDOrd); comp.CompanyName = reader.GetString(cNOrd); if (reader["BuildingName"].Equals(DBNull.Value)) { comp.BuildingName = ""; } else { comp.BuildingName = reader.GetString(cBNaOrd); } if (reader["BuildingNumber"].Equals(DBNull.Value)) { comp.BuildingNumber = ""; } else { comp.BuildingNumber = reader.GetString(cBNuOrd); } comp.StreetAddress = reader.GetString(cAOrd); comp.City = reader.GetString(cCOrd); comp.State = reader.GetString(cSOrd); comp.ZipCode = reader.GetString(cZCOrd); if (reader["Website"].Equals(DBNull.Value)) { comp.Website = ""; } else { comp.Website = reader.GetString(cPOrd); } if (reader["AdditionalNotes"].Equals(DBNull.Value)) { comp.AdditionalNotes = ""; } else { comp.AdditionalNotes = reader.GetString(cEOrd); } companyList.Add(comp); } } catch (SqlException ex) { throw ex; } catch (Exception ex) { throw ex; } finally { connection.Close(); } return(companyList); }