private void Load(string table_name, string sql_query)
{
ColumnNames = new List <string>();
Rows = new List <DatabaseRow>();
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString); // Grab the conection string for accessing the database.
SqlCommand command = new SqlCommand(sql_query, connection); // Associate the given sql query with the connection.
connection.Open();
SqlDataReader reader = command.ExecuteReader(); // Send the sql query to the database for processing.
for (int n = 0; n < reader.FieldCount; ++n)
{
ColumnNames.Add(reader.GetName(n));
}
while (reader.Read()) // Access each student in turn (continue until there are no students left to read).
{
DatabaseRow dr = new DatabaseRow();
for (int column = 0; column < ColumnCount; ++column)
{
dr.Add(ColumnNames[column], reader[column].ToString());
}
Rows.Add(dr);
}
connection.Close();
}
/// <summary>
/// Initializes a new instance of the <see cref="UPCRMResultRow"/> class.
/// </summary>
/// <param name="databaseRow">
/// The database row.
/// </param>
/// <param name="result">
/// The result.
/// </param>
public UPCRMResultRow(DatabaseRow databaseRow, UPCRMResult result)
{
this.Result = result;
this.databaseRow = databaseRow;
this.serverData = null;
this.serverRecordIds = null;
}
/// <summary>
/// Offlines the records for request.
/// </summary>
/// <returns></returns>
public List <string> OfflineRecordsForRequest()
{
List <string> deleteRecordArray = null;
IDatabase database = this.Storage.Database;
database.BeginTransaction();
DatabaseRecordSet recordSet = new DatabaseRecordSet(database);
if (recordSet.Query.Prepare("SELECT infoAreaid, recordid FROM records WHERE requestnr = ? AND mode = 'NewOffline'"))
{
recordSet.Query.Bind(1, (int)this.RequestNr);
int ret = recordSet.Execute();
if (ret == 0)
{
int count = recordSet.GetRowCount();
if (count > 0)
{
deleteRecordArray = new List <string>();
for (int i = 0; i < count; i++)
{
DatabaseRow row = recordSet.GetRow(i);
deleteRecordArray.Add($"{row.GetColumn(0)}.{row.GetColumn(1)}");
}
}
}
}
database.Commit();
return(deleteRecordArray);
}
public string ResultToStringForStatement(string databaseStatement)
{
DatabaseRecordSet recordSet = new DatabaseRecordSet(this.Database);
int ret = recordSet.Execute(databaseStatement);
StringBuilder resultString = new StringBuilder();
if (ret == 0)
{
for (int i = 0; i < recordSet.GetColumnCount(); i++)
{
resultString.Append(i == 0 ? recordSet.GetColumnName(0) : $";{recordSet.GetColumnName(i)}");
}
resultString.AppendLine();
for (int i = 0; i < recordSet.GetRowCount(); i++)
{
DatabaseRow row = recordSet.GetRow(i);
for (int j = 0; j < recordSet.GetColumnCount(); j++)
{
resultString.Append(j == 0 ? row.GetColumn(0) : $";{row.GetColumn(j)}");
}
resultString.AppendLine();
}
}
return(resultString.ToString());
}
static void searchValue(SqliteConnection connection, string query)
{
List <DatabaseRow> records = new List <DatabaseRow>();
SqliteCommand command = new SqliteCommand($"SELECT * FROM whatdoesthefoxsay WHERE Service='{query}'", connection);
if (query == "AllServices")
{
command.CommandText = "SELECT * FROM whatdoesthefoxsay";
}
SqliteDataReader r = command.ExecuteReader();
while (r.Read())
{
DatabaseRow record = new DatabaseRow();
record.ID = r.GetInt32(0);
record.Service = r.GetString(1);
record.Password = r.GetString(2);
records.Add(record);
}
Console.WriteLine($"Found {records.Count} records! \n ");
foreach (DatabaseRow row in records)
{
Console.WriteLine($"ID: {row.ID} Service: {row.Service} Password: {row.Password} \n ");
}
}
internal Dictionary <string, DatabaseRow> FetchAllDatabaseRowsFromSpreadsheet(string familyNamesColumn)
{
var familyNames = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{familyNamesColumn}:{familyNamesColumn}");
var characterNames = SpreadsheetHandler.GetValuesFromRange(_service, _spreadsheet.Id, $"{_spreadsheet.DatabaseSheet.Name}!{familyNamesColumn}:{familyNamesColumn}");
if (familyNames.Count == 0)
{
return(null);
}
var rowDictionary = new Dictionary <string, DatabaseRow>();
for (var i = 0; i < familyNames.Count; i++)
{
try
{
var familyName = familyNames[i][0].ToString();
var characterName = characterNames[i][0].ToString();
var member = new DatabaseMember(familyName, characterName);
var databaseRow = new DatabaseRow(i + 1, member);
rowDictionary.TryAdd(familyName, databaseRow);
}
catch { continue; }
}
_spreadsheet.DatabaseSheet.DatabaseRows = rowDictionary;
return(rowDictionary);
}
/// <summary>
/// Initializes a new instance of the <see cref="UPCRMResultRow"/> class.
/// </summary>
/// <param name="newRecordIdentification">
/// The new record identification.
/// </param>
/// <param name="result">
/// The result.
/// </param>
public UPCRMResultRow(string newRecordIdentification, UPCRMResult result)
{
this.Result = result;
this.databaseRow = null;
this.serverData = null;
this.serverRecordIds = null;
this.newRecordIdentification = newRecordIdentification;
}
/// <summary>
/// Initializes a new instance of the <see cref="UPCRMResultRow"/> class.
/// </summary>
/// <param name="rowServerResponse">
/// The row server response.
/// </param>
/// <param name="result">
/// The result.
/// </param>
public UPCRMResultRow(List <object> rowServerResponse, UPCRMResult result)
{
this.Result = result;
this.databaseRow = null;
this.serverData = rowServerResponse[1] as List <object>;
this.serverRecordIds =
(rowServerResponse[0] as List <string> ?? (rowServerResponse[0] as List <object>)?.Cast <string>())
?.ToList();
}
public DatabaseRow AddDatabaseRow(string Name)
{
DatabaseRow rowDatabaseRow = ((DatabaseRow)(this.NewRow()));
rowDatabaseRow.ItemArray = new object[] {
Name,
null
};
this.Rows.Add(rowDatabaseRow);
return(rowDatabaseRow);
}
/// <summary>
/// Loads from database.
/// </summary>
/// <param name="database">The database.</param>
/// <returns>
/// True if success, else false
/// </returns>
public override bool LoadFromDatabase(IDatabase database)
{
if (!base.LoadFromDatabase(database))
{
return(false);
}
int ret = 0;
DatabaseRecordSet recordSet = new DatabaseRecordSet(database);
if (recordSet.Query.Prepare("SELECT data, filename, mimetype, infoareaid, recordid, fieldid FROM documentuploads WHERE requestnr = ?"))
{
recordSet.Query.Bind(1, this.RequestNr);
ret = recordSet.Execute();
if (ret == 0)
{
int count = recordSet.GetRowCount();
if (count == 1)
{
DatabaseRow row = recordSet.GetRow(0);
string databaseValue = row.GetColumn(0);
if (databaseValue.StartsWith("file://"))
{
string lastPathComponent = databaseValue.StartsWith("file://localhost") ? Path.GetFileName(databaseValue) : databaseValue.Substring(7);
this.LocalFileUrl = this.Storage.GetOfflineStoragePath(lastPathComponent);
Task <byte[]> t = SimpleIoc.Default.GetInstance <IPlatformService>().StorageProvider.FileContents(this.LocalFileUrl);
this.Data = t.Result;
if (SimpleIoc.Default.GetInstance <ILogSettings>().LogUpSync)
{
SimpleIoc.Default.GetInstance <ILogger>().LogDebug($"Sync request {this.RequestNr}: document {this.LocalFileUrl} loaded ({this.Data.Length} bytes)", LogFlag.LogUpSync);
}
}
else
{
this.LocalFileUrl = null;
this.Data = Convert.FromBase64String(databaseValue);
}
this.FileName = row.GetColumn(1);
this.MimeType = row.GetColumn(2);
string infoAreaId = row.GetColumn(3);
string recordId = row.GetColumn(4);
this.RecordIdentification = StringExtensions.InfoAreaIdRecordId(infoAreaId, recordId);
this.FieldId = row.GetColumnInt(5);
}
}
}
return(ret == 0 && this.Data != null);
}
/// <summary>
/// Requests from result row.
/// </summary>
/// <param name="row">The row.</param>
/// <returns></returns>
public UPOfflineRequest RequestFromResultRow(DatabaseRow row)
{
var col = row.GetColumnInt(0);
var col1 = row.GetColumn(1);
var col2 = row.GetColumn(2);
if (col1 == null || col2 == null)
{
return(null);
}
return(this.RequestWithNrTypeProcessType(col,
(OfflineRequestType)Enum.Parse(typeof(OfflineRequestType), col1),
(OfflineRequestProcess)Enum.Parse(typeof(OfflineRequestProcess), col2)));
}
public DatabaseRow FindRow(string field, string value)
{
for (int r = 0; r < RowCount; ++r)
{
DatabaseRow dr = GetRow(r);
if (dr.ContainsField(field))
{
if (dr[field] == value)
{
return(dr);
}
}
}
return(null);
}
/// <summary>
/// Attaches the next server request number.
/// </summary>
public void AttachNextServerRequestNumber()
{
IDatabase database = this.Storage.Database;
database.BeginTransaction();
DatabaseRecordSet recordSet = new DatabaseRecordSet(database);
if (recordSet.Query.Prepare("SELECT nextRequestNumber FROM requestcontrol"))
{
if (recordSet.Execute() == 0)
{
if (recordSet.GetRowCount() == 1)
{
DatabaseRow row = recordSet.GetRow(0);
this.ServerRequestNumber = row.GetColumnInt(0);
DatabaseStatement updateStatement = new DatabaseStatement(database);
if (updateStatement.Prepare("UPDATE requestcontrol SET nextRequestNumber = ?"))
{
updateStatement.Bind(1, this.ServerRequestNumber + 1);
if (updateStatement.Execute() != 0)
{
this.ServerRequestNumber = -1;
}
}
if (this.RequestNr > 0)
{
updateStatement = new DatabaseStatement(database);
if (updateStatement.Prepare("UPDATE requests SET serverRequestNumber = ? WHERE requestnr = ?"))
{
updateStatement.Bind(1, this.ServerRequestNumber);
updateStatement.Bind(2, this.RequestNr);
updateStatement.Execute();
}
}
}
else
{
this.ServerRequestNumber = -1;
}
}
}
database.Commit();
}
internal DatabaseRow GetRowOfValue(string value, IList <IList <object> > columnValues)
{
DatabaseRow row = null;
int rowNumber = 0;
for (int i = 0; i < columnValues.Count; i++)
{
var columnValuesFlattened = columnValues[i];
if (columnValuesFlattened.Count == 0)
{
continue;
}
if (columnValuesFlattened[0].ToString() == value)
{
rowNumber = i + 1;
row = new DatabaseRow(rowNumber, _spreadsheet.DatabaseSheet.DatabaseRows.FirstOrDefault(x => x.Value.RowNumber == rowNumber).Value.Member);
break;
}
}
return(row);
}
//Function to query DB and return an array of rows
public override DatabaseRow[] ExecuteQuery(string query, params QueryParameter[] parameters)
{
try {
using (MySqlConnection connection = new MySqlConnection(connectionString)) {
using (MySqlCommand command = new MySqlCommand(query, connection)) {
foreach (QueryParameter parameter in parameters)
{
command.Parameters.AddWithValue(parameter.name, parameter.obj);
}
connection.Open();
using (MySqlDataReader reader = command.ExecuteReader()) {
int fieldCount = reader.FieldCount;
List <DatabaseRow> rows = new List <DatabaseRow> ();
while (reader.Read())
{
//For each row create a DatabaseRow
DatabaseRow row = new DatabaseRow();
//And add each field to it
for (int i = 0; i < fieldCount; i++)
{
row.Add(reader.GetName(i), reader.GetValue(i));
}
//Add it to the rows
rows.Add(row);
}
return(rows.ToArray());
}
}
}
}
catch (MySqlException e) {
throw new DatabaseException(e.Message, e);
}
}
/// <summary>
/// Loads this instance.
/// </summary>
/// <returns></returns>
private int Load()
{
CRMDatabase database = this.DataStore.DatabaseInstance;
DatabaseRecordSet recordSet = new DatabaseRecordSet(database);
int ret = recordSet.Execute("SELECT infoareaid, recordid, rollbackinfo FROM rollbackinfo WHERE requestnr = ?", this.RequestId);
if (ret != 0)
{
return(ret);
}
int rowCount = recordSet.GetRowCount();
for (int i = 0; i < rowCount; i++)
{
DatabaseRow row = recordSet.GetRow(i);
string recordIdentification = $"{row.GetColumn(0)}.{row.GetColumn(1)}";
var rollbackinfo = row.GetColumn(2);
this.AddRecordIdentificationRollbackInfo(recordIdentification, rollbackinfo);
}
return(0);
}
public void TradingLib_DatabaseConnector_InsertSelectRemoveTest()
{
DatabaseRow expectedDatabaseRow = new DatabaseRow(1, 2, 3, 4, 5, 6, 7);
DBC.InsertIntoDatabase(expectedDatabaseRow); // Inserts unlikely values into the database
List <DatabaseRow> values = DBC.SelectAllFromDatabase(); // Selects every row from the database
bool hasBeenFound = false; // False until found
string sql = "DELETE FROM prices WHERE " +
"date=1 AND " +
"price=2 AND " +
"bid_v=3 AND " +
"ask_v=4 AND " +
"change_perc=5 AND " +
"high=6 AND " +
"low=7;"; // Create sql to delete the row
foreach (DatabaseRow row in values) // Iterates through every row
{
if (row.data[0] == 1 &
row.data[1] == 2 &
row.data[2] == 3 &
row.data[3] == 4 &
row.data[4] == 5 &
row.data[5] == 6 &
row.data[6] == 7) // If the row is the values previously inserted
{
hasBeenFound = true; // Test is passed
SQLiteCommand command = new SQLiteCommand(sql, DBC.connection); // Create the command
command.ExecuteNonQuery(); // Execute the command
}
}
Assert.IsTrue(hasBeenFound);
}
public DatabaseRowChangeEvent(DatabaseRow row, global::System.Data.DataRowAction action) {
this.eventRow = row;
this.eventAction = action;
}
/// <summary>
/// Executes a query on the database returning an array of rows.
/// </summary>
/// <returns>The rows of the database selected.</returns>
/// <param name="query">The query.</param>
/// <param name="paramaters">The paramaters to be added to this query.</param>
public override DatabaseRow[] ExecuteQuery(string query, params QueryParameter[] parameters)
{
try
{
using (MySqlConnection connection = new MySqlConnection (connectionString))
{
using (MySqlCommand command = new MySqlCommand (query, connection))
{
foreach (QueryParameter parameter in parameters)
command.Parameters.AddWithValue(parameter.name, parameter.obj);
connection.Open();
using (MySqlDataReader reader = command.ExecuteReader())
{
int fieldCount = reader.FieldCount;
List<DatabaseRow> rows = new List<DatabaseRow> ();
while (reader.Read())
{
//For each row create a DatabaseRow
DatabaseRow row = new DatabaseRow();
//And add each field to it
for (int i = 0; i < fieldCount; i++)
{
row.Add(
reader.GetName(i),
reader.GetValue(i)
);
}
//Add it to the rows
rows.Add(row);
}
return rows.ToArray();
}
}
}
}
catch(MySqlException e)
{
throw new DatabaseException(e.Message, e);
}
}
internal Dictionary <string, DatabaseField> GetDatabaseFieldsFromConsecutiveColumns(DatabaseSheet sheet, Dictionary <string, DatabaseColumn> columns, DatabaseRow row)
{
var databaseFields = new Dictionary <string, DatabaseField>();
foreach (var column in columns)
{
var databaseField = new DatabaseField(sheet, column.Value, row);
databaseFields.TryAdd(column.Key, databaseField);
}
return(databaseFields);
}
/// <summary>
/// Checks the update before cache save with database.
/// </summary>
/// <param name="database">The database.</param>
/// <returns>0, if success, else error number</returns>
private int CheckUpdateBeforeCacheSaveWithDatabase(DatabaseBase database)
{
UPCRMTableInfo tableInfo = this.UndoRequest.DataStore.TableInfoForInfoArea(this.RecordIdentification.InfoAreaId());
StringBuilder selectStatement = new StringBuilder();
selectStatement.Append("SELECT ");
bool first = true;
List <string> allColumns = this.FieldDictionary.Keys.ToList();
foreach (string columnName in allColumns)
{
if (first)
{
first = false;
}
else
{
selectStatement.Append(", ");
}
selectStatement.Append(columnName);
}
if (first)
{
selectStatement.Append("recid");
}
selectStatement.Append($" FROM {tableInfo.DatabaseTableName} WHERE recid = ?");
CRMDatabase db = this.UndoRequest.DataStore.DatabaseInstance;
DatabaseRecordSet rs = new DatabaseRecordSet(db);
if (!rs.Query.Prepare(selectStatement.ToString()))
{
return(-1);
}
rs.Query.Bind(1, this.RecordIdentification.RecordId());
int ret = rs.Execute();
if (ret != 0)
{
return(ret);
}
int rc = rs.GetRowCount();
if (rc == 0)
{
this.Mode = "UpdateNew";
this.UndoOperation = "Delete";
return(0);
}
if (rc > 1)
{
return(-1);
}
this.UndoOperation = "Update";
int colIndex = 0;
DatabaseRow row = rs.GetRow(0);
foreach (string col in allColumns)
{
string v = row.GetColumn(colIndex++);
if (v != null)
{
UPCRMUndoField undoField = this.FieldDictionary[col];
undoField.OldValue = v;
}
}
return(0);
}
/// <summary>
/// Loads from database.
/// </summary>
/// <param name="database">The database.</param>
/// <returns>True if success, else false</returns>
public virtual bool LoadFromDatabase(IDatabase database)
{
DatabaseRecordSet recordSet = new DatabaseRecordSet(database);
string sql = @"SELECT json, error, errorcode, response, titleLine, detailsLine, imageName, serverRequestNumber, servertime,
sessionid, translationkey, relatedInfo, baseerror, appversion, errorstack FROM requests WHERE requestnr = ?";
recordSet.Query.Prepare(sql);
recordSet.Query.Bind(1, this.RequestNr);
int ret = recordSet.Execute();
if (ret == 0 && recordSet.GetRowCount() > 0)
{
this.Loaded = true;
DatabaseRow row = recordSet.GetRow(0);
this.Json = row.GetColumn(0);
this.blockExecution = false;
if (!row.IsNull(1))
{
string cError = row.GetColumn(1);
if (cError == Constants.OFFLINEREQUEST_BLOCKED_TEXT)
{
this.blockExecution = true;
}
this.Error = row.GetColumn(1);
}
this.Code = row.GetColumnInt(2, -1);
if (!row.IsNull(3))
{
this.Response = row.GetColumn(3);
}
if (!row.IsNull(4))
{
this.titleLine = row.GetColumn(4);
}
if (!row.IsNull(5))
{
this.detailsLine = row.GetColumn(5);
}
if (!row.IsNull(6))
{
this.imageName = row.GetColumn(6);
}
if (!row.IsNull(7))
{
this.ServerRequestNumber = row.GetColumnInt(7);
}
if (!row.IsNull(8))
{
this.ServerDateTime = row.GetColumn(8);
}
if (!row.IsNull(9))
{
this.ServerSessionId = row.GetColumn(9);
}
if (!row.IsNull(10))
{
this.ErrorTranslationKey = row.GetColumn(10);
}
if (!row.IsNull(11))
{
string _relatedInfo = row.GetColumn(11);
this.RelatedInfoDictionary = _relatedInfo.JsonDictionaryFromString();
}
if (!row.IsNull(12))
{
this.BaseErrorCode = row.GetColumnInt(12);
}
if (!row.IsNull(13))
{
this.ApplicationVersion = row.GetColumn(13);
}
if (!row.IsNull(14))
{
this.ErrorStack = row.GetColumn(14);
}
return(true);
}
return(false);
}
public void AddDatabaseRow(DatabaseRow row) {
this.Rows.Add(row);
}
public void RemoveDatabaseRow(DatabaseRow row) {
this.Rows.Remove(row);
}
public LookUpTableRow AddLookUpTableRow(string Name, string WhereClause, bool UseAsFullSelect, DatabaseRow parentDatabaseRowByDatabase_LookUpTable)
{
LookUpTableRow rowLookUpTableRow = ((LookUpTableRow)(this.NewRow()));
rowLookUpTableRow.ItemArray = new object[] {
Name,
WhereClause,
UseAsFullSelect,
parentDatabaseRowByDatabase_LookUpTable[1]
};
this.Rows.Add(rowLookUpTableRow);
return(rowLookUpTableRow);
}
public DatabaseRowChangeEvent(DatabaseRow row, DataRowAction action)
{
this.eventRow = row;
this.eventAction = action;
}
public void RemoveDatabaseRow(DatabaseRow row)
{
this.Rows.Remove(row);
}
public void AddDatabaseRow(DatabaseRow row)
{
this.Rows.Add(row);
}
