Parse IIS log files

Parse IIS log files (Log Type: W3CLOG):

SELECT 
    [LogFilename], 
    [cs-host], 
    [cs-uri-stem], 
    Count(1), 
    Sum([time-taken]) 
FROM '[LOGFILEPATH]'
GROUP BY [cs-uri-stem], [cs-host], [LogFilename] 
ORDER BY [cs-uri-stem], [cs-host], [LogFilename] 

Version control with Git

Version control with Git:

1) Register at GitLab web site; change member permission upon Maintainer.
2) Download and install Git for Windows.
3) Generate SSH key: C:\Users\yourname.ssh\id_rsa, id_rsa.pub (Git Bash => $ ssh-keygen -t rsa -C “user1@company1.tw" => Enter passphrase).
4) GitLab web site => User Settings => SSH Keys => Paste id_rsa.pub data.
5) Download and install Sourcetree for Windows.
6) Sourcetree => Tools => Options => General => SSH Client Configuration (use OpenSSH).
7) Create Project1 under your group on GitLab web site.
8) Create folder D:\Company1\SourceCode\category1\Project_1\
9) Git global setup (Git Bash => git config –global user.name “Your Name" => git config –global user.email “yourname@company1.tw").
10) Cpoy project1 url on GitLab web site (Clone with SSH).
11) Sourcetree => Clone.
12) Add Project1 source code files in the folder.
13) Add .gitingore file (for Visual Studio) in the folder.
14) Sourcetree => commit: Initial commit.
15) Sourcetree => Push => master.

Alternative of OR in WHERE condition

Alternative of OR in WHERE condition:

CREATE PROCEDURE [dbo].[TestSP]
  @IsTest      bit = 0 

AS
-- Alternative of OR in WHERE condition.
BEGIN

  SET NOCOUNT ON;

  IF @IsTest = 1 
  BEGIN
    SET @IsTest = NULL 
  END

  SELECT ID 
  FROM dbo.Test WITH(NOLOCK) 
  WHERE IsTest = ISNULL(@IsTest, IsTest) 

END

 

 

Query data by unix timestamp

Query data by unix timestamp:

CREATE PROCEDURE [dbo].[TestSP]
  @UnixTimestamp  BIGINT

AS
--Query data by unix timestamp.
BEGIN

  SET NOCOUNT ON;

  DECLARE
    @LastUpdateTime    DATETIME,
    @LocalTimestamp    BIGINT = 0

  IF @UnixTimestamp > 0
  BEGIN
    SET @LocalTimestamp = @UnixTimestamp - DATEDIFF(SECOND, CURRENT_TIMESTAMP, GETUTCDATE())
  END

  SET @LastUpdateTime = DATEADD(SECOND, @LocalTimestamp, {d '1970-01-01'})

  SELECT
    ID
  FROM
    dbo.Test WITH(NOLOCK, INDEX=[IX_Time])
  WHERE
    LastUpdateTime >= @LastUpdateTime

END

Reference:

1) How can I convert bigint (UNIX timestamp) to datetime in SQL Server? https://stackoverflow.com/questions/2904256/how-can-i-convert-bigint-unix-timestamp-to-datetime-in-sql-server
2) Convert Datetime to Unix timestamp https://stackoverflow.com/questions/34455408/convert-datetime-to-unix-timestamp

To generate random numbers

To generate random numbers:

public class TestClass
{
  private RNGCryptoServiceProvider _rng = new RNGCryptoServiceProvider();
  private byte[] _uint32Buffer = new byte[4];

  public int RandomNext(int min, int max)
  {
    if (min >= max)
    {
      return min;
    }

    long diff = max + 1 - min;

    while (true)
    {
      _rng.GetBytes(_uint32Buffer);
      uint rand = BitConverter.ToUInt32(_uint32Buffer, 0);

      long lMax = (1 + (long)uint.MaxValue);
      long remainder = lMax % diff;

      if (rand < lMax - remainder)
      {
        return (int)(min + (rand % diff));
      }
    }
  }

  private void ToTest()
  {
    StringBuilder log = new StringBuilder();
    for (int i = 0; i < 1000; i++)
    {
      log.Append(RandomNext(0, 1)).AppendLine();
    }

    WriteLog(log.ToString());
  }
}

 
“Since the numerator is the only place that we can introduce randomness, and since a Double is 64 bits long, rather than generating a UInt32 and dividing by UInt32.MaxValue+1, we could try generating a random UInt64 and dividing by UInt64.Max + 1, thereby doubling the amount of random bits in the input.
This, unfortunately, leads to another problem. Double-precision numbers store an approximation of a real number; System.Double, which complies with the IEEE 754 standard for binary floating-point arithmetic, provides at most 17 decimal digits of precision. Unfortunately, UInt64.MaxValue (18446744073709551615) requires 20 decimal digits of precision. As a result, under .NET floating-point arithmetic, UInt64.MaxValue is equal to UInt64.MaxValue + 1, and, thus, substituting UInt64 for UInt32 in our NextDouble equation will change our range from [0.0, 1.0) to [0.0, 1.0], which violates the design of System.Random’s implementation."

“For an example that demonstrates this skewing, let’s say we want a number in the range [21, 27]. (For this example, we’ll be selecting 8 bits rather than 32 bits just because it’s easier for demonstration, but the principle generalizes out to the 32 bits we’d actually be working with.) Following the equation shown earlier, we’ll now take a random number between 0 and 255 mod 6 + 21 to get our value. If the random number is in the range [0, 251] this works out great as each of the numbers [0,5] has come up exactly 42 times. However, once we move past 251, we’ll get 0 (252), 1 (253), 2 (245), and 3 (255) as values from our mod operation. That means that 0, 1, 2, and 3 have a 43/256 chance of being selected by the mod operation while 4 and 5 only have a 42/256 chance. Therefore, we’ve got a ~16.80 percent chance of getting 21, 22, 23, or 24 back, but only a ~16.41 percent chance of getting 25 or 26 back. Over the long term, if we were an attacker trying to guess outputs, we would be more successful by always choosing a number at the low end of the range."

“As we saw in the previous example, the favoritism happens when the randomly selected value satisfies the following condition:

RandomValue >= RandomRange - (RandomRange % TargetRange)

In our previous example, the RandomRange is 256 and the TargetRange is 6; therefore, favoritism happens when the RandomValue is >= 252."

 
Reference:

1) .NET Matters: Tales from the CryptoRandom https://docs.microsoft.com/en-us/archive/msdn-magazine/2007/september/net-matters-tales-from-the-cryptorandom

2) Buffered CryptoRandom implementation based on Stephen Toub and Shawn Farkas’ CryptoRandom https://gist.github.com/niik/1017834

3) RNGCryptoServiceProvider – generate number in a range faster and retain distribution? https://stackoverflow.com/questions/6299197/rngcryptoserviceprovider-generate-number-in-a-range-faster-and-retain-distribu

4) How do I generate a random int number? https://stackoverflow.com/questions/2706500/how-do-i-generate-a-random-int-number

 

受保護的文章：Convert prices

該內容受密碼保護。如欲檢視請在下方輸入你的密碼：

密碼：

TVP in SQL Server

Table-Valued Parameters (TVP) in SQL Server:

1. Create Type [TVP_Int]:

USE [DB01]
GO

CREATE TYPE [dbo].[TVP_Int] AS TABLE(
    [Val] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [Val] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

 

SSMS : programmability => Types => User-Defined Table Types;

 
2. Stored Procedure:

CREATE PROCEDURE [dbo].[List_TestInfo] 
  @IDs As TVP_Int Readonly

AS
BEGIN

  SET NOCOUNT ON;

  SELECT 
    ID, Name 
  FROM
    TestInfo WITH(NOLOCK) 
  WHERE 
    ID IN (SELECT Val FROM @IDs)

END

 

3. Test SP:

DECLARE @Tbl AS [TVP_Int];
INSERT INTO @Tbl (Val) VALUES (1001)
INSERT INTO @Tbl (Val) VALUES (1002)
INSERT INTO @Tbl (Val) VALUES (1003)

EXEC [List_TestInfo]
    @IDs = @Tbl 

 

4. (C#) Convert SqlParameter (List to DataTable):

private DataTable ToDataTable<T>(List<T> list)
{
  DataTable t = new DataTable();
  t.Columns.Add("Val", typeof(T));

  foreach (var i in list)
  {
    t.Rows.Add(i);
  }

  return t;
}

 

Reference:

1) SQL 2008 TVP資料匯入之火力展示 https://blog.darkthread.net/blog/import-with-sql-tvp
2) SqlBulkCopy Vs. TVP Vs. XML Parameter Vs. Individual Insert Procs for 20-200 Inserts? https://stackoverflow.com/questions/8618261/sqlbulkcopy-vs-tvp-vs-xml-parameter-vs-individual-insert-procs-for-20-200-ins
3) Table-Valued Parameters https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

 

受保護的文章：Probability Formula

該內容受密碼保護。如欲檢視請在下方輸入你的密碼：

密碼：

API response caching

API response caching:

public class CacheBase
{
  protected int _expireDuration;
  protected long _cacheTimestamp;

  public int KeyMaxCount { get; set; }
  public dynamic SingleData { get; set; }
  public List<dynamic> ListOfData { get; set; }

  public CacheBase()
  {
    KeyMaxCount = 2000;
  }

  public bool IsExpire()
  {
    if (CurrentTimestamp() - _cacheTimestamp < _expireDuration)
    {
      return false;
    }

    return true;
  }

  protected long CurrentTimestamp()
  {
    return (long)(DateTime.UtcNow.Subtract(new DateTime(1970, 1, 1))).TotalSeconds;
  }
}

 

public class Person
{
  public int ID { get; set; }
  public string Name { get; set; }
}

public class PersonCache : CacheBase
{
  public PersonCache(List<Person> dataList)
  {
    _expireDuration = 20;
    _cacheTimestamp = CurrentTimestamp();

    if (dataList != null)
    {
      DataList = dataList.Cast<dynamic>().ToList();
    }
  }
}

 

protected bool IsCacheExpire<U>(ConcurrentDictionary<string, U> cacheDict, string key, out U cache)
	where U : CacheBase
{
  cache = default(U);
  if (cacheDict.TryGetValue(key, out cache) && cache != null
    && !cache.IsExpire())
  {
    return false;
  }

  if (cache != null && cacheDict.Count > cache.KeyMaxCount)
  {
    CleanCache(cacheDict);
  }

  return true;
}

protected void CleanCache<U>(ConcurrentDictionary<string, U> cacheDict)
  where U : CacheBase
{
  if (cacheDict == null || cacheDict.Count == 0)
  {
    return;
  }

  NLog.Logger logger = NLog.LogManager.GetLogger("CleanCache");
  StringBuilder log = new StringBuilder();

  try
  {
    LogCache(cacheDict, ref log);

    U got;
    foreach (var cache in cacheDict)
    {
      if (cache.Value != null && cache.Value.IsExpire())
      {
        cacheDict.TryRemove(cache.Key, out got);
      }
    }

    log.AppendLine("[After clean]");
    LogCache(cacheDict, ref log);
    logger.Info(log.ToString());
  }
  catch (Exception ex)
  {
    logger.Error(ex, log.ToString());
  }
}

protected void LogCache<U>(ConcurrentDictionary<string, U> cacheDict, ref StringBuilder log)
{
  if (cacheDict == null || cacheDict.Count == 0)
  {
    return;
  }

  log.AppendFormat("Cache Count: {0}", cacheDict.Count).AppendLine();
  log.Append("Keys: ");
  foreach (var cache in cacheDict)
  {
    log.AppendFormat("{0},", cache.Key);
  }
  log.AppendLine();
}

protected void AddNewCache<T>(ConcurrentDictionary<int, T> cacheDict, int key, T data)
{  //To reset the Cache Timestamp.
  T cache;
  if (cacheDict.TryGetValue(key, out cache))
  {
    cacheDict.TryRemove(key, out cache);
  }
  cacheDict.TryAdd(key, data);
}

 

private static ConcurrentDictionary<int, PersonCache> _personCacheDict = new ConcurrentDictionary<int, PersonCache>();

public List<Person> GetPeople(int keys)
{

  #region Query from cache

  uncachedKeys = new List<int>();
  List<Person> rtn = new List<Person>();

  foreach (int key in keys)
  {
    PersonCache cache;
    if (!IsCacheExpire<PersonCache>(_personCacheDict, key, out cache) 
      && cache.DataList != null)
    {
      cache.DataList.ForEach(data => rtn.Add(data));
    }
    else
    {
      uncachedKeys.Add(key);
    }
  }

  #endregion

  #region Query from db

  List<Person> dbDataList = QueryFromDB(uncachedKeys);

  if (dbDataList.Count == 0)
  {
    return rtn;
  }

  #endregion

  #region Write into the cache

  foreach (int key in uncachedKeys)
  {
    List<Person> list = (from p in dbDataList where p.ID == key select p).ToList();
    AddNewCache(_personCacheDict, key, new PersonCache(list));
  }

  #endregion

  return rtn.AddRange(dbDataList);
}

 

.NET DateTime

.NET DateTime:

StringBuilder log = new StringBuilder();

log.Append("DateTime.Now:\t").Append(DateTime.Now).AppendLine();
log.Append("DateTime.UtcNow:\t").Append(DateTime.UtcNow).AppendLine().AppendLine();

log.Append("DateTime.Now.ToLocalTime():\t").Append(DateTime.Now.ToLocalTime()).AppendLine();
log.Append("DateTime.UtcNow.ToLocalTime():\t").Append(DateTime.UtcNow.ToLocalTime()).AppendLine().AppendLine();

log.Append("DateTime.Now.ToUniversalTime():\t").Append(DateTime.Now.ToUniversalTime()).AppendLine();
log.Append("DateTime.UtcNow.ToUniversalTime():\t").Append(DateTime.UtcNow.ToUniversalTime()).AppendLine().AppendLine();

log.Append("new DateTime(2019,5,1,0,0,0,0):\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, 0)).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Unspecified):\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Unspecified)).AppendLine().AppendLine();

log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Local):\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Local)).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Local).ToLocalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Local).ToLocalTime()).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Local).ToUniversalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Local).ToUniversalTime()).AppendLine().AppendLine();

log.Append("new DateTime(2019,5,1,0,0,0,0).ToLocalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, 0).ToLocalTime()).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Unspecified).ToLocalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Unspecified).ToLocalTime()).AppendLine().AppendLine();

log.Append("new DateTime(2019,5,1,0,0,0,0).ToUniversalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, 0).ToUniversalTime()).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Unspecified).ToUniversalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Unspecified).ToUniversalTime()).AppendLine().AppendLine();

log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Utc):\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Utc)).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Utc).ToLocalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Utc).ToLocalTime()).AppendLine();
log.Append("new DateTime(2019,5,1,0,0,0,DateTimeKind.Utc).ToUniversalTime():\t").Append(new DateTime(2019, 5, 1, 0, 0, 0, DateTimeKind.Utc).ToUniversalTime()).AppendLine().AppendLine();

WriteLog(log.ToString());

 

Result: