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);
}
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);
}
}
private IEnumerator DatabaseContains(string word, WVObject <bool?> result)
{
Debug.Log("Checking if db contains word " + word);
using (MySqlCommand command = _connection.CreateCommand())
{
command.CommandText = "SELECT " + TABLE_WORD + " FROM " + TABLE_NAME +
" WHERE " + TABLE_WORD + " = " + "'" + word + "'";
System.IAsyncResult asyncResult = command.BeginExecuteReader();
while (!asyncResult.IsCompleted)
{
yield return(null);
}
//Debug.Log(asyncResult.AsyncState);
//Debug.Log(asyncResult.IsCompleted);
try
{
MySqlDataReader data;
using (data = command.EndExecuteReader(asyncResult))
{
result.value = data.Read();
Debug.Log("DatabaseContains word '" + word + "': " + result.value.ToString());
//if (!data.IsClosed) data.Close();
}
}
catch (System.InvalidOperationException e)
{
Debug.LogError(e.StackTrace);
Debug.LogError(e.Message);
Debug.Log(Connection.State.ToString());
}
}
}
/**** FUNÇÕES PARA GERIR OS TIPOS DE WEBSITES *****/
public ArrayList LIST_WEBSITE_TYPES()
{
MySqlCommand CMD_GET_WEBSITE_TYPES = new MySqlCommand("site_tvtuga_app_admin_users._SP_SELECT_WebsiteTipo", LigacaoDB);
CMD_GET_WEBSITE_TYPES.CommandType = CommandType.StoredProcedure;
LigacaoDB.Open();
CMD_GET_WEBSITE_TYPES.Prepare();
IAsyncResult Working_ListType = CMD_GET_WEBSITE_TYPES.BeginExecuteReader();
Reader = CMD_GET_WEBSITE_TYPES.EndExecuteReader(Working_ListType);
while (Reader.Read())
{
Tipos.Add(Reader["nome"].ToString());
}
Reader.Close();
LigacaoDB.Close();
return(Tipos);
}
private void GET_TYPE_ID(string Tipo)
{
MySqlCommand CMD_GET_TYPE_ID = new MySqlCommand("site_tvtuga_app_admin_users._SP_SELECT_X_WebsiteTipo", LigacaoDB);
CMD_GET_TYPE_ID.CommandType = CommandType.StoredProcedure;
CMD_GET_TYPE_ID.Parameters.AddWithValue("_nome", Tipo);
LigacaoDB.Open();
CMD_GET_TYPE_ID.Prepare();
IAsyncResult Working_GetTypeID = CMD_GET_TYPE_ID.BeginExecuteReader();
Reader = CMD_GET_TYPE_ID.EndExecuteReader(Working_GetTypeID);
while (Reader.Read())
{
//Tipo = Reader.GetString(0);
Tipo = Reader["id"].ToString();
}
Reader.Close();
TYPE_ID = Convert.ToInt32(Tipo);
}
public void SelectBehaviorProcedures(string recipeID, int expertID)
{
MySqlConnection mysqlConnection = null;
try
{
this.behaviorProcedures.Clear();
string connectString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password ="******";";
mysqlConnection = new MySqlConnection(connectString);
mysqlConnection.Open();
string columnString = "step, procedure_number, grasped_objects, colliding_objects, behavior_type";
string selectSql = String.Format("SELECT {0} FROM {1}.{2} WHERE expert_id={3} AND recipe_id='{4}' ORDER BY step, procedure_number",
columnString, MySqlSchemaName, MySqlTableName_behaviorProcedures, expertID.ToString(), recipeID);
MySqlCommand mysqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mysqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mysqlDataReader = mysqlCommand.EndExecuteReader(iAsync);
while (mysqlDataReader.Read())
{
string procedureStr = mysqlDataReader.GetString(0);
for (int i = 1; i < 5; i++)
{
procedureStr += "\t" + mysqlDataReader.GetString(i);
}
this.behaviorProcedures.Add(procedureStr);
}
Debug.Log("Got behavior procedure");
mysqlDataReader.Close();
mysqlCommand.Dispose();
mysqlConnection.Close();
}
catch (Exception ex)
{
Debug.Log(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}
public async Task PollTest()
{
MySqlConnection connection;
MySqlCommand command = new MySqlCommand();
MySqlDataReader reader;
IAsyncResult asyncResult;
Console.WriteLine("程序已启动……");
connection = new MySqlConnection("Server=localhost; Database=Rainbow_Test_DB20180817; Uid=SkyUser; Pwd=Sky@4321;SslMode=none;");
command.CommandText = "select * from agent ;";
command.CommandType = CommandType.Text;
command.Connection = connection;
connection.Open();
var start = DateTime.Now;
asyncResult = command.BeginExecuteReader(CommandBehavior.CloseConnection);
while (!asyncResult.IsCompleted)
{
Console.WriteLine("正在取资料……");
Thread.Sleep(10);
}
reader = command.EndExecuteReader(asyncResult);
var count = 0;
while (await reader.ReadAsync())
{
count++;
}
var end = DateTime.Now;
var timeSpan = end.Subtract(start);
Console.WriteLine($"资料取出完成:{count.ToString()},总其用时{timeSpan.Milliseconds.ToString()}毫秒");
}
/// <summary>
/// <paramref name="cmd"/> 를 비동기 방식으로 실행하여, <see cref="Task{MySqlDataReader}"/>를 반환받습니다.
/// 받환받은 DataReader는 꼭 Dispose() 해 주어야 Connection이 닫힙니다.
/// </summary>
/// <param name="db">DAAB의 MySQL 용 Database</param>
/// <param name="cmd">실행할 <see cref="MySqlCommand"/> 인스턴스</param>
/// <param name="parameters">Command Parameters</param>
/// <returns><see cref="MySqlDataReader"/>를 결과로 반환하는 <see cref="Task"/></returns>
public static Task <MySqlDataReader> ExecuteReaderAsync(this MySqlDatabase db, MySqlCommand cmd,
params IAdoParameter[] parameters)
{
cmd.ShouldNotBeNull("cmd");
if (IsDebugEnabled)
{
log.Debug("MySqlCommand.ExecuteReader를 비동기 방식으로 실행합니다. CommandText=[{0}], Parameters=[{1}]",
cmd.CommandText, parameters.CollectionToString());
}
var newConnectionCreated = false;
if (cmd.Connection == null)
{
cmd.Connection = db.CreateMySqlConnection(ref newConnectionCreated);
}
if (parameters != null)
{
AdoTool.SetParameterValues(db, cmd, parameters);
}
var commandBehavior = newConnectionCreated ? CommandBehavior.CloseConnection : CommandBehavior.Default;
//! NOTE: FromAsync를 사용하지 못한 이유가 SqlCommand.BeginExecuteReader() 의 overloading이 많아서, 모호한 함수 호출 때문이다.
//
var ar = cmd.BeginExecuteReader(commandBehavior);
return
(Task <MySqlDataReader> .Factory
.StartNew(state => cmd.EndExecuteReader((IAsyncResult)state),
ar,
TaskCreationOptions.PreferFairness));
}
public void ThrowingExceptions()
{
MySqlCommand cmd = new MySqlCommand("SELECT xxx", Connection);
IAsyncResult r = cmd.BeginExecuteReader();
Exception ex = Assert.Throws <MySqlException>(() => cmd.EndExecuteReader(r));
Assert.Equal("Unknown column 'xxx' in 'field list'", ex.Message);
}
public async Task <string> SelectData()
{
return(await Task.Run <string>(() => {
// 三秒間だけ待ってやる.
Thread.Sleep(3000);
const string result = "データー取得しました\n";
MySqlConnection con = null;
string conCmd =
"Server=" + SERVER + ";" +
"Database=" + DATABASE + ";" +
"Userid=" + USERID + ";" +
"Password="******";" +
"Port=" + PORT + ";" +
"CharSet=utf8;";
try
{
Debug.Log("cmd : " + conCmd);
con = new MySqlConnection(conCmd);
Debug.Log("connection : " + con.State);
con.Open();
}
catch (MySqlException ex)
{
Debug.Log(ex.ToString());
}
string selCmd = "SELECT * FROM " + TABLENAME + " LIMIT 0, 1200;";
MySqlCommand cmd = new MySqlCommand(selCmd, con);
IAsyncResult iAsync = cmd.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
}
MySqlDataReader rdr = cmd.EndExecuteReader(iAsync);
while (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("card_number")))
{
Debug.Log("card number : " + rdr.GetString("card_number"));
}
}
rdr.Close();
rdr.Dispose();
con.Close();
con.Dispose();
return result;
}));
}
/// <summary>
/// Gets a word and EmotionIdeal associated with it. This assumes that
/// the database contains the word!
/// </summary>
/// <param name="word">Word to search for</param>
/// <returns>WordAndEmoIdeal -- struct containing string and emotion ideal</returns>
private IEnumerator GetWord(string word, WVObject <WordAndEmoIdeal> result)
{
Debug.Log("Getting word " + word + " from db");
//WVObject<WordAndEmoIdeal> result = new WVObject<WordAndEmoIdeal>(word);
WVObjectList_WordAndEmoIdeal.Add(result);
WordAndEmoIdeal wordEmoIdeal = new WordAndEmoIdeal();
using (MySqlCommand command = _connection.CreateCommand())
{
command.CommandText = "SELECT " + TABLE_WORD + ", " + TABLE_EMO_IDEAL + " FROM " + TABLE_NAME +
" WHERE " + TABLE_WORD + " = " + "'" + word + "'";
System.IAsyncResult asyncResult = command.BeginExecuteReader(System.Data.CommandBehavior.CloseConnection);
Debug.Log("Connection before command: " + Connection.State.ToString());
while (!asyncResult.IsCompleted)
{
yield return(null);
}
try
{
MySqlDataReader data;
using (data = command.EndExecuteReader(asyncResult))
{
//Debug.LogWarning("before data.Read()");
while (data.Read())
{
string w = (string)data[TABLE_WORD];
EmotionModel.EmotionIdeal emoIdeal = (EmotionModel.EmotionIdeal)
System.Enum.Parse(typeof(EmotionModel.EmotionIdeal),
data[TABLE_EMO_IDEAL].ToString());
wordEmoIdeal.word = w;
wordEmoIdeal.emoEnum = emoIdeal;
result.value = wordEmoIdeal;//at this point, since it's not null, we set it!
//Debug.LogWarning("reading data");
}
Debug.LogWarning("Data.isClosed = " + data.IsClosed.ToString());
//if (!data.IsClosed) data.Close();
}
//Debug.LogWarning("Data.isClosed AFTER using: " + data.IsClosed.ToString());
//Debug.Log("Connection after everything: " + Connection.State.ToString());
}
catch (System.InvalidOperationException e)
{
Debug.LogError(e.StackTrace);
Debug.LogError(e.Message);
Debug.Log(Connection.State);
}
}
}
private void SelectMotionsFromMySQL()
{
MySqlConnection mysqlConn = null;
try
{
string connString =
"server =" + this.mysqlIpInputField.text + ";" +
"port =" + this.mysqlPortInputField.text + ";" +
"database=" + MysqlSchemaName + ";" +
"userid =" + this.mysqlUserInputField.text + ";" +
"password="******"SELECT * FROM " + MysqlSchemaName + "." + MysqlTableName + " WHERE recording_id=" + uniqueIdInputField.text;
MySqlCommand mysqlCommand = new MySqlCommand(selectSql, mysqlConn);
IAsyncResult iAsync = mysqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mysqlDataReader = mysqlCommand.EndExecuteReader(iAsync);
List <string> motionsDataList = new List <string>();
while (mysqlDataReader.Read())
{
motionsDataList.Add(mysqlDataReader.GetString("motion_data"));
}
mysqlDataReader.Close();
mysqlCommand.Dispose();
mysqlConn.Close();
this.CreatePlayingTransformList(motionsDataList);
this.isReading = false;
}
catch (Exception ex)
{
SIGVerseLogger.Error(ex.Message);
SIGVerseLogger.Error(ex.StackTrace);
if (mysqlConn != null)
{
mysqlConn.Close();
}
Application.Quit();
}
}
public static string checkPaid()
{
bool paid = false;
JavaScriptSerializer serializer = new JavaScriptSerializer();
string UserInfo = ConfigurationManager.ConnectionStrings["KcGameOnSQL"].ConnectionString;
MySqlDataReader paidReader = null;
MySqlCommand mySQLCmd = null;
try
{
mySQLCmd = new MySqlCommand("SELECT paymentKey,verifiedPaid FROM payTable WHERE paidDate = (SELECT MAX(paidDate) FROM payTable where userName = \'" + SessionVariables.UserName.ToLower() + "\' AND ActiveIndicator = \'TRUE\')", new MySqlConnection(UserInfo));
mySQLCmd.CommandType = System.Data.CommandType.Text;
mySQLCmd.Connection.Open();
IAsyncResult result = mySQLCmd.BeginExecuteReader();
paidReader = mySQLCmd.EndExecuteReader(result);
result = mySQLCmd.BeginExecuteReader();
if (paidReader == null || !paidReader.HasRows)
{
paid = false;
return(serializer.Serialize(paid));
}
else
{
while (paidReader.Read())
{
if (paidReader["verifiedPaid"].ToString().Equals("Y"))
{
paid = true;
}
}
if (paid != true)
{
paid = checkPayPal();
}
return(serializer.Serialize(paid));
}
}
catch (Exception)
{
}
finally
{
if (paidReader != null)
{
paidReader.Close();
}
if (mySQLCmd != null)
{
mySQLCmd.Connection.Close();
}
}
paid = false;
return(serializer.Serialize(paid));
}
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");
MySqlDataReader reader = null;
try
{
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);
}
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);
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
finally
{
if (reader != null)
{
reader.Close();
}
}
}
private string RetrieveSingleDataFromMySQL(MySqlCommand _cmd /*, string _request*/)
{
//_cmd.CommandText = _request;
MySqlDataReader resultReader = _cmd.EndExecuteReader(_cmd.BeginExecuteReader()); //Executes query and acquire readeable result data
if (resultReader.HasRows && resultReader.Read()) //If result contains something(in this case password), opens for reading and check if data is not null
{
string result = resultReader.GetString(0);
resultReader.Close();
return(result);
}
resultReader.Close();
return("");
}
public void ThrowingExceptions()
{
MySqlCommand cmd = new MySqlCommand("SELECT xxx", conn);
IAsyncResult r = cmd.BeginExecuteReader();
try
{
using (MySqlDataReader reader = cmd.EndExecuteReader(r))
{
Assert.Fail("EndExecuteReader should have thrown an exception");
}
}
catch (MySqlException)
{
}
}
private static void AsyncCmdEnded(IAsyncResult result)
{
using (var dataReader = _asyncQueryCmd.EndExecuteReader(result))
{
var dataRow = new object[dataReader.FieldCount];
while (dataReader.Read())
{
var colCount = dataReader.GetValues(dataRow);
for (var i = 0; i < colCount; i++)
{
Console.Write($"| {dataRow[i]} ");
}
Console.WriteLine("|");
}
}
}
public virtual string FinalizeOperation(MySqlCommand command, IJoinQueryResultInternal result)
{
try
{
using (MySqlDataReader Reader = command.EndExecuteReader(result.AsyncResult))
{
FillResult(Reader, out List <IResultRow> Rows);
result.SetCompletedWithRows(Rows);
return($"succeeded, {Rows.Count} row(s) returned");
}
}
catch
{
result.SetCompleted(false, ResultError.ErrorExceptionCompletingJoinQuery);
throw;
}
}
public void ExecuteReader()
{
if (st.Version < new Version(5, 0))
{
return;
}
if (st.conn.State != ConnectionState.Open)
{
st.conn.Open();
}
st.execSQL("DROP TABLE IF EXISTS Test");
st.execSQL("DROP PROCEDURE IF EXISTS spTest");
st.execSQL("CREATE TABLE Test (id int)");
st.execSQL("CREATE PROCEDURE spTest() BEGIN INSERT INTO Test VALUES(1); " +
"SELECT SLEEP(2); SELECT 'done'; END");
MySqlCommand proc = new MySqlCommand("spTest", st.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.NotNull(reader);
Assert.True(count > 0, "count > 0");
Assert.True(reader.Read(), "can read");
Assert.True(reader.NextResult());
Assert.True(reader.Read());
Assert.Equal("done", reader.GetString(0));
reader.Close();
proc.CommandType = CommandType.Text;
proc.CommandText = "SELECT COUNT(*) FROM Test";
object cnt = proc.ExecuteScalar();
Assert.Equal(1, Convert.ToInt32(cnt));
}
}
public void GET_USER_DB_ID(string Username)
{
try
{
MySqlCommand CMD_GET_USER_ID = new MySqlCommand();
CMD_GET_USER_ID.Connection = LigacaoDB.CONNECTION;
CMD_GET_USER_ID.CommandText = "SELECT id FROM site_tvtuga_app_admin_users.admin_users WHERE username = '******'";
LigacaoDB.OPEN_CONNECTION();
CMD_GET_USER_ID.Prepare();
IAsyncResult Working_GetUserID = CMD_GET_USER_ID.BeginExecuteReader();
//while (!Working_GetUserID.IsCompleted)
//{
// Accções Adicionais
//}
Reader = CMD_GET_USER_ID.EndExecuteReader(Working_GetUserID);
while (Reader.Read())
{
for (int VALUE = 0; VALUE < Reader.FieldCount; VALUE++)
{
UserID = Convert.ToInt32(Reader.GetValue(VALUE));
}
}
Reader.Close();
}
catch (Exception EX)
{
MessageBox.Show("Ocorreu um erro de ligação. Verifique a sua configuração.\nDescrição do erro: \n\n " + EX.Message + "", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
if (LigacaoDB.CONNECTION.State == ConnectionState.Open)
{
LigacaoDB.CLOSE_CONNECTION();
}
}
}
private void SelectUserData()
{
MySqlConnection mysqlConnection = null;
string connString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password="******";";
try
{
mysqlConnection = new MySqlConnection(connString);
mysqlConnection.Open();
string selectSql = "SELECT * FROM " + MySqlSchemaName + "." + MySqlTableName_userData;
MySqlCommand mySqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mySqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mySqlDataReader = mySqlCommand.EndExecuteReader(iAsync);
while (mySqlDataReader.Read())
{
usernameIdMap.Add(mySqlDataReader.GetString("user_name"), mySqlDataReader.GetString("user_id"));
}
mySqlDataReader.Close();
mySqlCommand.Dispose();
mysqlConnection.Close();
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}
IEnumerator SelectData()
{
MySqlConnection con = null;
string conCmd =
"server=" + SERVER + ";" +
"database=" + DATABASE + ";" +
"userid=" + USERID + ";" +
"port=" + PORT + ";" +
"password="******"SELECT * FROM TABLENAME LIMIT 0, 1200;";
MySqlCommand cmd = new MySqlCommand(selCmd, con);
IAsyncResult iAsync = cmd.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
yield return(0);
}
MySqlDataReader rdr = cmd.EndExecuteReader(iAsync);
while (rdr.Read())
{
if (!rdr.IsDBNull(rdr.GetOrdinal("ID")))
{
//Debug.Log ( "ID : " + rdr.GetString ("ID") );
}
}
rdr.Close();
rdr.Dispose();
con.Close();
con.Dispose();
}
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();
}
}
}
public void FILL_WEBSITE_DATA(string Nome)
{
try
{
GET_WEBSITE_ID(Nome);
MySqlCommand CMD_GET_WEBSITE_DATA = new MySqlCommand("site_tvtuga_app_admin_users._SP_SELECT_X_Websites_Data", LigacaoDB);
CMD_GET_WEBSITE_DATA.CommandType = CommandType.StoredProcedure;
CMD_GET_WEBSITE_DATA.Parameters.AddWithValue("_nome", Nome);
if (LigacaoDB.State == ConnectionState.Closed)
{
LigacaoDB.Open();
}
CMD_GET_WEBSITE_DATA.Prepare();
IAsyncResult Working_WebsiteData = CMD_GET_WEBSITE_DATA.BeginExecuteReader();
Reader = CMD_GET_WEBSITE_DATA.EndExecuteReader(Working_WebsiteData);
while (Reader.Read())
{
Websites_Data.Add(Reader["WebsiteNome"].ToString());
Websites_Data.Add(Reader["WebsiteEndereco"].ToString());
Websites_Data.Add(Reader["TipoNome"].ToString());
}
}
catch (Exception EX)
{
MessageBox.Show("Ocorreu um erro. Descrição do erro: \n\n " + EX.Message + "", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
Reader.Close();
LigacaoDB.Close();
}
}
public virtual string FinalizeOperation(MySqlCommand command, ISingleQueryResultInternal result)
{
try
{
using (MySqlDataReader Reader = command.EndExecuteReader(result.AsyncResult))
{
ITableDescriptor Table = Context.Table;
ISchemaDescriptor Schema = Table.Schema;
IReadOnlyCollection <IColumnDescriptor> TableStructure = Schema.Tables[Table];
FillResult(Reader, TableStructure, out List <IResultRow> Rows);
result.SetCompletedWithRows(Rows);
return($"succeeded, {Rows.Count} row(s) returned");
}
}
catch
{
result.SetCompleted(false, ResultError.ErrorExceptionCompletingQuery);
throw;
}
}
public void ThrowingExceptions()
{
MySqlCommand cmd = new MySqlCommand("SELECT xxx", conn);
IAsyncResult r = cmd.BeginExecuteReader();
try
{
using (MySqlDataReader reader = cmd.EndExecuteReader(r))
{
Assert.Fail("EndExecuteReader should have thrown an exception");
}
}
catch (MySqlException)
{
}
}
public void SelectBehaviorTypes()
{
MySqlConnection mysqlConnection = null;
try
{
this.behaviorType2InstructionMap.Clear();
this.behaviorTypes.Clear();
this.processTypes.Clear();
this.graspTypes.Clear();
this.collideTypes.Clear();
string connectString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password ="******";";
mysqlConnection = new MySqlConnection(connectString);
mysqlConnection.Open();
string selectString = "SELECT * FROM " + MySqlSchemaName + "." + MySqlTableName_behaviorTypes;
MySqlCommand mysqlCommand = new MySqlCommand(selectString, mysqlConnection);
IAsyncResult iAsync = mysqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mysqlDataReader = mysqlCommand.EndExecuteReader(iAsync);
while (mysqlDataReader.Read())
{
string behaviorType = mysqlDataReader.GetString("behavior_type");
this.behaviorType2InstructionMap.Add(behaviorType, mysqlDataReader.GetString("instruction_form"));
this.behaviorType2MeaningMap.Add(behaviorType, mysqlDataReader.GetString("meaning"));
this.behaviorTypes.Add(behaviorType);
if (Boolean.Parse(mysqlDataReader.GetString("is_processed")))
{
this.processTypes.Add(behaviorType);
}
if (Boolean.Parse(mysqlDataReader.GetString("is_grasped")))
{
this.graspTypes.Add(behaviorType);
}
if (Boolean.Parse(mysqlDataReader.GetString("is_collided")))
{
this.collideTypes.Add(behaviorType);
}
}
Debug.Log("Got motion types");
mysqlDataReader.Close();
mysqlCommand.Dispose();
mysqlConnection.Close();
}
catch (Exception ex)
{
Debug.Log(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}
public void LogIn(string name)
{
Debug.Log("Log in");
MySqlConnection mysqlConnection = null;
string connString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password ="******";";
try
{
mysqlConnection = new MySqlConnection(connString);
mysqlConnection.Open();
string selectSql = "SELECT * FROM " + MySqlSchemaName + "." + MySqlTableName_userData + " WHERE user_name='" + name + "'";
MySqlCommand mySqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mySqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mySqlDataReader = mySqlCommand.EndExecuteReader(iAsync);
List <string> userInfoList = new List <string>();
int user_id = 0;
while (mySqlDataReader.Read())
{
userInfoList.Add(mySqlDataReader.GetString("user_name"));
user_id = int.Parse(mySqlDataReader.GetString("user_id"));
}
this.participantID = user_id;
this.participantName = name;
mySqlDataReader.Close();
mySqlCommand.Dispose();
mysqlConnection.Close();
if (userInfoList.Count == 0)
{
SignUp(name);
}
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}
public List <string> SelectHandMotions(string recipeID, int step, int procedure, string table)
{
MySqlConnection mysqlConnection = null;
List <string> motionDataList = new List <string>();
try
{
this.handMotionData.Clear();
this.graspedObjectData.Clear();
this.collidingObjectData.Clear();
string connString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password ="******";";
mysqlConnection = new MySqlConnection(connString);
mysqlConnection.Open();
string tableName = MySqlTableName_playbackMotions; //if (table == "participant")
int recordingID = this.participantID;
if (table == "expert")
{
tableName = MySqlTableName_expertMotions;
recordingID = this.expertID;
}
string columnString = "hand_data";
string selectSql = String.Format("SELECT {0} FROM {1}.{2} WHERE recording_id={3} AND recipe_id='{4}' AND step={5} AND procedure_number={6}",
columnString, MySqlSchemaName, tableName, recordingID.ToString(), recipeID, step.ToString(), procedure.ToString());
MySqlCommand mysqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mysqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mysqlDataReader = mysqlCommand.EndExecuteReader(iAsync);
while (mysqlDataReader.Read())
{
if (!mysqlDataReader.IsDBNull(0))
{
motionDataList.Add(mysqlDataReader.GetString(0));
}
}
motionDataList.RemoveAt(0);
Debug.Log("Got hand motion length: " + motionDataList.Count);
mysqlDataReader.Close();
mysqlCommand.Dispose();
mysqlConnection.Close();
return(motionDataList);
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
return(motionDataList);
}
}
private List <string> SelectRecipeInfoFromTable(string tableName, string recipeId)
{
MySqlConnection mysqlConnection = null;
List <string> taskInfoList = new List <string>();
string connString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + CookingRecipeSchema + ";" +
"userid =" + mySqlUsername + ";" +
"password="******";";
if (this.isSSHConnection)
{
connString =
"server =" + CookingRecipeIP + ";" +
"port =" + CookingRecipePort + ";" +
"database =" + CookingRecipeSchema + ";" +
"userid =" + CookingRecipeUsername + ";" +
"password="******";";
}
string idColumn = "";
string selectColumn = "";
string option = "";
if (tableName == "recipes")
{
idColumn = "id";
selectColumn = "title";
}
else if (tableName == "steps")
{
idColumn = "recipe_id";
selectColumn = "memo";
option = "ORDER BY position";
}
else if (tableName == "ingredients")
{
idColumn = "recipe_id";
selectColumn = "name";
}
try
{
mysqlConnection = new MySqlConnection(connString);
mysqlConnection.Open();
string selectSql = String.Format("SELECT * FROM {0}.{1} WHERE {2}='{3}' {4}", CookingRecipeSchema, tableName, idColumn, recipeId, option);
MySqlCommand mySqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mySqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mySqlDataReader = mySqlCommand.EndExecuteReader(iAsync);
while (mySqlDataReader.Read())
{
taskInfoList.Add(mySqlDataReader.GetString(selectColumn));
}
mySqlDataReader.Close();
mySqlCommand.Dispose();
mysqlConnection.Close();
return(taskInfoList);
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
return(taskInfoList);
}
}
public void SelectGuidanceRecipes()
{
MySqlConnection mysqlConnection = null;
try
{
this.recipeIDs.Clear();
this.recipeNumberMap.Clear();
this.recipeId2ProcessedFoodMap.Clear();
string connectString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password ="******";";
mysqlConnection = new MySqlConnection(connectString);
mysqlConnection.Open();
string selectString = "SELECT * FROM " + MySqlSchemaName + "." + MySqlTableName_guidanceRecipes;
MySqlCommand mysqlCommand = new MySqlCommand(selectString, mysqlConnection);
IAsyncResult iAsync = mysqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
this.recipeIDs.Clear();
MySqlDataReader mysqlDataReader = mysqlCommand.EndExecuteReader(iAsync);
while (mysqlDataReader.Read())
{
this.recipeNumberMap[mysqlDataReader.GetString("recipe_id")] = int.Parse(mysqlDataReader.GetString("task_id"));
if (!this.finishedTasks.Contains(int.Parse(mysqlDataReader.GetString("task_id"))))
{
this.recipeIDs.Add(mysqlDataReader.GetString("recipe_id"));
}
if (!mysqlDataReader.IsDBNull(2))
{
this.recipeId2ProcessedFoodMap.Add(mysqlDataReader.GetString("recipe_id"), mysqlDataReader.GetString("processed_foods"));
}
}
Debug.Log("the number of recipes: " + this.recipeIDs.Count);
mysqlDataReader.Close();
mysqlCommand.Dispose();
mysqlConnection.Clone();
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}
public void SelectFinishedTasks()
{
MySqlConnection mysqlConnection = null;
string connString =
"server =" + mySqlIP + ";" +
"port =" + mySqlPort + ";" +
"database =" + MySqlSchemaName + ";" +
"userid =" + mySqlUsername + ";" +
"password="******";";
try
{
this.finishedTasks.Clear();
mysqlConnection = new MySqlConnection(connString);
mysqlConnection.Open();
string selectSql = String.Format("SELECT user_id,finished_tasks FROM {0}.{1} WHERE user_name='{2}'", MySqlSchemaName, MySqlTableName_userData, this.participantName);
MySqlCommand mySqlCommand = new MySqlCommand(selectSql, mysqlConnection);
IAsyncResult iAsync = mySqlCommand.BeginExecuteReader();
while (!iAsync.IsCompleted)
{
Thread.Sleep(100);
}
MySqlDataReader mySqlDataReader = mySqlCommand.EndExecuteReader(iAsync);
string userID = "";
string taskString = "";
while (mySqlDataReader.Read())
{
if (!mySqlDataReader.IsDBNull(0))
{
userID = mySqlDataReader.GetString(0);
}
if (!mySqlDataReader.IsDBNull(1))
{
taskString = mySqlDataReader.GetString(1);
}
}
if (userID == "")
{
SignUp(this.participantName);
}
else if (taskString != "")
{
Debug.Log("finished tasks: " + taskString);
List <string> finishedStrings = new List <string>(taskString.Split(','));
this.finishedTasks = finishedStrings.ConvertAll(x => Int32.Parse(x));
}
mySqlDataReader.Close();
mySqlCommand.Dispose();
mysqlConnection.Close();
}
catch (Exception ex)
{
Debug.LogError(ex);
if (mysqlConnection != null)
{
mysqlConnection.Close();
}
throw ex;
}
}