[EF] Transaction

閱讀 EF6 官方文章 - 使用交易 的整理筆記，紀錄最常用的兩種方式，分別為

• SaveChange()、Database.ExecuteSqlCommand()
• BeginTransaction()

環境建置

在 AdventureWorks2019 內建立 EFTransaction Table 來存放資料，並開啟 SQL Profile 來觀察交易

EFTransacton Table Script

USE [AdventureWorks2019]
GO

CREATE TABLE [dbo].[EFTransaction]
(
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[RecordTime] [datetime] NULL,
	CONSTRAINT [PK_EFTransaction] PRIMARY KEY CLUSTERED 
	(
		[ID] ASC
	)
)

Profile 內開啟交易事件

SaveChange()、Database.ExecuteSqlCommand()

SaveChange() 和 Database.ExecuteSqlCommand() 預設都會開啟交易，隔離層級為資料庫預設層級，以 MS SQL 為例，預設隔離層級是 read committed

DbFirstDbContext dbFirstDbContext = new DbFirstDbContext();
dbFirstDbContext.EFTransaction.Add(new EFTransaction() { RecordTime = DateTime.Now });
dbFirstDbContext.SaveChanges();

string TSQL = "DELETE FROM EFTransaction WHERE RecordTime < getdate()";
dbFirstDbContext.Database.ExecuteSqlCommand(TSQL);

從下圖 profile 截圖可以觀察到，SaveChange() 和 Database.ExecuteSqlCommand() 都包在各自的交易內，且從 Audit Login 內可以看到 read committed

 

BeginTransaction()

BeginTransaction() 可以把多個資料庫操作包在同一個交易內，以上述的 SaveChange() 和 Database.ExecuteSqlCommand() 來觀察

using (DbFirstDbContext dbFirstDbContext = new DbFirstDbContext())
using (DbContextTransaction tran = dbFirstDbContext.Database.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
{
    dbFirstDbContext.EFTransaction.Add(new EFTransaction() { RecordTime = DateTime.Now });
    dbFirstDbContext.SaveChanges();

    string TSQL =
        "insert into EFTransaction (RecordTime)" +
        " values(getdate())";
    dbFirstDbContext.Database.ExecuteSqlCommand(TSQL);

    tran.Commit();
}

從下圖 profile 截圖可以觀察到，SaveChange() 和 Database.ExecuteSqlCommand() 的 insert 動作都包在同一個交易內，且從 Audit Login 內可以看到 read committed

另外透過 BeginTranaction() 來開啟交易，是可以明確指定交易層級的，該範例雖然有明確指定 read committed，但 read committed 是預設值 

文章內關於 BeginTransaction() 的備註說明

Database.BeginTransaction() will open the connection if it is not already opened. If DbContextTransaction opened the connection then it will close it when Dispose() is called.

• 延伸閱讀
• [SQL] SQL Profile 觀察交易

[SQL] 作業失敗

收到 Job fail mail 通知，是偵測 block 超過設定時間並收集相關資訊的 job 發生異常

收到的通知 mail   

SQL Agent 內查到的錯誤訊息

錯誤訊息

轉換 expression 到資料類型 int 時發生算術溢位錯誤。 [SQLSTATE 22003] (錯誤 8115) 陳述式已經結束。 [SQLSTATE 01000] (錯誤 3621)

情況筆記

該 Job 會把捕捉到的 block 資訊 insert 進特定 table 內留存，方便事後分析，從錯誤訊息來看是資料要 insert 進 int 欄位時發生錯誤，從文件上確認 DMV 資料型別發現，原來 sys.dm_os_waiting_tasks wait_duration_ms 欄位是 bigint，但是收集 table 上是開 int，收到 mail 通知時有趕快去 product 上手動執行 DMV 語法查 block，有重現該 insert 錯誤

其實不知道為什麼會發生錯誤，因為 wait_duration_ms 沒有破 int 大小限制，然後 table 內有很多資料都比抓到的 wait_duration_ms 還要大，不知道 SQL Server 內部到底轉換了甚麼 ，反正該機制有回復正常就好

• 參考資料
• int、bigint、smallint 和 tinyint

[SSRS] A data source instance has not been supplied

改報表時有變更 SSRS 內的 data source，然後報表拋出下面訊息

後來發現 C# 內設定的 data source 和 SSRS 內不一致造成，修正後報表就正常顯示啦

ReportViewer.LocalReport.DataSources.Add(new ReportDataSource(name, dataSourceValue));

久久碰一次報表常常會莫名卡關

[SSRS] 報表資料

開啟 VS 要開發 SSRS 時，突然發現 [報表資料]視窗不見，下意識去 [VS 檢視] 內要把它叫出來，竟然找不到，傻眼

在官方文件 - 停駐報表設計師中的報表資料窗格 內有找到快捷鍵 - [Ctrl + Alt + D] 把視窗叫出來，Google 時有發現該篇討論，在叫出報表視窗時也有發現到 [VS 檢視] 內的 [報表資料] 選項時有時無，話說這討論背景是 VS 2010，我現在用的是 VS2022，Orz

[VS] 偵錯 - 區域變數

跟同事 PC 上討論時，偵錯時慣性去看 [區域變數] 視窗觀察變數值，但當下一直找不到它在哪，該篇紀錄開啟步驟

偵錯模式 => 偵錯 => 視窗 => 區域變數

• 參考資料
• 檢查 [自動變數] 和 [區域變數] 視窗中的變數

[SQL] 查詢 Table 資料筆數

常看見社群上提到使用 count() 來查詢 Table 資料筆數，該篇紀錄查詢資料筆數的兩種方式，以 AdventureWorks2019 的 Person Table 當成查詢目標紀錄

SSMS 上操作

Person Table => 滑鼠右鍵 => 儲存體 => 資料列計數

Person Table => 統計資訊 => 任一統計資訊 (該範例以 PK 為主) => 滑鼠右鍵 => 統計資訊詳細資料

單一 Table 沒有切割任何 parition

使用 sys.partition 來查詢資料筆數，而在 sys.dm_db_partition_stats 內有該備註說明

If a heap or index is not partitioned, it is made up of one partition (with partition number = 1); therefore, only one row is returned for that heap or index. The sys.partitions catalog view can be queried for metadata about each partition of all the tables and indexes in a database.

SELECT
    T.[name] AS 資料表名稱,
    P.rows AS 資料筆數
FROM sys.tables AS T  
	JOIN sys.partitions AS P ON T.[object_id] = P.[object_id]
WHERE P.index_id IN (0,1) -- 0：代表 Heap、1 代表 Clustered Index
    AND T.[name] = 'Person' 
ORDER BY 資料筆數 DESC

• 參考資料
• sys.dm_db_partition_stats
• 查詢目前資料庫內，每一個資料表的磁碟空間使用資訊 - 使用 sys.dm_db_partition_stats
• 取得資料表之筆數及所用空間 - 使用 sys.allocation_units

[SQL] 資料表值建構函式 - 跨欄位彙總

網路上發現的報表彙總問題，問題為申請流程會有三道文件申請

• 每道文件申請彼此之間沒有順序性
• 每道文件申請會有多次通過紀錄，必須找出最早審核通過日期
• 完成三道文件申請後，要顯示最後該文件完成日期

原始資料和預期結果如下

練習時雖然有達到需求但很亂，Google 到該篇文章 - Find MAX value from multiple columns in a SQL Server table，打開資料表值建構函式應用視野，以前對於資料表值建構函式使用都僅止 [SQL] 資料表值建構函式，這次才發現衍伸資料表使用方式，可以達到跨欄位彙總

TSQL 和說明如下

DECLARE @申請文件 TABLE
(
	[文件ID] CHAR(20),
	[顧客ID] CHAR(9),
	[文件類型] CHAR(1),
	[文件申請日期] DATE,
	[文件審核日期] DATE,
	[審核結果] NCHAR(6)
);

INSERT INTO @申請文件 VALUES 
----- 原問題資料
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/01/01', '2022/01/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/02/15', '2022/02/19', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'A', '2022/03/01', '2022/03/05', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'B', '2022/02/01', '2022/02/20', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'B', '2022/03/01', '2022/03/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'C', '2022/01/15', '2022/01/22', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0001', 'C', '2022/02/20', '2022/02/27', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'A', '2022/05/01', '2022/05/06', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'B', '2022/05/20', '2022/05/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'B', '2022/05/15', '2022/05/17', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0002', 'C', '2022/05/15', '2022/05/18', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'A', '2022/03/20', '2022/03/22', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/03/14', '2022/03/20', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/06/01', '2022/06/22', N'不通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'B', '2022/07/30', '2022/07/31', N'通過'),
('xxxxxxxxxxxx', 'xxxx-0003', 'C', '2022/03/14', '2022/03/15', N'通過'),
----- 故意建立一筆沒有完成流程資料
('xxxxxxxxxxxx', 'xxxx-0004', 'A', '2022/03/14', '2022/03/15', N'通過');

WITH CTE AS 
(
  SELECT *
  FROM
    (
      SELECT
        [文件ID] ,
        [顧客ID] ,		
        [文件類型] ,
        [文件審核日期]
      FROM @申請文件
      WHERE [審核結果] = N'通過'
    ) AS P
    PIVOT
    (
      -- 找出 A、B、C 文件申請最早通過日期
      MIN([文件審核日期]) FOR [文件類型] IN ([A] , [B] , [C])
    ) AS PV
)
SELECT 
  T.[文件ID] ,
  T.[顧客ID] ,	
  T.A AS [文件 A 最早通過時間] ,
  T.B AS [文件 B 最早通過時間] ,
  T.C AS [文件 C 最早通過時間] ,
  -- A、B、C 文件申請都過，才算是完整通過
  IIF(A IS NULL OR B IS NULL OR C IS NULL , NULL , LastUpdateDate) AS 最早申請完成時間
FROM
  (
    SELECT
      * ,
      (
        -- 使用衍伸資料表來達到跨欄位取最大日期
        SELECT 
          MAX(LastUpdateDate)
        FROM 
          (
            VALUES (A),(B),(C)            -- A、B、C 為 PIVOT 後的欄位名稱
          ) AS UpdateDate(LastUpdateDate) -- 定義 Table 名稱 (欄位名稱)
      ) AS LastUpdateDate
    FROM CTE
  ) AS T

TSQL 執行結果

 

• 參考資料
• FB 討論
• 資料表值建構函式
• Find MAX value from multiple columns in a SQL Server table

[SQL] 資料表值建構函式

SQL Server 2008 推出的新語法 - 資料表值建構函式（Table Value Constructor），主要有兩種使用方式，分別為

• 單一 insert 插入多筆資料 (insert values)
• 衍伸資料表 (derived table)

單一 insert 插入多筆資料

該作法最常見，常用來取代 insert 資料時，不用一直反覆寫 insert into TableName 這段語法，只要在 values 後面用括號包住資料並用逗號分隔

USE AdventureWorks2019;  
GO  

DECLARE @Table_Value_Constructor TABLE (id int , LastName nchar(4) , FirstName nchar(4))

INSERT INTO @Table_Value_Constructor VALUES 
    (1 , N'張' , N'三'), 
    (2 , N'李' , N'四'),
    (3 , N'王' , N'五');  
GO  

衍伸資料表

values 接括號資料並用逗號分隔後產生 Table 名稱和欄位名稱

SELECT *
FROM 
    (	
        VALUES
        ('20221001', '20221002'),
        ('20221003', '20221004'), 
        ('20221005', '20221006'), 
        ('20221007', '20221008'), 
        ('20221009', '20221010') 
    ) AS Table_Value_Constructor(Date1, Date2);  -- 指定 Table 名稱和欄位名稱

限制事項 from 官方文件

• 用作衍生資料表時，資料列數目沒有限制。 
• 作為 INSERT ... VALUES 陳述式的 VALUES 子句時，資料列數目限制為 1000 個。如果資料列數目超過最大值，就會傳回錯誤 10738。 若要插入 1000 個以上的資料列，請使用下列其中一種方法： 
• 建立多個 INSERT 陳述式 
• 使用衍生資料表
• 使用 bcp 公用程式 ( .NET SqlBulkCopy class、OPENROWSET (BULK ...)) 或 BULK INSERT 陳述式來大量匯入資料

• 參考資料
• 資料表值建構函式
• SQL Server 2012 T-SQL 資料庫設計 - P308
• 利用SQL2008版本後的資料表值建構函式（Table Value Constructor），大量寫入資料
• T-SQL -- INSERT加強版之資料表值建構函式(VALUES)
• SQL 資料表 Value 建構式實現衍生資料表

[SQL] 查詢特定資料庫內的指定欄位

看到 Line 社群內問題當下，直覺是常用的 sys.columns，information_schema 可能是名字比較長，沒有記住過，筆記幫助記憶

USE AdventureWorks2019
GO

SELECT
	object_name([object_id]) AS TableName, 
	name AS ColName
FROM sys.columns 
WHERE name = 'ModifiedDate'
ORDER BY TableName

SELECT
	Table_Name ,
	Column_Name
FROM INFORMATION_SCHEMA.COLUMNS
WHERE Column_Name = 'ModifiedDate'
ORDER BY Table_Name

• 參考資料
• sys.columns、COLUMNS
• [SQL]列出某個資料庫中所有的欄位資訊
• [SQL]列出特定資料庫內資料表結構的常用方法
• stackoverflow 討論

[SQL] 錯誤 22022

把之前交易複寫的模擬環境重啟使用，手動執行複寫 Job 要透過散發者把資料傳送到訂閱者上都失敗，從 Agent 提供的錯誤訊息去 Google，錯誤 22022 基本上都是找到 SQL Server Agent 服務沒有啟動，但很肯定服務已正常啟動

在複寫監視器內才發現真正原因，原來 Node2 雖然有開啟，但是 Hyper-V 上的網路沒有通，抓不到它，把網路設定好後就 Job 也就正常囉