public static int InsertarGenericoID(String sql)
{
//colocar el cursor en espera:
Cursor.Current = Cursors.WaitCursor;
//Haces tus operaciones
MySqlConnection conn = Persistencia.conexion_bd.abrir();
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = sql;
comm.ExecuteNonQuery();
conn.ClearAllPoolsAsync();
conn.Dispose();
conn.Close();
//Colocas tu cursor en estado normal:
Cursor.Current = Cursors.Default;
return((int)comm.LastInsertedId);
}
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();
}
public static Boolean InsertarGenerico(String sql)
{
try
{
//colocar el cursor en espera:
Cursor.Current = Cursors.WaitCursor;
//Haces tus operaciones
MySqlConnection conn = Persistencia.conexion_bd.abrir();
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandTimeout = 0;
comm.CommandText = sql;
comm.ExecuteNonQuery();
conn.ClearAllPoolsAsync();
conn.Dispose();
conn.Close();
//Colocas tu cursor en estado normal:
Cursor.Current = Cursors.Default;
return(true);
}
catch (Exception e) {
MessageBox.Show("Ha habido un error al insertar en el generico" + e.Message);
return(false);
}
}
/// <summary>
/// Checks if the server is started. The most reliable way is simply to check if we can connect to it
/// </summary>
///
private void waitForStartup()
{
int totalWaitTime = 0;
int sleepTime = 100;
int maxWaitTimes = 50;
Exception lastException = new Exception();
if (_testConnection == null)
{
_testConnection = new MySqlConnection(GetConnectionString());
}
while (!_testConnection.State.Equals(System.Data.ConnectionState.Open))
{
if (totalWaitTime == (sleepTime * maxWaitTimes))
{
throw new Exception("Server could not be started." + lastException.Message);
}
totalWaitTime = totalWaitTime + sleepTime;
try {
_testConnection.Open();
} catch (Exception e) {
_testConnection.Close();
lastException = e;
Thread.Sleep(sleepTime);
}
}
System.Console.WriteLine("Database connection established after " + totalWaitTime.ToString() + " miliseconds");
_testConnection.ClearAllPoolsAsync();
_testConnection.Close();
_testConnection.Dispose();
_testConnection = null;
}
/* Exports */
public static DataTable ExecuteQueryWithResult(string sql, bool isRecconect = false)
{
using (var conn = new MySqlConnection(_connStr))
{
try
{
conn.Open();
MySqlCommand cmd = new MySqlCommand(sql, conn);
MySqlDataReader rdr = cmd.ExecuteReader();
DataTable results = new DataTable();
results.Load(rdr);
rdr.Close();
conn.ClearAllPoolsAsync();
conn.CloseAsync();
return(results);
}
catch (Exception ex)
{
Debug.WriteLine("DATABASE: [CONNECT] " + (conn.State == ConnectionState.Open));
Debug.WriteLine("DATABASE: [ERROR] " + ex);
}
}
if (isRecconect)
{
return(new DataTable());
}
Thread.Sleep(5000);
return(ExecuteQueryWithResult(sql, true));
}
public async Task ClearAllPoolsAsync()
{
MySqlConnection c1 = new MySqlConnection(Connection.ConnectionString);
MySqlConnection c2 = new MySqlConnection(Connection.ConnectionString);
c1.Open();
c2.Open();
c1.Close();
c2.Close();
await c1.ClearAllPoolsAsync();
await c2.ClearAllPoolsAsync();
}
public void ClearAllPoolsAsync()
{
MySqlConnection c1 = new MySqlConnection(st.GetConnectionString(true));
MySqlConnection c2 = new MySqlConnection(st.GetConnectionString(true));
c1.Open();
c2.Open();
c1.Close();
c2.Close();
c1.ClearAllPoolsAsync();
System.Threading.Thread.Sleep(500);
c2.ClearAllPoolsAsync();
System.Threading.Thread.Sleep(500);
}
public void FecharCon()
{
try
{
con = new MySqlConnection(conec);
con.Close();
con.Dispose();
con.ClearAllPoolsAsync();
}
catch (Exception ex)
{
MessageBox.Show("Erro na conexão com o Banco! " + ex.Message);
}
}
public async Task ClearAllPoolsAsync()
{
MySqlConnection c1 = new MySqlConnection(st.GetConnectionString(true));
MySqlConnection c2 = new MySqlConnection(st.GetConnectionString(true));
c1.Open();
c2.Open();
c1.Close();
c2.Close();
await c1.ClearAllPoolsAsync();
await c2.ClearAllPoolsAsync();
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 static void InsertRuta(String IdTipoDoc, String fecha, String GeneralComunidades, String Descripcion, String Ruta)
{
MySqlConnection conn = Persistencia.conexion_bd.abrir();
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO com_Documentos(IdTipoDocumento, FechaCreacion, GeneralComunidades, Descripcion, Ruta) VALUES(@idtipodoc, @fecha, @comunidades, @descripcion, @ruta)";
comm.Parameters.AddWithValue("@idtipodoc", IdTipoDoc);
comm.Parameters.AddWithValue("@fecha", fecha);
comm.Parameters.AddWithValue("@comunidades", GeneralComunidades);
comm.Parameters.AddWithValue("@descripcion", Descripcion);
comm.Parameters.AddWithValue("@ruta", Ruta);
comm.ExecuteNonQuery();
conn.ClearAllPoolsAsync();
conn.Dispose();
conn.Close();
}
public static int InsertEntidad(String entidad, String nombreCorto, String CIF, String Notas, String entidad_sinAcentos, String Ruta)
{
MySqlConnection conn = Persistencia.conexion_bd.abrir();
conn.Open();
MySqlCommand comm = conn.CreateCommand();
comm.CommandText = "INSERT INTO ctos_entidades(Entidad,NombreCorto,CIF,Notas,EntidadSinAcentos,Ruta) VALUES(@entidad, @NombreCorto,@CIF,@Notas,@SinAcentos,@Ruta)";
comm.Parameters.AddWithValue("@entidad", entidad);
comm.Parameters.AddWithValue("@NombreCorto", nombreCorto);
comm.Parameters.AddWithValue("@CIF", CIF);
comm.Parameters.AddWithValue("@Notas", Notas);
comm.Parameters.AddWithValue("@SinAcentos", entidad_sinAcentos);
comm.Parameters.AddWithValue("@Ruta", Ruta);
comm.ExecuteNonQuery();
conn.ClearAllPoolsAsync();
conn.Dispose();
conn.Close();
return((int)comm.LastInsertedId);
}
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 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 static DataTable select(String sql)
{
DataTable dt = null;
try {
//Abro la conexión con la función que se encuentra en otra clase.
MySqlConnection conn = Persistencia.conexion_bd.abrir();
conn.Open();
MySqlDataAdapter da = new MySqlDataAdapter(selectCommandText: sql, connection: conn);
DataSet ds = new DataSet();
da.Fill(ds);
dt = ds.Tables[0];
conn.ClearAllPoolsAsync();
conn.Dispose();
Persistencia.conexion_bd.cerrar(conn);
return(dt);
}
catch (Exception ex) {
MessageBox.Show(ex.Message);
return(dt);
}
}
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 static void ClearPools()
{
con.Close();
con.ClearAllPoolsAsync();
con.CloseAsync();
}