public SQLResult Select(string sql, params object[] parameters)
{
using (var pClient = GetClient())
{
var sqlString = new StringBuilder();
// Fix for floating point problems on some languages
sqlString.AppendFormat(CultureInfo.GetCultureInfo("en-US").NumberFormat, sql, parameters);
var sqlCommand = new SqlCommand(sqlString.ToString());
try
{
sqlCommand.Parameters.AddRange(parameters);
using (var sqlData = pClient.ExecuteReader(sqlCommand))
{
using (var retData = new SQLResult())
{
retData.Load(sqlData);
retData.Count = retData.Rows.Count;
return(retData);
}
}
}
catch (SqlException ex)
{
DatabaseLog.Write(ex, $"Error With Query {sqlCommand.CommandText}");
return(null);
}
}
}
//Handling exception...^^
public T ToOrDefault <T>(DataRow row, string columnName)
where T : IConvertible
{
try
{
return((T)Convert.ChangeType(row[columnName], typeof(T), CultureInfo.InvariantCulture));
}
catch (InvalidCastException ex)
{
DatabaseLog.Write(ex, "Invalid cast (ColumName {0} : {1})", columnName, row[columnName]);
}
catch (FormatException ex)
{
DatabaseLog.Write(ex, "Invalid Format (ColumName {0} : {1})", columnName, row[columnName]);
}
catch (OverflowException ex)
{
DatabaseLog.Write(ex, "Overflowed (ColumName {0} : {1})", columnName, row[columnName]);
}
catch (ArgumentException ex)
{
DatabaseLog.Write(ex, "Invalid Argument (ColumName {0} : {1})", columnName, row[columnName]);
}
return(default(T));
}
public static SQLResult Select(DatabaseType pType, string sql, params object[] parameters)
{
if (DbManagerList.TryGetValue(pType, out var pManager))
{
return(pManager.Select(sql, parameters));
}
DatabaseLog.Write(DatabaseLogLevel.DatabaseClientError, "Unknown Manager {0}", pType);
return(null);
}
public static void RunSQL(DatabaseType pType, string sql, params object[] parameter)
{
if (DbManagerList.TryGetValue(pType, out var pManager))
{
pManager.RunSQL(pType, sql, parameter);
return;
}
DatabaseLog.Write(DatabaseLogLevel.DatabaseClientError, "Unknown Manager {0}", pType);
}
/// <summary>
/// Called when released from using() scope - does not actually dispose, just marks as available
/// </summary>
public void Dispose()
{
ResetCommand();
Available = true;
UpdateLastActivity();
if (ServerMainDebug.DebugSql)
{
DatabaseLog.Write(DatabaseLogLevel.Debug, "(Sql)Released client " + Id + " for availability.");
}
}
public static void DisposeManager(DatabaseType pType)
{
if (DbManagerList.TryGetValue(pType, out var pManager))
{
pManager.Dispose();
}
else
{
DatabaseLog.Write(DatabaseLogLevel.DatabaseClientError, "Failed to Dispose {0} DB Monitor, Unknown Manager", pType);
}
}
public void SetClientAmount(int clientAmount, string logReason = "Unknown")
{
int diff;
lock (_mSyncRoot)
{
diff = clientAmount - ClientCount;
if (diff > 0)
{
for (var i = 0; i < diff; i++)
{
var newId = GenerateClientId();
_mClients.Add(newId, CreateClient(newId));
}
}
else
{
var toDestroy = -diff;
var destroyed = 0;
foreach (var client in _mClients.Values)
{
if (!client.Available)
{
continue;
}
if (destroyed >= toDestroy || ClientCount <= _mDatabase.MinPoolSize)
{
break;
}
client.Close();
_mClients.Remove(client.Id);
destroyed++;
}
}
}
if (ServerMainDebug.DebugSql)
{
DatabaseLog.Write(DatabaseLogLevel.Debug,
$"(Sql) Client availability: {clientAmount}; modifier: {diff}; reason: {logReason}.");
}
}
public DatabaseClient GetClient()
{
lock (_mSyncRoot)
{
foreach (var client in _mClients.Values)
{
if (!client.Available)
{
continue;
}
if (ServerMainDebug.DebugSql)
{
DatabaseLog.Write(DatabaseLogLevel.Debug,
$"(Sql) Assigned client {client.Id}.");
}
if (!client.CheckConnection())
{
client.Dispose();
return(GetClient());
}
client.Available = false;
return(client);
}
if (_mDatabase.MaxPoolSize <= 0 || ClientCount < _mDatabase.MaxPoolSize) // Max pool size ignored if set to 0 or lower
{
SetClientAmount(ClientCount + 1, "out of assignable clients in GetClient()");
return(GetClient());
}
_mStarvationCounter++;
DatabaseLog.Write(DatabaseLogLevel.Warning,
$"(Sql) Client starvation; out of assignable clients/maximum pool size reached. Consider increasing the `mysql.pool.max` configuration value. Starvation count is {_mStarvationCounter}.");
// Wait until an available client returns
Monitor.Wait(_mSyncRoot);
return(GetClient());
}
}
public static bool AddManager(DatabaseType pType, DatabaseConfiguration dbConfig)
{
string dbName;
switch (pType)
{
case DatabaseType.Account:
dbName = dbConfig.AccountDatabase;
break;
case DatabaseType.AccountLog:
dbName = dbConfig.AccountLogDatabase;
break;
case DatabaseType.Character:
dbName = dbConfig.CharacterDatabase;
break;
case DatabaseType.GameLog:
dbName = dbConfig.GameLogDatabase;
break;
default:
return(false);
}
var mManager = new DatabaseManager(GenerateDatabaseServer(dbConfig), GenerateDatabase(dbConfig, dbName));
if (mManager.TestConnection(out var exceptionMsg))
{
DbManagerList.Add(pType, mManager);
DatabaseLog.Write(DatabaseLogLevel.Startup, $"Successfully connected to {dbName} Database!");
return(true);
}
DatabaseLog.Write(DatabaseLogLevel.DatabaseClientError, $"Failed to connect {dbName} Database: {exceptionMsg}");
return(false);
}
bool IServerTask.Update(GameTime gameTime)
{
if (_isDisposedInt == 1)
{
return(false);
}
if (ClientCount <= _mDatabase.MinPoolSize)
{
return(true);
}
lock (_mSyncRoot)
{
var toDisconnect = new List <int>();
foreach (var client in _mClients.Values)
{
if (client.Available && client.TimeInactive >= _mDatabase.ClientLifeTime)
{
toDisconnect.Add(client.Id);
}
}
foreach (var disconnectId in toDisconnect)
{
_mClients[disconnectId].Close();
_mClients.Remove(disconnectId);
}
if (toDisconnect.Count > 0)
{
DatabaseLog.Write(DatabaseLogLevel.Debug, $"(Sql)Disconnected {toDisconnect.Count} inactive client(s).");
}
Monitor.PulseAll(_mSyncRoot);
}
return(true);
}
