//Note: this version will pickup the providerProductId
public static void SaveAssetsBulk(List <AssetModel> assets)
{
int providerProductId;
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)
{
providerProductId = connection.Query <int>(@$ "select Id from ProviderProduct
WHERE Provider=@Provider
AND Product=@Product", new { item.ProviderProduct.Provider, item.ProviderProduct.Product }).First();
connection.Execute(sqlStatement, new { providerProductId, item.BluePrintPath }, transaction);
}
transaction.Commit();
}
catch (Exception e)
{
transaction.Rollback();
Log.Trace($"Bulk save in database for assets failed, rolled back", e, LogEventType.Error);
}
}
}
}
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);
}
}
}
}
/// <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 Int32 GetDatabaseRecordId()
{
try
{
using IDbConnection Db = new SQLiteConnection(AssetDatabaseAccess.GetConnectionString());
var output = Db.Query <Int32>($"select Id from ProviderProduct WHERE Provider='{Provider}' AND Product='{Product}'", new DynamicParameters());
return(output.First());
}
catch (Exception e)
{
Console.WriteLine(e);
throw;
}
}
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();
ProviderProductDatabaseCollectionModel.SaveProviderProductsBulk(providerProducts);
AssetDatabaseCollectionModel.SaveAssetsBulk(assets);
providerProducts = assets.Select(x => x.ProviderProduct).ToList();
// make sure all providerProducts have an Id
ProviderProductDatabaseCollectionModel.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
{
var db = new AssetDatabaseAccess();
// Get all routes
string sqlStatement = @$"SELECT * FROM Routes";
var output = db.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);
}
}
}
}