Query plan in MS SQL

Hello everybody,

some MS SQL internals.

Imagine, you want to see what MS SQL cached. How you can do it? One of the ways is to look into sys.dm_exec_cached_plans. 

For example like this:

select * from sys.dm_exec_cached_plans

this code will return to you all what is cached in your MS-SQL database. Most probably result will puzzle you. What to do with all of it?

Take look at the screenshot :

the area of interest for us is column plan_handle. 

You can use it for another sql request. Like this:

select * from sys.dm_exec_query_plan(0x06000C002D51D1304021F8AE000000000000000000000000);

if you click at column with xml you'll see the following picture:

How to interpret those results? I hope to describe in one of other notes on my blog.

For now another question, how to see the text of cached value? 

You can do it with another sql 

for example like this:

select * from sys.dm_exec_sql_text(0x06000C002D51D1304021F8AE000000000000000000000000)

in the column text you'll see column text which will represent what MS SQL cached. In my case it is following text:

select [first name], [last name], dense_rank() over (order by [last name]) as rn from employees

 

Moq and unit test

Hello everybody,

today I want to write few words about Moq and unit testing. 

First of all you can have a question, why do I need Moq at all?

Moq is especially useful for cases if we don't have desire to code interface, then code implementation of interface, and then code fake implementation. As usually those simple implementation are relatively simple so somebody ( as Moq team ) can organize code generation for your simple interface implementations.

So, unit test is something that should follow 3 A:

  1. Arrange
  2. Act
  3. Assert

General schema of Unit test implementation can be like this:

        [Test]
        public void SomeTest()
        {
            //Arrange
            var mockRepository = new Mock<IBaseInterface>();
            mockRepository.Setup(x => x.FunctionInIBaseInterface(It.IsAny<SomeClass>()));
            
            var classWhichUsesImplementationOfinterface = new ClassWhichUsesImplementationOfinterface (mockRepository.Object);

            //Act
            classWhichUsesImplementationOfinterface.Create(new SomeClassWithEmptConstructor());

            //Assert
            mockRepository.VerifyAll();
        }            

The most intersting part for me was mockRepository.Setup(x => x.FunctionInIBaseInterface(It.IsAny<SomeClass>())); Setup here can be considered as saying: setup this expectation.  This code arranges setup for a calling to a void method. But void method inside of it should call function FunctionInIBaseInterface.  In other case unit test will fail.

How many times some function was executed. 

 someRepository.Verify(x=>x.SomeFunction(It.IsAny<SomeClass>()), Times.Exactly(5));

This code will check that SomeFunction was executed exactly 5 times and it expects instane of any SomeClass class.

Another usefull functions for unit testing

Setup function after dot has interesting list. 

I'll mention them from simplest ( from my viewpoint ) to most complicated.

mockRepository

                    .Setup(x => x.TryParse(It.IsAny<string>(), out address))

 

                    .Returns(true);

this setup will configure expectation that function TryParse will be executed and expects that function TryParse will return true.

In order to execute it your implementation should call this function.

Very surprising is usage of Callback. Look at this code:

 

var i = 1;

mockIdFactory.Setup(x => x.Create())

       .Returns(() => i)

 

       .Callback(() => i++);

This code will cause passing Create vales for mockIdFactory not just one value, but ints which will be incremented one by one.

 

Some bootstrap fundamentals

Hello everybody,

today I want to write some moments about bootstrap.

If to speak in general bootstrap is beast which has some javascript and css code. 

One of the ways to modify behaviour of bootstrap is to add reference to bootstrap style, and then add reference to your customization of css in which you can change boostrap settings with overriding basic bootstrap code. For example you can achieve it in the following way:

<link rel="stylesheet" href="css/bootstrap.min.css">
<link rel="stylesheet" href="css/yourCustomized.css">

One more interested features of bootstrap is grid layout of something. 
If in your html you'll write something like this:

<section id="body" class="container">
then you section body will have 12 cells. And you can manage postion and size of cell with help of bootstrap classes. 

The following options are awailable:
  • col-lg-* - screens which are wider or equal then 1200 px
  • col-md-* screens which are wider or equal then 992 px
  • col-sm-* screens which are wider or equal then 768 px
  • col-xs-* screens which are smaller or equal then 768 px

Another interesting feature is building architecute of bootstrap. It is build with mobile first approach. If to put simply it means that it was built initially for mobile devices with small screens ( screen smaller then 768 px ), then with bigger ( more then 768 px ), more bigger ( more then 992 px ) and large ( more then 1200 px ). Try to guess from which style life of bootstrap started?

So, what is value of all of this staff?

One of the most interesting features is option of defining look and feel of your "cells" at each platform.

For example you can write something that will be applied to four platform for which bootstrap was initially builted:

class="col-lg-3 col-md-6 col-sm-4 col-xs-3"

That statement will be interpreted in the following way:

locate item with that class as third column on large screen, as six column on middle screen at fourth column at small screen and as thir column at extra small screen.

Other usefull classes of bootstrap which are particularly useful for images:

pull-left - locate image with binding to left side

pull-right - locate image with binding to right side

img-thumbnail - display image as thumbnail instead of displaying at as full size image

hidden-md - don't display image at middle size screen

visible-md - always display image at middle size screen.

Very usefull site bootswatch.com - it has free bootstrap themes, which you can use for free without fear of court suit against you.

Override Equals method of value types

Hello everybody.

Today I want to give some demo.

using System;
using System.Diagnostics;

namespace StackOverflowQuest
{
    class Program
    {
        struct StructTest
        {
            public string TestString { get; set; }

            //public override bool Equals(object obj)
            //{
            //    var ct = (StructTest)obj;
            //    return ct.TestString == this.TestString;
            //}
        }
        class ClassTest
        {
            public string TestString { get; set; }

            public override bool Equals(object obj)
            {
                var ct = obj as ClassTest;
                if (ct == null)
                    return false;
                return ct.TestString == this.TestString;
            }
        }

        static void Main(string[] args)
        {
            StructTest st = new StructTest() { TestString = "water"};
            StructTest st2 = new StructTest() { TestString = "water" };

            ClassTest ct1 = new ClassTest() { TestString = "water" };
            ClassTest ct2 = new ClassTest() { TestString = "water" };

            int numberOfIterations = 500000;

            Stopwatch sw2 = new Stopwatch();
            sw2.Start();
            for (int i = 0; i < numberOfIterations; i++)
            {
                ct1.Equals(ct2);
            }
            sw2.Stop();
            Console.WriteLine("class perfomance = {0} Elapsed Milliseconds", sw2.ElapsedMilliseconds);

            Stopwatch sw1 = new Stopwatch();
            sw1.Start();
            for(int i = 0; i < numberOfIterations; i++)
            {
                st.Equals(st2);
            }
            sw1.Stop();
            Console.WriteLine("structs perfomance = {0}  Elapsed Milliseconds", sw1.ElapsedMilliseconds);
            Console.ReadKey();
        }
    }
}

Take note of commented method of Equals method. If I execute that code on my machine, I'm getting difference in perfomance in 6 times. But if to uncomment Equals, then class and struct have comparable perfomance.

How to set current record in Acumatica

Hello everybody,

today I want to share one trick which can save you few lines of code.

Imagine case: you created graph, and want to read something from db, and then set current property of that graph view to some value. How to do it in one line? The anser is simple, with help of Search<>

For example it can be like this:

public PXSelectOrderBy<SalesOrder, OrderBy<Asc<SalesOrder.orderNbr>>> Orders;
...
graph.Orders.Current = graph.Orders.Search<SalesOrder.orderNbr>(order.OrderNbr);

Inheritance strategy in code-first

Hello everybody,

today I want to write about reflecting hierarchy of classes in db with help of code-first.

There are three types: 

  1. Table per Hierarchy
  2. Table per Type
  3. Table per conreate class.

Below goes more detailed description

 

  • Table per Hierarchy (TPH): one table for the entire class inheritance hierarchy. Table includes discriminator column which distinguishes between inheritance classes. This is a default inheritance mapping strategy in Entity Framework.
  • Table per Type (TPT): This approach suggests a separate table for each domain class.
  • Table per Concrete class (TPC): one table for one concrete class, but not for the abstract class. So, if you inherit the abstract class in multiple concrete classes, then the properties of the abstract class will be part of each table of the concrete class.

IDisposable pattern for C# objects

Hello everybody,

today I want to share my usage if IDisposable interface in C#.

Before code presentation some explanations:

1. If created by me class uses some unmanaged resources then it means that I should also implement IDisposable interface in order to clean memory. 
2. Clean objects as soon as I finished usage of it. 
3. In my dispose method I iterate over all IDisposable members of class and call Dispose.
4. In my Dispose method call GC.SuppressFinalize(this) in order to notify garbage collector that my object was already cleaned up. I do it because calling of GC is expensive operation.
5. As additional precaution I try to make possible calling of Dispose() multiple times.
6. Sometime I add private member _disposed and check in method calls did object was cleaned up. And if it was cleaned up then generate ObjectDisposedException 

 

public class SomeClass : IDisposable
        {
            /// <summary>
            /// As usually I don't care about it
            /// </summary>
            public void SomeMethod()
            {
                if (_disposed)
                    throw new ObjectDisposedException("SomeClass instance been disposed");
            }

            public void Dispose()
            {
                Dispose(true);
            }

            private bool _disposed;

            protected virtual void Dispose(bool disposing)
            {
                if (_disposed)
                    return;
                if (disposing)//we are in the first call
                {
                }
                _disposed = true;
            }
        }

 

Search for duplicates

Hello everybody,

Here is just shorp version of application which searches for duplicates in folder.

In  downloadable archive you can find my implementation of searcher with Task, ContinueWith and some other new features of C# 4.5

How to filter add some info to filter

Hello everybody.

Today I want to share notice of how to work with processing screens.

But of course not in very standard situation. One of my clients requested me the following: he wanted at processing screen to select some items in screen in part 1, then monitor 

results in part two, and see some totals in part three. 

As you understand it's not easy to ahcieve this goal only with help of selectors. It is possible with usage of delegates in Acumatica and with usage of it's cache.

Initial initialization of page was the following:

 [Serializable]
    public class RRCashRequestProcessor : PXGraph<RRCashRequestProcessor>
    {
        public PXCancel<CreatePaymentFilter> Cancel;
        public PXFilter<CreatePaymentFilter> FilterApprover;

        [PXFilterable]
        public PXFilteredProcessingJoin<UsrRRCashRequest, CreatePaymentFilter,
            InnerJoin<UsrRRCashBudgetItem, On<UsrRRCashBudgetItem.itemRefNbr, Equal<UsrRRCashRequest.budgetItemRefNbr>>>, 
                Where<UsrRRCashRequest.branchID, Equal<NonExistentBranch.nonBranch>>> CashRequests;

        public RRCashRequestProcessor()
        {
            CashRequests.SetProcessDelegate(RequestCasheList);
        }

And then I've got a problem, how to put some data into FilterApprover DAC from CashRequests DAC if to keep in mind that top of the screen is used for making bottom. 

For such cases it's useful to use this.Caches object of graph or Base.Caches if you work with extension graphs. Take note how I filled Total Generated value of column:

 

protected IEnumerable filterApprover()
{
      PXCache cache = this.Caches[typeof(CreatePaymentFilter)];
      if (cache != null)
      {
          CreatePaymentFilter filter = cache.Current as CreatePaymentFilter;
          if (filter != null)
          {
                var cashRecs = CashRequests.Select().Select(a => a.GetItem<UsrRRCashRequest>());
                filter.TotalGenerated = cashRecs.Sum(a => a.Amt ?? 0);
          }
       }

      yield return cache.Current;
}

How to make quick estimate

Hello everybody,

today I want to write a notice about Project estimation. One of the important facetcs of making estimates is to make document, in which to describe definitions of project, which need to be estimated.

For this purpose I will create Word document with title page, table of contents and following categories:

In order to make good estimate it's good to clarify with potential stakeholder following categories:

  1. Purpose
  2. Goals/Objectives
  3. Success Criteria
  4. What's in/out of scope
  5. Assumptions
  6. Recommended Approach
  7. Business Drivers
  8. Budget
  9. Schedule
  10. Return on Investment
  11. Constraints
  12. Risks
  13. Stakeholders
  14. Preliminary Schedule
  15. Preliminary Estimates
  16. Policies and Standards
  17. Legal Regulatory
  18. Competitor Drivers

If to clarify those matters with potential product owner then to make estimate will be much easier