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