public static DataTable GetTable(string query, MySqlConnection conn, bool CloseConnAfter = true, bool dontshowerrors = false)
{
if (conn == null)
{
if (!dontshowerrors)
{
MessageBox.Show("Connection is Null", "Connectivity Error: GetTable()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(new DataTable()); //empty datatable
}
if (conn.State != ConnectionState.Open)
{
if (!dontshowerrors)
{
MessageBox.Show("Connection is not open", "Connectivity Error: GetTable()", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
return(new DataTable()); //empty datatable
}
DataTable dt = new DataTable();
try
{
MySqlDataAdapter da = new MySqlDataAdapter(query, conn);
//da.SelectCommand.CommandTimeout = 0;
dt.Clear();
da.Fill(dt);
if (CloseConnAfter)
{
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
da.Dispose();
return(dt);
}
catch (Exception ex)
{
if (!dontshowerrors)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
return(null);
}
finally
{
if (CloseConnAfter)
{
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
}
}
public async Task SaveBotStats(BotStats stats)
{
var Query = "UPDATE `BotStats` SET `YouTubeAlertCount`=" + stats.YouTubeAlertCount +
",`MixerAlertCount`=" + stats.BeamAlertCount +
",`SmashcastAlertCount`=" + stats.HitboxAlertCount +
",`PicartoAlertCount`=" + stats.PicartoAlertCount +
",`VidMeAlertCount`=" + stats.VidMeAlertCount +
",`UptimeMinutes`=" + stats.UptimeMinutes +
",`HaiBaiCount`=" + stats.HaiBaiCount +
",`FlipCount`=" + stats.FlipCount +
",`UnflipCount`=" + stats.UnflipCount +
",`TwitchAlertCount`=" + stats.TwitchAlertCount +
",`LoggingStartDate`='" + stats.LoggingStartDate +
"',`LastRestart`='" + stats.LastRestart.ToString("yyyy-MM-dd HH:mm:ss") + "' WHERE Id = 1";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(Query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
while (MyReader2.Read())
{
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
}
private Task ClearPoolAsync(MySqlConnection connection)
{
#if BASELINE
return(connection.ClearPoolAsync(connection));
#else
return(MySqlConnection.ClearPoolAsync(connection));
#endif
}
public async Task ClearPoolAsync()
{
MySqlConnection c1 = new MySqlConnection(Connection.ConnectionString);
MySqlConnection c2 = new MySqlConnection(Connection.ConnectionString);
c1.Open();
c2.Open();
c1.Close();
c2.Close();
await c1.ClearPoolAsync(c1);
await c2.ClearPoolAsync(c1);
}
public void ClearPoolAsync()
{
MySqlConnection c1 = new MySqlConnection(st.GetConnectionString(true));
MySqlConnection c2 = new MySqlConnection(st.GetConnectionString(true));
c1.Open();
c2.Open();
c1.Close();
c2.Close();
c1.ClearPoolAsync(c1);
System.Threading.Thread.Sleep(500);
c2.ClearPoolAsync(c1);
System.Threading.Thread.Sleep(500);
}
public async Task ClearPoolAsync()
{
MySqlConnection c1 = new MySqlConnection(st.GetConnectionString(true));
MySqlConnection c2 = new MySqlConnection(st.GetConnectionString(true));
c1.Open();
c2.Open();
c1.Close();
c2.Close();
await c1.ClearPoolAsync(c1);
await c2.ClearPoolAsync(c1);
c1.Dispose();
c2.Dispose();
}
public async Task LogRestartTime()
{
string query = "Update BotStats SET `LastRestart`='" + DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") + "' where Id = 1";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
while (MyReader2.Read())
{
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
}
public async Task LogAlert(string platform, ulong guildId)
{
var query = "INSERT INTO `alertlog`(`Platform`, `GuildId`, `CreatedDate`) VALUES ('" + platform + "','" + guildId + "','" + DateTime.UtcNow.ToString("yyyy-MM-dd HH:mm:ss") + "')";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
while (MyReader2.Read())
{
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
}
public async Task AddToBeamAlertCount()
{
var current = await GetBeamAlertCount().ConfigureAwait(false);
current++;
string query = "Update BotStats SET `MixerAlertCount`=" + current + " where Id = 1";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
while (MyReader2.Read())
{
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
}
public static MySqlConnection OpenSqlConnection(string server, string db, string u, string p, string port)
{
string connStr = string.Format("server={0};database={1};user={2};port={3};password={4};Allow User Variables=true;",
server, db, u, port, p);
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
return(conn);
}
catch (Exception ex)
{
MessageBox.Show("Error Occurred: " + ex.ToString());
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
return(null);
}
}
public static MySqlConnection OpenSqlConnection(string server, string db, string u, string p, string port, bool dontShowErrors = false)
{
string connStr = string.Format("server={0};database={1};user={2};port={3};password={4};Allow User Variables=true;", server, db, u, port, p);
MySqlConnection conn = new MySqlConnection(connStr);
try
{
conn.Open();
return(conn);
}
catch (Exception ex)
{
if (!dontShowErrors)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
return(null);
}
}
public async Task <BotStats> GetBotStats()
{
BotStats stats = null;
string query = "SELECT * FROM BotStats where Id = 1";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
while (MyReader2.Read())
{
stats = new BotStats();
stats.BeamAlertCount = int.Parse(MyReader2["MixerAlertCount"].ToString());
stats.FlipCount = int.Parse(MyReader2["FlipCount"].ToString());
stats.HaiBaiCount = int.Parse(MyReader2["Id"].ToString());
stats.HitboxAlertCount = int.Parse(MyReader2["SmashcastAlertCount"].ToString());
stats.LastRestart = DateTime.Parse(MyReader2["LastRestart"].ToString());
stats.LoggingStartDate = DateTime.Parse(MyReader2["LoggingStartDate"].ToString());
stats.PicartoAlertCount = int.Parse(MyReader2["PicartoAlertCount"].ToString());
stats.TwitchAlertCount = int.Parse(MyReader2["TwitchAlertCount"].ToString());
stats.UnflipCount = int.Parse(MyReader2["UnflipCount"].ToString());
stats.UptimeMinutes = int.Parse(MyReader2["UptimeMinutes"].ToString());
stats.VidMeAlertCount = int.Parse(MyReader2["VidMeAlertCount"].ToString());
stats.YouTubeAlertCount = int.Parse(MyReader2["YouTubeAlertCount"].ToString());
stats.MobcrushAlertCount = int.Parse(MyReader2["MobcrushAlertCount"].ToString());
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
return(stats);
}
public async Task <int> GetMobcrushAlertCount()
{
string query = "SELECT MobcrushAlertCount FROM BotStats where Id = 1";
MySqlConnection MyConn2 = new MySqlConnection(_botSettings.ConnectionStrings.BotContext);
MySqlCommand MyCommand2 = new MySqlCommand(query, MyConn2);
MySqlDataReader MyReader2;
MyConn2.Open();
MyReader2 = MyCommand2.ExecuteReader();
int count = 0;
while (MyReader2.Read())
{
count = int.Parse(MyReader2["MobcrushAlertCount"].ToString());
}
MyConn2.Close();
await MyConn2.ClearPoolAsync(MyConn2);
await MyConn2.ClearAllPoolsAsync();
return(count);
}
public void Dispose()
{
_con.ClearPoolAsync(this._con);
_con.Close();
}
private void FrmUserAuth_Load(object sender, EventArgs e)
{
DataTable ConnConfigDT = new DataTable();
try
{
string path = System.IO.Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location);
FileStream fs = new FileStream(path + @"\Config.dat", FileMode.Open);
BinaryFormatter bf = new BinaryFormatter();
ConnConfigDT = (DataTable)bf.Deserialize(fs);
fs.Close();
if (ConnConfigDT.Rows[0]["Database"].ToString() != "")
{
Program.currentDB = ConnConfigDT.Rows[0]["Database"].ToString();
Program.IsDefaultDBset = true;
}
else
{
Program.currentDB = "";
Program.IsDefaultDBset = false;
}
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
}
if (Program.IsDefaultDBset)
{
label3.Text = "Current Database: " + Program.currentDB;
lb_databases.Hide();
this.Height = 164;
}
if (Program.IsDefaultDBset)
{
return; //dont get list of databases
}
lb_databases.Items.Clear();
string connStr = string.Format("server={0};user={1};port={2};password={3};", Program.Server, Program.SqlUser, Program.Port, Program.Password);
MySqlConnection conn = new MySqlConnection(connStr);
MySqlCommand cmd;
string s0;
try
{
// show list of databases
conn.Open();
s0 = "SHOW DATABASES;";
cmd = new MySqlCommand(s0, conn);
MySqlDataReader Reader = cmd.ExecuteReader();
while (Reader.Read())
{
string row = "";
for (int i = 0; i < Reader.FieldCount; i++)
{
row = Reader.GetValue(i).ToString();
if (row.Length > 4)
{
if (row.Substring(0, 4) == "gbc_")
{
lb_databases.Items.Add(row.Substring(4).Replace('_', ' ').ToUpper());
}
}
}
}
if (lb_databases.Items.Count == 0)
{
MessageBox.Show("No database exists. Please create new database through accounting system.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
this.Close();
}
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
catch (Exception ex)
{
ExceptionMB emb = new ExceptionMB(); emb.ShowMB(ex);
MessageBox.Show("Not able to connect to MySQL Server. Please Enter MySQL Server Information.", "Connection Failure", MessageBoxButtons.OK, MessageBoxIcon.Information);
FrmConnectionConfig FCC = new FrmConnectionConfig();
FCC.ShowDialog();
this.Close();
conn.Close(); conn.ClearPoolAsync(conn);
conn.Dispose();
}
if (lb_databases.Items.Count > 0)
{
lb_databases.SetSelected(0, true);
txb_userid.Focus();
}
}