private XElement ReadTransactionTable(SqliteDatabaseContext databaseContext, string tableName, IEnumerable <string> transactionIds)
{
XElement table = new XElement(tableName);
XElement tableRow;
SqliteTableSchema tableSchemaInfo = databaseContext.GetTableSchemaInfo(tableName);
foreach (string transactionId in transactionIds)
{
// To prevent SQL injection, we need to use parameterized SQL as followed.
string queryTransactionStatement = string.Format("SELECT * FROM {0} WHERE TRANSACTIONID = {1} COLLATE NOCASE;", tableName, TransactionIdParameter);
SqlQuery query = new SqlQuery(queryTransactionStatement);
query.Parameters.Add(TransactionIdParameter, transactionId);
try
{
using (var resultSet = databaseContext.ExecuteQuery(query))
{
while (resultSet.Read())
{
List <XAttribute> attributes = new List <XAttribute>();
for (int i = 0; i < resultSet.FieldCount; ++i)
{
if (resultSet.GetValue <object>(i) == null)
{
attributes.Add(new XAttribute(resultSet.GetName(i).ToUpperInvariant(), string.Empty));
}
else
{
Type expectedManagedType = tableSchemaInfo.ColumnsByColumnName[resultSet.GetName(i).ToUpperInvariant()].ManagedType;
attributes.Add(new XAttribute(resultSet.GetName(i).ToUpperInvariant(), resultSet.GetValue(i, expectedManagedType)));
}
}
tableRow = new XElement("ROW", attributes);
table.Add(tableRow);
}
}
}
catch (DatabaseException ex)
{
throw new StorageException(
StorageErrors.Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError,
(int)ex.ErrorCode,
ex,
"Cannot read transaction data from transaction tables in the underlying SQLite database. See inner exception for details.");
}
}
return(table);
}
private IEnumerable <string> GetTablesInOfflineTransactionDatabase(SqliteDatabaseContext databaseContext)
{
const int IndexOfName = 1;
List <string> databaseList = new List <string>();
SqlQuery queryDatabaseList = new SqlQuery("PRAGMA database_list;");
try
{
using (var result = databaseContext.ExecuteQuery(queryDatabaseList))
{
while (result.Read())
{
string databaseName = result.GetValue <string>(IndexOfName);
if (string.Compare(databaseName, "MAIN", StringComparison.OrdinalIgnoreCase) != 0 &&
string.Compare(databaseName, "TEMP", StringComparison.OrdinalIgnoreCase) != 0)
{
databaseList.Add(databaseName);
}
}
}
}
catch (DatabaseException exception)
{
throw new StorageException(
StorageErrors.Microsoft_Dynamics_Commerce_Runtime_CriticalStorageError,
(int)exception.ErrorCode,
exception,
"Failed to read from the database. See inner exception for details");
}
// There should be only 1 attached database associated with the connection. Test AssertSqliteDatabaseIntegrity checks this property.
string offlineTransactionDatabaseAlias = databaseList[0];
SqlQuery queryTableList = new SqlQuery(string.Format("SELECT NAME FROM [{0}].[SQLITE_MASTER] WHERE TYPE = 'table';", offlineTransactionDatabaseAlias));
ReadOnlyCollection <string> tableList = databaseContext.ExecuteScalarCollection <string>(queryTableList);
List <string> formalizedTableList = new List <string>();
foreach (string tableName in tableList)
{
formalizedTableList.Add(tableName.ToUpperInvariant());
}
return(formalizedTableList);
}
