/// <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; }