Lib: 2010/04

-- Group by month
SELECT
YEAR([ImportDate]) iYear,
MONTH([ImportDate]) iMonth,
SUM([SourceItemCount]) ItemCount,
SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY YEAR([ImportDate]), MONTH([ImportDate])
ORDER BY iYear DESC, iMonth DESC
-- Group by week
SELECT
DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690) iWeek,
SUM([SourceItemCount]) ItemCount,
SUM([ImportItemCount]) ImportCount
FROM [RssFromOrgImportLog] WITH (NOLOCK)
GROUP BY DATEADD(dd, (DATEDIFF(dd, -53690, [ImportDate])/7) * 7, -53690)
ORDER BY iWeek DESC

public class GZipLogger
{
    /// <summary>
    /// GZip起始位元
    /// </summary>
    private static byte[] m_byGzHeader = new byte[] { 
        31, 139, 8, 0, 0, 0, 0, 0, 4, 0, 237, 189, 7, 96, 28, 73, 150, 37, 38, 47, 109, 
        202, 123, 127, 74, 245, 74, 215, 224, 116, 161, 8, 128, 96, 19, 36, 216, 144, 64, 
        16, 236, 193, 136, 205, 230, 146, 236, 29, 105, 71, 35, 41, 171, 42, 129, 202, 101,
        86, 101, 93, 102, 22, 64, 204, 237, 157, 188, 247, 222, 123, 239, 189, 247, 222,
        123,239, 189, 247, 186, 59, 157, 78, 39, 247, 223, 255, 63, 92, 102, 100, 1, 108, 
        246, 206, 74, 218, 201, 158, 33, 128, 170, 200, 31, 63, 126, 124, 31, 63, 34 
    };
    /// <summary>
    /// 記錄Log
    /// </summary>
    /// <param name="strLogFileName">檔案位置</param>
    /// <param name="strMessage">訊息</param>
    public static void Log(string strLogFileName, string strMessage)
    {
        FileStream fsFileInput = new FileStream(strLogFileName, FileMode.Append);
        GZipStream gzsFileInput = new GZipStream(fsFileInput, CompressionMode.Compress);
        byte[] byBuffer = Encoding.UTF8.GetBytes(strMessage);
        gzsFileInput.Write(byBuffer, 0, byBuffer.Length);
        gzsFileInput.Close();
        fsFileInput.Close();
    }
    /// <summary>
    /// 讀取Log
    /// </summary>
    /// <param name="strLogFileName">Log檔位置</param>
    /// <returns>Log內容</returns>
    public static string Read(string strLogFileName)
    {
        if (String.IsNullOrEmpty(strLogFileName))
            throw new ArgumentException("strLogFileName is null or empty.", "strLogFileName");
 
        StringBuilder sbTemp = new StringBuilder();
 
        #region 讀檔
        FileStream fsInputFile = new FileStream(strLogFileName, FileMode.Open);
        MemoryStream msInputFile = new MemoryStream();
        int iReaded;
        byte[] byBuffer = new byte[512];
        while ((iReaded = fsInputFile.Read(byBuffer, 0, byBuffer.Length)) > 0)
        {
            msInputFile.Write(byBuffer, 0, iReaded);
        }
        fsInputFile.Close();
        msInputFile.Close();
        #endregion
 
        List<byte[]> lsSection = SplitBytes(msInputFile.ToArray());
        foreach (byte[] bySection in lsSection)
        {
            sbTemp.Append(UnZip(bySection));
        }
        return sbTemp.ToString();
    }
    /// <summary>
    /// 還原壓縮的GZip位元陣列為字串
    /// </summary>
    /// <param name="byBuffer">已壓縮的GZip位元陣列</param>
    /// <returns>還原的字串</returns>
    private static string UnZip(byte[] byBuffer)
    {
        if (byBuffer == null || byBuffer.Length == 0)
            throw new ArgumentException("byBuffer is null or empty.", "byBuffer");
 
        MemoryStream msInput = new MemoryStream(byBuffer);
        GZipStream gzsInput = new GZipStream(msInput, CompressionMode.Decompress);
        StreamReader srInput = new StreamReader(gzsInput);
        string strContent = srInput.ReadToEnd();
        srInput.Close();
        gzsInput.Close();
        msInput.Close();
        return strContent;
    }
    /// <summary>
    /// 分割GZip位元陣列
    /// </summary>
    /// <param name="byBuffer">來源位元陣列</param>
    /// <returns>分割結果</returns>
    private static List<byte[]> SplitBytes(byte[] byBuffer)
    {
        if (byBuffer == null || byBuffer.Length == 0)
            throw new ArgumentException("byBuffer is null or empty.", "byBuffer");
 
        #region 記錄每個區段起始點
        List<int> lsBreaks = new List<int>();
        int iStartIndex = 0;
        while ((iStartIndex = IndexOf(byBuffer, iStartIndex)) > -1)
        {
            lsBreaks.Add(iStartIndex);
            iStartIndex++;
        }
        lsBreaks.Add(byBuffer.Length);
        #endregion
 
        #region 分割
        List<byte[]> lsSection = new List<byte[]>();
        for (int i = 0; i < lsBreaks.Count - 1; i++)
        {
            int iArrayLength = lsBreaks[i + 1] - lsBreaks[i];
            byte[] byTemp = new byte[iArrayLength];
            Array.Copy(byBuffer, lsBreaks[i], byTemp, 0, byTemp.Length);
            lsSection.Add(byTemp);
        }
        #endregion
 
        return lsSection;
    }
    /// <summary>
    /// 傳回下一個GZip位元陣列的起始位置
    /// </summary>
    /// <param name="byBuffer">來源位元陣列</param>
    /// <param name="iStartIndex">起始位置，若找不到則傳回-1</param>
    /// <returns></returns>
    private static int IndexOf(byte[] byBuffer, int iStartIndex)
    {
        if (byBuffer == null || byBuffer.Length == 0)
            throw new ArgumentException("byBuffer is null or empty.", "byBuffer");
 
        if (iStartIndex > -1 && (iStartIndex + m_byGzHeader.Length < byBuffer.Length))
        {
            for (int i = iStartIndex; i < byBuffer.Length; i++)
            {
                // 起始位元相同, 且剩餘長度大於GZip標頭時
                if (byBuffer[i] == m_byGzHeader[0] && byBuffer.Length - i - 1 >= m_byGzHeader.Length)
                {
                    #region 比對標頭
                    byte[] temp = new byte[m_byGzHeader.Length];
                    Array.Copy(byBuffer, i, temp, 0, temp.Length);
                    if (CompareHeader(temp))
                    {
                        return i;
                    }
                    #endregion
                }
            }
        }
        return -1;
    }
    /// <summary>
    /// 比對起始的GZip位元
    /// </summary>
    /// <param name="byBuffer">來源位元陣列</param>
    /// <returns>位元陣列相同為True, 反之為False</returns>
    private static bool CompareHeader(byte[] byBuffer)
    {
        if (byBuffer == null || byBuffer.Length == 0)
            throw new ArgumentException("byBuffer is null or empty.", "byBuffer");
 
        if (byBuffer.Length != m_byGzHeader.Length) return false;
        for (int i = 0; i < byBuffer.Length; i++)
        {
            if (byBuffer[i] != m_byGzHeader[i]) return false;
        }
        return true;
    }
 
}

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace TestConsole
{
class Program
{
static void Main(string[] args)
{
Person p = new Person("Bruce", 30);
Console.WriteLine("PrintXML : ");
PrintXML(p);
Console.WriteLine("PrintTwo : ");
PrintTwo("Time", DateTime.Now);
StringBuilder sb = new StringBuilder();
//p = CreateGenericNew(p); // without public non-parameter constructor, not allowed
sb = CreateGenericNew(sb); // it's ok
Console.WriteLine("Object is null : {0}", sb == null);
p = CreateGenericDefault(p); // it's ok
sb = CreateGenericDefault(sb); // it's ok
Console.WriteLine("Object is null : {0}", sb == null);
Console.Read();
}
static void PrintXML<T>(T obj) where T : ISerializeXML
{
Console.WriteLine(obj.ToXML());
}
static void PrintTwo<T, V>(T key, V value)
{
Console.WriteLine("{0}={1}", key, value);
}
static T CreateGenericNew<T>(T obj) where T : new()
{
return new T();
}
static T CreateGenericDefault<T>(T obj)
{
return default(T);
}
}
interface ISerializeXML
{
string ToXML();
}
class Person : ISerializeXML
{
public string Name { get; private set; }
public int Age { get; private set; }
public Person(string name, int age)
{
this.Name = name;
this.Age = age;
}
#region ISerializeXML 成員
public string ToXML()
{
return string.Format(
@"<person><name>{0}</name><age>{1}</age></person>",
this.Name,
this.Age
);
}
#endregion
public override string ToString()
{
return string.Format("{0},{1}", this.Name, this.Age);
}
}
}