[SSMS] 索引標籤文字

發現 SSMS 索引標籤文字是可以設定，下圖為預設顯示，包含

• Login 名稱
• Server 名稱
• Database 名稱
• File 名稱

選項 => 文字編輯器 => 編輯器索引標籤和狀態列 => 索引標籤文字內可以進行更改

下圖為保留 Database 名稱和 File 名稱效果

[EFCore] SqlQuery、SqlQueryRaw - 欄位對應

延續 [EFCore] SqlQuery、SqlQueryRaw 筆記，在 Raw SQL queries for unmapped types 內還有提到 Data Annotations - ColumnAttribute 應用，之前把 Product Name 欄位在 PurchaseOrderDetailViewModel class 內轉為 ProductName，造成寫 TSQL 時，一定會需要 P.[Name]  AS ProductName 這段轉換，要不然會拋出錯誤。

TSQL Name 欄位未轉換為 ProductName

CASE 1：沒有任何 where 條件

string TSQL10 = @"
    SELECT 
        P.ProductID ,
        P.[Name] ,
        POD.OrderQty ,
        POD.UnitPrice ,
        POD.LineTotal 
    FROM [Purchasing].[PurchaseOrderHeader] AS POH
        JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
    ";

var result10 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL10);

var result = await result10.ToListAsync();
Console.WriteLine(result.Count().ToString());
// TSQL 語法正常輸出
//    SELECT
//        P.ProductID ,
//        P.[Name] , 
//        POD.OrderQty ,
//        POD.UnitPrice ,
//        POD.LineTotal
//    FROM[Purchasing].[PurchaseOrderHeader] AS POH
//        JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
//        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID

執行後會拋出該錯誤，原來 SqlQueryRaw 底層是 FromSql 去執行的，所以每個 Property 都要有值才行，在 TSQL pass 但卡在 EFCore 上。

Unhandled exception. System.InvalidOperationException: The required column 'ProductName' was not present in the results of a 'FromSql' operation.

CASE2：TSQL WHERE 沒有條件，但 LINQ WHERE 內有

string TSQL11 = @"
    SELECT 
        P.ProductID ,
        P.[Name] , 
        POD.OrderQty ,
        POD.UnitPrice ,
        POD.LineTotal 
    FROM [Purchasing].[PurchaseOrderHeader] AS POH
        JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
    ";

var result11 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL11)
    .Where(vw => vw.ProductID == productID);

var result = await result11.ToListAsync();
Console.WriteLine(result.Count().ToString());

產生 TSQL

exec sp_executesql N'
	SELECT 
	    [e].[LineTotal], 
	    [e].[OrderQty], 
	    [e].[ProductID], 
	    [e].[ProductName], -- 外層為 ProductName
	    [e].[UnitPrice]
	FROM
		(
			SELECT
				P.ProductID,
				P.[Name], -- 內層為 Name
				POD.OrderQty,
				POD.UnitPrice,
				POD.LineTotal
			FROM[Purchasing].[PurchaseOrderHeader] AS POH
				JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
				JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
		) AS[e]
	WHERE[e].[ProductID] = @__productID_1',
	N'@__productID_1 int',@__productID_1=1

執行後會拋出該錯誤，從 TSQL 語法可以觀察到，LINQ WHERE 條件，會根據 PurchaseOrderDetailViewModel Property 轉成對應 TSQL 欄位，因為 TSQL 內層沒有把 Name 轉成 ProductName，導致外層抓不到 ProductName。

Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 無效的資料行名稱 'ProductName'。

Data Annotations - ColumnAttribute

在 PurchaseOrderDetailViewModel 上把 ProductName 加上 ColumnAttribute

namespace EFCoreQuery.Models
{
    public class PurchaseOrderDetailViewModel
    {
        public int ProductID { get; set; }

        /// <summary>
        /// 把 Name 轉為 ProductName
        /// </summary>
        [Column("Name")]
        public string ProductName { get; set; }

        public Int16 OrderQty { get; set; }

        public decimal UnitPrice { get; set; }

        public decimal LineTotal { get; set; }
    }
}

CASE 1：沒有任何 where 條件

string TSQL13 = @"
    SELECT 
        P.ProductID ,
        P.[Name] ,
        POD.OrderQty ,
        POD.UnitPrice ,
        POD.LineTotal 
    FROM [Purchasing].[PurchaseOrderHeader] AS POH
        JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
    ";

var result13 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL13);

var result = await result13.ToListAsync();
Console.WriteLine(result.Count().ToString());
// TSQL 語法正常輸出
//    SELECT
//        P.ProductID ,
//        P.[Name] , 
//        POD.OrderQty ,
//        POD.UnitPrice ,
//        POD.LineTotal
//    FROM[Purchasing].[PurchaseOrderHeader] AS POH
//        JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
//        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID

正常執行，沒有拋出 FromSql ProductName 沒有值的錯誤，從中斷點去觀察，也發現值有塞進去，TSQL Name 欄位和 PurchaseOrderDetailViewModel ProudctName 屬性會自動對應。

CASE2：TSQL WHERE 沒有條件，但 LINQ WHERE 內有

int productID = 1;

string TSQL14 = @"
    SELECT 
        P.ProductID ,
        P.[Name] , 
        POD.OrderQty ,
        POD.UnitPrice ,
        POD.LineTotal 
    FROM [Purchasing].[PurchaseOrderHeader] AS POH
        JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
        JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
    ";

var result14 = dbContext.Database
    .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL14)
    .Where(vw => vw.ProductID == productID);

var result = await result14.ToListAsync();
Console.WriteLine(result.Count().ToString());

產生 TSQL

exec sp_executesql N'
	SELECT 
	    [e].[LineTotal], 
	    [e].[OrderQty], 
	    [e].[ProductID], 
	    [e].[Name], -- 外層為 Name
	    [e].[UnitPrice]
	FROM
		(
			SELECT
				P.ProductID,
				P.[Name], -- 內層為 Name
				POD.OrderQty,
				POD.UnitPrice,
				POD.LineTotal
			FROM[Purchasing].[PurchaseOrderHeader] AS POH
				JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
				JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
		) AS[e]
	WHERE[e].[ProductID] = @__productID_1',
	N'@__productID_1 int',@__productID_1=1

外層 TSQL 產生 Name 而不是 ProductName

在 EF 學習 Data Annotations 時，是在 Code First FluentAPI 內設定給 Migration 使用，沒想到在 EFCore DbFirst 已經可以影響 TSQL 產生。

• 參考資料
• 實體屬性
• Data Annotations - Column Attribute in EF 6 & EF Core

[EFCore] SqlQuery、SqlQueryRaw

延續 [EFCore] FromSql、FromSqlRaw 筆記，根據 SQL Queries 和 Raw SQL queries for unmapped types 文章內容，來記錄 SqlQuery 和 SqlQueryRaw，使用 EFCore 8、AdventureWorks2022 Purchasing 相關 Table 為資料來源並開啟 Sql Profile 來觀察產生的 TSQL 語法。

SqlQuery 和 SqlQueryRaw

• 兩者都是在 EFore 7.0 新功能
• EFCore 7.0：只能回傳 Scalar Value，EX：string、datetime、int
• EFCore 8.0：能回傳 unmapped types，EX：DTO、ViewModel

PurchaseOrderDetailViewModel

用來承接 SqlQuery、SqlQueryRaw 結果

namespace EFCoreQuery.Models
{
    public class PurchaseOrderDetailViewModel
    {
        public int ProductID { get; set; }

        /// <summary>
        /// 把 Name 轉為 ProductName
        /// </summary>
        public string ProductName { get; set; }

        public Int16 OrderQty { get; set; }

        public decimal UnitPrice { get; set; }

        public decimal LineTotal { get; set; }
    }
}

EFCore 7.0 實際測試

namespace EFCoreQuery
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022Context();

            // CASE 1：使用 EFCore 7.0，只能回傳 Scalar Value
            var productID = new SqlParameter("@ProductID", SqlDbType.Int);
            productID.Value = 403;

            var result6 = await dbContext.Database.SqlQuery<int>(
                $"SELECT ProductID FROM Production.[Product] WHERE ProductID = {productID}")
                .ToListAsync();
            Console.WriteLine(result6.Count().ToString());
            // TSQL：exec sp_executesql
            //     N'SELECT ProductID FROM Production.[Product] WHERE ProductID = @ProductID',
            //     N'@ProductID int',@ProductID=403

            // CASE 2：EFCore 7.0 不支援回傳 unmapped types
            var purchaseOrderID = new SqlParameter("@PurchaseOrderID", SqlDbType.SmallInt);
            purchaseOrderID.Value = 8;

            string TSQL7 = @"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderDetail] AS POD
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                WHERE POD.PurchaseOrderID = @PurchaseOrderID
                ";

            var result7 = await dbContext.Database.SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL7, purchaseOrderID).ToListAsync();
            Console.WriteLine(result7.Count().ToString());
            // EFCore 7.0 會拋出下列錯誤，EFCore 8.0 才能正常執行
            // Unhandled exception. System.InvalidOperationException:
            // The element type 'EFCoreQuery.Models.PurchaseOrderDetailViewModel' used in 'SqlQuery' method is not natively supported by your database provider.
            // Either use a supported element type, or use ModelConfigurationBuilder.DefaultTypeMapping to define a mapping for your type.
        }
    }
}

EFCore 8.0 實際測試

TSQL 和 LINQ WHERE 條件混搭方式在 EFCore 7.0 就可以，該筆記寫在 EFCore 8.0 內紀錄而已，SqlQuery 和 SqlQueryRaw 回傳 IQueryable，所以後續才能再使用 LINQ WHERE 來篩選資料

namespace EFCoreQuery
{
    internal class Program
    {
        static async Task Main(string[] args)
        {
            using var dbContext = new AdventureWorks2022Context();

            // CASE 1：：EFCore 8.0 支援 unmapped types
            var orderDate = new SqlParameter("@OrderDate", SqlDbType.DateTime);
            orderDate.Value = new DateTime(2014, 1, 1);

            FormattableString TSQL8 = $@"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderHeader] AS POH
                    JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                WHERE POH.OrderDate >= {orderDate}
                ";

            var result8 = await dbContext.Database
                .SqlQuery<PurchaseOrderDetailViewModel>(TSQL8)
                .Where(vw => vw.UnitPrice > 80)
                .ToListAsync();

            Console.WriteLine(result8.Count().ToString());
            // TSQL：exec sp_executesql
            //     N'SELECT [e].[LineTotal], [e].[OrderQty], [e].[ProductID], [e].[ProductName], [e].[UnitPrice]
            //       FROM(
            //              SELECT
            //                  P.ProductID,
            //                  P.[Name] AS ProductName,
            //                  POD.OrderQty,
            //                  POD.UnitPrice,
            //                  POD.LineTotal
            //              FROM[Purchasing].[PurchaseOrderHeader] AS POH
            //                  JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
            //                  JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
            //              WHERE POH.OrderDate >= @OrderDate
            //           ) AS[e]
            //       WHERE[e].[UnitPrice] > 80.0',
            //     N'@OrderDate datetime',@OrderDate='2014 - 01 - 01 00:00:00'

            // CASE 2：模擬條件串接情況
            int? productID = 1;
            short? orderQty = 4;

            string TSQL9 = @"
                SELECT 
                    P.ProductID ,
                    P.[Name] AS ProductName , 
                    POD.OrderQty ,
                    POD.UnitPrice ,
                    POD.LineTotal 
                FROM [Purchasing].[PurchaseOrderHeader] AS POH
                    JOIN [Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
                    JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
                ";

            var result = dbContext.Database
                .SqlQueryRaw<PurchaseOrderDetailViewModel>(TSQL9);

            if (productID.HasValue)
                result = result.Where(vw => vw.ProductID == productID.Value);

            if (orderQty.HasValue)
                result = result.Where(vw => vw.OrderQty >= orderQty.Value);

            Console.WriteLine((await result.ToListAsync()).Count().ToString());

            // TSQL：exec sp_executesql 
            //     N'SELECT [e].[LineTotal], [e].[OrderQty], [e].[ProductID], [e].[ProductName], [e].[UnitPrice]
            //       FROM(
            //              SELECT
            //                  P.ProductID,
            //                  P.[Name] AS ProductName,
            //                  POD.OrderQty,
            //                  POD.UnitPrice,
            //                  POD.LineTotal
            //              FROM[Purchasing].[PurchaseOrderHeader] AS POH
            //                  JOIN[Purchasing].[PurchaseOrderDetail] AS POD ON POH.PurchaseOrderID = POD.PurchaseOrderID
            //                  JOIN Production.[Product] AS P ON POD.ProductID = P.ProductID
            //           ) AS[e]
            //       WHERE[e].[ProductID] = @__productID_Value_1
            //              AND[e].[OrderQty] >= @__orderQty_Value_2',
            //       N'@__productID_Value_1 int, @__orderQty_Value_2 smallint',@__productID_Value_1=1,@__orderQty_Value_2=4

        }
    }
}

從上述兩段語法就可以發現 LINQ WHERE 語法產生條件，會在主要的 TSQL 語法外再包一層

• 參考資料
• Raw SQL Query without DbSet - Entity Framework Core

[RV] 影像控件 - QRCode 顯示

論壇問題，基本上是在 SSRS 上顯示 QRCode，剛好可以拿來練習影像控件 (Image) 顯示圖檔。

SSRS Image 控件有三種影像來源模式，分別為

• 外部 (External)：[RV] 顯示外部圖片
• 內崁 (Embedded)：[SSRS] 格式化條件 - 顯示圖片
• 資料庫 (data-bound)

該筆記是使用 [資料庫 (data-bound)] ，來製作財產標籤，標籤內包含 QRCode

資料來源

在 AdventureWork2022 內建立 Asset

USE [AdventureWorks2022]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Asset](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[NO] [char](5) NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
	[PurchDate] [date] NULL,
	[Location] [nvarchar](20) NOT NULL,
	[Department] [nvarchar](20) NOT NULL,
 CONSTRAINT [PK_Asset] 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

ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_NO]  DEFAULT ('') FOR [NO]
GO

ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_Name]  DEFAULT (N'') FOR [Name]
GO

ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_Location]  DEFAULT (N'') FOR [Location]
GO

ALTER TABLE [dbo].[Asset] ADD  CONSTRAINT [DF_Asset_Department]  DEFAULT (N'') FOR [Department]
GO

-- 建立假資料
INSERT INTO [dbo].[Asset] ([NO],[Name],[PurchDate],[Location],[Department]) VALUES
	('A0001' , N'商用電腦' , '20240101' , N'職員一' , N'行政') ,
	('A0002' , N'事務機'   , '20230707' , N'職員一' , N'行政') ,
	('A0003' , N'油壓台車' , '20200820' , N'職員二' , N'運輸') ,
	('A0004' , N'貨車'     , '20190505' , N'職員二' , N'運輸') ,
	('A0005' , N'手機'     , '20190607' , N'職員二' , N'運輸')
GO

Model 和 ReportViewModel

使用 Entity Framework 來存取資料並建立 ReportViewModel 來呈現 QRCode

namespace ZXingQRCode
{
    public class AssetReportModel : Asset
    {
        public byte[] QRcodeInfo { get; set; }
    }
}

QRCode

使用 ZXing 套件來產生 QRCode 圖檔

using System;
using System.Drawing;
using System.Drawing.Imaging;
using System.IO;
using ZXing;

namespace ZXingQRCode
{
    internal static class QRCodeGenerator
    {
        public static byte[] Create(string content)
        {
            if (string.IsNullOrWhiteSpace(content))
                throw new ArgumentNullException(content);

            BarcodeWriter writer = new BarcodeWriter
            {
                Format = BarcodeFormat.QR_CODE,
                Options = new ZXing.QrCode.QrCodeEncodingOptions
                {
                    Margin = 0,
                    Width = 200,
                    Height = 200
                }
            };

            using (Bitmap bitmap = writer.Write(content))
            using (MemoryStream stream = new MemoryStream())
            {
                bitmap.Save(stream, ImageFormat.Png);
                return stream.ToArray();
            }
        }
    }
}

報表設計

設計 8 X 5 cm 大小標籤，並使用清單來呈現每一筆資料，同時把自訂編號資訊轉成 QRCode 來呈現

影像控件設定

程式執行

using Microsoft.Reporting.WinForms;
using System;
using System.Data;
using System.Linq;
using System.Windows.Forms;

namespace ZXingQRCode
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            var dbContext = new AdventureWorks2022Entities();
            var dataSources = dbContext.Asset.ToList();
            var reportSource = dataSources.Select(s => new AssetReportModel()
            {
                NO = s.NO,
                Name = s.Name,
                PurchDate = s.PurchDate,
                Location = s.Location,
                Department = s.Department,
                QRcodeInfo = QRCodeGenerator.Create(s.NO)
            }).ToList();

            reportViewer1.LocalReport.DataSources.Add(new ReportDataSource(nameof(AssetReportModel), reportSource));
            reportViewer1.RefreshReport();
        }
    }
}

• 延伸閱讀
• [SSRS] 民國日期

[SQL] 彙總函數應用 - 字串欄位分組

網路問題，基本上和 [SQL] 彙總函數應用 - 資料分組 是類似問題但相同解法，透過累計加總 (Running Total) 來做到字串欄位的資料分組

use tempdb
GO

DROP TABLE IF EXISTS #Temp

CREATE TABLE #Temp 
(
    ID int IDENTITY(1,1),
    Data char(1) null
)

INSERT INTO #Temp VALUES
    ('1') , (null) , (null) ,
    ('5') , (null) , (null) ,
    ('8') , (null) , (null) 

SELECT * , 
    SUM(IIF(Data IS NULL , 0 , 1)) OVER 
        (
            ORDER BY ID 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 
        ) AS GroupNo
FROM #Temp

• 延伸閱讀
• [SQL] 視窗函數應用 - 連續字串分組