2023-02-14

Code►C#

C# Distinct objects

In the case, I want to remove the duplicate objects of the list.
I use the object key, UserId and Type, to distinguish them in the list.
After I distinct the objects, the count of the list becomes 2.
The numbers of the list are 2023021403 and 2023021401.

using System;
using System.Collections.Generic;
using System.Linq;

namespace App
{
    internal class Program
    {
        private class OrderModel
        {
            public int UserId { get; set; }
            public int Type { get; set; }
            public string Number { get; set; }
        }

        static void Main(string[] args)
        {
            var orders = new List<OrderModel>
            {
                new OrderModel
                {
                    UserId = 1,
                    Type = 1,
                    Number = "2023021401"
                },
                new OrderModel
                {
                    UserId = 1,
                    Type = 1,
                    Number = "2023021402"
                }
                ,
                new OrderModel
                {
                    UserId = 1,
                    Type = 2,
                    Number = "2023021403"
                }
            };

            var distinctList = orders
                               .GroupBy(x => new { x.Type, x.UserId })
                               .Select(x => x.FirstOrDefault())
                               .ToList();

            foreach (var o in distinctList)
            {
                Console.WriteLine($"Number:{o.Number}");
                Console.WriteLine($"UserId:{o.UserId}");
                Console.WriteLine($"Type:{o.Type}");
                Console.WriteLine("===");
            }
        }
    }
}

Output:

Number:2023021401
UserId:1
Type:1
===
Number:2023021403
UserId:1
Type:2
===

2023-02-03

Database►SQL Server

Simplify queries in SQL Server using CTE

If you use queries in SQL, you could involve complex statements that could cause some maintenance or reading problems. You can still do them, but you can choose a new way, CTE.

What is CTE?
The common table expression (CTE) is a powerful construct in SQL that helps simplify a query.

With CTE, you can split the query into different temporary tables and use them.

Example

If I want to create a report about the total amount of the user’s order, I could use CTE to simplify it.

[dbo].[Orders]

OrderNumber	UserId	Amount
2023020300000001	1	500
2023020300000002	3	100
2023020300000003	1	300
2023020300000004	2	400
2023020300000005	1	100

[dbo].[Users]

Id	Name	Age
1	CK	30
2	RR	29
3	NC	25

Then I use the CTE to create a temp table and join them.

Result:

Id	Name	Age	OrderTotalAmount
1	CK	30	900
2	RR	29	400
3	NC	25	100

SQL:

USE OrderDB
GO
WITH Order_User_Total_CTE (UserId, OrderTotalAmount)
AS
-- Define the User Order Total CTE query.
(
    SELECT UserId, SUM(Amount) AS OrderTotalAmount
    FROM Orders
    GROUP BY UserId
)
-- Query
SELECT 
	U.*, 
	OUTC.OrderTotalAmount
FROM Users U
LEFT JOIN Order_User_Total_CTE OUTC ON U.Id = OUTC.UserId
GO

Now, if I need to create complex reports, summaries or stored procedures , I will use CTE to help me.

2023-02-02

Database►SQL Server

Improve SQL speed by adding index in SQL Server

In the case, I have a simple sql about select, which sorts by date, OrderDate. At the same time, there are 5,000,000 records in the table, Orders. Due to the large amount of data, the SQL speed becomes very slow, about 6 seconds.

SELECT TOP 1000 [Id]
      ,[CreatedOn]
      ,[UpdatedOn]
      ,[IsValid]
      ,[OrderDate]
      ,[OrderNumber]
FROM [OrderDB].[dbo].[Orders]
ORDER BY [OrderDate] DESC

After running the sql script, the speed of the result was very slow, about 6 seconds.

So to imporve this problem, I add the index, NONCLUSTERED INDEX.

USE OrderDB;  
GO  
CREATE NONCLUSTERED INDEX [IX_Orders_OrderDate]  
    ON Orders ([OrderDate]);   
GO

After running the script, I found the speed has been improved by index, about 0 seconds, and there’re still 5,000,000 records in the table.

2023-02-02

Database►SQL Server

Generate the test data in SQL Server by stored procedure example

In the case, I want to create a simple script about creating the test data of 100000 records in SQL Server stored procedure.

Today, I have a table, Orders, and I want to generate the test data to it.

USE [OrderDB]
GO
/****** Object:  Table [dbo].[Orders]    Script Date: 2/2/2023 3:14:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Orders](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[CreatedOn] [datetime] NULL,
	[UpdatedOn] [datetime] NULL,
	[IsValid] [bit] NULL,
	[OrderDate] [datetime] NULL,
	[OrderNumber] [varchar](50) NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

Then I write a simple stored procedure.
You can control the data count you want to generate.

If I want to create the 100000 records, I can do this.

1
2
3

use OrderDB
go
execute dbo.CreateTestData 100000

Below is the complete script, I wrote.

USE [OrderDB]
GO
/****** Object:  StoredProcedure [dbo].[CreateTestData]    Script Date: 2/2/2023 2:46:33 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[CreateTestData] @Number int
AS
BEGIN
	DECLARE
		@Counter int = 1
	WHILE @Counter <= @Number
	BEGIN
		INSERT INTO Orders (OrderDate,OrderNumber,IsValid,CreatedOn,UpdatedOn) 
		VALUES 
		(
			DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2000-01-01'),
			CONVERT(CHAR(8), FORMAT(GetDate(),'yyyyMMdd')) + right('00000000'+cast(@Counter as varchar(8)),8),
			1,
			GETDATE(),
			GETDATE()
		)
		SET @Counter= @Counter + 1
	END
END

The stored procedure inserts a random date into OrderDate and a sequence number into OrderNumber.

Id	CreatedOn	UpdatedOn	IsValid	OrderDate	OrderNumber
1	2023-02-02 15:27:27.763	2023-02-02 15:27:27.763	1	2002-11-30 00:00:00.000	2023020200000001
2	2023-02-02 15:27:27.763	2023-02-02 15:27:27.763	1	2007-04-29 00:00:00.000	2023020200000002
3	2023-02-02 15:27:27.763	2023-02-02 15:27:27.763	1	2002-03-18 00:00:00.000	2023020200000003
4	2023-02-02 15:27:27.763	2023-02-02 15:27:27.763	1	2003-03-27 00:00:00.000	2023020200000004
5	2023-02-02 15:27:27.763	2023-02-02 15:27:27.763	1	2007-11-07 00:00:00.000	2023020200000005
...

2023-01-30

Topic►SystemStructure

簡易後端處理商業邏輯的方式-系統架構篇

在常見的API設計中，透過接收參數到後端服務中，再透過一連串資料操作的邏輯再寫入資料庫或做額外的操作。

這時情況會像是下面這樣:

因為有過多的操作，且這些操作可能在其他的API也會遇到
此時，就可以把他們抽出來成:處理程序(Handler)

透過Handler，把相同的服務可以用的Method或Function整理在一起。

舉個例子:
有支API叫做建立訂單(CreateOrder)
他可能的商業邏輯操作如:建立訂單、修正庫存…等等。
那我們就可以把它抽出來叫做OrderHandler，把複雜的商業邏輯封裝在裡面。

這時如果要使用到建立訂單時就能透過這Handler去幫我們處理。

1	OrderHandler.CreateOrder()

比如API叫做:

1	/Order/CreateOrder

那他的Controller裡面就可以這樣寫

OrderController裡的Action(CreateOrder)就能呼叫OrderHandler.CreateOrder()去協助建立訂單的操作。

這樣做的好處是能集中管理服務的功能，比較不會遇到全都散在Controller的情況。此外，其他Controller要使用到相同的功能，只要呼叫該Handler，而不用整段複製過去。

2022-12-07

Code►C#

C#中使用雙問號??取值用法

在C#中可以運用??來判斷前者是否為null，若為null則將後面值遞補上去。
如下，因Amount為空，所以會套用後面的default。
而default就是decimal的預設值0。
因此，在最後會得到amount結果為0。

namespace Application
{
    public class OrderDetail
    {
        /// <summary>
        /// amount
        /// </summary>
        public decimal? Amount;
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            var orderDetail = new OrderDetail();
            var amount = orderDetail.Amount ?? default;
        }
    }
}

2022-12-07

Code►C#

在C#中何時使用問號?去拿資料

在處理C#語言中的物件，常會去拿物件底下子物件的值，常常會遇到錯誤即空物件錯誤，如底下程式碼的amount1變數。因為我們在拿amount1這個值時前面的OrderDetail物件還沒被初始化。
這時就要使用問號”?”去取值避免拋錯，如amount2變數。
資料在處理時總會拿到髒資料，所以先預判，避免出錯。

程式碼如下:

namespace Application
{
    /// <summary>
    /// DataProvider
    /// </summary>
    public class DataProvider
    {
        /// <summary>
        /// OrderDetail
        /// </summary>
        public OrderDetail OrderDetail { get; set; }
    }
    public class OrderDetail
    {
        /// <summary>
        /// amount
        /// </summary>
        public decimal? Amount;
    }

    internal class Program
    {
        static void Main(string[] args)
        {
            var data = new DataProvider();

            // amount1: error
            // It will be error. Due to the object, OrderDetail,  is null and doesn't initialize.
            // var amount1 = data.OrderDetail.Amount ?? default;

            // amount2: 0 
            // we can add the symbol "?" after the null object, and it can avoid being an error.
            var amount2 = data.OrderDetail?.Amount ?? default;
        }
    }
}

2022-09-01

Code►Refactoring

Combine your multiple commits into one commit in git

When you use the git in your project, you could face a problem about how to combine your loose commits into one commit.

For Examples, I have the commit log like this.

First commit 
Fix1
Fix2
Adjust wording

I want to conbine commits of the fix1, fix2 and adjust wording into second commit.
It looks great and clear.

1 2	First commit Second commit

In order to become this result, we can use the git command, rebase.

1	git rebase -i <SHA>

SHA : It means the base commit. In this case, the SHA is First commit.

Then you can use the two commands and adjust your commits.

pick : use commit
squash : use commit, but meld into previous commit

The git-operation gif

The steps following:

1. Use the command "git rebase -i <SHA>"
2. Use pick and squash to adjust your commits
3. Update the combined-commit message
4. Finish

2022-07-29

Daily Log

The first half of 2022

In the first half of 2022, I worked from Kaohsiang to Taipei, and then started a financial-related job, which is loan service, and I write code in C# and Typescript to code everyday.

However, in my rest time, I prepared for Toeic and learned English online cuz I thought it’s a challenge for me and if I got it over, I could do more thing such as joining a game team, doing business or even playing the video game in full English that made me getting more interesting stuff in my life, but my listen of English still needed to improve.

In terms of living expenses, I just gained some money and improved my life. I thought the money was not very important, but actually I still needed it to live. I work just to improve my life quality and family environment.

Finally, I want to share a video. I think it’s fun to explore and study the unknown things, and it can give you a different perspective.

https://www.youtube.com/watch?v=aRCHzso-ni0

2022-06-28

Daily Log

Explore New Path What I Wanna Be

Since I chose the job of software engineer to work, it has been four years. But sometimes I’m still not satisfied because every day is very regular.

After I Get Off Work And Back Home

In four years, when I get off work, I will open the video game to rest, such as BloodBorne, Dark Souls III and Monster Hunter: World. I have no idea, but they can let me reflect on myself. Other times I just listen to hip hop music or rap or paint something.

I usually think about what I should do, but my skills, acknowledgement of technique, or environment always restrict me. My mind told me I couldn’t integrate them. If I can’t solve it, finally, I will just be a technical guy with no soul, which I don’t want to see.

I Need To Lit The Bonfire Of Life

Now I am starting to integrate my acknowledgement, focus on what I felt, and get out of my way like Dark Souls. I have to explore and fail to go ahead, and don’t be afraid to try those paths.

I will try the following things:

Integrate my acknowledgement to explore what I want to do.
Don’t be afraid to try those paths of failing and risk.

Finally, I’ve to thanks to the Grammar Checker that corrected my grammar.
https://writer.com/grammar-checker/

[中文版]

自從我選擇軟體工程師工作已經有四年之久，但有時候我仍然覺得不滿足，因為每天似乎都過得很規律。

當我結束工作回到家

在這四年間，當我下班了，回到家會打開電視遊樂器來放鬆，像是血源詛咒、暗黑靈魂3、魔物獵人世界。我沒有什麼特別想法，只是它們能夠讓我放鬆反思自己。其他時間則是聽嘻哈音樂或是饒舌，以及畫一些畫。

我時常在想我該怎麼做，但我的技能、技術認知、環境總是限制我。我內心總告訴我整合不了他們。如果我沒辦法解決掉這問題，最後，我可能就只會成為一個沒靈魂的科技宅，這是我不想見到的。

我需要點燃生命的篝火

從現在起我開始整合我的認知，專注在感受上，就像暗黑靈魂，走出一條自己的路。
我必須探索、失敗，從中前進，並且不膽怯嘗試那些路。
我會嘗試以下的事物:

整合認知來開拓我想做的事
不要膽怯嘗試那些失敗、有風險的路

UM's Coding Note

I record my coding experience at this place🤙

C# Distinct objects

Simplify queries in SQL Server using CTE

Example

Improve SQL speed by adding index in SQL Server

Generate the test data in SQL Server by stored procedure example

簡易後端處理商業邏輯的方式-系統架構篇

C#中使用雙問號??取值用法

在C#中何時使用問號?去拿資料

Combine your multiple commits into one commit in git

The git-operation gif

The first half of 2022

Explore New Path What I Wanna Be

After I Get Off Work And Back Home

I Need To Lit The Bonfire Of Life

當我結束工作回到家

我需要點燃生命的篝火