public Options(OdbcConnectionStringBuilder connectionString)
{
dbConnectionString = connectionString.ToString();
using (OdbcConnection connection = new OdbcConnection(dbConnectionString))
{
string SQLString = $"SELECT ShowResults, ShowPercentage, LeadCard, BM2ValidateLeadCard, BM2Ranking, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, EnterResultsMethod, TabletsMove FROM Settings";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
connection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
reader.Read();
ShowTraveller = reader.GetBoolean(0);
ShowPercentage = reader.GetBoolean(1);
EnterLeadCard = reader.GetBoolean(2);
ValidateLeadCard = reader.GetBoolean(3);
ShowRanking = reader.GetInt32(4);
ShowHandRecord = reader.GetBoolean(5);
NumberEntryEachRound = reader.GetBoolean(6);
NameSource = reader.GetInt32(7);
EnterResultsMethod = reader.GetInt32(8);
if (EnterResultsMethod != 1)
{
EnterResultsMethod = 0;
}
TabletsMove = reader.GetBoolean(9);
reader.Close();
cmd.Dispose();
}
}
public bool Connect()
{
try {
OdbcConnectionStringBuilder mcsb = new OdbcConnectionStringBuilder();
mcsb["DRIVER"] = this.Drivername;
mcsb["SERVER"] = this.Hostname;
mcsb["PORT"] = this.Port == 0 ? 3306 : this.Port;
if (this.Database != "")
{
mcsb["DATABASE"] = this.Database;
}
mcsb["USER"] = this.Username;
mcsb["PASSWORD"] = this.Password;
mcsb["CHARSET"] = "UTF8";
_odbcConnectionString = mcsb.ToString();
try {
using (OdbcConnection oc = new OdbcConnection(_odbcConnectionString)) {
oc.Open();
oc.Close();
}
}
catch (Exception hEx) {
sLastError = hEx.Message;
sLastSql = "Connect";
return(false);
}
return(true);
}
catch (Exception) { _odbcConnectionString = ""; return(false); }
}
private void InsertRow(int count, string[] row)
{
try
{
StringBuilder queryString = new StringBuilder();
for (int i = 0; i < count; i++)
{
queryString.Append("INSERT INTO " + Config.Sets.Primary_SQL_TableName + " (" + Config.Sets.Primary_SQL_IDColName + ", " + Config.Sets.Primary_SQL_ValColName + ", " + Config.Sets.Primary_SQL_DATColName + ") Values(" + row[3 * i] + ", " + row[3 * i + 1] + ", '" + row[3 * i + 2] + "')");
}
OdbcCommand command = new OdbcCommand(queryString.ToString());
using (OdbcConnection connection = new OdbcConnection(connStringBuilder.ToString()))
{
command.Connection = connection;
connection.Open();
command.ExecuteNonQuery();
// The connection is automatically closed at the end of the Using block.
}
queryString.Clear();
}
catch (Exception ex)
{
if (BGW_OPCUA.IsBusy)
{
BGW_OPCUA.CancelAsync();
}
MessageBox.Show(ex.Message, "Error");
}
}
private void cbxDsName_SelectedIndexChanged(object sender, EventArgs e)
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(tbConnectionString.Text);
builder["Dsn"] = cbxDsName.SelectedItem as string;
tbConnectionString.Text = builder.ToString();
}
private void SaveDsn(bool forceOverwrite)
{
var errorMessage = string.Empty;
var dsnResult = RebuildAndValidateDsn();
if (!dsnResult)
{
return;
}
var dsn = Builder.ToString();
var flag = forceOverwrite ? 1u : 0;
var result = saveDsn(dsn, ref errorMessage, flag);
if (result >= 0 || (forceOverwrite && result == ESODBC_DSN_EXISTS_ERROR))
{
Close();
return;
}
// Specific handling for prompting the user if result is an overwrite action
if (forceOverwrite == false &&
result == ESODBC_DSN_EXISTS_ERROR)
{
var dialogResult = MessageBox.Show("The DSN already exists, are you sure you wish to overwrite it?", "Overwrite", MessageBoxButtons.YesNo);
if (dialogResult == DialogResult.Yes)
{
SaveDsn(true);
}
return;
}
if (errorMessage.Length <= 0)
{
errorMessage = (result == ESODBC_DSN_NAME_INVALID_ERROR)
? "Invalid DSN name"
: "Saving the DSN failed";
}
MessageBox.Show(errorMessage, "Operation failure", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
/// <inheritdoc/>
protected override string GetConnectionStringWithLoginInfo(string userName, string password)
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(ConnectionString);
builder.Remove("uid");
builder.Add("uid", userName);
builder.Remove("pwd");
builder.Add("pwd", password);
return(builder.ToString());
}
public БанкСведения[] ПоискБанка(string БИК)
{
//SELECT VKEY, `REAL`, PZN, UER, RGN, IND, TNP, NNP, ADR, RKC, NAMEP, NAMEN, NEWNUM, NEWKS, PERMFO, SROK, AT1, AT2, TELEF, REGN, OKPO, DT_IZM,
// CKS, KSNP, DATE_IN, DATE_CH, VKEYDEL
//FROM bnkseek
//where NEWNUM = @bik
var list = new List <БанкСведения>();
var stringBuilder = new OdbcConnectionStringBuilder(System.Configuration.ConfigurationManager.ConnectionStrings["БазаДанныхБанков"].ConnectionString)
{
Driver = "Microsoft dBASE Driver (*.dbf)"
};
using (var connection = new OdbcConnection(stringBuilder.ToString()))
{
var command = connection.CreateCommand();
command.CommandText = string.Format("SELECT * FROM bnkseek.dbf WHERE NEWNUM = '{0}'", БИК);
command.CommandType = CommandType.Text;
try
{
if (((command.Connection.State & System.Data.ConnectionState.Open) != System.Data.ConnectionState.Open))
{
command.Connection.Open();
}
using (OdbcDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
list.Add(new БанкСведения()
{
Название = Convert.ToString(reader["NAMEP"]),
КорСчет = Convert.ToString(reader["KSNP"]),
БИК = Convert.ToString(reader["NEWNUM"]),
Адрес = Convert.ToString(reader["ADR"]),
Город = Convert.ToString(reader["NNP"])
});
}
}
}
finally
{
if ((command.Connection.State == System.Data.ConnectionState.Closed))
{
command.Connection.Close();
}
}
}
return(list.ToArray());
}
public HandsList(OdbcConnectionStringBuilder connectionString)
{
using (OdbcConnection connection = new OdbcConnection(connectionString.ToString()))
{
string SQLString = $"SELECT Section, Board, NorthSpades, NorthHearts, NorthDiamonds, NorthClubs, EastSpades, EastHearts, EastDiamonds, EastClubs, SouthSpades, SouthHearts, SouthDiamonds, SouthClubs, WestSpades, WestHearts, WestDiamonds, WestClubs FROM HandRecord";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
connection.Open();
try
{
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
Hand hand = new Hand
{
SectionID = reader.GetInt32(0),
BoardNumber = reader.GetInt32(1),
NorthSpades = reader.GetValue(2).ToString(),
NorthHearts = reader.GetValue(3).ToString(),
NorthDiamonds = reader.GetValue(4).ToString(),
NorthClubs = reader.GetValue(5).ToString(),
EastSpades = reader.GetValue(6).ToString(),
EastHearts = reader.GetValue(7).ToString(),
EastDiamonds = reader.GetValue(8).ToString(),
EastClubs = reader.GetValue(9).ToString(),
SouthSpades = reader.GetValue(10).ToString(),
SouthHearts = reader.GetValue(11).ToString(),
SouthDiamonds = reader.GetValue(12).ToString(),
SouthClubs = reader.GetValue(13).ToString(),
WestSpades = reader.GetValue(14).ToString(),
WestHearts = reader.GetValue(15).ToString(),
WestDiamonds = reader.GetValue(16).ToString(),
WestClubs = reader.GetValue(17).ToString()
};
Add(hand);
}
reader.Close();
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S02") // Error other than HandRecord table does not exist
{
throw e;
}
}
cmd.Dispose();
}
}
/// <summary>
/// Creates a new connection to the database
/// </summary>
/// <returns></returns>
private OdbcConnection GetOdbcConnection()
{
//Credentials
string user = "******";
string pass = "******";
string server = "server";
string database = "database";
//Create the Connection String
OdbcConnectionStringBuilder mscsb = new OdbcConnectionStringBuilder();
mscsb.Add("Server", server);
mscsb.Add("Database", database);
mscsb.Add("User", user);
mscsb.Add("Password", pass);
mscsb.Add("Connection Timeout", 120);
//Return a new connection using the connection string
return(new OdbcConnection(mscsb.ToString()));
}
public static string GetNameFromExternalDatabase(int playerNumber)
{
string name = "";
OdbcConnectionStringBuilder externalDB = new OdbcConnectionStringBuilder {
Driver = "Microsoft Access Driver (*.mdb)"
};
externalDB.Add("Dbq", @"C:\Bridgemate\BMPlayerDB.mdb");
externalDB.Add("Uid", "Admin");
using (OdbcConnection connection = new OdbcConnection(externalDB.ToString()))
{
object queryResult = null;
string SQLString = $"SELECT Name FROM PlayerNameDatabase WHERE ID={playerNumber}";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
connection.Open();
ODBCRetryHelper.ODBCRetry(() =>
{
queryResult = cmd.ExecuteScalar();
if (queryResult == null)
{
name = "Unknown #" + playerNumber;
}
else
{
name = queryResult.ToString();
}
});
}
catch (OdbcException) // If we can't read the external database for whatever reason...
{
name = "#" + playerNumber;
}
finally
{
cmd.Dispose();
}
}
return(name);
}
public void WriteToDB(OdbcConnectionStringBuilder connectionString)
{
using (OdbcConnection connection = new OdbcConnection(connectionString.ToString()))
{
connection.Open();
string SQLString = "DELETE FROM HandRecord";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
foreach (Hand hc in this)
{
if (hc.NorthSpades != "###")
{
SQLString = $"INSERT INTO HandRecord (Section, Board, NorthSpades, NorthHearts, NorthDiamonds, NorthClubs, EastSpades, EastHearts, EastDiamonds, EastClubs, SouthSpades, SouthHearts, SouthDiamonds, SouthClubs, WestSpades, WestHearts, WestDiamonds, WestClubs) VALUES ({hc.SectionID}, {hc.BoardNumber}, '{hc.NorthSpades}', '{hc.NorthHearts}', '{hc.NorthDiamonds}', '{hc.NorthClubs}', '{hc.EastSpades}', '{hc.EastHearts}', '{hc.EastDiamonds}', '{hc.EastClubs}', '{hc.SouthSpades}', '{hc.SouthHearts}', '{hc.SouthDiamonds}', '{hc.SouthClubs}', '{hc.WestSpades}', '{hc.WestHearts}', '{hc.WestDiamonds}', '{hc.WestClubs}')";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
}
cmd.Dispose();
}
}
public Options(OdbcConnectionStringBuilder connectionString)
{
dbConnectionString = connectionString.ToString();
using (OdbcConnection connection = new OdbcConnection(dbConnectionString))
{
string SQLString = $"SELECT ShowResults, ShowPercentage, BM2Ranking, BM2ViewHandRecord, BM2NumberEntryEachRound, BM2NameSource, PollInterval FROM Settings";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
connection.Open();
OdbcDataReader reader = cmd.ExecuteReader();
reader.Read();
ShowTraveller = reader.GetBoolean(0);
ShowPercentage = reader.GetBoolean(1);
ShowRanking = reader.GetInt32(2);
ShowHandRecord = reader.GetBoolean(3);
NumberEntryEachRound = reader.GetBoolean(4);
NameSource = reader.GetInt32(5);
PollInterval = reader.GetInt32(6);
reader.Close();
cmd.Dispose();
}
}
protected override void SetConnectionString(string value)
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(value);
FillDataSources();
if (String.IsNullOrEmpty(value))
{
rbDsName.Checked = true;
}
else if (builder.ContainsKey("Dsn"))
{
string dsn = builder["Dsn"] as string;
if (cbxDsName.Items.Contains(dsn))
{
cbxDsName.SelectedIndex = cbxDsName.Items.IndexOf(dsn);
rbDsName.Checked = true;
}
else
{
rbConnectionString.Checked = true;
}
}
if (builder.ContainsKey("uid"))
{
tbUserName.Text = builder["uid"] as string;
builder.Remove("uid");
}
if (builder.ContainsKey("pwd"))
{
tbPassword.Text = builder["pwd"] as string;
builder.Remove("pwd");
}
// display the string w/o uid, pwd
tbConnectionString.Text = builder.ToString();
// update the enabled state
rbDsName_CheckedChanged(this, EventArgs.Empty);
}
public HandEvaluationsList(OdbcConnectionStringBuilder connectionString)
{
dbConnectionString = connectionString.ToString();
using (OdbcConnection connection = new OdbcConnection(dbConnectionString))
{
connection.Open();
string SQLString = "CREATE TABLE HandEvaluation (Section SHORT, Board SHORT, NorthSpades SHORT, NorthHearts SHORT, NorthDiamonds SHORT, NorthClubs SHORT, NorthNoTrump SHORT, EastSpades SHORT, EastHearts SHORT, EastDiamonds SHORT, EastClubs SHORT, EastNoTrump SHORT, SouthSpades SHORT, SouthHearts SHORT, SouthDiamonds SHORT, SouthClubs SHORT, SouthNotrump SHORT, WestSpades SHORT, WestHearts SHORT, WestDiamonds SHORT, WestClubs SHORT, WestNoTrump SHORT, NorthHcp SHORT, EastHcp SHORT, SouthHcp SHORT, WestHcp SHORT)";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count > 1 || e.Errors[0].SQLState != "42S01") // Error other than HandEvaluation table already exists
{
throw e;
}
}
cmd.Dispose();
}
}
protected override string GetConnectionString()
{
OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder(tbConnectionString.Text);
if (!String.IsNullOrEmpty(tbUserName.Text))
{
builder.Add("uid", tbUserName.Text);
}
else
{
builder.Remove("uid");
}
if (!String.IsNullOrEmpty(tbPassword.Text))
{
builder.Add("pwd", tbPassword.Text);
}
else
{
builder.Remove("pwd");
}
return(builder.ToString());
}
private void Open(string providerName, string connectionString)
{
switch (providerName.ToLower())
{
case "lcpi.ibprovider":
{
OleDbConnectionStringBuilder alias = new OleDbConnectionStringBuilder();
alias.Provider = "LCPI.IBProvider";
alias.Add("Location", connectionString);
alias.Add("User ID", "????????");
alias.Add("password", "????????");
alias.Add("ctype", "win1251");
alias.Add("auto_commit", true);
connection = new OleDbConnection(alias.ToString());
break;
}
case "odbc.firebird":
{
OdbcConnectionStringBuilder alias = new OdbcConnectionStringBuilder();
alias.Driver = "Firebird/InterBase(r) driver";
alias.Add("DBNAME", connectionString);
alias.Add("UID", "????????");
alias.Add("PWD", "????????");
alias.Add("CHARSET", "win1251");
alias.Add("DIALECT", "1");
connection = new OdbcConnection(alias.ToString());
break;
}
case "odbc.postgres":
case "odbc.postgresql":
{
OdbcConnectionStringBuilder alias = new OdbcConnectionStringBuilder();
string[] words = connectionString.Split(':');
if (words.Length < 1 || words.Length > 3)
{
throw new ArgumentException("PostgreSQL syntax: SERVER:DATABASE or SERVER:PORT:DATABASE");
}
alias.Driver = "PostgreSQL ODBC Driver(ANSI)";
alias.Add("Servername", words[0]);
if (words.Length == 2)
{
alias.Add("Database", words[1]);
}
else
{
alias.Add("Port", words[1]);
alias.Add("Database", words[2]);
}
byte[] key = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
byte[] buffer = { 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00,
0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x00 };
for (int i = 0; i < buffer.Length; i++)
{
buffer[i] ^= key[i % key.Length];
}
alias.Add("Username", "????????");
alias.Add("Password", "{" + Encoding.UTF8.GetString(buffer) + "}");
connection = new OdbcConnection(alias.ToString());
break;
}
default: throw new ArgumentException("The '" + providerName + "' provider is not supported.");
}
if (!Machine.Interactive)
{
for (int i = 1; i <= (Machine.HasEntryPoint ? 8 : 15); i++)
{
try
{
connection.Open();
return;
}
catch (DbException)
{
}
Thread.Sleep(i * 1000);
}
}
connection.Open();
}
public static bool Initialize(OdbcConnectionStringBuilder connectionString)
{
if (connectionString == null)
{
return(false);
}
using (OdbcConnection connection = new OdbcConnection(connectionString.ToString()))
{
try
{
connection.Open();
// Check sections
int sectionID = 0;
string SQLString = "SELECT ID, Letter, [Tables], Winners FROM Section";
OdbcCommand cmd = new OdbcCommand(SQLString, connection);
OdbcDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
sectionID = reader.GetInt32(0);
string sectionLetter = reader.GetString(1);
int numTables = reader.GetInt32(2);
if (sectionID < 1 || sectionID > 4 || (sectionLetter != "A" && sectionLetter != "B" && sectionLetter != "C" && sectionLetter != "D"))
{
reader.Close();
MessageBox.Show("Database contains incorrect Sections. Maximum 4 Sections labelled A, B, C, D", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
if (numTables > 30)
{
reader.Close();
MessageBox.Show("Database contains > 30 Tables in a Section", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
reader.Close();
if (sectionID == 0)
{
MessageBox.Show("Database contains no Sections", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
// Add column 'BiddingStarted' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD BiddingStarted YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'BiddingComplete' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD BiddingComplete YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'BiddingStarted' to table 'Tables' if it doesn't already exist
SQLString = "ALTER TABLE [Tables] ADD PlayComplete YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'Name' to table 'PlayerNumbers' if it doesn't already exist
SQLString = "ALTER TABLE PlayerNumbers ADD [Name] VARCHAR(30)";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Add column 'Round' to table 'PlayerNumbers' if it doesn't already exist
SQLString = "ALTER TABLE PlayerNumbers ADD [Round] SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Ensure that all Round values are set to 0 to start with
SQLString = "UPDATE PlayerNumbers SET [Round]=0";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
// Check if this is an individual event, when RoundData will have a 'South' column
bool individualEvent = true;
SQLString = $"SELECT TOP 1 South FROM RoundData";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteScalar();
}
catch (OdbcException)
{
individualEvent = false;
}
// If this is an individual event, add extra columns South and West to ReceivedData and IntermediateData if they don't exist
if (individualEvent)
{
SQLString = "ALTER TABLE ReceivedData ADD South SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE ReceivedData ADD West SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE IntermediateData ADD South SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE IntermediateData ADD West SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
}
// Add a new column 'TabPlayPairNo' to table 'PlayerNumbers' if it doesn't exist and populate it if possible
SQLString = "ALTER TABLE PlayerNumbers ADD TabPlayPairNo SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "SELECT Section, [Table], Direction FROM PlayerNumbers";
cmd = new OdbcCommand(SQLString, connection);
reader = cmd.ExecuteReader();
OdbcCommand cmd2 = new OdbcCommand();
while (reader.Read())
{
int section = reader.GetInt32(0);
int table = reader.GetInt32(1);
string direction = reader.GetString(2);
if (individualEvent)
{
switch (direction)
{
case "N":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "S":
SQLString = $"SELECT South FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "E":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "W":
SQLString = $"SELECT West FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
}
}
else
{
switch (direction)
{
case "N":
case "S":
SQLString = $"SELECT NSPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
case "E":
case "W":
SQLString = $"SELECT EWPair FROM RoundData WHERE Section={section} AND [Table]={table} AND ROUND=1";
break;
}
}
cmd2 = new OdbcCommand(SQLString, connection);
object queryResult = cmd2.ExecuteScalar();
string pairNo = queryResult.ToString();
SQLString = $"UPDATE PlayerNumbers SET TabPlayPairNo={pairNo} WHERE Section={section} AND [Table]={table} AND Direction='{direction}'";
cmd2 = new OdbcCommand(SQLString, connection);
cmd2.ExecuteNonQuery();
}
cmd2.Dispose();
// Add various columns to table 'Settings' if they don't already exist and set defaults
SQLString = "ALTER TABLE Settings ADD ShowResults YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowResults=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD ShowPercentage YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowPercentage=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD LeadCard YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET LeadCard=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2ValidateLeadCard YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2ValidateLeadCard=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2NumberEntryEachRound YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2NumberEntryEachRound=NO";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2ViewHandRecord YESNO";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2ViewHandRecord=YES";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2Ranking SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET BM2Ranking=1";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD BM2NameSource SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = "UPDATE Settings SET ShowResults=0";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
SQLString = "ALTER TABLE Settings ADD PollInterval SHORT";
cmd = new OdbcCommand(SQLString, connection);
try
{
cmd.ExecuteNonQuery();
SQLString = $"UPDATE Settings SET PollInterval={Properties.Settings.Default.PollInterval}";
cmd = new OdbcCommand(SQLString, connection);
cmd.ExecuteNonQuery();
}
catch (OdbcException e)
{
if (e.Errors.Count != 1 || e.Errors[0].SQLState != "HYS21")
{
throw e;
}
}
// Check if any previous results in database
object Result;
SQLString = "SELECT * FROM ReceivedData";
cmd = new OdbcCommand(SQLString, connection);
Result = cmd.ExecuteScalar();
if (Result != null)
{
MessageBox.Show("Database contains previous results", "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
cmd.Dispose();
}
catch (Exception e)
{
MessageBox.Show(e.Message, "TabPlayStarter", MessageBoxButtons.OK, MessageBoxIcon.Error);
return(false);
}
}
return(true);
}