/// <summary>
/// Gets the next id for use in creating a new account.
/// </summary>
/// <returns></returns>
private async Task <int> GetNextId()
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
connection.Open();
MySqlCommand command = new MySqlCommand("SELECT max(id) as rownum FROM auth.battlenet_accounts;", connection);
return(Convert.ToInt32(await command.ExecuteScalarAsync()) + 1);
}
}
public override async Task <string> GetLastKeyEntry()
{
using (var cmd = new MySqlCommand("SELECT `Key` FROM Hashes ORDER BY 1 DESC LIMIT 1", Conn, Tran))
{
cmd.CommandType = System.Data.CommandType.Text;
var str = await cmd.ExecuteScalarAsync();
return(str == null ? null : (string)str);
}
}
public static async Task <object> ExecuteScalarAsync(string connectionString, string sql)
{
using (var conn = new MySqlConnection(connectionString))
using (var cmd = new MySqlCommand(sql, conn))
{
await conn.OpenAsync();
return(await cmd.ExecuteScalarAsync());
}
}
public static async Task <object> ExecuteScalarAsync(IDbTransaction transaction, string sql)
{
var trans = transaction as MySqlTransaction;
var connection = trans.Connection;
using (MySqlCommand command = new MySqlCommand(sql, connection, trans))
{
return(await command.ExecuteScalarAsync());
}
}
public async Task <long> GetLocalTimestampAsync(DbConnection connection, DbTransaction transaction)
{
var commandText = $"Select {MySqlObjectNames.TimestampValue}";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
long result = Convert.ToInt64(await command.ExecuteScalarAsync().ConfigureAwait(false));
return(result);
}
}
public async Task <bool> NeedToCreateServerScopeInfoTableAsync(DbConnection connection, DbTransaction transaction)
{
var tableName = $"{scopeTableName.Unquoted().Normalized().ToString()}_server";
var commandText = $"select count(*) from information_schema.TABLES where TABLE_NAME = '{tableName}' and TABLE_SCHEMA = schema() and TABLE_TYPE = 'BASE TABLE'";
using (var command = new MySqlCommand(commandText, (MySqlConnection)connection, (MySqlTransaction)transaction))
{
return(((long)await command.ExecuteScalarAsync()) != 1);
}
}
public override async Task <object> ExecuteCommandScalarAsync(string storeProcedureName, IDictionary <string, object> parameters)
{
try
{
if (_connection.State != ConnectionState.Open)
{
_connection.ConnectionString = _connectionString;
await _connection.OpenAsync();
}
_command = _connection.CreateCommand();
if (Transaction)
{
if (_transaction == null)
{
_transaction = _connection.BeginTransaction();
}
_command.Transaction = _transaction;
}
_command.CommandText = storeProcedureName;
_command.CommandType = CommandType.StoredProcedure;
foreach (var param in parameters)
{
var parameter = _command.Parameters.AddWithValue(param.Key, param.Value);
try
{
var parameterObject = (DataAccessParameter)param.Value;
parameter.Value = parameterObject.ParameterValue;
parameter.Direction = parameterObject.ParameterDirection;
}
catch { parameter.Direction = ParameterDirection.Input; }
}
var result = await _command.ExecuteScalarAsync();
CommandParameters = _command.Parameters.GetParameterValuePairs();
return(result);
}
catch (Exception e)
{
_log.Error(e.Message, e);
return(null);
}
finally
{
Dispose();
}
}
protected override async Task <object> ReaderAsync(MySqlCommand command)
{
var result = await command.ExecuteScalarAsync();
if (result != null && result.GetType() == typeof(DBNull))
{
result = null;
}
return(result);
}
public static async Task <object> ExecuteScalarAsync(string connectionString, string sql)
{
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
await connection.OpenAsync();
using (MySqlCommand command = new MySqlCommand(sql, connection))
{
return(await command.ExecuteScalarAsync());
}
}
}
public async Task SimpleTest()
{
string query = "SELECT 1;";
await _connection.OpenAsync();
var cmd = new MySqlCommand(query, _connection);
var reader = (await cmd.ExecuteScalarAsync());
Assert.Equal((long)1, reader);
await _connection.CloseAsync();
}
public static async Task <bool> CheckHasPlayedSet(SdlPlayer player)
{
MySqlCommand selectCommand =
new MySqlCommand(
$"SELECT COUNT(*) " +
$"FROM `Draft Log` " +
$"WHERE `Alpha Players` LIKE '%{player.DiscordId}%' " +
$"OR `Bravo Players` LIKE '%{player.DiscordId}%'",
mySqlConnection);
return(Convert.ToInt32(await selectCommand.ExecuteScalarAsync()) > 0);
}
private async void button1_Click_1(object sender, EventArgs e)
{
String subject = comboSubjectsField.Text;
int group = int.Parse(selectedGroup.Text);
String student = selectedStudent.Text;
int mark = int.Parse(selectedMark.Text);
MySqlCommand command = new MySqlCommand($"SELECT `studentMarks` FROM `students` WHERE `name` = '{student}'", mysql.GetConnection()); // выбираем оценки пользователя
var exist = Convert.ToString(await command.ExecuteScalarAsync());
if (exist.ToString() != string.Empty) // если строка не пустая, выполняем условие
{
var jsonData = JsonConvert.DeserializeObject <List <SubjectModel> >(exist); // десериализуем список оценок
if (jsonData != null)
{
var findExsistSubjects = jsonData.Any(x => x.Subject == subject); // смотрим, стоит ли оценка по предмету. Если да - изменяем, нет - добавляем
var findIndex = jsonData.FindIndex(x => x.Subject == subject);
if (findExsistSubjects)
{
jsonData.RemoveAt(findIndex);
MessageBox.Show("Оцінка змінена");
jsonData.Add(new SubjectModel {
Subject = subject, Mark = mark
});
}
else
{
jsonData.Add(new SubjectModel {
Subject = subject, Mark = mark
});
}
}
else
{
jsonData.Add(new SubjectModel {
Subject = subject, Mark = mark
});
}
command = new MySqlCommand($"UPDATE students SET studentMarks = '{JsonConvert.SerializeObject(jsonData)}' WHERE name = '{student}'", mysql.GetConnection()); // обновляем список оценок
command.ExecuteNonQuery();
}
else
{
var list = new List <SubjectModel>();
list.Add(new SubjectModel {
Subject = subject, Mark = mark
});
command = new MySqlCommand($"UPDATE students SET studentMarks = '{JsonConvert.SerializeObject(list)}' WHERE name = '{student}'", mysql.GetConnection()); // обновляем список оценок
command.ExecuteNonQuery();
}
UpdateDataGrid(); // обновляем данные таблицы
}
public static async Task <int> SqlSecondConnectionGetResultFromMySql(string connectionString, string command)
{
long result;
using (var connection = new MySqlConnection(connectionString))
{
connection.Open();
var cmd = new MySqlCommand(command, connection);
result = (long)await cmd.ExecuteScalarAsync();
}
return((int)result);
}
public async Task Execute(string sql, MySqlConnection connection = null)
{
connection = connection ?? new MySqlConnection(_connString);
using (connection)
{
await connection.OpenAsync();
using var command = new MySqlCommand(sql, connection);
await command.ExecuteScalarAsync();
await connection.CloseAsync();
}
}
public async Task AddToRoleAsync(ApplicationUser user, string roleName, CancellationToken cancellationToken)
{
using var conn = new MySqlConnection(_connectionWriterString);
await conn.OpenAsync(cancellationToken);
using var comm2 = new MySqlCommand("select Id from AspNetRoles where Name=@rolename or NormalizedName=@rolename;", conn);
comm2.Parameters.Add(new MySqlParameter("@rolename", MySqlDbType.VarChar, 256)
{
Value = roleName
});
string roleId = (await comm2.ExecuteScalarAsync(cancellationToken)).ToString();
using var comm1 = new MySqlCommand("select count(*) from AspNetUserRoles where UserId=@userid and RoleId=@roleid;", conn);
comm1.Parameters.AddRange(new MySqlParameter[]
{
new MySqlParameter("@userid", MySqlDbType.VarChar, 256)
{
Value = user.Id
},
new MySqlParameter("@roleid", MySqlDbType.VarChar, 256)
{
Value = roleId
}
});
int userrole = int.Parse((await comm1.ExecuteScalarAsync(cancellationToken)).ToString());
if (userrole == 0)
{
using var comm3 = new MySqlCommand("INSERT INTO AspNetUserRoles (UserId,RoleId) values(@userid, @roleid)", conn);
comm3.Parameters.AddRange(new MySqlParameter[]
{
new MySqlParameter("@userid", MySqlDbType.VarChar, 256)
{
Value = user.Id
},
new MySqlParameter("@roleid", MySqlDbType.VarChar, 256)
{
Value = roleId
}
});
int values = await comm3.ExecuteNonQueryAsync(cancellationToken);
if (values != 1)
{
throw new ArgumentException("Userid or Roleid is wrong");
}
}
}
/// <summary>
/// 创建管理数据库
/// </summary>
public async static void CreateManagementDb()
{
var masterConn = M.ConnManager.GetMasterConnString();
if (string.IsNullOrEmpty(masterConn))
{
LoggerHelper.Error(new Exception("无法获取数据库连接字符串，请设置数据库连接信息！"));
Environment.Exit(0);
}
var manageDbName = M.ConnManager.GetManagementDbName();
LoggerHelper.Info("正在检测管理数据库是否存在...");
//检测管理数据库是否存在
MySqlConnection conn = null;
try
{
conn = new MySqlConnection(masterConn);
var cmd = new MySqlCommand($"select * from SCHEMATA where SCHEMA_NAME = '{manageDbName}'", conn);
await conn.OpenAsync();
var n = await cmd.ExecuteScalarAsync();
if (n != null)
{
LoggerHelper.Info("成功获取管理数据库！");
return;
}
}
catch (Exception e)
{
LoggerHelper.Error("无法连接数到据库服务器,请重新设置连接字符串", e);
Environment.Exit(0);
}
finally
{
if (conn != null)
{
conn.Close();
}
}
LoggerHelper.Info("正在创建管理数据库...");
//创建数据库
var result = await CreateMgntDatabaseAsync(manageDbName);
if (result.ResultType == ResultType.Error)
{
LoggerHelper.Error("创建管理数据库失败！", result.Error);
Environment.Exit(0);
}
}
private Task <bool> ExistsAsync(bool retryOnNotExists, CancellationToken cancellationToken)
=> Dependencies.ExecutionStrategyFactory.Create().ExecuteAsync(
DateTime.UtcNow + RetryTimeout, async(giveUp, ct) =>
{
while (true)
{
var opened = false;
try
{
MySqlConnectionStringBuilder settings = new MySqlConnectionStringBuilder(_connection.ConnectionString);
string database = settings.Database;
if (string.IsNullOrWhiteSpace(database))
{
throw new ArgumentNullException("Database");
}
settings.Database = string.Empty;
using (MySqlConnection conn = new MySqlConnection(settings.ToString()))
{
await conn.OpenAsync(cancellationToken);
MySqlCommand cmd = conn.CreateCommand();
cmd.CommandText = $"SHOW DATABASES LIKE '{database}'";
var result = await cmd.ExecuteScalarAsync(cancellationToken);
if (result == null)
{
return(false);
}
else
{
return(((string)result).Equals(database, StringComparison.OrdinalIgnoreCase));
}
}
}
catch (MySqlException e)
{
if (!retryOnNotExists &&
IsDoesNotExist(e))
{
return(false);
}
if (DateTime.UtcNow > giveUp ||
!RetryOnExistsFailure(e))
{
throw;
}
await Task.Delay(RetryDelay, ct);
}
}
}, cancellationToken);
private async Task <bool> DatabaseExists(string name)
{
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync();
}
await using var cmd = new MySqlCommand(
$"SELECT 1 FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME = '{name}';",
_connection);
var result = await cmd.ExecuteScalarAsync();
return(result != null);
}
private async Task <bool> UserHasAccess(string name, string database)
{
if (_connection.State != ConnectionState.Open)
{
await _connection.OpenAsync();
}
await using var cmd = new MySqlCommand(
$"SELECT 1 FROM mysql.db WHERE user = '******' AND db = '{database}';",
_connection);
var result = await cmd.ExecuteScalarAsync();
return(result != null);
}
public async Task <object> ExecuteScalar(string sqltext)
{
try
{
using (MySqlConnection conn = DBContext() as MySqlConnection)
{
MySqlCommand comm = new MySqlCommand(sqltext, conn);
return(await comm.ExecuteScalarAsync());
}
}
catch (Exception ex)
{
return(null);
}
}
private async void button5_Click(object sender, EventArgs e)
{
String subject = subjectField.Text;
MySqlCommand command = new MySqlCommand($"SELECT `subject` FROM `subjects` WHERE `subject` = '{subject}'", mysql.GetConnection());
var exist = Convert.ToString(await command.ExecuteScalarAsync()); // выполняем запрос
if (exist != string.Empty)
{
command.CommandText = $"DELETE FROM `subjects` WHERE `subject` = '{subject}'";
command.ExecuteNonQuery();
MessageBox.Show("Предмет видалений");
UpdateDataGrid();
}
}
private async Task <ulong> GetAutoIncrementValue(string table)
{
string databaseName = Connection.Database;
const string commandText =
"SELECT AUTO_INCREMENT " +
"FROM INFORMATION_SCHEMA.TABLES " +
"WHERE TABLE_SCHEMA = @databasename AND TABLE_NAME = @tablename";
await using var createTableCommand = new MySqlCommand(commandText, Connection)
.SetParam("@databasename", databaseName)
.SetParam("@tablename", table);
return(Convert.ToUInt64(await createTableCommand.ExecuteScalarAsync()));
}
public async Task BulkCopyAbort(int notifyAfter, int rowCount, int abortAfter, int expectedEventCount, int expectedRowsCopied, long expectedCount)
{
using var connection = new MySqlConnection(GetLocalConnectionString());
await connection.OpenAsync();
using (var cmd = new MySqlCommand(@"drop table if exists bulk_copy_abort;
create table bulk_copy_abort(value longtext);" , connection))
{
await cmd.ExecuteNonQueryAsync();
}
var bulkCopy = new MySqlBulkCopy(connection)
{
NotifyAfter = notifyAfter,
DestinationTableName = "bulk_copy_abort",
};
int eventCount = 0;
long rowsCopied = 0;
bulkCopy.MySqlRowsCopied += (s, e) =>
{
eventCount++;
rowsCopied = e.RowsCopied;
if (e.RowsCopied >= abortAfter)
{
e.Abort = true;
}
};
var dataTable = new DataTable()
{
Columns = { new DataColumn("value", typeof(string)) },
};
var str = new string('a', 62500);
foreach (var x in Enumerable.Range(1, rowCount))
{
dataTable.Rows.Add(new object[] { str });
}
await bulkCopy.WriteToServerAsync(dataTable);
Assert.Equal(expectedEventCount, eventCount);
Assert.Equal(expectedRowsCopied, rowsCopied);
using (var cmd = new MySqlCommand("select count(value) from bulk_copy_abort;", connection))
Assert.Equal(expectedCount, await cmd.ExecuteScalarAsync());
}
public async Task ExecuteScalarAsync()
{
executeSQL("CREATE PROCEDURE CMDScalarAsyncSpTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) BEGIN SET valout=valin; SELECT 'Test'; END");
MySqlCommand cmd = new MySqlCommand("CMDScalarAsyncSpTest", Connection);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?valin", "valuein");
cmd.Parameters.Add(new MySqlParameter("?valout", MySqlDbType.VarChar));
cmd.Parameters[1].Direction = ParameterDirection.Output;
object result = await cmd.ExecuteScalarAsync();
Assert.Equal("Test", result);
Assert.Equal("valuein", cmd.Parameters[1].Value);
}
public async Task <long?> GetGlobalCheckpoint(CancellationToken cancellationToken = default)
{
_logger.LogTrace(new EventId(3, "Get Checkpoint"),
$"Getting Checkpoint with Id '{Constants.GlobalCheckpointId}'");
await using var connection = _createConnection();
await connection.OpenAsync(cancellationToken).ConfigureAwait(false);
await using var command = new MySqlCommand(_scripts.GetCheckpoint, connection);
command.Parameters.Add(new MySqlParameter("@CheckpointId", MySqlDbType.String, 500)
{
Value = Constants.GlobalCheckpointId
});
return(await command.ExecuteScalarAsync(cancellationToken).ConfigureAwait(false) as long?);
}
/// <summary>
/// Pega informaçoes de um determinado plugin e guarda na propriedade <see cref="DataTable"/>.
/// </summary>
/// <param name="itemProcurar">Index (ID ou Nome do plugin) do plugin que deseja as informaçoes.</param>
/// <param name="messageReturn">true, se deseja informar informar a mensagem no canto da tela mostrando a quantidade de resultados encontrados.</param>
/// <returns>Retorna o resultado da tarefa em bool.</returns>
public async Task <bool> InformacoesAsync(string itemProcurar, bool messageReturn)
{
using (MySqlConnection con = new MySqlConnection(await PegarConexaoMySQL.ConexaoAsync()))
{
try
{
await con.OpenAsync();
using (MySqlCommand get = new MySqlCommand(CmdText, con))
{
_ = get.Parameters.Add(new MySqlParameter("@a", "%" + itemProcurar + "%"));
using (MySqlDataAdapter mySqlData = new MySqlDataAdapter())
{
mySqlData.SelectCommand = get;
DataTable dataTable = new DataTable();
_ = await mySqlData.FillAsync(dataTable);
DataTable = dataTable;
NomeColunas();
using (MySqlCommand num = new MySqlCommand(CmdText1, con))
{
_ = num.Parameters.Add(new MySqlParameter("@a", "%" + itemProcurar + "%"));
string qtd = (await num.ExecuteScalarAsync()).ToString();
if (qtd == "0")
{
return(false);
}
if (messageReturn)
{
MetodosConstantes.EnviarMenssagem(@var.Parse(qtd) + " Plugins encontrados!");
}
}
return(true);
}
}
}
catch (Exception e)
{
MetodosConstantes.MostrarExceptions(e);
return(false);
}
}
}
private async Task <Product> AddProduct(Product p)
{
// Connection
MySqlConnection con = new MySqlConnection(MyConst.dbconn);
con.Open();
// Command
MySqlCommand cmd = new MySqlCommand();
cmd.CommandText = @"INSERT INTO products
(ProductName, ProductPrice, ProductDescription, ProductShippingOunces, ProductTypeID)
VALUES (@ProductName, @ProductPrice, @ProductDescription, @ProductShippingOunces, @ProductTypeID);
SELECT LAST_INSERT_ID();";
cmd.Connection = con;
cmd.Parameters.Add("@ProductName", MySqlDbType.VarChar);
cmd.Parameters["@ProductName"].Value = p.ProductName;
cmd.Parameters.Add("@ProductPrice", MySqlDbType.Decimal);
cmd.Parameters["@ProductPrice"].Value = p.ProductPrice;
cmd.Parameters.Add("@ProductDescription", MySqlDbType.VarChar);
cmd.Parameters["@ProductDescription"].Value = p.ProductDescription;
cmd.Parameters.Add("@ProductShippingOunces", MySqlDbType.Int32);
cmd.Parameters["@ProductShippingOunces"].Value = p.ProductShippingOunces;
cmd.Parameters.Add("@ProductTypeID", MySqlDbType.Int32);
cmd.Parameters["@ProductTypeID"].Value = p.ProductTypeName;
// insert product by run the SQL.
Object idresult = await cmd.ExecuteScalarAsync();
// Close Connection
con.Close();
int id;
if (idresult != null)
{
id = Convert.ToInt32(idresult);
// Get the last insert product.
var result = await GetProduct(id);
return(result);
}
return(null);
}
public async Task <object> ExecuteScalar(MySqlCommand stmt)
{
object result = null;
if (connState == ConnectionState.Open)
{
try
{
result = await stmt.ExecuteScalarAsync();
}
catch (MySqlException e)
{
Logger.LOG_DATABASE.Error("Could not execute prepared statement " + stmt.ToString() + " as scalar. " + e.Message);
}
}
return(result);
}
public async Task <string> CreateAsync(Slot slot)
{
System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
using (MySqlConnection connection = new MySqlConnection(_appSettings.ConnectionString))
{
await connection.OpenAsync();
using (MySqlCommand command = new MySqlCommand())
{
command.Connection = connection;
command.CommandType = CommandType.StoredProcedure;
command.CommandText = Routines.CreateSlot;
AddSlotParams(command, slot);
return(Convert.ToString(await command.ExecuteScalarAsync()));
}
}
}
public async Task <int> UpdateMood(int moodID, Mood mood)
{
MySqlConnection connection = GetConnection();
await connection.OpenAsync();
MySqlCommand myCommand = new MySqlCommand("UPDATE Mood SET name = @name WHERE moodID = @moodID", connection);
myCommand.Parameters.AddWithValue("@name", mood.Name);
myCommand.Parameters.AddWithValue("@moodID", moodID);
await myCommand.ExecuteScalarAsync();
await connection.CloseAsync();
return(moodID);
}
public async Task ExecuteScalarAsync()
{
if (st.Version < new Version(5, 0)) return;
if (st.conn.connectionState != ConnectionState.Open)
st.conn.Open();
st.execSQL("CREATE PROCEDURE CMDScalarAsyncSpTest( IN valin VARCHAR(50), OUT valout VARCHAR(50) ) BEGIN SET valout=valin; SELECT 'Test'; END");
MySqlCommand cmd = new MySqlCommand("CMDScalarAsyncSpTest", st.conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("?valin", "valuein");
cmd.Parameters.Add(new MySqlParameter("?valout", MySqlDbType.VarChar));
cmd.Parameters[1].Direction = ParameterDirection.Output;
object result = await cmd.ExecuteScalarAsync();
Assert.Equal("Test", result);
Assert.Equal("valuein", cmd.Parameters[1].Value);
}
