public static List <showApplicationClass> getAll(string applicationID, string qualificationID, string status) //match: applicationID
{
List <showApplicationClass> recordList = new List <showApplicationClass>();
// Connection string
String connStr = "data source=localhost;initial catalog=NoTreal;integrated security=true";
SqlConnection dbConn = new SqlConnection(connStr);
SqlCommand sqlStmt = new SqlCommand("SELECT applicationfee, a.description, type, name, country, admittedDate, graduationDate, programTitle, gpa, q.description, testID, testName, testScore, testDate, testLocation FROM Application a, AcademicQualification q, EnglishTest e WHERE a.qualificationID=q.qualificationID and e.applicationID=a.applicationID and a.applicationID= '" + applicationID + "' and a.qualificationID = '" + qualificationID + "' and status = '" + status + "'",
dbConn);
SqlDataReader reader = null;
try
{
dbConn.Open(); // Attempt to connect to database
reader = sqlStmt.ExecuteReader();
if (reader.HasRows)
{
// Need a loop to process all the rows inside reader
while (reader.Read())
{
// Now process one row
Decimal applicationFee = reader.GetDecimal(0);
String applicationDescription = reader.GetString(1);
String type = reader.GetString(2);
String name = reader.GetString(3);
String country = reader.GetString(4);
DateTime admittedDate = reader.GetDateTime(5);
DateTime graduationDate = reader.GetDateTime(6);
String programTitle = reader.GetString(7);
Decimal gpa = reader.GetDecimal(8);
String qualificationDescription = reader.GetString(9);
String testID = reader.GetString(10);
String testName = reader.GetString(11);
Decimal testScore = reader.GetDecimal(12);
DateTime testDate = reader.GetDateTime(13);
String testLocation = reader.GetString(14);
// Create an object
showApplicationClass record = new showApplicationClass(applicationFee, applicationDescription, type, name, country, admittedDate, graduationDate, programTitle, gpa, qualificationDescription, testID, testName, testScore, testDate, testLocation);
// Add to List<showApplicationClass>
recordList.Add(record);
}
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "Error");
}
finally
{
if (reader != null)
{
reader.Close();
}
if (dbConn != null)
{
dbConn.Close();
}
}
return(recordList);
}
public static List <showApplicationClass> getRecordID()
{
List <showApplicationClass> recordList = new List <showApplicationClass>();
// Connection string
String connStr = "data source=localhost;initial catalog=NoTreal;integrated security=true";
SqlConnection dbConn = new SqlConnection(connStr);
SqlCommand sqlStmt = new SqlCommand("SELECT applicationID, qualificationID, status FROM Application",
dbConn);
SqlDataReader reader = null;
try
{
dbConn.Open(); // Attempt to connect to database
reader = sqlStmt.ExecuteReader();
if (reader.HasRows)
{
// Need a loop to process all the rows inside reader
while (reader.Read())
{
// Now process one row
String applicationID = reader.GetString(0);
String qualificationID = reader.GetString(1);
String status = reader.GetString(2);
// Create an object
showApplicationClass record = new showApplicationClass(applicationID, qualificationID, status);
// Add to List<showApplicationClass>
recordList.Add(record);
}
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "Error");
}
finally
{
if (reader != null)
{
reader.Close();
}
if (dbConn != null)
{
dbConn.Close();
}
}
return(recordList);
}
public static showApplicationClass validateTest(String applicationID)
{
showApplicationClass record = null;
String connStr = "data source=localhost;initial catalog=NoTreal;integrated security=true";
SqlConnection dbConn = new SqlConnection(connStr);
SqlCommand sqlStmt =
new SqlCommand("SELECT testID FROM englishTest WHERE applicationID = @papplicationID",
dbConn);
SqlParameter param = new SqlParameter("@papplicationID", applicationID);
sqlStmt.Parameters.Add(param);
SqlDataReader reader = null;
try
{
dbConn.Open();
reader = sqlStmt.ExecuteReader();
bool isEnglishNull = false;
if (reader.HasRows)
{
reader.Read();
if (reader["testID"] == DBNull.Value)
{
isEnglishNull = true;
}
Console.WriteLine(isEnglishNull);
record = new showApplicationClass(applicationID, isEnglishNull);
}
}
catch (SqlException error)
{
MessageBox.Show(error.Message, "Error");
}
finally
{
if (reader != null)
{
reader.Close();
}
if (dbConn != null)
{
dbConn.Close();
}
}
return(record);
}
