星期日, 1月 19, 2020

[VFP] CursorAdapter 和參數相依

VFP CursorAdapter SelectCommand 內的 TSQL 語法,可以直接抓參數塞入,參數和 CursorAdapter 有了相依,萬一參數名稱要進行修正,還要進到 SelectCommand 內去修正,那不幸該 CursorAdapter 用在多個地方,就變成一件大工程,同事修改 Code 時就遇上該情況

[VFP] CursorAdapter 和變數相依-3
loDemoTable = Createobject("caCollection.caDemoTable")
liID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)
討論時,直覺是在 CursorAdapter 建立 Property,外面針對 Property 來進行設定,CursorAdapter SelectCommand 就直接抓該 Property 就行

[VFP] CursorAdapter 和變數相依-2

[VFP] CursorAdapter 和變數相依-4
loDemoTable = Createobject("caCollection.caDemoTable")
loDemoTable.ID = 2
lbDemoTable = loDemoTable.CursorFill(.T.)
經過測試,確認 SelectCommand 是可以直接抓 Property 來當成參數,SQL Profile 內都是相同的 TSQL 語法

[VFP] CursorAdapter 和變數相依-1

VFP 寫久麻痺,沒有考慮到兩者相依性問題

星期五, 1月 10, 2020

電源插頭轉換器

研討會問券禮,實用

電源插頭轉換器

星期六, 1月 04, 2020

[EF] 使用 Function

看見網路問題才發現到,原來 EF 可以使用 SQL Server 內的使用者自訂函數,利用 AdventureWorks2017 來筆記一下 ,兩個重點
  • 呼叫 SQL Server 的使用者自訂函數
  • 利用 EF 6.0 的 DbFunctions 來使用 SQL Server 日期函數
使用 AdventureWork2017 內現有的 Function - ufnGetSalesOrderStatusText
CREATE FUNCTION [dbo].[ufnGetSalesOrderStatusText](@Status [tinyint])
RETURNS [nvarchar](15) 
AS 
BEGIN
    DECLARE @ret [nvarchar](15);

    SET @ret = 
        CASE @Status
            WHEN 1 THEN 'In process'
            WHEN 2 THEN 'Approved'
            WHEN 3 THEN 'Backordered'
            WHEN 4 THEN 'Rejected'
            WHEN 5 THEN 'Shipped'
            WHEN 6 THEN 'Cancelled'
            ELSE '** Invalid **'
        END;
    
    RETURN @ret
END;
GO
使用 [ADO.NET 實體資料模型] 把 [SalesOrderHeader] Table 和 [ufnGetSalesOrderStatusText] Function 拉進來使用

[EF] 使用 Function-1

利用 partial class 擴充 DbContext 類別
using System.Data.Entity;

namespace EFSQLFunction
{
    public partial class EFDBContext
    {                  
        // SQL Server 名稱為 ufnGetSalesOrderStatusText,在 VS 內為滿足命名規則,把前綴詞的 ufn 移除
        [DbFunction("AdventureWorks2017Model.Store" , "ufnGetSalesOrderStatusText")]
        public string GetSalesOrderStatusText(byte Status)
        {
            throw new NotSupportedException("Direct calls are not Support");
        }
    }
}
DbFunctionAttrubute 參數一為 namespace、參數二為 Function 名稱,可以參考下圖輸入

[EF] 使用 Function-3

驗證在 LINQ 內使用 Function 能轉成 TSQL 語法
using System.Data.Entity;

namespace EFSQLFunction
{
    class Program
    {
        static void Main(string[] args)
        {
            using (EFDBContext context = new EFDBContext())
            {
                context.Database.Log = Console.Write;

                DateTime target = new DateTime(2011, 5, 31);
                context.SalesOrderHeader
                    // 透過 DbFunctions 來使用內建 Function
                    .Where(w => w.OrderDate >= target
                    && w.OrderDate <= DbFunctions.AddDays(target, 7))
                    .Select(s => new
                    {
                        s.SalesOrderNumber,
                        s.Status,
                        // 呼叫自定義的 Function
                        StatusText = context.GetSalesOrderStatusText(s.Status)
                    })
                    .ToList();
            }
        }
    }
}
觀察產生的 TSQL 語法

[EF] 使用 Function-2

無法轉換的話,會拋出下圖的 Exception

[EF] 使用 Function-4

星期六, 12月 28, 2019

[C#] 利用 OleDB 來存取 Excel

學習在 C# 上透過 OleDB 存取 Excel

Sample Excel Data

[C#] 利用 OleDB 來存取 Excel-1


Sample Code
// 必須加入該命名空間
using System.Data.OleDb;

namespace XLSByOleDB
{
    class Program
    {
        static void Main(string[] args)
        {
            string FileFullName = @"D:\XlsDemo.xlsx";

            string ExcelConnectionString = GetExcelConnectionString(FileFullName);

            DataTable sheetsInfo = GetExcelSheetNames(ExcelConnectionString);

            string sheetName;
            foreach (DataRow sheet in sheetsInfo.Rows)
            {
                sheetName = sheet["Table_Name"].ToString();
                // OR sheetName = sheet[2].ToString();
                Console.WriteLine($"SheetName:{sheetName} 相關資料");

                DataTable data = GetExcelData(ExcelConnectionString, sheetName);
                foreach (DataRow row in data.Rows)
                {
                    // 欄位1 為該 Sheet 第一筆資料,也就是欄位名稱
                    Console.WriteLine(row["欄位1"].ToString());
                }
            }
        }

        /// <summary>
        /// 產生 Excel 檔案連線字串
        /// </summary>
        /// <param name="FileFullName">Excel 檔案完整路徑</param>
        /// <returns>Excel 檔案連線字串</returns>
        private static string GetExcelConnectionString(string FileFullName)
        {
            OleDbConnectionStringBuilder builder = new OleDbConnectionStringBuilder
            {
                Provider = "Microsoft.ACE.OLEDB.12.0",
                DataSource = FileFullName
            };

            string Extension = Path.GetExtension(FileFullName);
            string ExcelVersion;
            string XML;
            if (Extension == ".xlsx")
            {
                ExcelVersion = "12.0";
                XML = nameof(XML);
            }
            else
            {
                ExcelVersion = "8.0";
                XML = string.Empty;
            }
            builder["Extended Properties"] = $"Excel {ExcelVersion} {XML};HDR=YES;IMEX=1";

            return builder.ConnectionString;
        }

        /// <summary>
        /// 抓取 Excel 內全部 Sheet 
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <returns>Excel 內全部 Sheet</returns>
        private static DataTable GetExcelSheetNames(string connectionString)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                // OR
                // DataTable dt = conn.GetSchema("Tables");
                return dt;
            }
        }

        /// <summary>
        /// 取得指定 Sheet 內資料
        /// </summary>
        /// <param name="connectionString">Excel 檔案連線字串</param>
        /// <param name="sheetName">指定 Sheet 名稱</param>
        /// <returns>指定 Sheet 內資料</returns>
        private static DataTable GetExcelData(string connectionString, string sheetName)
        {
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                // Sheet名稱,必需用中括號 [] 包起來
                string TSQL = $"SELECT * FROM [{sheetName}]";
                OleDbDataAdapter da = new OleDbDataAdapter(TSQL, connectionString);
                DataTable dt = new DataTable();
                da.Fill(dt);
                return dt;
            }
        }

    }
}
結果

[C#] 利用 OleDB 來存取 Excel-2

星期二, 12月 24, 2019

[SQL] 資料表提示-ReadPast

討論 NOLOCK 常常會一併討論 READPAST,相較於 NOLOCK 會產生 Dirty Read 問題,READPAST 沒有 Dirty Read 困惱,但 READPAST 會略過被封鎖資料

官方文件說明如下
指定 Database Engine 不讀取其他交易已鎖定的資料列。 當指定 READPAST 時,系統會略過資料列層級鎖定,但不會略過頁面層級的鎖定。 亦即,Database Engine 會略過資料列,而不會封鎖目前的交易,直到釋放鎖定為止。 例如,假設資料表 T1 包含了值為 1、2、3、4、5 的單一整數資料行。 如果交易 A 將 3 的值變更為 8,但是尚未認可,則 SELECT * FROM T1 (READPAST) 會產生 1、2、4、5 的值。 READPAST 主要是在實作使用 SQL Server 資料表的工作佇列時用來減少鎖定競爭。 使用 READPAST 的佇列讀取器會略過其他交易已鎖定的佇列項目,直接到下一個可用的佇列項目,不需要等待其他交易釋放鎖定。
依上述範例來驗證
USE AdventureWorks
GO

DROP TABLE IF EXISTS tbReadPastDemo

CREATE TABLE tbReadPastDemo
(
  ID int identity(1,1) Primary key,
  Col1 int
)
 
INSERT INTO tbReadPastDemo (Col1) 
VALUES(1) , (2) , (3) , (4) , (5)
 
-- 顯示建立資料
SELECT * FROM tbReadPastDemo

----- ReadPast Demo
----- Session A
-- Step1:開啟交易並更新 ID = 3 資料為 8,此時交易還在
begin transaction
UPDATE tbReadPastDemo SET Col1 = 8 WHERE ID = 3

-- Step3:結束交易
ROLLBACK

----- Session B
-- Step2:利用 ReadPast 查詢 ID = 2 資料,此時 Session1 還在交易內
SELECT * FROM tbReadPastDemo WITH (ReadPast)
從 Session2 可以發現,只有 4 筆資料,ID = 3 的資料被忽略了

[SQL] 資料表提示-ReadPast-1

UPDATE、DELETE 官方文件說明
UPDATE 或 DELETE 陳述式所參考的任何資料表,以及 FROM 子句所參考的任何資料表,都可以指定 READPAST。 當在 UPDATE 陳述式中指定 READPAST 時,只有在讀取資料來識別要更新的記錄時,才會套用 READPAST,不論是在陳述式的哪個位置指定,都是如此。 INSERT 陳述式 INTO 子句中的資料表不能指定 READPAST。 當讀取外部索引鍵或索引檢視表時,或當修改次要索引時,使用 READPAST 的更新或刪除作業可能會進行封鎖。