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 checkDbConnection(String inConnectString)
{
bool dbConnGood = false;
SqlCeConnection myDbConn = null;
try {
myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection();
myDbConn.ConnectionString = inConnectString;
myDbConn.Open();
dbConnGood = true;
} catch (Exception ex) {
dbConnGood = false;
MessageBox.Show("checkDbConnection:Exception: " + ex.Message);
} finally {
if (myDbConn != null)
{
myDbConn.Close();
}
}
return(dbConnGood);
}
private void InitConnection()
{
this._connection = new global::System.Data.SqlServerCe.SqlCeConnection();
this._connection.ConnectionString = ("Data Source ="
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\dbe.sdf;"));
}
private void InitConnection() {
this._connection = new global::System.Data.SqlServerCe.SqlCeConnection();
this._connection.ConnectionString = ("Data Source ="
+ (System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetExecutingAssembly().GetName().CodeBase) + "\\Products.sdf;"));
}
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;
}
public bool setConnectionString(String inDataDirectory, String inDatabaseFileName, RegistryKey inAppRegKey)
{
bool curReturn = false;
String curAttrName, curAttrValue, newConnectionString = "";
String curAppConnectString = Properties.Settings.Default.waterskiConnectionStringApp;
String[] curAttrEntry;
String[] curConnAttrList = curAppConnectString.Split(';');
for (int idx = 0; idx < curConnAttrList.Length; idx++)
{
curAttrEntry = curConnAttrList[idx].Split('=');
curAttrName = curAttrEntry[0];
curAttrValue = curAttrEntry[1];
if (newConnectionString.Length > 1)
{
newConnectionString += ";";
}
if (curAttrName.ToLower().Trim().Equals("data source"))
{
newConnectionString += curAttrName + "=|DataDirectory|\\" + inDatabaseFileName;
}
else
{
newConnectionString += curAttrName + "=" + curAttrValue;
}
}
Properties.Settings.Default.waterskiConnectionStringApp = newConnectionString;
inAppRegKey.SetValue("DataDirectory", inDataDirectory);
AppDomain.CurrentDomain.SetData("DataDirectory", inDataDirectory);
Boolean dbConnGood = false;
SqlCeConnection myDbConn = null;
try {
myDbConn = new global::System.Data.SqlServerCe.SqlCeConnection();
myDbConn.ConnectionString = newConnectionString;
myDbConn.Open();
dbConnGood = true;
UpgradeDatabase curUpgradeDatabase = new UpgradeDatabase();
curUpgradeDatabase.checkForUpgrade();
MessageBox.Show("Database connection successful!"
+ "\n ConnectionString: " + Properties.Settings.Default.waterskiConnectionStringApp
+ "\n\n Data location: " + AppDomain.CurrentDomain.GetData("DataDirectory")
+ "\n\n You must close the application and restart it to use the new database selection"
);
curReturn = true;
} catch (Exception ex) {
dbConnGood = false;
MessageBox.Show("Database connection failed!"
+ "\n ConnectionString: " + myDbConn.ConnectionString
+ "\n\n Data location: " + inDataDirectory
);
} finally {
if (myDbConn != null)
{
myDbConn.Close();
}
}
return(curReturn);
}
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 InitConnection() {
this._connection = new global::System.Data.SqlServerCe.SqlCeConnection();
this._connection.ConnectionString = global::DatabaseLib.Properties.Settings.Default.ScannedProductsConnectionString1;
}
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);
}
