/// <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;
        }
Beispiel #8
0
        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;
 }