public static void UpdateListIdsFromDatabase(List <ProviderProductModel> providerProducts)
{
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "SELECT Id FROM ProviderProduct WHERE Provider=@Provider AND Product=@Product;";
try
{
foreach (var item in providerProducts)
{
IdModel idModel = connection.Query <IdModel>(sqlStatement, new { item.Provider, item.Product }, transaction).First();
item.Id = idModel.Id;
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Log.Trace($"Something went wrong during bulk getting id's for ProviderProduct from database", ex, LogEventType.Error);
}
}
}
}
private static void UpdateBulkStatus(List <ProviderProductModel> providerProducts, string fieldName)
{
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "UPDATE OR IGNORE ProviderProduct SET {fieldName}=1 WHERE Id=@Id";
try
{
foreach (var item in providerProducts)
{
if (item.Id <= 0)
{
Log.Trace($"Internal error. Id for ProviderProduct not set in {item}", LogEventType.Error);
throw new InvalidDataException($"Internal error. Id for ProviderProduct not set in {item}");
}
connection.Execute(sqlStatement, new { item.Id }, transaction);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Log.Trace($"Something went wrong during bulk update InGame/InArchive field ProviderProduct to database", ex, LogEventType.Error);
}
}
}
}
/// <summary>
/// Reads all provider products from the database.
/// </summary>
/// <returns>List<ProviderProductModel>.</returns>
public static List <ProviderProductModel> ReadAllProviderProductsFromDatabase()
{
using IDbConnection Db = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString());
var output = Db.Query <ProviderProductModel>("SELECT * FROM ProviderProduct", new {});
return(output.ToList());
}
public static void SaveAssetsBulk(List <AssetModel> assets, int providerProductId)
{
AssetModel itemForLog = new AssetModel();
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "INSERT OR IGNORE INTO Assets (ProvProdId, BluePrintPath)
VALUES (@providerProductId, @BluePrintPath )";
try
{
foreach (var item in assets)
{
itemForLog = item;
connection.Execute(sqlStatement, new { providerProductId, item.BluePrintPath }, transaction);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Log.Trace($"Bulk save in database for assets failed {itemForLog}, rolled back", e, LogEventType.Error);
}
}
}
}
//Note: this version will pickup the providerProductId
public static void SaveAssetsBulk(List <AssetModel> assets)
{
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "INSERT OR IGNORE INTO Assets (ProvProdId, BluePrintPath)
VALUES (@providerProductId, @BluePrintPath)";
try
{
foreach (var item in assets)
{
var providerProductId = connection.Query <int>(@$ "select Id from ProviderProduct
WHERE Provider=@Provider
AND Product=@Product", new { item.ProviderProduct.Provider, item.ProviderProduct.Product }).First();
var result = connection.Execute(sqlStatement, new { providerProductId, item.BluePrintPath }, transaction);
item.IsNew = result > 0; // DEBUG
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Log.Trace($"Bulk save in database for assets failed, rolled back", e, LogEventType.Error);
}
}
}
}
private static void GetScenarioIds(List <ScenarioModel> scenarioList)
{
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "SELECT Id FROM Scenarios WHERE ScenarioGuid= @ScenarioGuid;";
try
{
foreach (var item in scenarioList)
{
IdModel idModel = connection.Query <IdModel>(sqlStatement, new { item.ScenarioGuid }, transaction).First();
item.Id = idModel.Id;
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Log.Trace($"Something went wrong during getting id's for Scenarios from database", ex, LogEventType.Error);
}
}
}
}
public static List <FlatAssetModel> ReadAllAssetsFromDatabase()
{
using IDbConnection Db = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString());
var output = Db.Query <FlatAssetModel>("SELECT * FROM BluePrintView", new { });
return(output.ToList());
}
public static void SaveScenariosBulkList(List <ScenarioModel> scenarioList, Boolean inGame, Boolean inArchive)
{
string fieldName = Converters.LocationToString(inGame, inArchive);
using (IDbConnection connection =
new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement =
@$ "INSERT OR IGNORE INTO Scenarios (ScenarioGuid, ScenarioTitle, ScenarioClass, RouteId, Pack, IsPacked, IsValid{fieldName}) VALUES (@ScenarioGuid, @ScenarioTitle, @ScenarioClass, @RouteId, @Pack, @IsPacked, @IsValid{fieldName})";
try
{
foreach (var item in scenarioList)
{
connection.Execute(sqlStatement, new { item.ScenarioGuid, item.ScenarioTitle, item.ScenarioClass, item.RouteId, item.Pack, item.IsPacked, item.IsValidInGame, item.IsValidInArchive },
transaction);
}
transaction.Commit();
GetScenarioIds(scenarioList);
UpdateBulkStatus(scenarioList, fieldName);
}
catch (Exception ex)
{
transaction.Rollback();
Log.Trace($"Something went wrong during bulk save Routes to database", ex,
LogEventType.Error);
}
}
}
}
public static List <ScenarioModel> ReadScenariosFromDatabase(string routeGuid)
{
var routeId = RoutesCollectionDataAccess.GetRouteId(routeGuid);
using IDbConnection Db = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString());
var output = Db.Query <ScenarioModel>("SELECT * FROM Scenarios WHERE RouteId=@routeId", new { routeId });
return(output.ToList());
}
public static void UpdateBulkStatus(List <AssetModel> assetList, string fieldName)
{
AssetModel itemForLog = new AssetModel();
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "UPDATE OR IGNORE Assets SET {fieldName}=1 WHERE Id=@Id";
try
{
foreach (var item in assetList)
{
itemForLog = item;
if (item.Id <= 0)
{
item.Id = GetAssetIdFromDatabase(item, connection, transaction);
}
if (item.Id <= 0)
{
// if we now do not have an Id, we have a big problem, the record is not in the database.
Log.Trace($"Internal error. Id for Assets not set in {item}", LogEventType.Error);
throw new InvalidDataException($"Internal error. Id for Assets not set in {item}");
}
try
{
var result = connection.Execute(sqlStatement, new { item.Id }, transaction);
if (result != 1)
{
Log.Trace($"Location not updated for {item}", LogEventType.Error);
}
}
catch (Exception ex)
{
Log.Trace($"Location update error {item} skipped", ex, LogEventType.Message);
}
}
//transaction.Commit();
}
catch (Exception ex)
{
// transaction.Rollback();
// Log.Trace($"Something went wrong during bulk update {itemForLog.ProviderProduct.ProviderProduct} {itemForLog.BluePrintPath} {itemForLog.Id} {itemForLog.ProviderProduct.Id} InGame/InArchive field Assets to database", ex, LogEventType.Error);
Log.Trace($" {itemForLog.Id} {itemForLog.ProviderProduct.Id} InGame/InArchive field Assets to database", ex, LogEventType.Error);
}
}
}
}
private static void SaveRouteAssetsBulkToDatabase(RouteModel route,
List<RouteAssetsModel> routeAssets)
{
// Step 1: add all assets tot he assets table
// Step 2: insert provider products in the table
// Step 3: get for each asset the id from the assets table (all assets will be there)
// Step 4: insert route Id and asset id in the
List<AssetModel> assets = routeAssets.Select(x => x.Asset).ToList();
List<ProviderProductModel> providerProducts =
routeAssets.Select(x => x.Asset.ProviderProduct).DistinctBy(x => x.ProviderProduct)
.ToList();
ProviderProductCollectionDataAccess.SaveProviderProductsBulk(providerProducts);
AssetCollectionDataAccess.SaveAssetsBulk(assets);
providerProducts = assets.Select(x => x.ProviderProduct).ToList();
// make sure all providerProducts have an Id
ProviderProductCollectionDataAccess.UpdateListIdsFromDatabase(providerProducts);
// Bulk insert
using (IDbConnection connection =
new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$"INSERT OR IGNORE INTO RouteAssets (RouteId,AssetId)
SELECT Routes.Id, Assets.Id FROM Routes, Assets
WHERE Routes.Id= (SELECT Id FROM Routes WHERE RouteGuid= @RouteGuid) AND
Assets.Id= (SELECT Id FROM Assets WHERE BlueprintPath=@BluePrintPath AND
Assets.ProvProdId=@Id); ";
try
{
foreach (var item in routeAssets)
{
connection.Execute(sqlStatement, new { item.Route.RouteGuid, item.Asset.BluePrintPath, item.Asset.ProviderProduct.Id }, transaction);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Log.Trace($"Bulk save in database for assets failed, rolled back", e,
LogEventType.Error);
}
}
}
}
public static List<RouteModel> LoadRoutesToList()
{
try
{
// Get all routes
string sqlStatement = @$"SELECT * FROM Routes";
var output = AssetDatabaseAccess.LoadData<RouteModel, dynamic>(sqlStatement, new { },
AssetDatabaseAccess.GetConnectionString());
if (output.Count == 0)
{
Log.Trace("No routes found in database. You need to save routes before using them.",
null, LogEventType.Message);
}
return output;
}
catch (Exception e)
{
Log.Trace("Failed to load routes from database to list", e, LogEventType.Error);
throw;
}
}
/// <summary>
/// Bulk save into database for ProviderProductModels, using transaction
/// </summary>
/// <param name="providerProducts">List of ProviderProducts</param>
public static void SaveProviderProductsBulk(List <ProviderProductModel> providerProducts)
{
using (IDbConnection connection = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString()))
{
connection.Open();
using (IDbTransaction transaction = connection.BeginTransaction())
{
string sqlStatement = @$ "INSERT OR IGNORE INTO ProviderProduct (Provider, Product, Pack) VALUES (@Provider, @Product, @Pack)";
try
{
foreach (var item in providerProducts)
{
connection.Execute(sqlStatement, new { item.Provider, item.Product, item.Pack }, transaction);
}
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Log.Trace($"Something went wrong during bulk save ProviderProduct to database", ex, LogEventType.Error);
}
}
}
}