private bool execUpdateAlumniSlalomScores() { bool curReturnValue = true; SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = null; DataTable myDataTable = new DataTable(); StringBuilder curSqlStmt = new StringBuilder(); int rowsProc = 0; try { myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); sqlStmt = myDbConn.CreateCommand(); sqlStmt.CommandText = "Update SlalomScore " + "SET Score = ( ( FinalPassNum - 1 ) * 6 ) + FinalPassScore " + "Where SanctionId = '" + mySanctionNum + "' " + "And Score > 6"; rowsProc = sqlStmt.ExecuteNonQuery(); MessageBox.Show(String.Format("{0} slalom scores updated ", rowsProc)); } catch (Exception excp) { String curMsg = ":Error attempting to update slalom scores for alumni tournament \n" + excp.Message; MessageBox.Show(curMsg); curReturnValue = false; } finally { myDbConn.Close(); } return(curReturnValue); }
private bool execUpdateNops() { //Update NOPS values due to unavailable 2014 NOPS tables //Prior to version 1.0.1.82 bool curReturnValue = true; SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = null; DataTable myDataTable = new DataTable(); StringBuilder curSqlStmt = new StringBuilder(); List<Common.ScoreEntry> curScoreList = new List<Common.ScoreEntry>(); int rowsProc, rowUpdateCount = 0; try { myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); sqlStmt = myDbConn.CreateCommand(); myNopsCalc = CalcNops.Instance; myNopsCalc.LoadDataForTour(); curScoreList.Add( new Common.ScoreEntry( "Trick", 0, "", 0 ) ); curSqlStmt = new StringBuilder(); curSqlStmt.Append( "Select PK, SanctionId, MemberId, AgeGroup, Score, Round " ); curSqlStmt.Append( "From TrickScore " ); curSqlStmt.Append( "Where SanctionId = '" + mySanctionNum + "' " ); curSqlStmt.Append( "Order by AgeGroup, MemberId, Round " ); myDataTable = getData( curSqlStmt.ToString() ); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = Convert.ToDecimal( (Int16)curRow["Score"] ); myNopsCalc.calcNops( (String)curRow["AgeGroup"], curScoreList ); sqlStmt.CommandText = "Update TrickScore " + "SET NopsScore = " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error updating trick NOPS " + "\n\nError: " + ExcpMsg ); curReturnValue = false; } } MessageBox.Show( myDataTable.Rows.Count.ToString() + " Trick scores read" + "\nTrick NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed" ); curScoreList[0].Event = "Jump"; curSqlStmt = new StringBuilder(); curSqlStmt.Append( "Select PK, SanctionId, MemberId, AgeGroup, ScoreFeet, Round " ); curSqlStmt.Append( "From JumpScore " ); curSqlStmt.Append( "Where SanctionId = '" + mySanctionNum + "' " ); curSqlStmt.Append( "Order by AgeGroup, MemberId, Round " ); myDataTable = getData( curSqlStmt.ToString() ); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = (Decimal)curRow["ScoreFeet"]; myNopsCalc.calcNops( (String)curRow["AgeGroup"], curScoreList ); sqlStmt.CommandText = "Update JumpScore " + "SET NopsScore = " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error updating jump NOPS \n\nError: " + ExcpMsg ); curReturnValue = false; } } MessageBox.Show( myDataTable.Rows.Count.ToString() + " Jump scores read" + "\nJump NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed" ); curScoreList[0].Event = "Slalom"; curSqlStmt = new StringBuilder(); curSqlStmt.Append( "Select PK, SanctionId, MemberId, AgeGroup, Score, Round " ); curSqlStmt.Append( "From SlalomScore " ); curSqlStmt.Append( "Where SanctionId = '" + mySanctionNum + "' " ); curSqlStmt.Append( "Order by AgeGroup, MemberId, Round " ); myDataTable = getData( curSqlStmt.ToString() ); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = (Decimal)curRow["Score"]; myNopsCalc.calcNops( (String)curRow["AgeGroup"], curScoreList ); sqlStmt.CommandText = "Update SlalomScore " + "SET NopsScore = " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round( curScoreList[0].Nops, 1 ).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error updating Slalom NOPS \n\nError: " + ExcpMsg ); curReturnValue = false; } } MessageBox.Show( myDataTable.Rows.Count.ToString() + " Slalom scores read" + "\nSlalom NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed" ); } catch (Exception excp) { String curMsg = ":Error attempting to update 2015 Nops values \n" + excp.Message; MessageBox.Show( curMsg ); curReturnValue = false; } finally { myDbConn.Close(); } return curReturnValue; }
private bool execUpdateAlumniSlalomScores() { bool curReturnValue = true; SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = null; DataTable myDataTable = new DataTable(); StringBuilder curSqlStmt = new StringBuilder(); int rowsProc = 0; try { myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); sqlStmt = myDbConn.CreateCommand(); sqlStmt.CommandText = "Update SlalomScore " + "SET Score = ( ( FinalPassNum - 1 ) * 6 ) + FinalPassScore " + "Where SanctionId = '" + mySanctionNum + "' " + "And Score > 6"; rowsProc = sqlStmt.ExecuteNonQuery(); MessageBox.Show( String.Format("{0} slalom scores updated ", rowsProc) ); } catch ( Exception excp ) { String curMsg = ":Error attempting to update slalom scores for alumni tournament \n" + excp.Message; MessageBox.Show(curMsg); curReturnValue = false; } finally { myDbConn.Close(); } return curReturnValue; }
public void importData(String inFileName) { string inputBuffer, colValue, MatchCommand = "", curMatchCommand = "", curLastUpdateDateIn = ""; string[] inputCols = null, inputColNames = null, inputKeys = null, curImportDataMatchMsg = { "", "", "", "" }; char[] tabDelim = new char[] { '\t' }; char[] singleQuoteDelim = new char[] { '\'' }; DateTime curLastUpdateDate = new DateTime(), curDateValue = new DateTime(); Boolean rowFound = false; bool curImportConfirmMsg = true; int curInputLineCount = 0; int idx = 0, rowsRead = 0, rowsfound = 0, rowsAdded = 0, rowsUpdated = 0, rowsSkipped = 0; StringBuilder stmtSelect = new StringBuilder( "" ); StringBuilder stmtWhere = new StringBuilder( "" ); StringBuilder stmtInsert = new StringBuilder( "" ); StringBuilder stmtData = new StringBuilder( "" ); StreamReader myReader = null; SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = null; DataTable curDataTable = null; myProgressInfo = new ProgressWindow(); ArrayList curFileList = new ArrayList(); if ( inFileName == null ) { curFileList = getImportFileList(); try { mySanctionNum = Properties.Settings.Default.AppSanctionNum; if ( mySanctionNum == null ) { mySanctionNum = ""; } else { if ( mySanctionNum.Length < 6 ) { mySanctionNum = ""; } } } catch { mySanctionNum = ""; } } else { curFileList.Add( inFileName ); mySanctionNum = ""; } if (curFileList.Count > 0) { DialogResult msgResp = MessageBox.Show( "Do you want a confirmation dialog for each successful data type imported?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1 ); if (msgResp == DialogResult.Yes) { curImportConfirmMsg = true; } else { curImportConfirmMsg = false; } try { myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); foreach (String curFileName in curFileList) { myReader = getImportFile( curFileName ); if (myReader != null) { curInputLineCount = 0; while (( inputBuffer = myReader.ReadLine() ) != null) { curInputLineCount++; myProgressInfo.setProgressValue( curInputLineCount ); rowFound = false; inputCols = inputBuffer.Split( tabDelim ); if (inputCols[0].ToLower().Equals( "table:" ) || inputCols[0].ToLower().Equals( "tablename:" )) { //Display statistics when another table entry is found if (myTableName != null) { if (curImportConfirmMsg) { MessageBox.Show( "Info: Import data processed for " + myTableName + "\nRows Read: " + rowsRead + "\nRows Added: " + rowsAdded + "\nRows Matched: " + rowsfound + "\nRows Updated: " + rowsUpdated + "\nRows Skipped: " + rowsSkipped ); rowsRead = 0; rowsfound = 0; rowsAdded = 0; rowsUpdated = 0; rowsSkipped = 0; } } //Check for table name and assume all subsequent records are for this table TableName = inputCols[1]; myProgressInfo.setProgessMsg( "Processing " + TableName ); myProgressInfo.Refresh(); inputColNames = null; inputKeys = getTableKeys( TableName ); } else if (inputColNames == null) { //Column names are required and must preceed the data rows inputColNames = new string[inputCols.Length]; for (idx = 0; idx < inputCols.Length; idx++) { inputColNames[idx] = inputCols[idx]; } } else { #region Process data rows for table and columns on input file //Process data rows. Table name and column names are required //before data rows can be processed if (myTableName == null) { MessageBox.Show( "Error: Table name not provide. Unable to process import file." ); break; } else if (inputColNames == null) { MessageBox.Show( "Error: Column definitions not provide. Unable to process import file." ); break; } else { rowsRead++; stmtSelect = new StringBuilder( "" ); stmtWhere = new StringBuilder( "" ); sqlStmt = myDbConn.CreateCommand(); if (inputKeys != null) { //Use update date if available curLastUpdateDateIn = findColValue( "LastUpdateDate", inputColNames, inputCols ); if (curLastUpdateDateIn == null) curLastUpdateDateIn = ""; #region Identify key columns if available //Use key column data items to see if input row already exists on database foreach (string keyName in inputKeys) { colValue = findColValue( keyName, inputColNames, inputCols ); if (colValue == null) colValue = ""; if (stmtSelect.Length > 1) { stmtSelect.Append( ", " + keyName ); stmtWhere.Append( " AND " + keyName + " = '" + colValue + "'" ); } else { stmtSelect.Append( "Select " ); if (curLastUpdateDateIn.Length > 0) { stmtSelect.Append( "LastUpdateDate, " ); } stmtSelect.Append( keyName ); stmtWhere.Append( " Where " + keyName + " = '" + colValue + "'" ); } } try { curMatchCommand = ""; curDataTable = getData( stmtSelect.ToString() + " From " + myTableName + stmtWhere.ToString() ); if (curDataTable.Rows.Count > 0) { rowFound = true; rowsfound++; if (!( MatchCommand.ToLower().Equals( "skipall" ) )) { if (curLastUpdateDateIn.Length > 0) { try { curLastUpdateDate = (DateTime)curDataTable.Rows[0]["LastUpdateDate"]; curDateValue = Convert.ToDateTime( curLastUpdateDateIn ); if (curDateValue > curLastUpdateDate) { curMatchCommand = "Update"; } else { curMatchCommand = "Skip"; } } catch { curMatchCommand = "Update"; curLastUpdateDate = Convert.ToDateTime( "01/01/2000" ); } } } } } catch (Exception ex) { String ExcpMsg = ex.Message; MessageBox.Show( "Error: Checking " + myTableName + " for import data" + "\n\nError: " + ExcpMsg ); return; } #endregion } stmtInsert = new StringBuilder( "" ); stmtData = new StringBuilder( "" ); if (rowFound) { #region Show information if input data found on database //Show information if input data found on database //Skip display if previoius display specfied to process all records the same if (MatchCommand.Length < 2) { if (curMatchCommand.Equals( "" ) || curMatchCommand.ToLower().Equals( "update" )) { curImportDataMatchMsg[0] = "Table: " + myTableName; curImportDataMatchMsg[1] = stmtWhere.ToString(); if (curMatchCommand.ToLower().Equals( "update" )) { curImportDataMatchMsg[2] = "Current record date = " + curLastUpdateDate.ToString(); curImportDataMatchMsg[3] = " Import record date = " + curLastUpdateDateIn; } else { curImportDataMatchMsg[2] = ""; curImportDataMatchMsg[3] = ""; } MatchDialog.ImportKeyDataMultiLine = curImportDataMatchMsg; MatchDialog.MatchCommand = MatchCommand; if (MatchDialog.ShowDialog() == DialogResult.OK) { MatchCommand = MatchDialog.MatchCommand; } } } if (curMatchCommand.Equals( "skip" )) { rowsSkipped++; //Re-initialize dialog response unless specified to process rows if (MatchCommand.ToLower().Equals( "skip" )) { MatchCommand = ""; } } else { if (MatchCommand.ToLower().Equals( "update" ) || MatchCommand.ToLower().Equals( "updateall" )) { //Build update command with input record if specified idx = 0; foreach (string colName in inputColNames) { if (inputKeys.Contains( colName ) || colName.ToLower().Equals( "pk" )) { } else if (colName.Equals( "TimeInTol1" ) || colName.Equals( "TimeInTol2" ) || colName.Equals( "TimeInTol3" ) || colName.Equals( "BoatSplitTimeTol" ) || colName.Equals( "BoatSplitTime2Tol" ) || colName.Equals( "BoatEndTimeTol" ) || ( colName.Equals( "Pass1VideoUrl" ) && myTableName.Equals( "TrickScore" ) ) || ( colName.Equals( "Pass2VideoUrl" ) && myTableName.Equals( "TrickScore" ) ) ) { } else { if (stmtData.Length > 1) { stmtData.Append( ", [" + colName + "] = " ); } else { stmtData.Append( "[" + colName + "] = " ); } if (inputCols[idx].Length > 0) { String tempValue = stringReplace( inputCols[idx], singleQuoteDelim, "''" ); stmtData.Append( "'" + tempValue + "'" ); } else { if (inputKeys.Contains( colName )) { stmtData.Append( " ''" ); } else { stmtData.Append( " null" ); } } } idx++; } try { //Update database with input record if specified //Delete detail if event scores which assumes the detail will also be imported if (myTableName.ToLower().Equals( "slalomscore" )) { sqlStmt.CommandText = "Delete SlalomRecap " + stmtWhere.ToString(); int rowsDeleted = sqlStmt.ExecuteNonQuery(); } else if (myTableName.ToLower().Equals( "trickscore" )) { sqlStmt.CommandText = "Delete TrickPass " + stmtWhere.ToString(); int rowsDeleted = sqlStmt.ExecuteNonQuery(); } else if (myTableName.ToLower().Equals( "jumpscore" )) { sqlStmt.CommandText = "Delete JumpRecap " + stmtWhere.ToString(); int rowsDeleted = sqlStmt.ExecuteNonQuery(); } sqlStmt.CommandText = "Update " + myTableName + " set " + stmtData.ToString() + stmtWhere.ToString(); int rowsProc = sqlStmt.ExecuteNonQuery(); rowsUpdated++; } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error: Adding import data to " + myTableName + "\n\nError: " + ExcpMsg ); return; } //Re-initialize dialog response unless specified to process rows if (MatchCommand.ToLower().Equals( "update" )) { MatchCommand = ""; } } else { rowsSkipped++; //Re-initialize dialog response unless specified to process rows if (MatchCommand.ToLower().Equals( "skip" )) { MatchCommand = ""; } } } #endregion } else { #region New data identified and will be added //Database record does not exist therefore data is added to database //Build insert command idx = 0; foreach (string colName in inputColNames) { if (colName.ToLower().Equals( "pk" ) || colName.Equals( "TimeInTol1" ) || colName.Equals( "TimeInTol2" ) || colName.Equals( "TimeInTol3" ) || colName.Equals( "BoatSplitTimeTol" ) || colName.Equals( "BoatSplitTime2Tol" ) || colName.Equals( "BoatEndTimeTol" ) || ( colName.Equals( "Pass1VideoUrl" ) && myTableName.Equals( "TrickScore" ) ) || ( colName.Equals( "Pass2VideoUrl" ) && myTableName.Equals( "TrickScore" ) ) ) { } else { if (stmtInsert.Length > 1) { stmtInsert.Append( ", [" + colName + "]" ); if (inputCols[idx].Length > 0) { String tempValue = stringReplace( inputCols[idx], singleQuoteDelim, "''" ); stmtData.Append( ", '" + tempValue + "'" ); } else { if (inputKeys.Contains( colName )) { stmtData.Append( ", ''" ); } else { stmtData.Append( ", null" ); } } } else { stmtInsert.Append( "[" + colName + "]" ); if (inputCols[idx].Length > 0) { String tempValue = stringReplace( inputCols[idx], singleQuoteDelim, "''" ); stmtData.Append( "'" + tempValue + "'" ); } else { if (inputKeys.Contains( colName )) { stmtData.Append( "''" ); } else { stmtData.Append( "null" ); } } } } idx++; } try { sqlStmt.CommandText = "Insert " + myTableName + " (" + stmtInsert.ToString() + ") Values (" + stmtData.ToString() + ")"; int rowsProc = sqlStmt.ExecuteNonQuery(); rowsAdded++; } catch (Exception ex) { rowsSkipped++; String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error: Adding import data to " + myTableName + "\n\nError: " + ExcpMsg ); } #endregion } } #endregion } } if (inFileName == null) { if (curImportConfirmMsg) { MessageBox.Show( "Info: Import data processed for " + myTableName + "\nRows Read: " + rowsRead + "\nRows Added: " + rowsAdded + "\nRows Matched: " + rowsfound + "\nRows Updated: " + rowsUpdated + "\nRows Skipped: " + rowsSkipped ); } else { MessageBox.Show( "Info: Total import data processed" + "\nRows Read: " + rowsRead + "\nRows Added: " + rowsAdded + "\nRows Matched: " + rowsfound + "\nRows Updated: " + rowsUpdated + "\nRows Skipped: " + rowsSkipped ); } rowsRead = 0; rowsAdded = 0; rowsfound = 0; rowsUpdated = 0; rowsSkipped = 0; } } } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error: Performing SQL operations" + "\n\nError: " + ExcpMsg ); } finally { myDbConn.Close(); myReader.Close(); myReader.Dispose(); } myProgressInfo.Close(); } }
public bool truncateMemberData() { String dialogMsg = "All members will be removed!" + "\n This will not affect any tournament registrations or scores." + "\n Do you want to continue?"; DialogResult msgResp = MessageBox.Show( dialogMsg, "Truncate Warning", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1 ); if ( msgResp == DialogResult.Yes ) { try { //Prepare database interaction combonents for processing SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); //Open database connection myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); sqlStmt = myDbConn.CreateCommand(); sqlStmt.CommandText = "Delete MemberList "; int rowsProc = sqlStmt.ExecuteNonQuery(); MessageBox.Show( rowsProc + " members removed" ); return true; } catch ( Exception excp ) { MessageBox.Show( "Error attempting to save changes \n" + excp.Message ); return false; } } else if ( msgResp == DialogResult.No ) { return false; } else { return false; } }
private bool importMemberData( bool inNcwsa ) { bool curReturn = true, curTeamHeaderActive = false, curNcwsa = inNcwsa, curTourRegFmt = false; string inputBuffer, myfileName, MemberId; string[] inputCols; string[] inputColsSaved = null; char[] tabDelim = new char[] { '\t' }; DateTime curFileDate; SqlCeCommand sqlStmt = null; StreamReader myReader; DialogResult msgResp; int numCk = 0, idxMemberId = 0, idx2010FmtCheck = 18 ; int curInputLineCount = 0; myTourEventReg = new TourEventReg(); myMemberIdValidate = new MemberIdValidate(); myCountMemberInput = 0; myCountMemberAdded = 0; myCountMemberUpdate = 0; myCountTourRegAdded = 0; myCountSlalomAdded = 0; myCountTrickAdded = 0; myCountJumpAdded = 0; //Choose an input file to be processed String curPath = Properties.Settings.Default.ExportDirectory; OpenFileDialog myFileDialog = new OpenFileDialog(); myFileDialog.InitialDirectory = curPath; myFileDialog.Filter = "txt files (*.txt)|*.txt|All files (*.*)|*.*"; myFileDialog.FilterIndex = 2; try { if ( myFileDialog.ShowDialog() == DialogResult.OK ) { myfileName = myFileDialog.FileName; if ( myfileName == null ) { return false; } else { myProgressInfo.setProgessMsg( "File selected " + myfileName ); myProgressInfo.Show(); myProgressInfo.Refresh(); //Prepare database interaction combonents for processing SqlCeConnection myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); //Get file date and prepare to read input data curFileDate = File.GetLastWriteTime( myfileName ); try { curInputLineCount = 0; myReader = new StreamReader( myfileName ); while ( ( inputBuffer = myReader.ReadLine() ) != null ) { curInputLineCount++; } myReader.Close(); myProgressInfo.setProgressMin( 1 ); myProgressInfo.setProgressMax( curInputLineCount ); } catch ( Exception ex ) { MessageBox.Show( "Error: Could not read file" + myFileDialog.FileName + "\n\nError: " + ex.Message ); return false; } curInputLineCount = 0; myReader = new StreamReader( myfileName ); try { //Open database connection myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); #region Process each input file data row while ( ( inputBuffer = myReader.ReadLine() ) != null ) { curInputLineCount++; myProgressInfo.setProgressValue( curInputLineCount ); //Initialize SQL statement variable for database processing sqlStmt = myDbConn.CreateCommand(); //Check input row to ensure it is a data row if ( inputBuffer.ToLower().IndexOf( "end-of-list" ) > -1 || inputBuffer.ToLower().IndexOf( "end of list" ) > -1 || inputBuffer.ToLower().IndexOf( "end_of_list" ) > -1 || inputBuffer.ToLower().IndexOf( "endoflist" ) > -1 ) { break; } else { inputCols = inputBuffer.Split( tabDelim ); if ( inputCols.Length > 8 ) { //Check first line of input file to analyze the file format supplied if ( curInputLineCount == 1 && curNcwsa == false ) { if ( inputCols.Length > 25 ) { if ( inputCols[26].ToLower().IndexOf( "membership status" ) > -1 ) { curTourRegFmt = true; } else if ( inputCols[25].ToLower().IndexOf( "membership status" ) > -1 ) { curTourRegFmt = true; } else if ( inputCols[12].ToLower().IndexOf( "membership status" ) > -1 ) { curNcwsa = true; } } else if (inputCols.Length > 12) { if (inputCols[12].ToLower().IndexOf( "membership status" ) > -1) { curNcwsa = true; } } } else { if ( inputCols[idxMemberId].Trim().Length > 10 ) { MemberId = inputCols[idxMemberId].Substring( 0, 3 ) + inputCols[idxMemberId].Substring( 4, 2 ) + inputCols[idxMemberId].Substring( 7, 4 ); } else { MemberId = "---"; } //If a valid member id is detected in the first column //assume valid record available for input if ( int.TryParse( MemberId.Substring( 0, 3 ), out numCk ) ) { curTeamHeaderActive = false; if (curNcwsa) { inputColsSaved = new String[inputCols.Length]; curReturn = procMemberInput( sqlStmt, inputCols, MemberId, curFileDate, curTourRegFmt, curNcwsa, inputColsSaved ); } else { curReturn = procMemberInput( sqlStmt, inputCols, MemberId, curFileDate, curTourRegFmt, curNcwsa, null ); } if ( curReturn ) { if (curNcwsa && inputColsSaved != null) { if ( inputColsSaved[0] != null ) { curReturn = procMemberInput( sqlStmt, inputColsSaved, MemberId, curFileDate, curTourRegFmt, curNcwsa, null ); } } } else { msgResp = MessageBox.Show( "Error encountered on last record./n Do you want to continue processing?", "Warning", MessageBoxButtons.YesNo, MessageBoxIcon.Warning, MessageBoxDefaultButton.Button1 ); if ( msgResp == DialogResult.Yes ) { curReturn = true; } else { curReturn = false; break; } } } else { if ( inputCols[idxMemberId].ToLower().Equals( "end-of-list" ) || inputCols[idxMemberId].ToLower().Equals( "end of list" ) || inputCols[idxMemberId].ToLower().Equals( "end_of_list" ) || inputCols[idxMemberId].ToLower().Equals( "endoflist" ) ) { break; } else if ( ( inputCols[0].ToLower().IndexOf( "team header" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "teamheader" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "team-header" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "team_header" ) > -1 ) ) { curTeamHeaderActive = true; curReturn = procTeamHeaderInput( sqlStmt, inputCols, inNcwsa ); } else if ( inputCols[idxMemberId].ToLower().Equals( "tourn name:" ) ) { curTeamHeaderActive = false; String curInputSanctionId = inputCols[6]; if ( curInputSanctionId.Length > 6 ) { curInputSanctionId = curInputSanctionId.Substring( 0, 6 ); } if ( !( curInputSanctionId.Equals( mySanctionNum ) ) ) { String dialogMsg = "Sanction number on import file is " + inputCols[6] + "\n Current tournament being scored is " + mySanctionNum + "\n\n Do you still want to continue?"; msgResp = MessageBox.Show( dialogMsg, "Database Copy", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1 ); if ( msgResp == DialogResult.No ) { break; } } } else { if ( curTeamHeaderActive ) { curReturn = procTeamHeaderInput( sqlStmt, inputCols, inNcwsa ); } } } } } else { if ( inputCols.Length > 3 ) { if ( ( inputCols[0].ToLower().IndexOf( "team header" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "teamheader" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "team-header" ) > -1 ) || ( inputCols[0].ToLower().IndexOf( "team_header" ) > -1 ) ) { curTeamHeaderActive = true; curReturn = procTeamHeaderInput( sqlStmt, inputCols, inNcwsa ); } } } } } MessageBox.Show( "Info: Member import processed" + "\nMember records read: " + myCountMemberInput + "\nmyCountMemberAdded: " + myCountMemberAdded + "\nmyCountMemberUpdate: " + myCountMemberUpdate + "\nmyCountTourRegAdded: " + myCountTourRegAdded + "\nmyCountSlalomAdded: " + myCountSlalomAdded + "\nmyCountTrickAdded: " + myCountTrickAdded + "\nmyCountJumpAdded: " + myCountJumpAdded ); #endregion } catch ( Exception ex ) { String ExcpMsg = ex.Message; if ( sqlStmt != null ) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show( "Error: Performing SQL operations" + "\n\nError: " + ExcpMsg ); } finally { myDbConn.Close(); myReader.Close(); myReader.Dispose(); } } } else { return false; } } catch ( Exception ex ) { MessageBox.Show( "Error: Could not read file" + myFileDialog.FileName + "\n\nError: " + ex.Message ); return false; } return true; }
private void saveButton_Click(object sender, EventArgs e) { StringBuilder curSqlStmt = new StringBuilder( "" ); SqlCeConnection curDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); SqlCeCommand curSqlCmd = curDbConn.CreateCommand(); try { curDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; curDbConn.Open(); if (editEntityName.Text.Equals( "TR" )) { //Insert OfficialWork curSqlStmt.Append( "Insert INTO OfficialWork (" ); curSqlStmt.Append( "SanctionId, MemberId, LastUpdateDate" ); curSqlStmt.Append( ", JudgeSlalomRating, JudgeTrickRating, JudgeJumpRating" ); curSqlStmt.Append( ", DriverSlalomRating, DriverTrickRating, DriverJumpRating" ); curSqlStmt.Append( ", ScorerSlalomRating, ScorerTrickRating, ScorerJumpRating" ); curSqlStmt.Append( ", SafetyOfficialRating, TechOfficialRating, AnncrOfficialRating " ); curSqlStmt.Append( ") Values (" ); curSqlStmt.Append( "'" + mySanctionNum + "'" ); curSqlStmt.Append( ", '" + editMemberId.Text + "'" ); curSqlStmt.Append( ", getdate() " ); curSqlStmt.Append( ", '" + JudgeSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + JudgeTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + JudgeJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + DriverSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + DriverTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + DriverJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + ScorerSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + ScorerTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + ScorerJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + SafetyRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + TechOfficialRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", '" + AnncrOfficialRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ")" ); } else { //Update OfficialWork curSqlStmt.Append( "Update OfficialWork Set " ); curSqlStmt.Append( "LastUpdateDate = GETDATE() " ); curSqlStmt.Append( ", JudgeSlalomRating = '" + JudgeSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", JudgeTrickRating = '" + JudgeTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", JudgeJumpRating = '" + JudgeJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", DriverSlalomRating = '" + DriverSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", DriverTrickRating = '" + DriverTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", DriverJumpRating = '" + DriverJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", ScorerSlalomRating = '" + ScorerSlalomRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", ScorerTrickRating = '" + ScorerTrickRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", ScorerJumpRating = '" + ScorerJumpRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", SafetyOfficialRating = '" + SafetyRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", TechOfficialRating = '" + TechOfficialRatingSelect.SelectedItem + "'" ); curSqlStmt.Append( ", AnncrOfficialRating = '" + AnncrOfficialRatingSelect.SelectedItem + "' " ); curSqlStmt.Append( "Where SanctionId = '" + mySanctionNum + " ' AND MemberId = '" + editMemberId.Text + "' " ); } curSqlCmd.CommandText = curSqlStmt.ToString(); int rowsProc = curSqlCmd.ExecuteNonQuery(); if (rowsProc > 0) { MessageBox.Show( "Official ratings for skier have been updated" ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (curSqlCmd != null) { ExcpMsg += "\n" + curSqlCmd.CommandText; } MessageBox.Show( "Error attempting to save officials ratings " + "\n\nError: " + ExcpMsg ); } finally { curDbConn.Close(); } }
private void saveButton_Click(object sender, EventArgs e) { StringBuilder curSqlStmt = new StringBuilder(""); SqlCeConnection curDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); SqlCeCommand curSqlCmd = curDbConn.CreateCommand(); try { curDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; curDbConn.Open(); if (editEntityName.Text.Equals("TR")) { //Insert OfficialWork curSqlStmt.Append("Insert INTO OfficialWork ("); curSqlStmt.Append("SanctionId, MemberId, LastUpdateDate"); curSqlStmt.Append(", JudgeSlalomRating, JudgeTrickRating, JudgeJumpRating"); curSqlStmt.Append(", DriverSlalomRating, DriverTrickRating, DriverJumpRating"); curSqlStmt.Append(", ScorerSlalomRating, ScorerTrickRating, ScorerJumpRating"); curSqlStmt.Append(", SafetyOfficialRating, TechOfficialRating, AnncrOfficialRating "); curSqlStmt.Append(") Values ("); curSqlStmt.Append("'" + mySanctionNum + "'"); curSqlStmt.Append(", '" + editMemberId.Text + "'"); curSqlStmt.Append(", getdate() "); curSqlStmt.Append(", '" + JudgeSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + JudgeTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + JudgeJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + DriverSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + DriverTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + DriverJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + ScorerSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + ScorerTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + ScorerJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + SafetyRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + TechOfficialRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", '" + AnncrOfficialRatingSelect.SelectedItem + "'"); curSqlStmt.Append(")"); } else { //Update OfficialWork curSqlStmt.Append("Update OfficialWork Set "); curSqlStmt.Append("LastUpdateDate = GETDATE() "); curSqlStmt.Append(", JudgeSlalomRating = '" + JudgeSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", JudgeTrickRating = '" + JudgeTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", JudgeJumpRating = '" + JudgeJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", DriverSlalomRating = '" + DriverSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", DriverTrickRating = '" + DriverTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", DriverJumpRating = '" + DriverJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", ScorerSlalomRating = '" + ScorerSlalomRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", ScorerTrickRating = '" + ScorerTrickRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", ScorerJumpRating = '" + ScorerJumpRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", SafetyOfficialRating = '" + SafetyRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", TechOfficialRating = '" + TechOfficialRatingSelect.SelectedItem + "'"); curSqlStmt.Append(", AnncrOfficialRating = '" + AnncrOfficialRatingSelect.SelectedItem + "' "); curSqlStmt.Append("Where SanctionId = '" + mySanctionNum + " ' AND MemberId = '" + editMemberId.Text + "' "); } curSqlCmd.CommandText = curSqlStmt.ToString(); int rowsProc = curSqlCmd.ExecuteNonQuery(); if (rowsProc > 0) { MessageBox.Show("Official ratings for skier have been updated"); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (curSqlCmd != null) { ExcpMsg += "\n" + curSqlCmd.CommandText; } MessageBox.Show("Error attempting to save officials ratings " + "\n\nError: " + ExcpMsg ); } finally { curDbConn.Close(); } }
private bool execUpdateNops() { //Update NOPS values due to unavailable 2014 NOPS tables //Prior to version 1.0.1.82 bool curReturnValue = true; SqlCeCommand sqlStmt = null; SqlCeConnection myDbConn = null; DataTable myDataTable = new DataTable(); StringBuilder curSqlStmt = new StringBuilder(); List <Common.ScoreEntry> curScoreList = new List <Common.ScoreEntry>(); int rowsProc, rowUpdateCount = 0; try { myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection(); myDbConn.ConnectionString = Properties.Settings.Default.waterskiConnectionStringApp; myDbConn.Open(); sqlStmt = myDbConn.CreateCommand(); myNopsCalc = CalcNops.Instance; myNopsCalc.LoadDataForTour(); curScoreList.Add(new Common.ScoreEntry("Trick", 0, "", 0)); curSqlStmt = new StringBuilder(); curSqlStmt.Append("Select PK, SanctionId, MemberId, AgeGroup, Score, Round "); curSqlStmt.Append("From TrickScore "); curSqlStmt.Append("Where SanctionId = '" + mySanctionNum + "' "); curSqlStmt.Append("Order by AgeGroup, MemberId, Round "); myDataTable = getData(curSqlStmt.ToString()); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = Convert.ToDecimal((Int16)curRow["Score"]); myNopsCalc.calcNops((String)curRow["AgeGroup"], curScoreList); sqlStmt.CommandText = "Update TrickScore " + "SET NopsScore = " + Math.Round(curScoreList[0].Nops, 1).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round(curScoreList[0].Nops, 1).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show("Error updating trick NOPS " + "\n\nError: " + ExcpMsg); curReturnValue = false; } } MessageBox.Show(myDataTable.Rows.Count.ToString() + " Trick scores read" + "\nTrick NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed"); curScoreList[0].Event = "Jump"; curSqlStmt = new StringBuilder(); curSqlStmt.Append("Select PK, SanctionId, MemberId, AgeGroup, ScoreFeet, Round "); curSqlStmt.Append("From JumpScore "); curSqlStmt.Append("Where SanctionId = '" + mySanctionNum + "' "); curSqlStmt.Append("Order by AgeGroup, MemberId, Round "); myDataTable = getData(curSqlStmt.ToString()); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = (Decimal)curRow["ScoreFeet"]; myNopsCalc.calcNops((String)curRow["AgeGroup"], curScoreList); sqlStmt.CommandText = "Update JumpScore " + "SET NopsScore = " + Math.Round(curScoreList[0].Nops, 1).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round(curScoreList[0].Nops, 1).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show("Error updating jump NOPS \n\nError: " + ExcpMsg); curReturnValue = false; } } MessageBox.Show(myDataTable.Rows.Count.ToString() + " Jump scores read" + "\nJump NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed"); curScoreList[0].Event = "Slalom"; curSqlStmt = new StringBuilder(); curSqlStmt.Append("Select PK, SanctionId, MemberId, AgeGroup, Score, Round "); curSqlStmt.Append("From SlalomScore "); curSqlStmt.Append("Where SanctionId = '" + mySanctionNum + "' "); curSqlStmt.Append("Order by AgeGroup, MemberId, Round "); myDataTable = getData(curSqlStmt.ToString()); rowUpdateCount = 0; foreach (DataRow curRow in myDataTable.Rows) { try { curScoreList[0].Nops = 0; curScoreList[0].Score = (Decimal)curRow["Score"]; myNopsCalc.calcNops((String)curRow["AgeGroup"], curScoreList); sqlStmt.CommandText = "Update SlalomScore " + "SET NopsScore = " + Math.Round(curScoreList[0].Nops, 1).ToString() + " " + "WHERE PK = " + (Int64)curRow["PK"] + " And NopsScore != " + Math.Round(curScoreList[0].Nops, 1).ToString() + " "; rowsProc = sqlStmt.ExecuteNonQuery(); rowUpdateCount += rowsProc; if (rowsProc == 0) { //MessageBox.Show( "Record for " + (String)curRow["MemberId"] + " not updated \nCalculated value is " + Math.Round( curScoreList[0].Nops, 1 ).ToString() ); } } catch (Exception ex) { String ExcpMsg = ex.Message; if (sqlStmt != null) { ExcpMsg += "\n" + sqlStmt.CommandText; } MessageBox.Show("Error updating Slalom NOPS \n\nError: " + ExcpMsg); curReturnValue = false; } } MessageBox.Show(myDataTable.Rows.Count.ToString() + " Slalom scores read" + "\nSlalom NOPS scores updated = " + rowUpdateCount.ToString() + "\nNote: Records with no change in score were bypassed"); } catch (Exception excp) { String curMsg = ":Error attempting to update 2015 Nops values \n" + excp.Message; MessageBox.Show(curMsg); curReturnValue = false; } finally { myDbConn.Close(); } return(curReturnValue); }