[SQL] Nonrepeatable Read

SQL Server 預設隔離層級為 Read Committed，該隔離層級不會有 Dirty Read 問題，但會發生 Nonrepeatable Read，也就是交易內多次讀取資料，結果會有所不同

官方文件說明

指定陳述式不能讀取其他交易已修改而尚未認可的資料。 這個選項可避免中途讀取。 目前交易內個別陳述式之間的其他交易可以變更資料，這會產生不可重複的讀取或虛設項目資料。 這個選項是 SQL Server 的預設值。

Sample Code

USE AdventureWorks
GO 

DROP TABLE IF EXISTS tbRepeatableReadDemo

CREATE TABLE tbRepeatableReadDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)

INSERT INTO tbRepeatableReadDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)

-- 顯示建立資料
SELECT * FROM tbRepeatableReadDemo

----- Nonrepteated Read Demo
----- Session1
-- Step1：開啟交易並讀取資料，此時交易還在
BEGIN TRAN
SELECT * FROM tbRepeatableReadDemo
WAITFOR DELAY '00:00:05'
-- Step3：再次讀取資料 
SELECT * FROM tbRepeatableReadDemo

----- Session2
-- Step2：更新資料，把數字都加 1
UPDATE tbRepeatableReadDemo
SET Col1 = Col1 + 1

上述 Code 流程圖示化

 

Session1 查詢結果

 

因為交易層級層級 - Read Committed，對於 S-Lock 是查詢完畢後立即釋放，而非交易完成後釋放，所以 Session2 的 Update 可以順利取得 X-Lock 來進行資料更新

• 參考資料
• Transaction Isolation Levels In Microsoft SQL Server

[SQL] 篩選索引和參數化查詢

把 AdventureWorks2017 的 Sales.SalesOrderHeader.PurchaseOrderNumber 欄位當成測試目標，來模擬 TSQL 參數化，對於篩選索引使用的影響

建立篩選索引

要針對欄位有值資料建立篩選索引，排除 NULL 和空值

USE [AdventureWorks2017]
GO

DROP INDEX IF EXISTS IF_SalesOrderHeader_PurchaseOrderNumber ON Sales.SalesOrderHeader

CREATE INDEX IF_SalesOrderHeader_PurchaseOrderNumber ON Sales.SalesOrderHeader (PurchaseOrderNumber)
WHERE PurchaseOrderNumber IS NOT NULL
   AND PurchaseOrderNumber <> ''

測試使用篩選索引來搜尋資料

從執行計畫中可以觀察到有用到篩選索引

SELECT PurchaseOrderNumber
FROM Sales.SalesOrderHeader
WHERE PurchaseOrderNumber = N'PO18850127500'

TSQL 參數化

從執行計化可以觀察到，參數化後 Query Optimizer 無法判斷是否使用篩選索引，跑回叢集索引去

DECLARE @TSQL nvarchar(max)
DECLARE @PurchaseOrderNumber nvarchar(25) = N'PO18850127500'

SET @TSQL = 
    'SELECT 
       PurchaseOrderNumber
     FROM Sales.SalesOrderHeader
     WHERE PurchaseOrderNumber = @PurchaseOrderNumber'

EXEC dbo.sp_executesql 
    @TSQL,
    N'@PurchaseOrderNumber nvarchar(25)',
    @PurchaseOrderNumber

TSQL 參數化改善

WHERE 內加入條件，讓 Query Optimizer 有評估篩選索引依據，就會使用到篩選索引

DECLARE @TSQL nvarchar(max)
DECLARE @PurchaseOrderNumber nvarchar(25) = N'PO18850127500'

SET @TSQL = 
    'SELECT 
        PurchaseOrderNumber
     FROM Sales.SalesOrderHeader
     WHERE PurchaseOrderNumber = @PurchaseOrderNumber
		AND PurchaseOrderNumber > '''''

EXEC dbo.sp_executesql 
    @TSQL,
    N'@PurchaseOrderNumber nvarchar(25)',
    @PurchaseOrderNumber

• 參考資料
• How to Overcome the SQL Server Filtered Index UnMatchedIndexes Issue
• Filtered Indexes and Dynamic SQL

[SQL] 交易式複寫-環境準備

根據官方網站文件-複寫教學課程，來學習交易式複寫，該篇紀錄 SQL Server 環境設定注意事項，準備兩台 SQL Server 2019，分別命名為 Node1\SQL2019 和 Node2\SQL2019

• Node1\SQL2019：發行者和散發者
• Node2\SQL2019：訂閱者

防火牆要開 1433 Port

SQL Server 預設 Port 為1433，建議參考資料：[Security] 若主機有對 Internet，且系統和 SQL Server 同一台，則不應打開 SQL Server TCP 1433 Port

   

SQL Server Browser

因為複寫關係，會用 Node1\SQL2019 來進行連線，不會使用 IP

在 教學課程：準備 SQL Server 進行複寫 (發行者、散發者、訂閱者) 內有該說明，記錄一下

SQL Server Management Studio (SSMS) 18.0 (和更新版本) 目前已知有以下問題：當使用 IP 位址連線至散發者卻仍無效時，「不」顯示警告訊息。 連線至散發者時應使用實際的伺服器名稱。

測試 DB - AdventrueWorks2019

在 Node1 上還原 AdventrueWorks2019 後，因為 SSID 不一致，所以擁有者會是空白，要自行調整，在練習過程中有踩到這個坑，Orz

Windows 電腦名稱和 SQL Server 名稱

Techdays 2013 - 跨洲際多點同時服務的資料庫解決方案 課程中，老師有特別提到要注意 Windows 名稱和 SQL Server 名稱是否一致，要不然複寫會有問題

檢查語法為

SELECT SERVERPROPERTY('ServerName') -- Windows 伺服器和執行個體名稱
SELECT @@SERVERNAME -- SQL Server 的本機伺服器名稱

修正 SQL 名稱語法為

EXEC sp_dropserver OldInstanceName
GO
EXEC sp_addserver NewInstanceName , 'local'
GO

• 延伸閱讀：
• [SQL] 錯誤的伺服器名稱

判斷字型是否安裝

紀錄 C# 和 VFP 判斷字型安裝語法

C#：利用 InstalledFontCollection 來判斷

private bool IsFontInstalled(string FontNameWithoutExtension)
{
    InstalledFontCollection installedFontCollection = new InstalledFontCollection();
    return installedFontCollection.Families.Any(font => font.Name == FontNameWithoutExtension);
}

VFP：利用 AFont() 來判斷

FUNCTION IsFontInstalled(FontNameWithoutExtension as string) AS Boolean
	RETURN AFONT(laFont,FontNameWithoutExtension)
ENDFUNC 

• 參考資料
• 網路討論
• 作法：列舉已安裝的字型

[Windows] 字型安裝

上一篇 - [C#] 字型安裝 完成後，同事跟我分享說，Windows 字型安裝，現在預設是在 User 內，不是在 Windows Fonts 內，乍聽之下是一頭霧水，這是怎麼回事。

查發現，原來複製貼上字型檔案至 C:\Windows\Fonts 內，字型會被放在 C:\Users\使用者名稱\AppData\Local\Microsoft\Windows\Fonts 內，且不會有註冊機碼，要在字型檔案上按滑鼠右鍵，並選擇 [為所以使用者安裝] ，檔案才會在 C:\Windows\Fonts 且會進行機碼註冊

直接把字型檔案複製進 Windows Fonts 資料夾內

    

用 [為所有使用者安裝] 才是預期中的安裝和註冊機碼

• 參考資料
• StackOverFlow 討論

[C#] 字型安裝

把字型加入專案內，並在程式啟動時把安裝至 Windows Fonts 內

把字型加入 Resource.resx 內

把字型加入後，在 Resource 資料夾內可以找到檔案，並把該檔案屬性建置動作，設定為內嵌資源

在程式執行時，偵測字型是否安裝，沒有就進行字型安裝

using System;
using System.Drawing.Text;
using System.IO;
using System.Linq;
using System.Runtime.InteropServices;
using System.Security.Principal;
using System.Windows.Forms;

namespace InstallFont
{
    public partial class Form1 : Form
    {
        [DllImport("gdi32")]
        public static extern int AddFontResource(string lpFileName);

        private const int WM_FONTCHANGE = 0x001D;
        private const int HWND_BROADCAST = 0xffff;

        [DllImport("user32.dll")]
        public static extern int SendMessage(int hWnd, uint Msg, int wParam, int lParam);

        [DllImport("kernel32.dll", SetLastError = true)]
        public static extern int WriteProfileString(string lpszSection, string lpszKeyName, string lpszString);

        public Form1()
        {
            InitializeComponent();

            string FontName = "Gulim.ttf";
            string FontFullName = Path.Combine(Path.GetTempPath(), FontName);

            GetFontFromResource(FontFullName);

            InstallFont(FontFullName);
            
            SetFont();
        }

        /// <summary>
        /// 把內嵌字型檔案抓出來存放在本機暫存區
        /// </summary>
        /// <param name="FontFullName">字型檔案完整路徑</param>
        private void GetFontFromResource(string FontFullName)
        {
            if (File.Exists(FontFullName)) return;

            byte[] FontData = Properties.Resources.gulim;
            File.WriteAllBytes(FontFullName, FontData);
        }

        /// <summary>
        /// 安裝字型
        /// </summary>
        /// <param name="FontFullName">字型檔案完整路徑</param>
        private void InstallFont(string FontFullName)
        {
            string FontNameWithoutExtension = Path.GetFileNameWithoutExtension(FontFullName);
            string FontName = Path.GetFileName(FontFullName);

            if (IsFontInstalled(FontNameWithoutExtension))
            {
                MessageBox.Show("字型已存在");
                return;
            }

            if (IsAdminstrator() == false)
            {
                MessageBox.Show("當前使用者無管理員許可權，無法安裝字型");
                return;
            }

            string WindowsFontFullName = Path.Combine(SystemPath_Fonts(), FontName);

            // 將字型檔案複製到系統字型資料夾
            File.Copy(FontFullName, WindowsFontFullName);

            // 把字型加入 System Font Table 內
            AddFontResource(WindowsFontFullName);

            // 廣播告知字型有所變更
            SendMessage(HWND_BROADCAST, WM_FONTCHANGE, 0, 0);

            // 安裝字型，寫進機碼內就是
            string Section = "Fonts"; // 不區分大小寫
            string KeyName = FontNameWithoutExtension + " (TrueType)";
            WriteProfileString(Section, KeyName, FontName);
        }

        private string SystemPath_Fonts()
        {
            return Environment.GetFolderPath(Environment.SpecialFolder.Fonts);
        }

        private bool IsFontInstalled(string FontNameWithoutExtension)
        {
            InstalledFontCollection installedFontCollection = new InstalledFontCollection();
            return installedFontCollection.Families.Any(font => font.Name == FontNameWithoutExtension);
        }

        private bool IsAdminstrator()
        {
            WindowsIdentity identity = WindowsIdentity.GetCurrent();
            WindowsPrincipal principal = new WindowsPrincipal(identity);
            return principal.IsInRole(WindowsBuiltInRole.Administrator);
        }
        
        private void SetFont()
        {
            lblGulim.Font = new Font(this.FontName, 20);
        }        
    }
}

AddFontResource 重點：要完成機碼註冊才算是完成字型安裝

This function installs the font only for the current session. When the system restarts, the font will not be present. To have the font installed even after restarting the system, the font must be listed in the registry.

A font listed in the registry and installed to a location other than the %windir%\fonts\ folder cannot be modified, deleted, or replaced as long as it is loaded in any session. In order to change one of these fonts, it must first be removed by calling RemoveFontResource, removed from the font registry (HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows NT\CurrentVersion\Fonts), and the system restarted. After restarting the system, the font will no longer be loaded and can be changed.

SendMessage 和 WM_FONTCHANGE 重點

An application that adds or removes fonts from the system (for example, by using the AddFontResource or RemoveFontResource function) should send this message to all top-level windows.

To send the WM_FONTCHANGE message to all top-level windows, an application can call the SendMessage function with the hwnd parameter set to HWND_BROADCAST.

WriteProfileStringA function (winbase.h) 重點：參數和機碼的顏色對應

字型名稱確認方式，要以字型檔案內名稱為主，以下圖來看，檔案總管內顯示的是 [Guilm 標準]，但是字型檔案內是 [Guilm]

   

程式執行結果

     

• 參考資料
• 作法：列舉已安裝的字型
• windows系統下，如何在C#程式中自動安裝字型
• C# WinForm使用新字型
• How do I embed my own fonts in a WinForms app?

[SQL] Alter 權限

網路問題，拿來複習權限設定相關操作，問題為 

 只開特定 Table 的 Alter 權限給指定使用者？

USE AdventureWorks2017
GO

-- 刪除 Table、User 和 Login，方便重覆執行
DROP TABLE IF EXISTS tblDemo
DROP USER IF EXISTS UserOnlyAlert
IF EXISTS ( SELECT 1 FROM  sys.sql_logins WHERE Name = 'LoginOnlyAlert' ) DROP LOGIN LoginOnlyAlert

-- 建立一個簡單 Table 來驗證，是否具備只有 Alter 權限
CREATE TABLE [dbo].[tblDemo](
   [ID] [int] NULL
) ON [PRIMARY]
GO

-- 建立 Login
CREATE LOGIN [LoginOnlyAlert] 
  WITH PASSWORD = N'P@ssw0rd', 
  DEFAULT_DATABASE = [AdventureWorks2017], 
  DEFAULT_LANGUAGE = [繁體中文], 
  CHECK_EXPIRATION = OFF, 
  CHECK_POLICY = OFF
GO

-- 建立 User 並指定預設 Schema
CREATE USER [UserOnlyAlert] FOR LOGIN [LoginOnlyAlert] WITH DEFAULT_SCHEMA = [dbo]
GO

-- 授給 UserOnlyAlert 對 tblDemo 進行 Alter 的權限
GRANT ALTER
ON AdventureWorks2017.dbo.tblDemo
TO [UserOnlyAlert]

-- 確認目前的 Login 和 User：
-- SUSER_SNAME()：TP-JENGTING\jengting
-- USER_NAME()  ：dbo
SELECT 
  SUSER_SNAME() , -- Instance Level
  USER_NAME()     -- DB Level

-- 切換 Login
EXECUTE AS LOGIN  = 'LoginOnlyAlert'

-- 確認目前的 Login 和 User：
-- SUSER_SNAME()：LoginOnlyAlert
-- USER_NAME()  ：UserOnlyAlert
SELECT 
  SUSER_SNAME() , -- Instance Level
  USER_NAME()     -- DB Level

-- 權限測試
-- 對 Demo Table 進行 Select、Insert、Update 和 Delete
SELECT * FROM [AdventureWorks2017].[dbo].[tblDemo]
ALTER TABLE tblDemo ADD ColName char(10) 

-- 測試訊息
-- 訊息 229，層級 14，狀態 5，行 52
-- 結構描述 'dbo'，資料庫 'AdventureWorks2017'，物件 'tblDemo' 沒有 SELECT 權限。

REVERT

新增欄位結果

• 參考資料
• 如何正確的授權 Azure SQL Database 資料庫權限給開發人員
• FB 討論

[C#] MemoryCache

官方文章 MemoryCache 類別 內的範例練習，修改為較容易觀察快取回收的情況

using System;
using System.Collections.Generic;
using System.IO;
using System.Runtime.Caching;
using System.Windows.Forms;

namespace MemoryCacheSample
{
    public partial class Form1 : Form
    {
        private ObjectCache cache = MemoryCache.Default;
        private string CacheKeyName = "filecontents";
        private string FileFullName = @"d:\cache\example.txt";

        public Form1()
        {
            InitializeComponent();

            // 每一秒觸發一次
            timer1.Interval = 1000;
            timer1.Enabled = true;
            timer1.Tick += Timer1_Tick;
        }

        private void CacheInit()
        {
            // 抓取檔案內容
            string fileContents = File.ReadAllText(FileFullName);

            CacheItemPolicy policy = new CacheItemPolicy();

            // policy 設定 1：設定快取回收：設定 2 秒快取回收，預設為 10 秒
            policy.AbsoluteExpiration = DateTimeOffset.Now.AddSeconds(2.0);

            // policy 設定 2：監控資料來源是否已改變
            List<string> filePaths = new List<string>() { FileFullName };
            policy.ChangeMonitors.Add(new HostFileChangeMonitor(filePaths));

            // policy 設定 3：快取異動通知
            policy.RemovedCallback = (arguments) =>
            {
                string CacheRemoveText = $"Cache：{arguments.CacheItem.Key} 回收，回收原因為 {arguments.RemovedReason}";
                TextFill(CacheRemoveText);
            };

            // 使用 Set 設定快取：快取不存在時會新增，快取已存在時會直接覆寫
            cache.Set(CacheKeyName, fileContents, policy);
        }

        private void Timer1_Tick(object sender, EventArgs e)
        {
            if (cache.Contains(CacheKeyName) == false)
                CacheInit();

            string CacheValue = cache.Get(CacheKeyName).ToString();
            TextFill(CacheValue);
        }

        private void TextFill(string contents)
        {
            string Text = $"{DateTime.Now.ToString("yyyy/MM/dd hh:mm:ss.fff")}-{contents}" + Environment.NewLine;
            textBox1.Text += Text;
        }
    }
}

• 參考資料
• CacheItemPolicy 類別
• HostFileChangeMonitor 類別
• .NET Framework 應用程式中的快取
• [C#] 初探 MemoryCache 及使用方式介紹
• ASP.NET 4 快取 API 有兩種：Cache 與 ObjectCache
• [C#] 記憶體快取 MemoryCache

[LINQ] Contains 轉 TSQL 語法

在 LinqPad 中連線 AdventureWork Perosn Table 來觀察 Contain 在不同應用轉成的 TSQL 語法

void Main()
{
	// 用法一：轉成 TSQL LIKE
	Persons
	.Where(w => w.LastName.Contains("T"))
	.Select(s => s.LastName)
	.Dump();
	
	// 用法二：轉為 TSQL IN
	string[] LastNames  = new string[] {"Alberts" , "Bennett"};

	Persons
	.Where(p => LastNames.Contains(p.LastName))
	.Select(s => s.LastName)
	.Dump();	
}

用法一：轉成 TSQL LIKE

用法二：轉為 TSQL IN

[Win] WinXP 離線安裝 .Net Framework 3.5 SP1

有需求必須在 XP 上安裝 .Net Framework 3.5 SP1，但該 PC 無法對外連線，研究發現，原來 Microsoft .NET Framework 3.5 Service Pack 1 (完整套件) 上的是離線版本，連網原因是因為需要 Microsoft .NET Framework 3.5 SP1 語言套件，透過下列語法可以在安裝時不去下載語言包

透過 /lang:eng 強制指定英文版本，就不會進行語言包下載

沒網路是要怎麼下載，還完成了，哈

安裝完成

最後在安裝中文語言包，打完收工

• 參考資料
• 完全離線安裝 .Net Framework 3.5 SP1
• Microsoft .NET Framework Runtime 離線安裝檔各版本下載網址