public IEnumerable <Dictionary <string, object> > Query(Sql sql, SQLiteConnection db)
{
try
{
db.Open();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = sql.SQL;
Sql.AddParams(cmd, sql.Arguments, "@");
using (var dataReader = cmd.ExecuteReader())
{
//var list = new List<Dictionary<string, object>>();
while (dataReader.Read())
{
var dict = new Dictionary <string, object>();
for (var i = 0; i < dataReader.FieldCount; i++)
{
dict.Add(dataReader.GetName(i), dataReader.GetValue(i));
}
yield return(dict);
//list.Add(dict);
}
//return list;
}
}
}
finally
{
db.Close();
}
}
public int ExecuteNonQuery(Sql sql, SQLiteConnection db)
{
try
{
db.Open();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = sql.SQL;
Sql.AddParams(cmd, sql.Arguments, "@");
return(cmd.ExecuteNonQuery());
}
}
finally
{
db.Close();
}
}
public T ExecuteScalar <T>(Sql sql, SQLiteConnection db)
{
try
{
db.Open();
using (var cmd = db.CreateCommand())
{
cmd.CommandText = sql.SQL;
Sql.AddParams(cmd, sql.Arguments, "@");
var val = cmd.ExecuteScalar();
return((T)Convert.ChangeType(val, typeof(T)));
}
}
finally
{
db.Close();
}
}
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 = (MySqlConnection)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 bool Handle()
{
//вывести SQL запрос и параметры
string GetCommandParams()
{
StringBuilder ParamsVals = new StringBuilder();
int LastIndex = this._cmd.Parameters.Count;
for (int i = 0; i < LastIndex; ++i)
{
//не добавляем запятую в последую строку
string EndChar = "";
if (i != LastIndex - 1)
{
EndChar = ", ";
}
IDbDataParameter item = this._cmd.Parameters[i];
ParamsVals.AppendFormat("{0}={1}{2}", item.ParameterName, item.Value, EndChar);//пример: @0=value
}
return(ParamsVals.ToString());
}
List <Dictionary <string, object> > list = null;
int nonQueryResult = 0;
long lastInsertRowId = 0L;
try {
if (Connection == null)
{
throw new Exception("Connection is null");
}
//if (_result == null)
//{
_connection = (MySqlConnection)Connection.Con;
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_cmd = _connection.CreateCommand();
_cmd.CommandTimeout = 120;
_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 (MySqlDataReader reader = _cmd.EndExecuteReader(_result)) {
list = new List <Dictionary <string, object> >();
while (reader.Read())
{
if (Connection.ConnectionPersistent && (Connection.Con.State == ConnectionState.Closed || Connection.Con.State == ConnectionState.Broken))
{
break;
}
var dict = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
list.Add(dict);
}
}
}
lastInsertRowId = _cmd.LastInsertedId;
Cleanup();
} catch (Exception ex) {
string message = "MySql handle raised an exception";
if (Connection?.Plugin != null)
{
message += $" in '{Connection.Plugin.Name} v{Connection.Plugin.Version}' plugin ";
}
//вывод в исключении текста команда и её параметров(должно помочь при отладке)
message += $"Command: \"{this._cmd.CommandText}\" Params: \"{GetCommandParams()}\"";
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) {
string message = "MySql command callback raised an exception";
if (Connection?.Plugin != null)
{
message += $" in '{Connection.Plugin.Name} v{Connection.Plugin.Version}' plugin ";
}
message += $"Command: \"{this._cmd.CommandText}\" Params: \"{GetCommandParams()}\"";
Interface.Oxide.LogException(message, ex);
}
Connection?.Plugin?.TrackEnd();
});
return(true);
}
public void Handle()
{
List <Dictionary <string, object> > list = null;
int nonQueryResult = 0;
long lastInsertRowId = 0L;
try
{
if (Connection == null)
{
throw new Exception("Connection is null");
}
_connection = (SQLiteConnection)Connection.Con;
if (_connection.State == ConnectionState.Closed)
{
_connection.Open();
}
_cmd = _connection.CreateCommand();
_cmd.CommandText = Sql.SQL;
Sql.AddParams(_cmd, Sql.Arguments, "@");
if (NonQuery)
{
nonQueryResult = _cmd.ExecuteNonQuery();
}
else
{
using (SQLiteDataReader reader = _cmd.ExecuteReader())
{
list = new List <Dictionary <string, object> >();
while (reader.Read())
{
Dictionary <string, object> dict = new Dictionary <string, object>();
for (int i = 0; i < reader.FieldCount; i++)
{
dict.Add(reader.GetName(i), reader.GetValue(i));
}
list.Add(dict);
}
}
}
lastInsertRowId = _connection.LastInsertRowId;
Cleanup();
}
catch (Exception ex)
{
string message = "Sqlite 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)
{
string message = "Sqlite 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();
});
}