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