/// <summary>
/// Checks when a client's characters were last cached, against a timestamp received from the client.
/// If the client's timestamp doesn't match the one in the DB (meaning it was older or newer), information
/// about all the characters is sent to the client.
/// </summary>
/// <param name="Timestamp">The timestamp received from the client.</param>
public static void CheckCharacterTimestamp(string AccountName, LoginClient Client, DateTime Timestamp)
{
MySqlCommand Command = new MySqlCommand("SELECT AccountName, NumCharacters, Character1, Character2, Character3 " +
"FROM Accounts");
Command.Connection = m_Connection;
EndCheckCharacterID(Command.BeginExecuteReader(System.Data.CommandBehavior.Default));
}
public void ExecuteReader()
{
if (Version < new Version(5, 0)) return;
execSQL("CREATE TABLE test (id int)");
execSQL("CREATE PROCEDURE spTest() BEGIN INSERT INTO test VALUES(1); " +
"SELECT SLEEP(2); SELECT 'done'; END");
MySqlCommand proc = new MySqlCommand("spTest", conn);
proc.CommandType = CommandType.StoredProcedure;
IAsyncResult iar = proc.BeginExecuteReader();
int count = 0;
while (!iar.IsCompleted)
{
count++;
System.Threading.Thread.Sleep(20);
}
using (MySqlDataReader reader = proc.EndExecuteReader(iar))
{
Assert.IsNotNull(reader);
Assert.IsTrue(count > 0, "count > 0");
Assert.IsTrue(reader.Read(), "can read");
Assert.IsTrue(reader.NextResult());
Assert.IsTrue(reader.Read());
Assert.AreEqual("done", reader.GetString(0));
reader.Close();
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM test";
object cnt = proc.ExecuteScalar();
Assert.AreEqual(1, cnt);
}
}
/// <summary>
/// Checks whether or not an account existed, and whether or not the password supplied was correct.
/// </summary>
/// <param name="AccountName">The name of the account.</param>
/// <param name="Client">The client that supplied the account.</param>
/// <param name="Hash">The hash of the password (with the username as a salt).</param>
public static void CheckAccount(string AccountName, LoginClient Client, byte[] Hash)
{
if (m_Connection == null)
{
if (GlobalSettings.Default.CreateAccountsOnLogin == false)
{
//TODO: Check if a flat file database exists, otherwise send an accountlogin failed packet.
}
else
{
//TODO: Write account into flat file DB if it doesn't exist.
}
}
//Gets the data from both rows (AccountName & Password)
MySqlCommand Command = new MySqlCommand("SELECT AccountName, Password FROM Accounts");
Command.Connection = m_Connection;
EndCheckAccount(Command.BeginExecuteReader(System.Data.CommandBehavior.Default));
}
public void ExecuteReaderAsyncTest() {
using(var conn = new MySqlConnection(DefaultConnectionString))
using(var cmd = new MySqlCommand(SQL_CUSTOMER_SELECT, conn)) {
conn.Open();
//! MySqlCommandAsync 를 참조하세요
//
var ar = cmd.BeginExecuteReader();
Thread.Sleep(1);
using(var reader = cmd.EndExecuteReader(ar)) {
var customers = reader.Map<Customer>(() => new Customer(), new TrimNameMapper());
customers.Count.Should().Be.GreaterThan(0);
customers.All(customer => customer.CompanyName.IsNotWhiteSpace()).Should().Be.True();
}
}
}
private void AsyncTest()
{
try
{
for (int i = 0; i < 100; i++)
{
ThreadPool.QueueUserWorkItem((obj) =>
{
Stopwatch watch = new Stopwatch();
watch.Start();
MySqlConnection con = new MySqlConnection(_connectinString);
MySqlCommand cmd = new MySqlCommand("select sleep(10);", con);
con.Open();
cmd.BeginExecuteReader(EndAsyncTest, cmd);
watch.Stop();
});
// Console.WriteLine("Begin:{0}ms", watch.ElapsedMilliseconds);
}
}
catch (Exception ex)
{
Console.WriteLine(ex);
}
}
/// <summary>
/// Callback mehod for CheckCharacterTimestamp.
/// This queries for the existence of a particular account
/// in the DB and retrieves the character IDs associated with it.
/// </summary>
private static void EndCheckCharacterID(IAsyncResult AR)
{
DatabaseAsyncObject AsyncObject = AR.AsyncState as DatabaseAsyncObject;
bool FoundAccountName = false;
int NumCharacters = 0;
int CharacterID1 = 0;
int CharacterID2 = 0;
int CharacterID3 = 0;
using (MySqlDataReader Reader = AsyncObject.MySQLCmd.EndExecuteReader(AR))
{
while (Reader.Read())
{
if (((string)Reader[0]).ToUpper() == AsyncObject.AccountName.ToUpper())
{
FoundAccountName = true;
NumCharacters = (int)Reader[1];
if (NumCharacters == 0)
break;
else if (NumCharacters == 1)
CharacterID1 = (int)Reader[2];
else if (NumCharacters == 2)
{
CharacterID1 = (int)Reader[2];
CharacterID2 = (int)Reader[3];
}
else if (NumCharacters == 3)
{
CharacterID1 = (int)Reader[2];
CharacterID2 = (int)Reader[3];
CharacterID3 = (int)Reader[4];
}
if (FoundAccountName == true)
break;
}
}
}
if (FoundAccountName)
{
if (NumCharacters > 0)
{
MySqlCommand Command = new MySqlCommand("SELECT CharacterID, LastCached, Name, Sex FROM Character");
AsyncObject.NumCharacters = NumCharacters;
AsyncObject.CharacterID1 = CharacterID1;
AsyncObject.CharacterID2 = CharacterID2;
AsyncObject.CharacterID3 = CharacterID3;
Command.Connection = m_Connection;
EndCheckCharacterTimestamp(Command.BeginExecuteReader(System.Data.CommandBehavior.Default));
}
else
{
PacketStream Packet = new PacketStream(0x05, 0);
Packet.WriteByte(0x00); //0 characters.
AsyncObject.Client.SendEncrypted(0x05, Packet.ToArray());
}
}
}
public bool Handle()
{
List<Dictionary<string, object>> list = null;
var nonQueryResult = 0;
var lastInsertRowId = 0L;
try
{
if (Connection == null) throw new Exception("Connection is null");
//if (_result == null)
//{
_connection = Connection.Con;
if (_connection.State == ConnectionState.Closed)
_connection.Open();
_cmd = _connection.CreateCommand();
_cmd.CommandText = Sql.SQL;
Sql.AddParams(_cmd, Sql.Arguments, "@");
_result = NonQuery ? _cmd.BeginExecuteNonQuery() : _cmd.BeginExecuteReader();
//}
_result.AsyncWaitHandle.WaitOne();
//if (!_result.IsCompleted) return false;
if (NonQuery)
nonQueryResult = _cmd.EndExecuteNonQuery(_result);
else
{
using (var reader = _cmd.EndExecuteReader(_result))
{
list = new List<Dictionary<string, object>>();
while (reader.Read())
{
var dict = new Dictionary<string, object>();
for (var i = 0; i < reader.FieldCount; i++)
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
list.Add(dict);
}
}
}
lastInsertRowId = _cmd.LastInsertedId;
Cleanup();
}
catch (Exception ex)
{
var message = "MySql handle raised an exception";
if (Connection?.Plugin != null) message += $" in '{Connection.Plugin.Name} v{Connection.Plugin.Version}' plugin";
Interface.Oxide.LogException(message, ex);
Cleanup();
}
Interface.Oxide.NextTick(() =>
{
Connection?.Plugin?.TrackStart();
try
{
if (Connection != null) Connection.LastInsertRowId = lastInsertRowId;
if (!NonQuery)
Callback(list);
else
CallbackNonQuery?.Invoke(nonQueryResult);
}
catch (Exception ex)
{
var message = "MySql command callback raised an exception";
if (Connection?.Plugin != null) message += $" in '{Connection.Plugin.Name} v{Connection.Plugin.Version}' plugin";
Interface.Oxide.LogException(message, ex);
}
Connection?.Plugin?.TrackEnd();
});
return true;
}
public void ThrowingExceptions()
{
MySqlCommand cmd = new MySqlCommand("SELECT xxx", conn);
IAsyncResult r = cmd.BeginExecuteReader();
try
{
MySqlDataReader reader = cmd.EndExecuteReader(r);
if (reader != null)
reader.Close();
Assert.Fail("EndExecuteReader should have thrown an exception");
}
catch (MySqlException)
{
}
}
private static ArrayList RunMySqlCommand(
string commandText,
string connectionString,
int no_of_fields)
{
ArrayList rows = new ArrayList();
using (MySqlConnection connection = new MySqlConnection(connectionString))
{
try
{
//Console.WriteLine("commandtext: " + commandText);
MySqlCommand command = new MySqlCommand(commandText, connection);
connection.Open();
IAsyncResult result = command.BeginExecuteReader();
int count = 0;
//DateTime start_time = DateTime.Now;
while (!result.IsCompleted)
{
count += 1;
//Console.WriteLine("Waiting ({0})", count);
System.Threading.Thread.Sleep(100);
//TimeSpan diff = DateTime.Now.Subtract(start_time);
//if (diff.TotalSeconds > 30) break;
}
MySqlDataReader query_result = command.EndExecuteReader(result);
while (query_result.Read())
{
ArrayList row = new ArrayList();
for (int i = 0; i < no_of_fields; i++)
{
row.Add(query_result.GetValue(i));
}
rows.Add(row);
}
connection.Close();
}
catch (MySqlException ex)
{
Console.WriteLine("Error ({0}): {1}", ex.Number, ex.Message);
connection.Close();
}
catch (InvalidOperationException ex)
{
Console.WriteLine("Error: {0}", ex.Message);
connection.Close();
}
catch (Exception ex)
{
Console.WriteLine("Error: {0}", ex.Message);
connection.Close();
}
}
return(rows);
}
IEnumerator SelectData()
{
// SQL�R�}���h��쐬
string conCmd =
"server=" + SERVER + ";" +
"database=" + DATABASE + ";" +
"userid=" + USERID + ";" +
// "port=" + PORT + ";" +
"password="******"SELECT * FROM TABLENAME LIMIT 0, 1200;";
MySqlCommand cmd = new MySqlCommand(selCmd, con);
// ��������J�n
IAsyncResult iAsync = cmd.BeginExecuteReader();
// ���ɂ��S�f�[�^�擾�����܂ő҂����킹��
while (!iAsync.IsCompleted)
{
yield return 0;
}
// �ꉞ�F
// ��L��while�Ŋ�����҂ȊO�ɁABegin�ɂ����J�n���ɃI�[�o�[���[�h��
// cmd.BeginExecuteReader(new AsyncCallback(AsyncCallbackMethod), cmd);
// �Ƃ��Ĕ�������������R�[���o�b�N���\�b�h��ĂсA���̒��Ŋ���EndExcuteReader
// ��ĂԎ���ł���B
// �������́A���������I������ۂɃR�[���o�b�N����郁�\�b�h�B
// ��������IAsyncResult.AsyncState�I�u�W�F�N�g�Ƃ��Ď擾�ł���I�u�W�F�N�g�B
// �ŁA�R�[���o�b�N���\�b�h��void AsyncCallbackMethod(IAsyncResult result)
// result�ɂ͑������Ŏw�肵��cmd������B
// ���������������
MySqlDataReader rdr = cmd.EndExecuteReader(iAsync);
// �擾�����f�[�^����ID�Ă݂�
while (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ID")))
{
Debug.Log ( "ID : " + rdr.GetString ("ID") );
}
}
// �S���\�[�X��N���[�Y����щ������
rdr.Close();
rdr.Dispose();
con.Close();
con.Dispose();
}
