//get the new results id for this instance of session form
//a method for getting the new results id for the current form
public static int getResultsID(DBConnect DBCon)
{
string resultsIDQuery = "SELECT (1+max(resultsID)) from result_;"; //set the query
queryRunner queryR = new queryRunner(resultsIDQuery); //initialize a new query runner object for running queries
string resultID = queryR.runQuery(DBCon.getConnection()); //sets the new result id equal to a new strin object
try
{
return Int32.Parse(resultID); //returns the int32 value parsed from the string object.
}
catch (FormatException) //if something goes wrong...
{
return 0; //return 0... if this happens then a sql exception may be thrown that is unaccounted for *TODO*
}
}
//a method for obtaining all of the new tireID's for the given session.
public static List<string> getNewTireIDs(DBConnect DBCon)
{
string tireQuery = "SELECT (1+max(LF_LFTireID)), (1+max(RF_RFTireID)), (1+max(LR_LRTireID)), (1+max(RR_RRTireID)) FROM result_;"; //define the tire query string
queryRunner queryR = new queryRunner(tireQuery); //make the query runner object for the tire query
queryR.runQuery(DBCon.getConnection()); //run the query
return queryR.getLastQueryResultsList(); //return the generic list object with the new tireIds
}
//an on click event for the page
protected void submitForm_Click(object sender, EventArgs e)
{
//make a new dbconnect object to pass to the makeSqlInsertStatements method
DBConnect DB = new DBConnect();
makeSQLInsertStatements(DB); //cal the makeSqlinsertstatements method with the dbConnect object as a parameter.
//need to make the page reset all fields after doing this.
Response.Redirect(Request.RawUrl); //resets the page when the submission is complete.
}
string todaysDate; //stores the date of today.
#endregion Fields
#region Constructors
public sqlTesting(DBConnect DBCon)
{
testingDB = DBCon; //initialize the testingDB instance varriable to the dbcon object passed in with the constructor
sqlQuery = ""; //set the sql query to an empty string.
todaysDate = DateTime.Now.ToString("yyyy-MM-dd"); //set todaysDate to todays date with the appropriate format
testingStrings = new List<string>(); //make a new generic list object that stores string typed varriables
queryR = new queryRunner(""); //initialize query runner with an empty string.
connection = testingDB.getConnection(); //set the inherited connection attribute to the connection provided to us in the DB object
}
protected void makeSQLInsertStatements(DBConnect con)
{
//an array of the laptimetextbox objects. Needed for looping through the names and making sql insert statements.
var lapTimeBoxes = new[] { lapTimeTextBox1, lapTimeTextBox2, lapTimeTextBox3, lapTimeTextBox4, lapTimeTextBox5, lapTimeTextBox6, lapTimeTextBox7, lapTimeTextBox8, lapTimeTextBox9, lapTimeTextBox10, lapTimeTextBox11, lapTimeTextBox12, lapTimeTextBox13, lapTimeTextBox14, lapTimeTextBox15, lapTimeTextBox16, lapTimeTextBox17, lapTimeTextBox18, lapTimeTextBox19, lapTimeTextBox20 };
String sessionInsertCmdString = ""; //initializing a string for use by insert statements
DateTime thisDay = DateTime.Today; //date for documentation with inserts
MySqlCommand insertCommand = new MySqlCommand(sessionInsertCmdString, con.getConnection()); //uses the connection passed to this method to make an insertcommand object that will be used to run insert statements.
sqlTesting tester = new sqlTesting(con); //make a new sqlTesting object for using the class
SessionFormClass session = new SessionFormClass(SQLUtility.getResultsID(con), SessionIDTextBox.Text);
insertCommand.Parameters.AddWithValue("@eventNotes", notesTextBox.Text); //adds a parameter that allows for safeguarding agains the insertion of special characters into the event notes box. Prevents sql injection attacks
//from happening, however it seems cross site scripting might still be a security concern.
eventDD.SelectedIndex = 0; //set the selected index for eventDropDown box to 0. Needs to be changed in the future when more than one event object exists.
if (tester.eventTest(eventDD.Text)) //if the eventTest returns true we need to make an entry for event so that the other queries will work.
{
SQLUtility.doAnInsert("insert into event values ('Asphalt', '" + CityTextBox.Text + "', '" + StateTextBox.Text + "', " + NumLapsTextBox.Text + ", STR_TO_DATE('" + thisDay.ToString("d") + "', '%m/%d/%Y'), '" + eventDD.Text + "');", insertCommand);
}
//Sets the selected index for sessionIdDD to 0. This should always happen since the field is not editable.
SessionIDTextBox.SelectedIndex = 0;
//this check might be valuable in the future but right now the below commented check is unnecessary.
//if (SQLUtility.sessionTest(SessionIDTextBox.Text, con))
//{
SQLUtility.doAnInsert("insert into session_ values ('" + session.getSessioNID() + "', '" + DateTime.Now.TimeOfDay + "', STR_TO_DATE('" + thisDay.ToString("d") + "', '%m/%d/%Y'), '" + eventDD.Text + "', @eventNotes);", insertCommand);
// }
//No setup table insert commands are needed since there are only 2 setups SU1 is the default setup
//No Kart insert statements are needed either, it is inferred if the drivers are using EV or Gas and the appropriate setup is applied
//put the new tireIDs into an array
string[] tireIDs = (SQLUtility.getNewTireIDs(con)).ToArray();
//tire insert statements; may want tire quality & prep level text boxes at some point in the future.
//Tire values are inserted when they have been input into text boxes. If no parseable values have been entered, then the tire details input fails, but puts a dummy entry in so that a results entry
//can be logged for the submitted form regardless.
if (SQLUtility.checkStrings(LFctTextBox.Text, LFhtTextBox.Text, LFhpTextBox.Text, LFcpTextBox.Text))
{
SQLUtility.doAnInsert("insert into lf values ('" + tireIDs[0] + "', '" + compoundTextBox.Text + "', '" + "" + "'," + double.Parse(LFctTextBox.Text) + ", " + double.Parse(LFhtTextBox.Text) + ", " + double.Parse(LFhpTextBox.Text) + ", '0', " + double.Parse(LFcpTextBox.Text) + ");", insertCommand);
}
else
{
SQLUtility.doAnInsert("insert into lf (LFTireID) values ('" + tireIDs[0] + "');", insertCommand);
}
if (SQLUtility.checkStrings(RFctTextBox.Text, RFhtTextBox.Text, RFhpTextBox.Text, RFcpTextBox.Text))
{
SQLUtility.doAnInsert("insert into rf values ('" + tireIDs[1] + "', '" + compoundTextBox.Text + "', '" + "" + "'," + double.Parse(RFctTextBox.Text) + ", " + double.Parse(RFhtTextBox.Text) + ", " + double.Parse(RFhpTextBox.Text) + ", '0', " + double.Parse(RFcpTextBox.Text) + ");", insertCommand);
}
else
{
SQLUtility.doAnInsert("insert into rf (rfTireID) values ('" + tireIDs[1] + "');", insertCommand);
}
if (SQLUtility.checkStrings(LRctTextBox.Text, LRhtTextBox.Text, LRhpTextBox.Text, LRcpTextBox.Text))
{
SQLUtility.doAnInsert("insert into lr values ('" + tireIDs[2] + "', '" + compoundTextBox.Text + "', '" + "" + "'," + double.Parse(LRctTextBox.Text) + ", " + double.Parse(LRhtTextBox.Text) + ", " + double.Parse(LRhpTextBox.Text) + ", '0', " + double.Parse(LRcpTextBox.Text) + ");", insertCommand);
}
else
{
SQLUtility.doAnInsert("insert into lr (LRTireID) values ('" + tireIDs[2] + "');", insertCommand);
}
if (SQLUtility.checkStrings(RRctTextBox.Text, RRhtTextBox.Text, RRhpTextBox.Text, RRcpTextBox.Text))
{
SQLUtility.doAnInsert("insert into rr values ('" + tireIDs[3] + "', '" + compoundTextBox.Text + "', '" + "" + "'," + double.Parse(RFctTextBox.Text) + ", " + double.Parse(RFhtTextBox.Text) + ", " + double.Parse(RFhpTextBox.Text) + ", '0', " + double.Parse(RFcpTextBox.Text) + ");", insertCommand);
}
else
{
SQLUtility.doAnInsert("insert into rr (RRTireID) values ('" + tireIDs[3] + "');", insertCommand);
}
//result insert statement. Has to be second to last since needed primary keys are being generated before this.
int resultsID = SQLUtility.getResultsID(con); //store the new resultsID so that the lap inserts can run with the proper id being linked to them.
SQLUtility.doAnInsert("insert into result_ values (" + session.getResultsID() + ", '" + timeSystemDropDownList.Text + "', '" + TrafficDropDownBox.SelectedIndex + "', " + SessionIDTextBox.Text + ", '" + KartFrameDD.Text + "', '" + driverPhoneDD.Text + "', '" + tireIDs[0] + "', '" + tireIDs[1] + "', '" + tireIDs[2] + "', '" + tireIDs[3] + "', '" + 0 + "', @eventNotes);", insertCommand);
for (int x = 0; x < 20; x++)
{
if ((lapTimeBoxes[x].Text) != "")
{
SQLUtility.doAnInsert("insert into LAP values (" + session.getResultsID() + " ," + x + " ," + lapTimeBoxes[x].Text + ");", insertCommand);
//run an insert statement for each lap textbox that is filled out.
}
}
}
