use AdventureWorks2022
go
CREATE OR ALTER PROCEDURE uspEF_ExecuteSqlCommand_Output
(
@BusinessEntityID int ,
@FullName varchar(100) OUTPUT
)
AS
BEGIN
SELECT
@FullName = TRIM(LastName) + '-' + TRIM(FirstName)
FROM Person.Person
WHERE BusinessEntityID = @BusinessEntityID
END
namespace EFSPOutput_ExecuteSqlCommand
{
internal class Program
{
static void Main(string[] args)
{
AdventureWorks2022Entities dbContext = new AdventureWorks2022Entities();
string TSQL = @"EXEC uspEF_ExecuteSqlCommand_Output @BusinessEntityID , @FullName OUTPUT";
List<SqlParameter> parameters = new List<SqlParameter>();
parameters.Add(new SqlParameter("@BusinessEntityID", SqlDbType.Int) { Value = 1 });
var outputPara = new SqlParameter("@FullName", SqlDbType.VarChar, 100)
{
Direction = ParameterDirection.Output
};
parameters.Add(outputPara);
dbContext.Database.ExecuteSqlCommand(TSQL, parameters.ToArray());
Console.WriteLine(outputPara.Value.ToString());
}
}
}
-- 把介面轉成 trunk
switchport mode trunk
-- 顯示目前設定為 trunk 的介面
show interface trunk
-- 顯示 switch 上的 vlan 設定
show vlan brief
-- 新增 vlan
vlan vlan數字
-- 刪除 vlan
no vlan vlan數字
-- vlan 更名
name vlan名稱
-- 指定多個 Port
interface range PortRange (EX：fa0/3 - fa0/4)
-- 指定 Port 所屬 vlan
switchport 存取模式 (access/trunk) vlan vlan數字
The DataGridView control does not support double buffering. If DoubleBuffered is set to true in a derived DataGridView control, users will not receive visual feedback when resizing rows, columns, or headers or when reordering columns.所在環境也是有自訂 DataGridView 並開啟 DoubleBuffered 來使用，一直以來沒有出現大問題，測試理解何謂 visual feedback 效果
using System.Windows.Forms;
namespace AvoidDoubleBuffer
{
public class UCDataGridView : DataGridView
{
public UCDataGridView()
{
DoubleBuffered = true;
}
}
}
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
Unhandled exception. System.InvalidOperationException: The required column 'ProductName' was not present in the results of a 'FromSql' operation.
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());
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
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): 無效的資料行名稱 'ProductName'。
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; }
}
}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
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());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
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; }
}
}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.
}
}
}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
}
}
}
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'運輸')
GOnamespace ZXingQRCode
{
public class AssetReportModel : Asset
{
public byte[] QRcodeInfo { get; set; }
}
}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();
}
}
}
}
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();
}
}
}
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 #Tempusing EFCoreQuery.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
namespace EFCoreQuery
{
internal class Program
{
static async Task Main(string[] args)
{
using var dbContext = new AdventureWorks2022Context();
// CASE 1：常數
var result1 = await dbContext.Product.FromSql(
$"SELECT * FROM Production.Product WHERE Class = N'H'").ToListAsync();
Console.WriteLine(result1.Count().ToString());
// TSQL：SELECT * FROM Production.Product WHERE Class = N'H'
// CASE2：C# 字串插補傳遞變數，TSQL 會參數化，資料型態、長度為 nvarchar(4000)
string data = "H";
var result2 = await dbContext.Product.FromSql(
$"SELECT * FROM Production.Product WHERE Class = {data}").ToListAsync();
Console.WriteLine(result2.Count().ToString());
// TSQL：exec sp_executesql
// N'SELECT * FROM Production.Product WHERE Class = @p0' ,
// N'@p0 nvarchar(4000)',@p0=N'H'
// CASE3：C# 字串插補傳遞 SqlParameter 並對應欄位資料型態、長度 nchar(2)，TSQL 會參數化
var para = new SqlParameter("@Class", SqlDbType.NChar, 2);
para.Value = "H";
var result3 = await dbContext.Product.FromSql(
$"SELECT * FROM Production.Product WHERE Class = {para}").ToListAsync();
Console.WriteLine(result3.Count().ToString());
// TSQL：exec sp_executesql
// N'SELECT * FROM Production.Product WHERE Class = @Class',
// N'@Class nchar(2)',@Class=N'H '
}
}
}using EFCoreQuery.Models;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore;
using System.Data;
namespace EFCoreQuery
{
internal class Program
{
static async Task Main(string[] args)
{
using var dbContext = new AdventureWorks2022Context();
// CASE1：C# 字串插補傳遞欄位名稱、SqlParameter 參數值
string columnName = "Class";
var para = new SqlParameter("@Class", SqlDbType.NChar, 2);
para.Value = "H";
var result4 = await dbContext.Product.FromSqlRaw(
$"SELECT * FROM Production.Product WHERE {columnName} = @Class", para).ToListAsync();
Console.WriteLine(result4.Count().ToString());
// TSQL：exec sp_executesql
// N'SELECT * FROM Production.Product WHERE Class = @Class',
// N'@Class nchar(2)',@Class=N'H '
// CASE2：C# 字串插補傳遞變數只能應用在欄位
string data = "H";
var result5 = await dbContext.Product.FromSqlRaw(
$"SELECT * FROM Production.Product WHERE Class = {data}").ToListAsync();
Console.WriteLine(result5.Count().ToString());
// Exception：Unhandled exception.
// Microsoft.Data.SqlClient.SqlException (0x80131904):
// 無效的資料行名稱 'H'
}
}
}
- The SQL query must return data for all properties of the entity type.
- The column names in the result set must match the column names that properties are mapped to. Note that this behavior is different from EF6; EF6 ignored property-to-column mapping for SQL queries, and result set column names had to match those property names.
- The SQL query can't contain related data. However, in many cases you can compose on top of the query using the Include operator to return related data (see Including related data).
namespace EFCoreQuery
{
internal class Program
{
static async Task Main(string[] args)
{
using var dbContext = new AdventureWorks2022Context();
// CASE：回傳的 Entity 每個 Property 都要對應到，故意只抓 ProductID 而已
var result = await dbContext.Product.FromSql(
$"SELECT ProductID FROM Production.Product WHERE Class = N'H'").ToListAsync();
Console.WriteLine(result.Count().ToString());
// Unhandled exception. System.InvalidOperationException:
// The required column 'Class' was not present in the results of a 'FromSql' operation.
}
}
}
namespace PathSample
{
internal class Program
{
static void Main(string[] args)
{
List<string> target = new List<string>();
target.Add(@"\2024-10-1817.29.37.777.jpg"); // 正常路徑
target.Add(@"\2024-10-1817.29.37.777"); // 尾端沒有副檔名
target.Add(@"\2024-10-1817.29.37.777.jpg."); // 尾端有 . 符號
target.Add(@"\2024-10-1817.29.37.777.jpg\"); // 尾端有 \ 符號
string extension = string.Empty;
foreach (var path in target)
{
extension = Path.GetExtension(path);
extension = string.IsNullOrWhiteSpace(extension) ? "空值" : extension;
Console.WriteLine($"{path} 副檔名：{extension}");
}
}
}
}
This method obtains the extension of path by searching path for a period (.), starting with the last character in path and continuing toward the first character. If a period is found before a DirectorySeparatorChar or AltDirectorySeparatorChar character, the returned string contains the period and the characters after it; otherwise, String.Empty is returned.C# SourceCode
