Friday, November 29, 2019

Bulk Insert With Dapper - The Right One

One of my favorite online trainers, Tim Corey, introduced Dapper for me two years ago. It was only the last week when I stumbled upon his advanced Dapper video, and implemented the correct version for a bulk insert, using this micro-ORM.

Prerequisites:
- install Dapper nuget package;
- intermediate C#/with MS SQL database;

The task of our bulk insert will be to insert the following data into an MS SQL database:

a. Table structure in SQL, defined by the query:

CREATE TABLE [dbo].[InvoiceSummary](
[id] [int] IDENTITY(1,1) NOT NULL,
[Inv_Number] [int] NOT NULL,
[IssueDate] [datetime] NOT NULL,
[BillingCode] [nvarchar](100) NOT NULL,
[EntityName] [nvarchar](200) NOT NULL,
[BUName] [nvarchar](100) NOT NULL,
[Value] [float] NOT NULL,
[VAT] [float] NOT NULL,
[TotalValue] [float] NOT NULL,
[Currency] [nvarchar](50) NOT NULL,
[ExchangeRate] [float] NOT NULL,
[Entity_Id] [int] NOT NULL,
[BU_id] [int] NOT NULL,
[UpdatedBy] [nvarchar](100) NOT NULL,
[UpdatedAt] [datetime] NOT NULL,
[PeriodID] [nvarchar](50) NOT NULL,
[Comments] [nvarchar](200) NOT NULL,
[Comment] [nvarchar](200) NOT NULL,
[Status] [nvarchar](50) NOT NULL,
[AttentionOf] [nvarchar](300) NOT NULL,
[CC] [nvarchar](300) NULL,
[HFMCode] [nvarchar](100) NULL,
 CONSTRAINT [PK_InvoiceSummary] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[InvoiceSummary] ADD  CONSTRAINT [DF_InvoiceSummary_UpdatedAt]  DEFAULT (getdate()) FOR [UpdatedAt]
GO

b. There is a type defined, for the table-valued parameter, see SQL statement below:

CREATE TYPE [dbo].[BasicUDT] AS TABLE(
[id] [int] NOT NULL,
[Inv_Number] [int] NOT NULL,
[IssueDate] [datetime] NOT NULL,
[BillingCode] [nvarchar](100) NOT NULL,
[Entity_Id] [int] NOT NULL,
[BU_id] [int] NOT NULL,
[EntityName] [nvarchar](200) NOT NULL,
[BUName] [nvarchar](100) NOT NULL,
[Value] [float] NOT NULL,
[VAT] [float] NOT NULL,
[TotalValue] [float] NOT NULL,
[Currency] [nvarchar](50) NOT NULL,
[ExchangeRate] [float] NOT NULL,
[Comments] [nvarchar](200) NOT NULL,
[AttentionOf] [nvarchar](300) NOT NULL,
[CC] [nvarchar](300) NULL,
[HFMCode] [nvarchar](100) NULL,
[UpdatedBy] [nvarchar](100) NOT NULL,
[UpdatedAt] [datetime] NOT NULL,
[PeriodID] [nvarchar](50) NOT NULL,
[Comment] [nvarchar](200) NOT NULL,
[Status] [nvarchar](50) NOT NULL
)
GO

c. Stored procedure, to insert into our table, using table valued parameter:

CREATE procedure [dbo].[spInvoiceSummaryInsertSet]
@invsummary BasicUDT readonly
as
begin
set nocount on;
INSERT INTO [dbo].[InvoiceSummary]
           ([Inv_Number]
           ,[IssueDate]
           ,[BillingCode]
           ,[Value]
           ,[VAT]
           ,[TotalValue]
           ,[Currency]
           ,[ExchangeRate] 
           ,[Entity_Id]
           ,[BU_id]           
           ,[UpdatedBy], UpdatedAt
           ,[PeriodID]
           ,[Comment]
           ,[Status],
    [Comments]           
           ,[AttentionOf]
           ,[CC]
           ,[HFMCode],
   [EntityName],
   [BUName]
   )
SELECT [Inv_Number]
           ,[IssueDate]
           ,[BillingCode]
           ,[Value]
           ,[VAT]
           ,[TotalValue]
           ,[Currency]
           ,[ExchangeRate] 
           ,[Entity_Id]
           ,[BU_id]           
           ,[UpdatedBy], CURRENT_TIMESTAMP
           ,[PeriodID]
           ,[Comment]
           ,[Status],
    [Comments]           
           ,[AttentionOf]
           ,[CC]
           ,[HFMCode],
   [EntityName],
   [BUName]
from @invsummary


end;

d. Our original list in C# will contain the values we need to insert into the table:

// myInvList is a list of InvoiceSummary items defined by the class above at a)
InvoiceSummaryInsertSet(myInvList)

e. Our list is getting inserted into SQL Server DB using the table valued parameter

        public void InvoiceSummaryInsertSet(List<InvoiceSummary> myInvList)        {

            var dt = new ExcelServices().ConvertToDataTable(myInvList);

            using (IDbConnection connection = new System.Data.SqlClient.SqlConnection(connectionString))
            {
                var p = new
                {
                    invsummary = dt.AsTableValuedParameter("BasicUDT")
                };

                connection.Execute("dbo.spInvoiceSummaryInsertSet ", p, commandType: CommandType.StoredProcedure);
            }
        }

f. helper function to transform a list into datatable, used above, is implemented as below:

        public System.Data.DataTable ConvertToDataTable<T>(IList<T> data)

        {
            PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));

            System.Data.DataTable table = new System.Data.DataTable();

            foreach (PropertyDescriptor prop in properties)
            {
                table.Columns.Add(prop.Name, Nullable.GetUnderlyingType(prop.PropertyType) ?? prop.PropertyType);
            }

            foreach (T item in data)

            {

                DataRow row = table.NewRow();

                foreach (PropertyDescriptor prop in properties)
                {
                    row[prop.Name] = prop.GetValue(item) ?? DBNull.Value;             

                }

                table.Rows.Add(row);
            }

            return table;

        }

That's all, it works ! Hopefully it helped some of you.

Thursday, November 28, 2019

The Challenges of Writing Business Apps/Tools


As mentioned in my previous posts on this blog, my developer journey began as an intersection between the corporate world (accounting) and programming. I used to support my own accounting processes and help others with various VBA/Excel, Access, SQL tools and that's how the journey began.

Outgrowing my role as a Management Accountant in this technological sense, in 2019 I began studying and writing full time additional applications for business/accounting problems.

The basic idea of the tools is automating Excel-based processes in a more coherent database-app approach. There are a lot of struggles in my journey, but and I have learnt a lot, especially .Net technologies and SQL, and in the meantime, the web-stack using JavaScript and lately ASP.NET Core/Razor pages.


Clearly, the biggest challenge in all these is the mapping of real world processes (based on Excel and manual work) to an application. What's needed for this to be successful? In no particular order, my conclusions are:
1. very good understanding of the processes. This takes time and lots of communication/questions.
2. Understanding inputs and outputs. Data structure, types, formats, steps, validation, internal checks of the applications, stages of completion, reports, application logic and flow all come from inputs and outputs.
3. Thinking about the entity/DB structure and program flow.
4. From the mental mapping above come the DB structure, and then flow. Usually the flow is the succession of operations: input data, review data, edit, delete, search, etc, add more data, filter, prepare intermediary reports and then further add data, filter, edit if needed and then generate final outcome (reports, files). This cycle above can have multiple stages and checks/reports built within.
5. Validate the proposed process with the customer.
4. Discussing old methods vs new optimized process: lessons learned and areas to improve on in the new system based on feedback.

My big projects in the last 2 years are:
1.  Manufacturing Reporting/Statistics tool in MS ACCESS, on key phases of the production line. (manufacturing shop floor).
2.  Internal recharge system of the company, for distribution of indirect/administrative costs. Technology: C#, .Net, WPF/MVVM and SQL Server
3. My current project is an invoicing software that collects and invoices costs by employees. Using same technology as above. + Asp.net Core Razor pages
4. Another project for 2020 will be a VAT reporting tool to automate an Excel-based approach inside the accounting department, consolidating, filtering, automating data collection from several internal business units (big departments), and preparing statutory reports for Value Added Tax.

Some clear lessons to change the Excel-Based approach into a more efficient application are:
1. Using one database instead of many files, on a central server/Azure, for each app.
2. Mapping the entities from the excel files => as database tables/ and POCO entities.
3. All the automation of the repetitive processes go to C# code = > helper, data manipulation functions, using LINQ with lists etc.
4. Report generation, using ReportViewer tool, or ClosedXML to export excel files upon request;
5. Code re-usability.

Some architectural conclusions of the journey are:
1. need to manage the users of the application
2. implementing a reporting system, with parametrised queries, using SQL/Dapper/Reportviewer and/or simple Excel reports
3. implementing a concept of "Period": the accounting month in which all transactions take place. Afterwards the period is closed, and next month is opened for another series of transactions.
4. Implementing different types of Tables / entities by functionality:
  • tables with reduced number of updates such as business units, customers, users
  • tables with some  more frequent updates (such as employees, price lists)
  • transaction tables (allocation keys calculated, monthly cost information, monthly invoice details, etc)
5. For transaction tables, insert the user and transaction date/time 
6. In some special cases, implement a history table for some entities for which the change log is important  (such as employee change history).
What do you think, what are the main points of producing such business/accounting tools? Would you do something differently from an architecture standpoint?


Monday, November 18, 2019

Why Microsoft Excel is So Popular - My Opinion



MS Excel is the most popular member of the Office family. Nothing can rival its popularity, and still today has no contender in the application world. Google Sheets, Libre Office, Open Office, are just pale imitations of what it can do. We are in the age of database applications, Sharepoint, Mobile and Web apps, and still, MS Excel flourishes.

As a former Management accountant myself, I used MS Excel a lot. Even having many internal tools, applications and databases, Excel was the most widely used, just for everything.


Still, it has some disadvantages that makes an application more viable, and I'll explore that later.

So, why is Excel so popular:
- no training is needed, you just open a sheet and type in information, write easy formulas
- no additional applications need to be installed (frameworks, databases, run-times)
- the file format is universal. Linux and Mac OS, mobile phones, web apps all can read and write to Excel.
- all kinds of activities can be done in Excel, such as:
  • store data. Its sheets are like database tables, you can save data in them. Thousands of rows, and Excel can manage them quickly.
  • filter, find, summarize, report on data. Filters, pivots, charts are very easy mechanism to analyze and search for data. No other tool can rival the ease of doing this.
  • Formulas - it lets you build scenarios, budgets, calculate, optimize and simulations. The flexibility is just unparalleled.
  • VBA : you can automate your work with programming. Very easy, using Visual Basic language.
  • Lots of other functionalities: statistical functions/packages, connections with databases, and the web, etc
  • Extreme flexibility: add comments, edit fields, change formulas, with a click.

Still, Excel has some disadvantages, mostly coming from its advantages:
  • cannot deal with big volumes of data. Starting with 10k rows in a sheet, things become slow and unpractical.
  • extreme flexibility has a cost. Data can be changed, formulas erased, information lost... and sharing the data is not efficient, leading to duplication, misunderstanding. The risks of running lots of complex data in Excel are very high.
  • Filtering and data search functions still have some limitations. SQL, in my opinion, is more powerful. (and harder to write, also)
  • VBA, the programming language and system of macros behind the data sheets is not efficient and clean code, scalability is an issue. You can use for simple things,but when there are multiple users, lots of data, complex business logic, a modern language such as C# can deal with these in a better way.
 
Somebody said that the 70% of all business logic of companies is in Excel. Working 15 years in a corporate environment (4 big companies), I can confirm this. Excel is the first option, when it comes to data analysis, reporting etc.

Do other applications have any chance to develop and flourish? Can they help to automate things and make life easier in a company? Yes and no.

Let's have a look to the possibilities for substituting MS Excel with an application.
Pros:
- in the case where the rules are clear and established, and the volume of data is big, an application makes more sense. 
Cons:
- where the data is small, and/or the rules are changing and much flexibility is needed, Excel is still a better solution.

So what's the right approach to substitute the bad part of Excel? Probably understanding the constant, rule-based, or algorithmic part of business processes, and write applications for them, while still using Excel in parallel to solve smaller, flexible tasks.


What do you think? Would you write an application to make Excel based processes more efficient?


Tuesday, November 12, 2019

Working from Home as a Developer

Remote work is trending in the last couple of years. And more and more, regular office jobs in big companies get days to work from home, even if they are not from the Tech area. As I was a corporate accountant before, I never experienced home-office or remote work before, but this year was the beginning!

I've been working from my home office since March 2019, and let me share my experiences.
First of all, I needed to make sure I can work comfortably from my home office desk, and for this I have a big screen monitor with a docking station and a good 14" laptop.

What are the advantages?
  • I can manage my time. Normally there is no pressure, I can prioritize my work and day.
  • No workplace stress, conflicts with coworkers, micro-managing boss etc. This is very appealing!
  • I can make as many breaks, or I can work as long as I wish.
  • I can listen to music, in the background.
  • I communicate by email, so there is not much interruption during the day.
  • I can make a big break in the middle of day to do the groceries, gardening, swimming, shopping etc
  • I can take days off or even a mini-vacation without disrupting my development process.
But there are disadvantages, also:
  • I need to plan my week to know what are the priorities. Nobody will tell this to me.
  • I have to be very careful with the pace. Not too much work, not too less. 
  • I really need to do some exercise, otherwise my concentration is going down.
  • There is a risk that I eat too much and have too less exercise => I might gain some weight.
  • Some isolation and loneliness, which I try to balance with exercise and reading, and study of new instrument.
  • Distraction - looking to youtube videos or just doing other things than work.
  • Fear that I will be left behind in terms of technology.
  • Not much technical supervision and mentoring, I need to research all the unknown technical stuff for the project. This requires some confidence and courage to find workarounds if the ideal solution is not found.

What do you think, would you work as a remote developer? Or anything, from home?