/// <summary>
/// Appends Transaction-specific parameters to the specificied SqlCommand.
/// </summary>
/// <param name="command">SqlCommand to be executed.</param>
/// <param name="alert">Instance of Transaction class</param>
/// <param name="action">Database action type (select, insert, update, delete).</param>
public static void AddEntityParameters(this SqlCommand command, Transaction transaction, DbActionType action)
{
command.AddCommonParameters(transaction.Id, action);
command.Parameters.Add("@date", SqlDbType.DateTime).Value = transaction.Date;
command.Parameters.Add("@amount", SqlDbType.Decimal).Value = transaction.Amount;
command.Parameters.Add("@merchant", SqlDbType.NVarChar).Value = transaction.Merchant.ToDbValue();
command.Parameters.Add("@id_account", SqlDbType.Int).Value = transaction.AccountId.ToDbValue();
command.Parameters.Add("@id_category", SqlDbType.Int).Value = transaction.CategoryId.ToDbValue();
command.Parameters.Add("@description", SqlDbType.NVarChar).Value = transaction.Description.ToDbValue();
command.Parameters.Add("@notes", SqlDbType.NVarChar).Value = transaction.Notes.ToDbValue();
command.Parameters.Add("@type", SqlDbType.Int).Value = transaction.TransactionType;
command.Parameters.Add("@direction", SqlDbType.Int).Value = transaction.Direction;
command.Parameters.Add("@include_in_tax", SqlDbType.Bit).Value = transaction.IncludeInTax;
command.Parameters.Add("@tags", SqlDbType.NVarChar).Value = string.Join(Transaction.TAG_SEPARATOR, transaction.TagIds);
}
public virtual int Insert(Transaction entity, int? companyId)
{
return Insert(entity, companyId, true);
}
private int InsertUpdate(Transaction entity, int? companyId, DbActionType action, bool intoCache)
{
var cache = new CacheWrapper();
var res = TransactionsDataAdapter.InsertUpdate(entity, companyId, action);
if (res == 0)
{
//if ok - update cache
if (intoCache)
{
if (action == DbActionType.Insert)
cache.AddToList(CacheKey, entity, companyId);
if (action == DbActionType.Update)
cache.UpdateList(CacheKey, entity, companyId);
}
}
return res;
}
public virtual int Update(Transaction entity, int? companyId)
{
return InsertUpdate(entity, companyId, DbActionType.Update, true);
}
public virtual int Insert(Transaction entity, int? companyId, bool intoCache)
{
return InsertUpdate(entity, companyId, DbActionType.Insert, intoCache);
}
public Transaction Clone()
{
var res = new Transaction();
res.Date = Date;
res.Description = Description;
res.CategoryId = CategoryId;
res.CurrencyId = CurrencyId;
res.Amount = Amount;
res.Merchant = Merchant;
res.Notes = Notes;
res.TagIds = TagIds;
res.TransactionType = TransactionType;
res.Direction = Direction;
res.AccountId = AccountId;
res.IncludeInTax = IncludeInTax;
return res;
}
private static IEnumerable<Transaction> GetTestTrans(int? companyId)
{
var res = new List<Transaction>();
var trans = new Transaction()
{
AccountId = 1,
Amount = 100,
CategoryId = 221,
CurrencyId = 1,
Date = new DateTime(),
Description = "test1 Description",
Direction = TransactionDirections.Expense,
Id = 1,
IncludeInTax = false,
Merchant = "test1 Merchant",
Notes = "test Notes"
};
trans.TagIds.Add("1");
res.Add(trans);
trans = trans.Clone();
trans.Id = 2;
trans.Merchant = "find Merchant";
trans.Description = "new desc";
res.Add(trans);
trans = trans.Clone();
trans.Id = 3;
trans.CategoryId = 200;
trans.Description = "find Category";
trans.Merchant = "new Merchant";
res.Add(trans);
trans = trans.Clone();
trans.Id = 4;
trans.AccountId = 2;
trans.CategoryId = 210;
trans.Merchant = "test2 Merchant";
trans.Description = "find Account";
trans.TagIds = new List<string>();
trans.TagIds.Add("20");
res.Add(trans);
trans = trans.Clone();
trans.Id = 5;
trans.AccountId = 3;
trans.CategoryId = 1;
trans.Description = "find Tag";
trans.TagIds = new List<string>();
trans.Merchant = null;
trans.TagIds.Add("20");
trans.TagIds.Add("21");
res.Add(trans);
return res;
}
static void Main(string[] args)
{
//Can't load 900000-600000 from 1 key -> buffer to small
//Can't save 1300000 to 1 key -> ?
//50 mb
//Took 3531.25ms to store (300000 records in 1 key)
//Took 2953.125ms to load (300000 records from 1 key)
//Removed all keys from db
//Took 17765.625ms to store (300000 records in list)
//Took 3140.625ms to load (300000 records from list)
///************************************
//Took 5265.625ms to store (500000 records in 1 key)
//Took 4921.875ms to load (500000 records from 1 key)
//Removed all keys from db
//Took 29531.25ms to store (500000 records in list)
//Took 5359.375ms to load (500000 records from list)
//noute
//Took 4243,2427ms to store (300000 records in 1 key)
//Took 4480,2562ms to load (300000 records from 1 key)
//Took 4448,2544ms to load (1 item from 1 key): test Description _Description_0
//Removed all keys from db
//Took 30159,725ms to store (300000 records in list)
//Took 4279,2447ms to load (300000 records from list)
//Took 22,0013ms to load (1 item from 1 key): test Description _Description_100
//Removed all keys from db
//************************
//Testing Raven DB...
//Took 14101,8066ms to store (30000 records in 1 key)
//Took 10028,5736ms to load (30000 records from 1 key)
//tst redis
int totalRecords = 300000;
//int totalRecords = 30000;
var trans = new List<Transaction>();
for (int i = 0; i < totalRecords; i++)
{
trans.Add(new Transaction() { Description = "test Description _Description_" + i, Id = i });
}
var before = DateTime.Now;
//TimeSpan exp = new TimeSpan(0, 2400, 0);
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<List<Transaction>>();
typedRedis.SetEntry("test", trans);
}
Console.WriteLine("Took {0}ms to store ({1} records in 1 key)", (DateTime.Now - before).TotalMilliseconds, totalRecords);
before = DateTime.Now;
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<List<Transaction>>();
trans = typedRedis["test"];
}
Console.WriteLine("Took {0}ms to load ({1} records from 1 key)", (DateTime.Now - before).TotalMilliseconds, trans.Count);
before = DateTime.Now;
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<List<Transaction>>();
trans = typedRedis["test"];
}
Console.WriteLine("Took {0}ms to load (1 item from 1 key): {1}", (DateTime.Now - before).TotalMilliseconds, trans[0].Description);
using (var redisClient = new RedisClient())
{
redisClient.FlushAll();
}
Console.WriteLine("Removed all keys from db");
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<Transaction>();
var currStat = typedRedis.Lists["test"];
//trans.ForEach(x => currStat.Add(x));
currStat.AddRange(trans);
}
Console.WriteLine("Took {0}ms to store ({1} records in list)", (DateTime.Now - before).TotalMilliseconds, totalRecords);
before = DateTime.Now;
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<Transaction>();
var currStat = typedRedis.Lists["test"];
trans = currStat.GetAll();
}
Console.WriteLine("Took {0}ms to load ({1} records from list)", (DateTime.Now - before).TotalMilliseconds, trans.Count);
before = DateTime.Now;
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<Transaction>();
var currStat = typedRedis.Lists["test"];
var tr = new Transaction() { Id = 100 };
var res = currStat[currStat.IndexOf(tr)];
Console.WriteLine("Took {0}ms to load (1 item from 1 key): {1}", (DateTime.Now - before).TotalMilliseconds, res.Description);
}
using (var redisClient = new RedisClient())
{
redisClient.FlushAll();
}
Console.WriteLine("Removed all keys from db");
/*Console.WriteLine("trying to fill all memory");
using (var redisClient = new RedisClient())
{
var typedRedis = redisClient.GetTypedClient<Transaction>();
var currStat = typedRedis.Lists["test"];
int i = 0;
while (true)
{
var tr = new Transaction() { Description = "test Description _Description_" + i, Id = i };
currStat.Add(tr);
i++;
}
}*/
Console.ReadLine();
return;
//Raven DB
Console.WriteLine("Testing Raven DB...");
Console.WriteLine("");
var rtrans = new TransactionsList();
rtrans.Transactions = new List<Transaction>();
for (int i = 0; i < totalRecords; i++)
{
rtrans.Transactions.Add(new Transaction() { Description = "test Description _Description_" + i, Id = i });
}
var store = new DocumentStore { Url = "http://localhost:8080" };
store.Initialize();
before = DateTime.Now;
using (var session = store.OpenSession())
{
session.Store(rtrans, "trans");
session.SaveChanges();
}
Console.WriteLine("Took {0}ms to store ({1} records in 1 key)", (DateTime.Now - before).TotalMilliseconds, totalRecords);
before = DateTime.Now;
using (var session = store.OpenSession())
{
var order = session.Load<TransactionsList>("trans");
Console.WriteLine("Took {0}ms to load ({1} records from 1 key)", (DateTime.Now - before).TotalMilliseconds, order.Transactions.Count);
}
//Load of 300000:
//simple-4900ms
//list-4600ms
//Save of 300000:
//simple-3200ms
//list-15300ms
Console.ReadLine();
return;
}
public static Transaction GetTransaction(int? id, int? companyId)
{
Transaction res = null;
//TODO: check that trans belongs to company
using (var holder = SqlConnectionHelper.GetConnection())
{
using (var cmd = holder.Connection.CreateSPCommand("transactions_get"))
{
cmd.Parameters.AddWithValue("@id_company", companyId);
cmd.Parameters.AddWithValue("@id_transaction", id);
try
{
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
res = new Transaction(reader);
}
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
}
return res;
}
public static int InsertUpdate(Transaction entity, int? companyId, DbActionType action)
{
var res = -1;
using (var holder = SqlConnectionHelper.GetConnection())
{
var commName = action == DbActionType.Insert ? "transaction_create" : "web.transaction_update";
var cmd = holder.Connection.CreateSPCommand(commName);
try
{
cmd.AddEntityParameters(entity, action);
if (action == DbActionType.Update)
cmd.Parameters.AddWithValue("@id_company", companyId);
cmd.ExecuteNonQuery();
if (action == DbActionType.Insert)
entity.Id = cmd.GetRowIdParameter();
res = cmd.GetReturnParameter();
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
return res;
}
public static List<Transaction> GetTransactions(int? companyId)
{
var res = new List<Transaction>();
using (var holder = SqlConnectionHelper.GetConnection())
{
using (var cmd = holder.Connection.CreateSPCommand("transactions_get"))
{
cmd.Parameters.AddWithValue("@id_company", companyId);
try
{
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var trans = new Transaction(reader);
res.Add(trans);
}
}
}
catch (SqlException e)
{
cmd.AddDetailsToException(e);
throw;
}
}
}
return res;
}
