///<summary>Inserts one ReqStudent into the database. Provides option to use the existing priKey.</summary>
internal static long Insert(ReqStudent reqStudent,bool useExistingPK)
{
if(!useExistingPK && PrefC.RandomKeys) {
reqStudent.ReqStudentNum=ReplicationServers.GetKey("reqstudent","ReqStudentNum");
}
string command="INSERT INTO reqstudent (";
if(useExistingPK || PrefC.RandomKeys) {
command+="ReqStudentNum,";
}
command+="ReqNeededNum,Descript,SchoolCourseNum,ProvNum,AptNum,PatNum,InstructorNum,DateCompleted) VALUES(";
if(useExistingPK || PrefC.RandomKeys) {
command+=POut.Long(reqStudent.ReqStudentNum)+",";
}
command+=
POut.Long (reqStudent.ReqNeededNum)+","
+"'"+POut.String(reqStudent.Descript)+"',"
+ POut.Long (reqStudent.SchoolCourseNum)+","
+ POut.Long (reqStudent.ProvNum)+","
+ POut.Long (reqStudent.AptNum)+","
+ POut.Long (reqStudent.PatNum)+","
+ POut.Long (reqStudent.InstructorNum)+","
+ POut.Date (reqStudent.DateCompleted)+")";
if(useExistingPK || PrefC.RandomKeys) {
Db.NonQ(command);
}
else {
reqStudent.ReqStudentNum=Db.NonQ(command,true);
}
return reqStudent.ReqStudentNum;
}
///<summary>Inserts one ReqStudent into the database. Returns the new priKey.</summary>
public static long Insert(ReqStudent reqStudent)
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
reqStudent.ReqStudentNum = DbHelper.GetNextOracleKey("reqstudent", "ReqStudentNum");
int loopcount = 0;
while (loopcount < 100)
{
try {
return(Insert(reqStudent, true));
}
catch (Oracle.DataAccess.Client.OracleException ex) {
if (ex.Number == 1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated"))
{
reqStudent.ReqStudentNum++;
loopcount++;
}
else
{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else
{
return(Insert(reqStudent, false));
}
}
private void butAdd_Click(object sender, EventArgs e)
{
if (gridReqs.SelectedIndices.Length == 0)
{
MsgBox.Show(this, "Please select at least one requirement from the list at the left first.");
return;
}
ReqStudent req;
for (int i = 0; i < gridReqs.SelectedIndices.Length; i++)
{
req = new ReqStudent();
req.AptNum = AptNum;
//req.DateCompleted
req.Descript = ReqTable.Rows[gridReqs.SelectedIndices[i]]["Descript"].ToString();
if (comboInstructor.SelectedIndex > 0)
{
req.InstructorNum = _listProviders[comboInstructor.SelectedIndex - 1].ProvNum;
}
req.PatNum = PatNum;
req.ProvNum = StudentList[gridStudents.GetSelectedIndex()].ProvNum;
req.ReqNeededNum = PIn.Long(ReqTable.Rows[gridReqs.SelectedIndices[i]]["ReqNeededNum"].ToString());
//req.ReqStudentNum=0 until synch on OK.
req.SchoolCourseNum = _listSchoolCourses[comboCourse.SelectedIndex].SchoolCourseNum;
reqsAttached.Add(req);
hasChanged = true;
}
FillAttached();
}
///<summary></summary>
public static void Insert(ReqStudent req)
{
if (PrefB.RandomKeys)
{
req.ReqStudentNum = MiscData.GetKey("reqstudent", "ReqStudentNum");
}
string command = "INSERT INTO reqstudent (";
if (PrefB.RandomKeys)
{
command += "ReqStudentNum,";
}
command += "ReqNeededNum,Descript,SchoolCourseNum,ProvNum,AptNum,PatNum,InstructorNum,DateCompleted) VALUES(";
if (PrefB.RandomKeys)
{
command += "'" + POut.PInt(req.ReqStudentNum) + "', ";
}
command +=
"'" + POut.PInt(req.ReqNeededNum) + "', "
+ "'" + POut.PString(req.Descript) + "', "
+ "'" + POut.PInt(req.SchoolCourseNum) + "', "
+ "'" + POut.PInt(req.ProvNum) + "', "
+ "'" + POut.PInt(req.AptNum) + "', "
+ "'" + POut.PInt(req.PatNum) + "', "
+ "'" + POut.PInt(req.InstructorNum) + "', "
+ POut.PDate(req.DateCompleted) + ")";
if (PrefB.RandomKeys)
{
General.NonQ(command);
}
else
{
req.ReqStudentNum = General.NonQ(command, true);
}
}
///<summary>Inserts one ReqStudent into the database. Returns the new priKey.</summary>
internal static long Insert(ReqStudent reqStudent)
{
if(DataConnection.DBtype==DatabaseType.Oracle) {
reqStudent.ReqStudentNum=DbHelper.GetNextOracleKey("reqstudent","ReqStudentNum");
int loopcount=0;
while(loopcount<100){
try {
return Insert(reqStudent,true);
}
catch(Oracle.DataAccess.Client.OracleException ex){
if(ex.Number==1 && ex.Message.ToLower().Contains("unique constraint") && ex.Message.ToLower().Contains("violated")){
reqStudent.ReqStudentNum++;
loopcount++;
}
else{
throw ex;
}
}
}
throw new ApplicationException("Insert failed. Could not generate primary key.");
}
else {
return Insert(reqStudent,false);
}
}
public ActionResult BookSuggestions([Bind(Include = "CustID,Name,AuthName,Isbn,Subject,Year,Type")] ReqStudent reqObj)
{
Debug.WriteLine("FUUCCKKK");
int cID = Int32.Parse(Session["custID"].ToString());
using (LibraryEntities db = new LibraryEntities())
{
// Add books.
if (ModelState.IsValid)
{
reqObj.CustID = cID;
reqObj.Type = "Book";
reqObj.ReqConfirmation = "Pending";
db.ReqStudents.Add(reqObj);
try
{
db.SaveChanges();
TempData["reqSent"] = "Request Sent";
return(RedirectToAction("UserArea"));
}
catch (Exception e)
{
Console.WriteLine(e);
// Provide for exceptions.
}
ModelState.Clear();
}
} //end ModelStateif
return(View());
} //end ActionResult
///<summary>Surround with try/catch.</summary>
public static void Delete(int reqStudentNum)
{
ReqStudent req = GetOne(reqStudentNum);
//if a reqneeded exists, then disallow deletion.
if (ReqNeededs.GetReq(req.ReqNeededNum) == null)
{
throw new Exception(Lan.g("ReqStudents", "Cannot delete requirement. Delete the requirement needed instead."));
}
string command = "DELETE FROM reqstudent WHERE ReqStudentNum = " + POut.PInt(reqStudentNum);
General.NonQ(command);
}
///<summary>Updates one ReqStudent in the database.</summary>
public static void Update(ReqStudent reqStudent)
{
string command = "UPDATE reqstudent SET "
+ "ReqNeededNum = " + POut.Long(reqStudent.ReqNeededNum) + ", "
+ "Descript = '" + POut.String(reqStudent.Descript) + "', "
+ "SchoolCourseNum= " + POut.Long(reqStudent.SchoolCourseNum) + ", "
+ "ProvNum = " + POut.Long(reqStudent.ProvNum) + ", "
+ "AptNum = " + POut.Long(reqStudent.AptNum) + ", "
+ "PatNum = " + POut.Long(reqStudent.PatNum) + ", "
+ "InstructorNum = " + POut.Long(reqStudent.InstructorNum) + ", "
+ "DateCompleted = " + POut.Date(reqStudent.DateCompleted) + " "
+ "WHERE ReqStudentNum = " + POut.Long(reqStudent.ReqStudentNum);
Db.NonQ(command);
}
///<summary>Inserts one ReqStudent into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(ReqStudent reqStudent)
{
if (DataConnection.DBtype == DatabaseType.MySql)
{
return(InsertNoCache(reqStudent, false));
}
else
{
if (DataConnection.DBtype == DatabaseType.Oracle)
{
reqStudent.ReqStudentNum = DbHelper.GetNextOracleKey("reqstudent", "ReqStudentNum"); //Cacheless method
}
return(InsertNoCache(reqStudent, true));
}
}
///<summary></summary>
public static void Update(ReqStudent req)
{
string command = "UPDATE reqstudent SET "
+ " ReqNeededNum = '" + POut.PInt(req.ReqNeededNum) + "'"
+ ",Descript = '" + POut.PString(req.Descript) + "'"
+ ",SchoolCourseNum = '" + POut.PInt(req.SchoolCourseNum) + "'"
+ ",ProvNum = '" + POut.PInt(req.ProvNum) + "'"
+ ",AptNum = '" + POut.PInt(req.AptNum) + "'"
+ ",PatNum = '" + POut.PInt(req.PatNum) + "'"
+ ",InstructorNum = '" + POut.PInt(req.InstructorNum) + "'"
+ ",DateCompleted = " + POut.PDate(req.DateCompleted)
+ " WHERE ReqStudentNum = '" + POut.PInt(req.ReqStudentNum) + "'";
General.NonQ(command);
}
public static ReqStudent GetOne(int ReqStudentNum)
{
string command = "SELECT * FROM reqstudent WHERE ReqStudentNum=" + POut.PInt(ReqStudentNum);
DataTable table = General.GetTable(command);
ReqStudent req = new ReqStudent();
req.ReqStudentNum = PIn.PInt(table.Rows[0][0].ToString());
req.ReqNeededNum = PIn.PInt(table.Rows[0][1].ToString());
req.Descript = PIn.PString(table.Rows[0][2].ToString());
req.SchoolCourseNum = PIn.PInt(table.Rows[0][3].ToString());
req.ProvNum = PIn.PInt(table.Rows[0][4].ToString());
req.AptNum = PIn.PInt(table.Rows[0][5].ToString());
req.PatNum = PIn.PInt(table.Rows[0][6].ToString());
req.InstructorNum = PIn.PInt(table.Rows[0][7].ToString());
req.DateCompleted = PIn.PDate(table.Rows[0][8].ToString());
return(req);
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List<ReqStudent> TableToList(DataTable table){
List<ReqStudent> retVal=new List<ReqStudent>();
ReqStudent reqStudent;
for(int i=0;i<table.Rows.Count;i++) {
reqStudent=new ReqStudent();
reqStudent.ReqStudentNum = PIn.Long (table.Rows[i]["ReqStudentNum"].ToString());
reqStudent.ReqNeededNum = PIn.Long (table.Rows[i]["ReqNeededNum"].ToString());
reqStudent.Descript = PIn.String(table.Rows[i]["Descript"].ToString());
reqStudent.SchoolCourseNum= PIn.Long (table.Rows[i]["SchoolCourseNum"].ToString());
reqStudent.ProvNum = PIn.Long (table.Rows[i]["ProvNum"].ToString());
reqStudent.AptNum = PIn.Long (table.Rows[i]["AptNum"].ToString());
reqStudent.PatNum = PIn.Long (table.Rows[i]["PatNum"].ToString());
reqStudent.InstructorNum = PIn.Long (table.Rows[i]["InstructorNum"].ToString());
reqStudent.DateCompleted = PIn.Date (table.Rows[i]["DateCompleted"].ToString());
retVal.Add(reqStudent);
}
return retVal;
}
///<summary>Converts a DataTable to a list of objects.</summary>
public static List <ReqStudent> TableToList(DataTable table)
{
List <ReqStudent> retVal = new List <ReqStudent>();
ReqStudent reqStudent;
for (int i = 0; i < table.Rows.Count; i++)
{
reqStudent = new ReqStudent();
reqStudent.ReqStudentNum = PIn.Long(table.Rows[i]["ReqStudentNum"].ToString());
reqStudent.ReqNeededNum = PIn.Long(table.Rows[i]["ReqNeededNum"].ToString());
reqStudent.Descript = PIn.String(table.Rows[i]["Descript"].ToString());
reqStudent.SchoolCourseNum = PIn.Long(table.Rows[i]["SchoolCourseNum"].ToString());
reqStudent.ProvNum = PIn.Long(table.Rows[i]["ProvNum"].ToString());
reqStudent.AptNum = PIn.Long(table.Rows[i]["AptNum"].ToString());
reqStudent.PatNum = PIn.Long(table.Rows[i]["PatNum"].ToString());
reqStudent.InstructorNum = PIn.Long(table.Rows[i]["InstructorNum"].ToString());
reqStudent.DateCompleted = PIn.Date(table.Rows[i]["DateCompleted"].ToString());
retVal.Add(reqStudent);
}
return(retVal);
}
///<summary></summary>
public static void Synch(int schoolClassNum, int schoolCourseNum)
{
//get list of all reqneededs for the given class and course.
DataTable table = Refresh(schoolClassNum, schoolCourseNum);
string command;
int reqNeededNum;
DataTable tStudent;
string descript;
ReqStudent req;
//1. Delete any reqstudents that do not have gradepoint
command = "DELETE FROM reqstudent "
+ "WHERE NOT EXISTS(SELECT * FROM reqneeded WHERE reqstudent.ReqNeededNum=reqneeded.ReqNeededNum) "
+ "AND reqstudent.DateCompleted < " + POut.PDate(new DateTime(1880, 1, 1));
General.NonQ(command);
for (int i = 0; i < table.Rows.Count; i++)
{
reqNeededNum = PIn.PInt(table.Rows[i]["ReqNeededNum"].ToString());
descript = PIn.PString(table.Rows[i]["Descript"].ToString());
//2. Update. Update the description for all students using this requirement.
command = "UPDATE reqstudent SET Descript='" + POut.PString(descript) + "' "
+ "WHERE ReqNeededNum=" + POut.PInt(reqNeededNum);
General.NonQ(command);
//3. Insert. Get list of students that do not have this req. For each student, insert.
command = "SELECT ProvNum FROM provider WHERE SchoolClassNum=" + POut.PInt(schoolClassNum)
+ " AND NOT EXISTS(SELECT * FROM reqstudent WHERE reqstudent.ProvNum=provider.ProvNum"
+ " AND ReqNeededNum=" + POut.PInt(reqNeededNum) + ")";
tStudent = General.GetTable(command);
for (int s = 0; s < tStudent.Rows.Count; s++)
{
req = new ReqStudent();
req.Descript = descript;
req.ProvNum = PIn.PInt(tStudent.Rows[s]["ProvNum"].ToString());
req.ReqNeededNum = reqNeededNum;
req.SchoolCourseNum = PIn.PInt(table.Rows[i]["SchoolCourseNum"].ToString());
ReqStudents.Insert(req);
}
}
}
///<summary>Inserts one ReqStudent into the database. Provides option to use the existing priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(ReqStudent reqStudent, bool useExistingPK)
{
bool isRandomKeys = Prefs.GetBoolNoCache(PrefName.RandomPrimaryKeys);
string command = "INSERT INTO reqstudent (";
if (!useExistingPK && isRandomKeys)
{
reqStudent.ReqStudentNum = ReplicationServers.GetKeyNoCache("reqstudent", "ReqStudentNum");
}
if (isRandomKeys || useExistingPK)
{
command += "ReqStudentNum,";
}
command += "ReqNeededNum,Descript,SchoolCourseNum,ProvNum,AptNum,PatNum,InstructorNum,DateCompleted) VALUES(";
if (isRandomKeys || useExistingPK)
{
command += POut.Long(reqStudent.ReqStudentNum) + ",";
}
command +=
POut.Long(reqStudent.ReqNeededNum) + ","
+ "'" + POut.String(reqStudent.Descript) + "',"
+ POut.Long(reqStudent.SchoolCourseNum) + ","
+ POut.Long(reqStudent.ProvNum) + ","
+ POut.Long(reqStudent.AptNum) + ","
+ POut.Long(reqStudent.PatNum) + ","
+ POut.Long(reqStudent.InstructorNum) + ","
+ POut.Date(reqStudent.DateCompleted) + ")";
if (useExistingPK || isRandomKeys)
{
Db.NonQ(command);
}
else
{
reqStudent.ReqStudentNum = Db.NonQ(command, true, "ReqStudentNum", "reqStudent");
}
return(reqStudent.ReqStudentNum);
}
///<summary>Returns true if Update(ReqStudent,ReqStudent) would make changes to the database.
///Does not make any changes to the database and can be called before remoting role is checked.</summary>
public static bool UpdateComparison(ReqStudent reqStudent, ReqStudent oldReqStudent)
{
if (reqStudent.ReqNeededNum != oldReqStudent.ReqNeededNum)
{
return(true);
}
if (reqStudent.Descript != oldReqStudent.Descript)
{
return(true);
}
if (reqStudent.SchoolCourseNum != oldReqStudent.SchoolCourseNum)
{
return(true);
}
if (reqStudent.ProvNum != oldReqStudent.ProvNum)
{
return(true);
}
if (reqStudent.AptNum != oldReqStudent.AptNum)
{
return(true);
}
if (reqStudent.PatNum != oldReqStudent.PatNum)
{
return(true);
}
if (reqStudent.InstructorNum != oldReqStudent.InstructorNum)
{
return(true);
}
if (reqStudent.DateCompleted.Date != oldReqStudent.DateCompleted.Date)
{
return(true);
}
return(false);
}
///<summary>Inserts one ReqStudent into the database. Provides option to use the existing priKey.</summary>
public static long Insert(ReqStudent reqStudent, bool useExistingPK)
{
if (!useExistingPK && PrefC.RandomKeys)
{
reqStudent.ReqStudentNum = ReplicationServers.GetKey("reqstudent", "ReqStudentNum");
}
string command = "INSERT INTO reqstudent (";
if (useExistingPK || PrefC.RandomKeys)
{
command += "ReqStudentNum,";
}
command += "ReqNeededNum,Descript,SchoolCourseNum,ProvNum,AptNum,PatNum,InstructorNum,DateCompleted) VALUES(";
if (useExistingPK || PrefC.RandomKeys)
{
command += POut.Long(reqStudent.ReqStudentNum) + ",";
}
command +=
POut.Long(reqStudent.ReqNeededNum) + ","
+ "'" + POut.String(reqStudent.Descript) + "',"
+ POut.Long(reqStudent.SchoolCourseNum) + ","
+ POut.Long(reqStudent.ProvNum) + ","
+ POut.Long(reqStudent.AptNum) + ","
+ POut.Long(reqStudent.PatNum) + ","
+ POut.Long(reqStudent.InstructorNum) + ","
+ POut.Date(reqStudent.DateCompleted) + ")";
if (useExistingPK || PrefC.RandomKeys)
{
Db.NonQ(command);
}
else
{
reqStudent.ReqStudentNum = Db.NonQ(command, true);
}
return(reqStudent.ReqStudentNum);
}
///<summary>Inserts one ReqStudent into the database. Returns the new priKey. Doesn't use the cache.</summary>
public static long InsertNoCache(ReqStudent reqStudent)
{
return(InsertNoCache(reqStudent, false));
}
///<summary>Updates one ReqStudent in the database.</summary>
public static void Update(ReqStudent reqStudent){
string command="UPDATE reqstudent SET "
+"ReqNeededNum = "+POut.Long (reqStudent.ReqNeededNum)+", "
+"Descript = '"+POut.String(reqStudent.Descript)+"', "
+"SchoolCourseNum= "+POut.Long (reqStudent.SchoolCourseNum)+", "
+"ProvNum = "+POut.Long (reqStudent.ProvNum)+", "
+"AptNum = "+POut.Long (reqStudent.AptNum)+", "
+"PatNum = "+POut.Long (reqStudent.PatNum)+", "
+"InstructorNum = "+POut.Long (reqStudent.InstructorNum)+", "
+"DateCompleted = "+POut.Date (reqStudent.DateCompleted)+" "
+"WHERE ReqStudentNum = "+POut.Long(reqStudent.ReqStudentNum);
Db.NonQ(command);
}
///<summary>Updates one ReqStudent in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables.</summary>
public static void Update(ReqStudent reqStudent, ReqStudent oldReqStudent)
{
string command = "";
if (reqStudent.ReqNeededNum != oldReqStudent.ReqNeededNum)
{
if (command != "")
{
command += ",";
}
command += "ReqNeededNum = " + POut.Long(reqStudent.ReqNeededNum) + "";
}
if (reqStudent.Descript != oldReqStudent.Descript)
{
if (command != "")
{
command += ",";
}
command += "Descript = '" + POut.String(reqStudent.Descript) + "'";
}
if (reqStudent.SchoolCourseNum != oldReqStudent.SchoolCourseNum)
{
if (command != "")
{
command += ",";
}
command += "SchoolCourseNum = " + POut.Long(reqStudent.SchoolCourseNum) + "";
}
if (reqStudent.ProvNum != oldReqStudent.ProvNum)
{
if (command != "")
{
command += ",";
}
command += "ProvNum = " + POut.Long(reqStudent.ProvNum) + "";
}
if (reqStudent.AptNum != oldReqStudent.AptNum)
{
if (command != "")
{
command += ",";
}
command += "AptNum = " + POut.Long(reqStudent.AptNum) + "";
}
if (reqStudent.PatNum != oldReqStudent.PatNum)
{
if (command != "")
{
command += ",";
}
command += "PatNum = " + POut.Long(reqStudent.PatNum) + "";
}
if (reqStudent.InstructorNum != oldReqStudent.InstructorNum)
{
if (command != "")
{
command += ",";
}
command += "InstructorNum = " + POut.Long(reqStudent.InstructorNum) + "";
}
if (reqStudent.DateCompleted != oldReqStudent.DateCompleted)
{
if (command != "")
{
command += ",";
}
command += "DateCompleted = " + POut.Date(reqStudent.DateCompleted) + "";
}
if (command == "")
{
return;
}
command = "UPDATE reqstudent SET " + command
+ " WHERE ReqStudentNum = " + POut.Long(reqStudent.ReqStudentNum);
Db.NonQ(command);
}
///<summary>Updates one ReqStudent in the database. Uses an old object to compare to, and only alters changed fields. This prevents collisions and concurrency problems in heavily used tables.</summary>
public static void Update(ReqStudent reqStudent,ReqStudent oldReqStudent){
string command="";
if(reqStudent.ReqNeededNum != oldReqStudent.ReqNeededNum) {
if(command!=""){ command+=",";}
command+="ReqNeededNum = "+POut.Long(reqStudent.ReqNeededNum)+"";
}
if(reqStudent.Descript != oldReqStudent.Descript) {
if(command!=""){ command+=",";}
command+="Descript = '"+POut.String(reqStudent.Descript)+"'";
}
if(reqStudent.SchoolCourseNum != oldReqStudent.SchoolCourseNum) {
if(command!=""){ command+=",";}
command+="SchoolCourseNum = "+POut.Long(reqStudent.SchoolCourseNum)+"";
}
if(reqStudent.ProvNum != oldReqStudent.ProvNum) {
if(command!=""){ command+=",";}
command+="ProvNum = "+POut.Long(reqStudent.ProvNum)+"";
}
if(reqStudent.AptNum != oldReqStudent.AptNum) {
if(command!=""){ command+=",";}
command+="AptNum = "+POut.Long(reqStudent.AptNum)+"";
}
if(reqStudent.PatNum != oldReqStudent.PatNum) {
if(command!=""){ command+=",";}
command+="PatNum = "+POut.Long(reqStudent.PatNum)+"";
}
if(reqStudent.InstructorNum != oldReqStudent.InstructorNum) {
if(command!=""){ command+=",";}
command+="InstructorNum = "+POut.Long(reqStudent.InstructorNum)+"";
}
if(reqStudent.DateCompleted != oldReqStudent.DateCompleted) {
if(command!=""){ command+=",";}
command+="DateCompleted = "+POut.Date(reqStudent.DateCompleted)+"";
}
if(command==""){
return;
}
command="UPDATE reqstudent SET "+command
+" WHERE ReqStudentNum = "+POut.Long(reqStudent.ReqStudentNum);
Db.NonQ(command);
}
