[C#] NuGet - LinqToExcel

• Install 

• Data

• Code
• 先建立 excel class 資訊

using LinqToExcel.Attributes;
using System;

namespace LinqToExcelDemo.Models
{
    class opensource
    {
        /// 

        /// 活動名稱
        /// 
        [ExcelColumn("活動名稱")]   //maps the "EventName" property to the "活動名稱" column
        public string EventName { get; set; }

        /// 

        /// 活動類別
        /// 
        [ExcelColumn("活動類別")]
        public string EventCategory  { get; set; }

        /// 

        /// 主辦單位
        /// 
        [ExcelColumn("主辦單位")]
        public string Organizer { get; set; }

        /// 

        /// 來源網站名稱
        /// 
        [ExcelColumn("來源網站名稱")]
        public string SourceSite { get; set; }

        /// 

        /// 活動起始日期
        /// 
        [ExcelColumn("活動起始日期")]
        public DateTime EventStartDate { get; set; }

        /// 

        /// 活動結束日期
        /// 
        [ExcelColumn("活動結束日期")]
        public DateTime EventEndDate { get; set; }

        /// 

        /// 點閱率
        /// 
        [ExcelColumn("點閱率")]
        public int ClickRate { get; set; }                
    }
}

• 使用 LinqToExcel

using LinqToExcel;
using LinqToExcelDemo.Models;
using System.Collections.Generic;
using System.Linq;
using System;

namespace LinqToExcelDemo
{
    class Program
    {
        static void Main(string[] args)
        {
            string FilePath = @".\export.xls";
            string SheetName = "活動基本資料";

            List<opensource> lOpenSource;

            ExcelQueryFactory excelFile = new ExcelQueryFactory(FilePath);
            // 1. Default worksheet name is "Sheet1"
            // 2. Query a specific worksheet by name
            lOpenSource = excelFile.Worksheet<opensource>(SheetName).ToList();
        }
    }
}

• Ref: 
• https://github.com/paulyoder/LinqToExcel
• https://chrisbitting.com/2015/12/24/reading-excel-files-in-net-using-linqtoexcel/

[C#] Performance Testing - Stopwatch

• Code

using System.Diagnostics;
// ...

Stopwatch sw = new Stopwatch();

sw.Start();

// ...

sw.Stop();

Console.WriteLine("Elapsed={0}",sw.Elapsed);

• Ref
• https://stackoverflow.com/questions/969290/exact-time-measurement-for-performance-testing

GoF - Factory

• GoF (3)
• Creational Patterns 
• UML
• 
• Code
• 實作 Adventurer

/// 

/// interface: Adventurer (Product)
/// 
public interface IAdventurer
{
    // 方法
    string getType();
}

• 實作 Archer

/// 

/// Archer 繼承 interface Adventurer (ConcreteProduct)
/// 
public class Archer : IAdventurer
{
    // 實作 getType 方法
    public string getType()
    {
        string message = "I am a Archer";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 Warrior

/// 

/// Warrior 繼承 interface Adventurer
/// 
public class Warrior : IAdventurer
{
    // 實作 getType 方法
    public string getType()
    {
        string message = "I am a Warrior";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 TrainingCamp

/// 

/// interface: TrainingCamp (Factory)
/// 
public interface ITrainingCamp 
{
    Adventurer trainAdventurer();
}

• 實作 ArcherTrainingCmap

/// 

/// ArcherTrainingCmap 繼承 interface ITrainingCamp (ConcreteFactory)
/// 
public class ArcherTrainingCmap : ITrainingCamp
{
    public Adventurer trainAdventurer()
    {
        System.Console.WriteLine("train a Archer");
        return new Archer();
    }
}

• 實作 WarriorTrainingCmap

/// 

/// WarriorTrainingCmap 繼承 interface ITrainingCamp (ConcreteFactory)
/// 
public class WarriorTrainingCmap : ITrainingCamp
{
    public Adventurer trainAdventurer()
    {
        System.Console.WriteLine("train a Warrior");
        return new Warrior();
    }
}

• Sample Code

public class SampleCode 
{
    public void Demo()
    {
        // Archer Training Camp
        TrainingCamp trainingCmap = new ArcherTrainingCamp();
        Adventurer memberA = trainingCmap.trainAdventurer();

        // Warrior Training Camp
        TrainingCamp trainingCmap = new WarriorTrainingCmap();        
        Adventurer memberB = trainingCmap.trainAdventurer();

        System.Console.WriteLine(memberA.getType());
        System.Console.WriteLine(memberB.getType());
    }
}

Ref

• 7 天學會設計模式-設計模式也可以這樣學
• 大話設計模式

GoF - Simple Factory (Interface)

• GoF (2)
• Creational Patterns 
• UML
• 
• Code
• 實作 Adventurer

/// 

/// interface: Adventurer (Product)
/// 
public interface IAdventurer
{
    // 方法
    string getType();
}

• 實作 Archer

/// 

/// Archer 繼承 interface Adventurer (ConcreteProduct)
/// 
public class Archer : IAdventurer
{
    // 實作 getType 方法
    public string getType()
    {
        string message = "I am a Archer";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 Warrior

/// 

/// Warrior 繼承 interface Adventurer (ConcreteProduct)
/// 
public class Warrior : IAdventurer
{
    // 實作 getType 方法
    public string getType()
    {
        string message = "I am a Warrior";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 TrainingCamp

/// 

/// TrainingCamp (SimpleFactory)
/// 
public class TrainingCamp 
{
    public Adventurer trainAdventurer(string type)
    {
        switch (tpye)
        {
            case "archer":
                System.Console.WriteLine("Training a Archer");
                return new Archer();
                break;
            case "warrior":
                System.Console.WriteLine("Training a Warrior");
                return new Warrior();
                break;
            default:
        }
    } 
}

• Sample Code

public class SampleCode 
{
    public void Demo()
    {
        // 創建 TrainingCamp
        TrainingCamp trainingCmap = new TrainingCamp();
        // 帶入不同的參數 archer / warrior
        Adventurer memberA = trainingCmap.trainAdventurer("archer");
        Adventurer memberB = trainingCmap.trainAdventurer("warrior");
        
        System.Console.WriteLine(memberA.getType());
        System.Console.WriteLine(memberB.getType());
    }
}

Ref

• 7 天學會設計模式-設計模式也可以這樣學
• 大話設計模式

GoF - Simple Factory

• GoF (2)
• Creational Patterns 
• UML
• 
• Code
• 實作 Adventurer

/// 

/// 一般類別: Adventurer (Product)
/// 
public class Adventurer
{
    // virtual： 虛擬方法
    public virtual string getType()
    {

    }
}

• 實作 Archer

/// 

/// Archer 繼承 Adventurer (ConcreteProduct)
/// 
public class Archer : Adventurer
{
    // override: 覆寫 getType 方法
    public override string getType()
    {
        string message = "I am a Archer";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 Warrior

/// 

/// Warrior 繼承 Adventurer (ConcreteProduct)
/// 
public class Warrior : Adventurer
{
    // override: 覆寫 getType 方法
    public override string getType()
    {
        string message = "I am a Warrior";
        System.Console.WriteLine(message);
        return message;
    }
}

• 實作 TrainingCamp

/// 

/// TrainingCamp (SimpleFactory)
/// 
public class TrainingCamp 
{
    public Adventurer trainAdventurer(string type)
    {
        switch (tpye)
        {
            case "archer":
                System.Console.WriteLine("Training a Archer");
                return new Archer();
                break;
            case "warrior":
                System.Console.WriteLine("Training a Warrior");
                return new Warrior();
                break;
            default:
        }
    } 
}

• Sample Code

public class SampleCode 
{
    public void Demo()
    {
        // 創建 TrainingCamp
        TrainingCamp trainingCmap = new TrainingCamp();
        // 帶入不同的參數 archer / warrior
        Adventurer memberA = trainingCmap.trainAdventurer("archer");
        Adventurer memberB = trainingCmap.trainAdventurer("warrior");
        
        System.Console.WriteLine(memberA.getType());
        System.Console.WriteLine(memberB.getType());
    }
}

Ref

• 7 天學會設計模式-設計模式也可以這樣學
• 大話設計模式

GoF - Singleton

• GoF (1)
• Creational Patterns 
• UML
• 
• Code

public class Singleton
{
    // 因建立物件需要花費許多資源，此一開始就不建立物件
    private static Singleton instance; 
    // [多執行緒] 建立一個靜態惟獨的行程輔助物件
    private static object sync = new object();
    
    private Singleton()
    {
        // 這裡面跑很多 Code
        // 建立物件需要花費許多資源
    }

    public static Singleton getInstance()
    {
        // 1. 第一次被呼叫的時候，instance為null，要建立物件
        // 2. 判斷實體是否存在，不存在再加鎖處理
        if (instance == null)
        {
            // [多執行緒] 加鎖建立物件，只有一個執行緒可以進入
            lock(sync)
            {
                // [多執行緒] 雙重鎖定
                // 當兩個執行緒調用 getInstance
                // 他們都可以夠過第一重 instance == null
                // 由 lock 機制，這兩個執行緒只能一個進入，另一個在外排隊等候
                // 其中一個進入並出來後，另一個才能進入
                // 如沒有第二重 instance == null
                // 第一個執行緒建立實體，第二個執行緒還是可以繼續在建立新的實體
                if(instance == null)
                {
                    instance = new Singleton();
                }
            }
            
        }

        // 已經有物件存在，直接回傳這個物件
        return instance;
    }
}

Ref: 

• 7 天學會設計模式-設計模式也可以這樣學
• 大話設計模式

[SQL Server] Query fast, but slow from stored procedure

1. ANSI_NULLS Issue
I found the problem, here's the script of the slow and fast versions of the stored procedure:

dbo.ViewOpener__RenamedForCruachan__Slow.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS OFF 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Slow
    @SessionGUID uniqueidentifier
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

dbo.ViewOpener__RenamedForCruachan__Fast.PRC

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE dbo.ViewOpener_RenamedForCruachan_Fast
    @SessionGUID uniqueidentifier 
AS

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

If you didn't spot the difference, I don't blame you. The difference is not in the stored procedure at all. The difference that turns a fast 0.5 cost query into one that does an eager spool of 6 million rows:

Slow: SET ANSI_NULLS OFF

Fast: SET ANSI_NULLS ON

---

This answer also could be made to make sense, since the view does have a join clause that says:

(table.column IS NOT NULL)

So there is some NULLs involved.

---

The explanation is further proved by returning to Query Analizer, and running

SET ANSI_NULLS OFF

.

DECLARE @SessionGUID uniqueidentifier
SET @SessionGUID = 'BCBA333C-B6A1-4155-9833-C495F22EA908'

.

SELECT *
FROM Report_Opener_RenamedForCruachan
WHERE SessionGUID = @SessionGUID
ORDER BY CurrencyTypeOrder, Rank

And the query is slow.

---

So the problem isn't because the query is being run from a stored procedure. The problem is that Enterprise Manager's connection default option is ANSI_NULLS off, rather than ANSI_NULLS on, which is QA's default.

Microsoft acknowledges this fact in KB296769 (BUG: Cannot use SQL Enterprise Manager to create stored procedures containing linked server objects). The workaround is include the ANSI_NULLS option in the stored procedure dialog:

Set ANSI_NULLS ON
Go
Create Proc spXXXX as
....

2. procedure cache issue
I had the same problem as the original poster but the quoted answer did not solve the problem for me. The query still ran really slow from a stored procedure. 
I found another answer here "Parameter Sniffing", Thanks Omnibuzz. Boils down to using "local Variables" in your stored procedure queries, but read the original for more understanding, it's a great write up. e.g.

Slow way:

CREATE PROCEDURE GetOrderForCustomers(@CustID varchar(20))

AS

BEGIN

    SELECT * 

    FROM orders

    WHERE customerid = @CustID

END

Fast way:

CREATE PROCEDURE GetOrderForCustomersWithoutPS(@CustID varchar(20))

AS

BEGIN

    DECLARE @LocCustID varchar(20)

    SET @LocCustID = @CustID




    SELECT * 

    FROM orders

    WHERE customerid = @LocCustID

END

Hope this helps somebody else, doing this reduced my execution time from 5+ minutes to about 6-7 seconds.

REF: 
http://chhtai.blogspot.tw/2016/01/normal-0-0-2-false-false-false-en-us-zh.html
http://stackoverflow.com/questions/440944/sql-server-query-fast-but-slow-from-procedure