private void FillQuestionsForDisplay(int id)
{
mQuestions = new List <Question>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT QuesID
FROM askstu_Question
WHERE (CategoryID = @myParam) AND (SubmitFAQ = 1)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
mQuestions.Add(new Question(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
Console.WriteLine(f.ToString());
}
finally
{
tempConn.CloseConnection();
}
}
public void Delete()
{
if (mWasFilled)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlParameter lId = new SqlParameter("@id", SqlDbType.Int);
lId.Value = mId;
SqlCommand myCommand = new SqlCommand(@"
DELETE FROM news_events
WHERE (id = @id)", tempConn.myConnection);
myCommand.Parameters.Add(lId);
myCommand.ExecuteNonQuery();
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
else
{
throw new Exception("Delete Failed Because Object not created from DB");
}
}
private void GetListPhotos()
{
mPhotos = new List <Image>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mId;
SqlCommand myCommand = new SqlCommand(
@"SELECT PhotoID
FROM photos_Images
WHERE AlbumID = @myParam",
tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
mPhotos.Add(new Image(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void GetCourses()
{
mCourses = new List <Course>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mId;
SqlCommand myCommand = new SqlCommand(@"
SELECT CourseID
FROM crshelp_TutoringCourses
WHERE (StuID = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
mCourses.Add(new Course(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
Console.WriteLine(f.ToString());
}
finally
{
tempConn.CloseConnection();
}
}
public static List <Testimonial> GetTestimonials()
{
List <Testimonial> temp = new List <Testimonial>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(@"
SELECT StuTID
FROM askstu_Testimonial
ORDER BY GradYear DESC", tempConn.myConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
temp.Add(new Testimonial(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
return(temp);
}
private void GetUserIDByLogin()
{
//Connect to DB
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
//Attempt to read the data from server
try
{
SqlDataReader myReader = null;
SqlParameter myLogin = new SqlParameter("@myLogin", SqlDbType.VarChar);
myLogin.Value = m_loginName;
SqlCommand myCommand = new SqlCommand("SELECT aspnetID FROM users WHERE = @myLogin", tempConn.myConnection);
myCommand.Parameters.Add(myLogin);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
m_id = (Guid)myReader["aspnetID"];
}
}
catch (Exception f)
{
Console.WriteLine(f.ToString());
}
tempConn.CloseConnection();
}
public static List <Course> GetListCSTCourses()
{
List <Course> tempList = new List <Course>(); //The list that will be returned.
Course tempCourse = new Course();
//Create connection string and attempt to connect
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
//Attempt to read the data from server and if possible tie data from server object's properties
try
{
//Create reader variable
SqlDataReader myReader = null;
//Create command
SqlCommand myCommand = new SqlCommand(@"
SELECT course_id, short_name, long_name, credit_hours, lecture_hours,
lab_hours, official_description, active
FROM Courses
WHERE short_name like 'CST%' AND active=1
ORDER BY short_name", tempConn.myConnection);
//Start reading results
myReader = myCommand.ExecuteReader();
//While there are more rows to read continue to do so.
//Due to this if there are more than one record with the same ID then the last one
//retrieved from the DB will be the one that will reside in the object.
//This should not happen if DB is done properly.
while (myReader.Read())
{
tempCourse = new Course();
tempCourse.mId = int.Parse(myReader[0].ToString());
tempCourse.mShortName = myReader[1].ToString();
tempCourse.mLongName = myReader[2].ToString();
tempCourse.mCreditHours = int.Parse(myReader[3].ToString());
tempCourse.mLectureHours = int.Parse(myReader[4].ToString());
tempCourse.mLabHours = int.Parse(myReader[5].ToString());
tempCourse.mOfficialDesc = myReader[6].ToString();
tempCourse.mActive = (bool)myReader[7];
tempList.Add(tempCourse);
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
//Close the connection
tempConn.CloseConnection();
}
return(tempList);
}
private void GetCourseByID(int id)
{
mId = id; //Set the object.mId to the id we want to read from the DB if available.
//Create connection string and attempt to connect
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
//Attempt to read the data from server and if possible tie data from server to the object's corresponding properties.
try
{
//Create reader
SqlDataReader myReader = null;
//Create any parameters that we need to look for in DB
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mId;
//Create an SQL command
SqlCommand myCommand = new SqlCommand(@"
SELECT short_name, long_name, credit_hours, lecture_hours,
lab_hours, official_description, active
FROM Courses
Where course_id = @myParam", tempConn.myConnection);
//Add parameters to SQL command
myCommand.Parameters.Add(myParam);
//Begin reading from DB
myReader = myCommand.ExecuteReader();
//While there are more rows to read continue to do so.
//Due to this if there are more than one record with the same ID then the last one
//retrieved from the DB will be the one that will reside in the object.
//This should not happen if DB is done properly.
if (!myReader.HasRows)
{
throw new Exception("No Course Recorded with the given id.");
}
while (myReader.Read())
{
mShortName = myReader[0].ToString();
mLongName = myReader[1].ToString();
mCreditHours = int.Parse(myReader[2].ToString());
mLectureHours = int.Parse(myReader[3].ToString());
mLabHours = int.Parse(myReader[4].ToString());
mOfficialDesc = myReader[5].ToString();
mActive = (bool)myReader[6];
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
//Close the connection
tempConn.CloseConnection();
}
}
private void GetNameOfClassesDB(int labID)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetlabs");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam3 = new SqlParameter("@Param3", SqlDbType.Int);
myParam3.Value = labID;
SqlCommand myCommand = new SqlCommand("SELECT DayWeekID, TimeDayID, Status FROM TimeBlock Where LabID = @Param3", tempConn.myConnection);
myCommand.Parameters.Add(myParam3);
myReader = myCommand.ExecuteReader();
int r = 0,
c = 0;
if (!myReader.HasRows)
{
throw new Exception("No records exist for a lab with the given id.");
}
while (myReader.Read())
{
r = int.Parse(myReader[1].ToString()) - 1;
c = int.Parse(myReader[0].ToString()) - 1;
classNames[r, c] = myReader[2].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
for (int r = 0; r < 15; r++)
{
for (int c = 0; c < 7; c++)
{
if (classNames[r, c] == "Closed")
{
backgroundColor[r, c] = "#990000";
}
else if (classNames[r, c] == "Open")
{
backgroundColor[r, c] = "#006600";
}
else
{
backgroundColor[r, c] = "#0000FF";
}
}
}
}
public void GetAwardById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
//Create reader
SqlDataReader myReader = null;
//Create any parameters that we need to look for in DB
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
//Create an SQL command
SqlCommand myCommand = new SqlCommand(@"
SELECT awards_Awards_Students.StudentAwardID, awards_Awards.Title, awards_Awards.Description, awards_Students.FirstName, awards_Students.LastName,
awards_Awards_Students.Year, awards_Awards_Students.Description AS SpecDescription
FROM awards_Awards_Students INNER JOIN
awards_Students ON awards_Awards_Students.StudentID = awards_Students.StudentID INNER JOIN
awards_Awards ON awards_Awards_Students.AwardID = awards_Awards.AwardID
WHERE (awards_Awards_Students.StudentAwardID = @myParam)
ORDER BY awards_Awards_Students.StudentAwardID", tempConn.myConnection);
//Add parameters to SQL command
myCommand.Parameters.Add(myParam);
//Begin reading from DB
myReader = myCommand.ExecuteReader();
//While there are more rows to read continue to do so.
//Due to this if there are more than one record with the same ID then the last one
//retrieved from the DB will be the one that will reside in the object.
//This should not happen if DB is done properly.
if (!myReader.HasRows)
{
throw new Exception("No Award recorded with the given id.");
}
while (myReader.Read())
{
mAwardEntryId = int.Parse(myReader[0].ToString());
mAwardTitle = myReader[1].ToString();
mAwardDescription = myReader[2].ToString();
mStudentName = myReader[3].ToString() + " " + myReader[4].ToString();
mYear = int.Parse(myReader[5].ToString());
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally //Always executes
{
//Close the connection
tempConn.CloseConnection();
}
}
public static List <SelectListItem> GetListLabs(int labId)
{
List <SelectListItem> listOfLabs = new List <SelectListItem>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetlabs");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand("SELECT Lab, LabID FROM Lab ORDER BY Lab", tempConn.myConnection);
myReader = myCommand.ExecuteReader();
int tempCheck = 0;
if (!myReader.HasRows)
{
throw new Exception("No records exist for labs in the database.");
}
while (myReader.Read())
{
tempCheck = int.Parse(myReader[1].ToString());
if (tempCheck != labId)
{
listOfLabs.Add(new SelectListItem
{
Text = myReader[0].ToString(),
Value = myReader[1].ToString()
});
}
else
{
listOfLabs.Add(new SelectListItem
{
Text = myReader[0].ToString(),
Value = myReader[1].ToString(),
Selected = true
});
}
}
}
catch (Exception f)
{
//Add log exceptions
throw;
}
finally
{
tempConn.CloseConnection();
}
return(listOfLabs);
}
private void FillFacultyById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(
@"SELECT UserID, FirstName, LastName, PreferredName, Title, Phone,
Email, Degrees, ImageFile, Office, Website, Active
FROM Faculty
WHERE FacultyID = @myParam", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record for a Faculty with the given id exists.");
}
while (myReader.Read())
{
mFacultyId = id;
mUserId = int.Parse(myReader[0].ToString());
mFirstName = myReader[1].ToString();
mLastName = myReader[2].ToString();
mPreferredName = myReader[3].ToString();
mTitle = myReader[4].ToString();
mPhone = myReader[5].ToString();
mEmail = myReader[6].ToString();
//mDegrees = myReader[7].ToString();
mImage = new Image(myReader[8].ToString());
//mThumbFile = "th_" + mImageFile;
mOffice = myReader[9].ToString();
mWebsite = myReader[10].ToString();
mActive = (bool)myReader[11];
FillListOfCoursesTaught();
FillListOfDegrees();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
//Close the connection
tempConn.CloseConnection();
}
}
public static void InsertNewsEvent(string title, string subtitle, string message,
string picture, DateTime startDate, DateTime endDate, int userID, int type,
bool notify)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlParameter lTitle = new SqlParameter("@title", SqlDbType.VarChar);
lTitle.Value = title;
SqlParameter lSub = new SqlParameter("@subtitle", SqlDbType.VarChar);
lSub.Value = subtitle;
SqlParameter lMess = new SqlParameter("@message", SqlDbType.Text);
lMess.Value = message;
SqlParameter lStart = new SqlParameter("@start", SqlDbType.DateTime);
lStart.Value = startDate;
SqlParameter lEnd = new SqlParameter("@end", SqlDbType.DateTime);
lEnd.Value = endDate;
SqlParameter lUser = new SqlParameter("@user", SqlDbType.Int);
lUser.Value = userID;
SqlParameter lType = new SqlParameter("@type", SqlDbType.Int);
lType.Value = type;
SqlParameter lNot = new SqlParameter("@notify", SqlDbType.Bit);
lNot.Value = notify;
SqlParameter lPic = new SqlParameter("@picture", SqlDbType.VarChar);
lPic.Value = picture;
SqlCommand myCommand = new SqlCommand(@"
INSERT INTO news_events (title, subtitle, message, start_date, end_date, [user], type, notify, image_name)
VALUES (@title, @subtitle, @message, @start, @end, @user, @type, @notify, @picture)", tempConn.myConnection);
myCommand.Parameters.Add(lTitle);
myCommand.Parameters.Add(lSub);
myCommand.Parameters.Add(lMess);
myCommand.Parameters.Add(lStart);
myCommand.Parameters.Add(lEnd);
myCommand.Parameters.Add(lUser);
myCommand.Parameters.Add(lType);
myCommand.Parameters.Add(lNot);
myCommand.Parameters.Add(lPic);
myCommand.ExecuteNonQuery();
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
public static List <SelectListItem> GetListOfDegrees(int currentDegreeId = 1)
{
List <SelectListItem> tempList = new List <SelectListItem>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(@"
SELECT short_name_text, degree_id
FROM Degrees", tempConn.myConnection);
myReader = myCommand.ExecuteReader();
int tempCheck = 0;
while (myReader.Read())
{
tempCheck = int.Parse(myReader[1].ToString());
if (tempCheck != currentDegreeId)
{
tempList.Add(new SelectListItem
{
Text = myReader[0].ToString(),
Value = myReader[1].ToString()
});
}
else
{
tempList.Add(new SelectListItem
{
Text = myReader[0].ToString(),
Value = myReader[1].ToString(),
Selected = true
});
}
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
return(tempList);
}
public static List <SelectListItem> GetDomains(string currentlySelected = "")
{
List <SelectListItem> tempList = new List <SelectListItem>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("PrintManager");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlCommand myCommand = new SqlCommand(
@"SELECT DISTINCT DomainName
FROM UserQuotas",
tempConn.myConnection);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
if (myReader["DomainName"].ToString() != currentlySelected)
{
tempList.Add(new SelectListItem
{
Text = myReader["DomainName"].ToString(),
Value = myReader["DomainName"].ToString()
});
}
else
{
tempList.Add(new SelectListItem
{
Text = myReader["DomainName"].ToString(),
Value = myReader["DomainName"].ToString(),
Selected = true
});
}
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
return(tempList);
}
public static void InsertRecruit(string first, string last, string street,
string city, string state, string zip, string phone, string email,
string school)
{
//Create connection string and attempt to connect
ConnectSovereignDB tempConn = new ConnectSovereignDB("Recruiting");
tempConn.OpenConnection();
try
{
SqlParameter fName = new SqlParameter("@first", SqlDbType.VarChar);
fName.Value = first;
SqlParameter lName = new SqlParameter("@last", SqlDbType.VarChar);
lName.Value = last;
SqlParameter emailP = new SqlParameter("@email", SqlDbType.VarChar);
emailP.Value = email;
SqlParameter streetP = new SqlParameter("@street", SqlDbType.VarChar);
streetP.Value = street;
SqlParameter cityP = new SqlParameter("@city", SqlDbType.VarChar);
cityP.Value = city;
SqlParameter stateP = new SqlParameter("@state", SqlDbType.Char);
stateP.Value = state;
SqlParameter zipP = new SqlParameter("@zip", SqlDbType.VarChar);
zipP.Value = zip;
SqlParameter phoneP = new SqlParameter("@phone", SqlDbType.VarChar);
phoneP.Value = phone;
SqlCommand myCommand = new SqlCommand(@"
INSERT INTO Tour (FirstName, LastName, Street, City, State, Zip, Phone, Email)
VALUES (@first, @last, @street, @city, @state, @zip, @phone, @email)", tempConn.myConnection);
myCommand.Parameters.Add(fName);
myCommand.Parameters.Add(lName);
myCommand.Parameters.Add(emailP);
myCommand.Parameters.Add(streetP);
myCommand.Parameters.Add(cityP);
myCommand.Parameters.Add(stateP);
myCommand.Parameters.Add(zipP);
myCommand.Parameters.Add(phoneP);
myCommand.ExecuteNonQuery();
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void FillEventById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@Param", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT news_events.id, news_events.title, news_events.subtitle, news_events.message, news_events.start_date, news_events.end_date,
news_events.image_name, users.first_name, users.last_name, news_events.notify, news_events.type
FROM news_events INNER JOIN
users ON news_events.[user] = users.id
WHERE (news_events.id = @Param)",
tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists of a NewsEvent with the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mTitle = myReader[1].ToString();
mSubtitle = myReader[2].ToString();
mMessage = myReader[3].ToString();
mStartDate = DateTime.Parse(myReader[4].ToString());
mEndDate = DateTime.Parse(myReader[5].ToString());
mPicture = new Image(myReader[6].ToString());
mUserName = myReader[7].ToString() + " " + myReader[8].ToString();
//mAlumni = bool.Parse(myReader[9].ToString());
mAlumni = false;
mType = int.Parse(myReader[10].ToString());
mWasFilled = true;
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void GetImageByFilename(string filename)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.VarChar);
myParam.Value = filename;
SqlCommand myCommand = new SqlCommand(@"
SELECT PhotoID, Caption, Filename, Thumbnail, Path
FROM photos_Images
WHERE (Filename = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No image exists in the db with the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mCaption = myReader[1].ToString();
mFilename = myReader[2].ToString();
mThumbFile = myReader[3].ToString();
mPath = myReader[4].ToString();
if (string.IsNullOrEmpty(mThumbFile))
{
mThumbFile = mFilename;
}
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
public void GetDegreeById(int id)
{
mId = id;
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mId;
SqlCommand myCommand = new SqlCommand(@"
SELECT short_name, long_name, official_description, active, degree_type_id,
short_name_text
FROM Degrees
WHERE degree_id = @myParam", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No Degree is recorded with the given id.");
}
while (myReader.Read())
{
mShortName = myReader[0].ToString();
mLongName = myReader[1].ToString();
mOfficialDesc = myReader[2].ToString();
mActive = (bool)myReader[3];
mDegreeType = new DegreeType(int.Parse(myReader[4].ToString()));
mShortNameText = myReader[5].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private int GetIDLabFromNameDB(string labName)
{
int labID = 1;
//Connect to DB
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetlabs");
tempConn.OpenConnection();
//Attempt to read the data from server and if possible tie data from server to properties
try
{
SqlDataReader myReader = null;
SqlParameter myParam1 = new SqlParameter("@Param1", SqlDbType.Char);
myParam1.Value = labName;
SqlCommand myCommand = new SqlCommand("SELECT LabID FROM Lab Where Lab = @Param1", tempConn.myConnection);
myCommand.Parameters.Add(myParam1);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No records exist for a Lab with the given name/number.");
}
while (myReader.Read())
{
labID = int.Parse(myReader[0].ToString());
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
if (labID == 0)
{
labID = 1;
}
return(labID);
}
private void FillTestimonialById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT StuTID, Fname, Lname, Testimonial, GradYear, Employer, PictureURL, ThumbURL
FROM askstu_Testimonial
WHERE (StuTID = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists of a Testimonial with the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mFirstName = myReader[1].ToString();
mLastName = myReader[2].ToString();
mTestimonial = myReader[3].ToString();
mGradYear = int.Parse(myReader[4].ToString());
mEmployer = myReader[5].ToString();
mPicture = new Image(myReader[6].ToString());
//mThumb = myReader[7].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
FillDegrees();
}
private void FillListOfDegrees()
{
mDegrees = new List <string>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mFacultyId;
SqlCommand myCommand = new SqlCommand(@"
SELECT Degree, Year
FROM Faculty_Degrees
WHERE (FacultyID = @myParam)
ORDER BY Year DESC", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
string temp = "";
string year = "";
while (myReader.Read())
{
temp = myReader[0].ToString();
year = myReader[1].ToString();
if (!string.IsNullOrEmpty(year))
{
temp += " (" + year + ")";
}
mDegrees.Add(temp);
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void FillTutorEventById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT TutID, StuID, TutDate, StartTime, EndTime, Title, Location
FROM crshelp_Tutoring
WHERE (TutID = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record of a tutor event with the given id exists.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mTutor = new Tutor(int.Parse(myReader[1].ToString()));
mDate = DateTime.Parse(myReader[2].ToString());
mStart = DateTime.Parse(myReader[3].ToString());
mEnd = DateTime.Parse(myReader[4].ToString());
mTitle = myReader[5].ToString();
mLocation = myReader[6].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void FillTutorById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT StuID, FirstName, LastName, StudentPic
FROM crshelp_Tutor
WHERE (StuID = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists of a Tutor with the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mFirst = myReader[1].ToString();
mLast = myReader[2].ToString();
mPic = myReader[3].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
GetCourses();
}
public static List <TutorEvent> GetEventsByMonth(DateTime month)
{
DateTime start = new DateTime(month.Year, month.Month, 1);
DateTime end = start.AddMonths(1);
List <TutorEvent> temp = new List <TutorEvent>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter lStart = new SqlParameter("@start", SqlDbType.DateTime);
lStart.Value = start;
SqlParameter lEnd = new SqlParameter("@end", SqlDbType.DateTime);
lEnd.Value = end;
SqlCommand myCommand = new SqlCommand(@"
SELECT TutID
FROM crshelp_Tutoring
WHERE (TutDate > @start) AND (TutDate < @end)", tempConn.myConnection);
myCommand.Parameters.Add(lStart);
myCommand.Parameters.Add(lEnd);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
temp.Add(new TutorEvent(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
Console.WriteLine(f.ToString());
}
finally
{
tempConn.CloseConnection();
}
return(temp);
}
private void GetQuotaInfo(string userName, string domain)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("PrintManager");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.NVarChar);
myParam.Value = userName;
SqlCommand myCommand = new SqlCommand(
@"SELECT UserName, TotalPagesPrinted, Balance, PaidBalance
FROM UserQuotas
WHERE (UserName = @myParam)",
tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists of a PrintQuota with the given information.");
}
while (myReader.Read())
{
mUserName = myReader["UserName"].ToString();
mTotalPagesPrinted = int.Parse(myReader["TotalPagesPrinted"].ToString());
mBalance = Math.Round(double.Parse(myReader["Balance"].ToString()), 2);
mPagesLeft = (int)Math.Round((mBalance / .05));
mPaidBalance = Math.Round(double.Parse(myReader["PaidBalance"].ToString()), 2);
mPaidPagesLeft = (int)Math.Round((mPaidBalance / .05));
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void GetPageById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@Param", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT id, text
FROM Pages
WHERE (id = @Param)",
tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists of a Page with the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mText = myReader[1].ToString();
}
mFromDB = true;
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void GetDegreeTypeById(int id)
{
mDegreeTypeId = id;
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = mDegreeTypeId;
SqlCommand myCommand = new SqlCommand(@"SELECT short_name, long_name, description
FROM DegreeTypes
WHERE degree_type_id = @myParam", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record of a DegreeType with given id exists.");
}
while (myReader.Read())
{
mShortName = myReader[0].ToString();
mLongName = myReader[1].ToString();
mDescription = myReader[2].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
private void FillQuestionById(int id)
{
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@myParam", SqlDbType.Int);
myParam.Value = id;
SqlCommand myCommand = new SqlCommand(@"
SELECT QuesID, Question, Answer
FROM askstu_Question
WHERE (QuesID = @myParam)", tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
if (!myReader.HasRows)
{
throw new Exception("No record exists for a Question that has the given id.");
}
while (myReader.Read())
{
mId = int.Parse(myReader[0].ToString());
mQuestion = myReader[1].ToString();
mAnswer = myReader[2].ToString();
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
}
public static List <NewsEvent> GetListEventsByTypeIgnoreDate(int typeId)
{
List <NewsEvent> tempList = new List <NewsEvent>();
ConnectSovereignDB tempConn = new ConnectSovereignDB("csetweb");
tempConn.OpenConnection();
try
{
SqlDataReader myReader = null;
SqlParameter myParam = new SqlParameter("@Param", SqlDbType.Int);
myParam.Value = typeId;
SqlCommand myCommand = new SqlCommand(@"
SELECT id
FROM news_events
WHERE (type = @Param)
ORDER BY start_date DESC",
tempConn.myConnection);
myCommand.Parameters.Add(myParam);
myReader = myCommand.ExecuteReader();
while (myReader.Read())
{
tempList.Add(new NewsEvent(int.Parse(myReader[0].ToString())));
}
}
catch (Exception f)
{
//Add log exception
throw;
}
finally
{
tempConn.CloseConnection();
}
return(tempList);
}