[C#] Enable index in SQLite (Code First)

We should specify which column would be indexed in the function 'OnModelCreating'

        protected override void OnModelCreating( ModelBuilder modelBuilder )
        {
            modelBuilder.Entity().HasIndex(b => new { b.Time });
            modelBuilder.Entity().HasIndex(b => new { b.CarAppraisalLicense });
            modelBuilder.Entity().HasIndex(b => new { b._CreateTime});
            modelBuilder.Entity().HasIndex(b => new { b._ModifiedTime });

            modelBuilder.Entity().HasIndex(b => new { b.carPlateModify});
            modelBuilder.Entity().HasIndex(b => new { b.licenseRfid});
            modelBuilder.Entity().HasIndex(b => new { b.carRfid});
            modelBuilder.Entity().HasIndex(b => new { b.updateTime });

            modelBuilder.Entity().HasIndex(b => new { b.licenseRfid });
            modelBuilder.Entity().HasIndex(b => new { b.licenseNo});
            modelBuilder.Entity().HasIndex(b => new { b.updateTime});
        }

Then do EF migration, downgrade/upgrade codes will be generated autometically.

    public partial class CreateIndex : Migration
    {
        protected override void Up(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs_CarAppraisalLicense",
                table: "VerifyLogs",
                column: "CarAppraisalLicense");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs_Time",
                table: "VerifyLogs",
                column: "Time");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs__CreateTime",
                table: "VerifyLogs",
                column: "_CreateTime");

            migrationBuilder.CreateIndex(
                name: "IX_VerifyLogs__ModifiedTime",
                table: "VerifyLogs",
                column: "_ModifiedTime");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_licenseNo",
                table: "DriverLicenses",
                column: "licenseNo");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_licenseRfid",
                table: "DriverLicenses",
                column: "licenseRfid");

            migrationBuilder.CreateIndex(
                name: "IX_DriverLicenses_updateTime",
                table: "DriverLicenses",
                column: "updateTime");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_carPlateModify",
                table: "CarLicenses",
                column: "carPlateModify");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_carRfid",
                table: "CarLicenses",
                column: "carRfid");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_licenseRfid",
                table: "CarLicenses",
                column: "licenseRfid");

            migrationBuilder.CreateIndex(
                name: "IX_CarLicenses_updateTime",
                table: "CarLicenses",
                column: "updateTime");
        }

        protected override void Down(MigrationBuilder migrationBuilder)
        {
            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs_CarAppraisalLicense",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs_Time",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs__CreateTime",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_VerifyLogs__ModifiedTime",
                table: "VerifyLogs");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_licenseNo",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_licenseRfid",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_DriverLicenses_updateTime",
                table: "DriverLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_carPlateModify",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_carRfid",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_licenseRfid",
                table: "CarLicenses");

            migrationBuilder.DropIndex(
                name: "IX_CarLicenses_updateTime",
                table: "CarLicenses");
        }
    }

After creating the index, we can reduce 1/10 DB query time on average.

[C#] Force app run as administrator

In order to force an app to run as administrator, we should add an Application Manifest file 'app.manifest' in the main project.

Below code is the default content of file 'app.manifest'.

<?xml version="1.0" encoding="utf-8"?>
<assembly manifestVersion="1.0" xmlns="urn:schemas-microsoft-com:asm.v1">
  <assemblyIdentity version="1.0.0.0" name="MyApplication.app" />
  <trustInfo xmlns="urn:schemas-microsoft-com:asm.v2">
    <security>
      <requestedPrivileges xmlns="urn:schemas-microsoft-com:asm.v3">
        <!-- UAC Manifest Options
             If you want to change the Windows User Account Control level replace the 
             requestedExecutionLevel node with one of the following.

        <requestedExecutionLevel  level="asInvoker" uiAccess="false" />
        <requestedExecutionLevel  level="requireAdministrator" uiAccess="false" />
        <requestedExecutionLevel  level="highestAvailable" uiAccess="false" />

            Specifying requestedExecutionLevel element will disable file and registry virtualization. 
            Remove this element if your application requires this virtualization for backwards
            compatibility.
        <requestedExecutionLevel level="asInvoker" uiAccess="false" />
        -->
        <requestedExecutionLevel level="asInvoker" uiAccess="false" />
      </requestedPrivileges>
      <applicationRequestMinimum>
        <defaultAssemblyRequest permissionSetReference="Custom" />
        <PermissionSet class="System.Security.PermissionSet" version="1" ID="Custom" SameSite="site" Unrestricted="true" />
      </applicationRequestMinimum>
    </security>
  </trustInfo>
  <compatibility xmlns="urn:schemas-microsoft-com:compatibility.v1">
    <application>
      <!-- A list of the Windows versions that this application has been tested on
           and is designed to work with. Uncomment the appropriate elements
           and Windows will automatically select the most compatible environment. -->
      <!-- Windows Vista -->
      <!--<supportedOS Id="{e2011457-1546-43c5-a5fe-008deee3d3f0}" />-->
      <!-- Windows 7 -->
      <!--<supportedOS Id="{35138b9a-5d96-4fbd-8e2d-a2440225f93a}" />-->
      <!-- Windows 8 -->
      <!--<supportedOS Id="{4a2f28e3-53b9-4441-ba9c-d69d4a4a6e38}" />-->
      <!-- Windows 8.1 -->
      <!--<supportedOS Id="{1f676c76-80e1-4239-95bb-83d0f6d0da78}" />-->
      <!-- Windows 10 -->
      <!--<supportedOS Id="{8e0f7a12-bfb3-4fe8-b9a5-48fd50a15a9a}" />-->
    </application>
  </compatibility>
  <!-- Indicates that the application is DPI-aware and will not be automatically scaled by Windows at higher
       DPIs. Windows Presentation Foundation (WPF) applications are automatically DPI-aware and do not need 
       to opt in. Windows Forms applications targeting .NET Framework 4.6 that opt into this setting, should 
       also set the 'EnableWindowsFormsHighDpiAutoResizing' setting to 'true' in their app.config. -->
  <!--
  <application xmlns="urn:schemas-microsoft-com:asm.v3">
    <windowsSettings>
      <dpiAware xmlns="http://schemas.microsoft.com/SMI/2005/WindowsSettings">true</dpiAware>
    </windowsSettings>
  </application>
  -->
  <!-- Enable themes for Windows common controls and dialogs (Windows XP and later) -->
  <!--
  <dependency>
    <dependentAssembly>
      <assemblyIdentity
          type="win32"
          name="Microsoft.Windows.Common-Controls"
          version="6.0.0.0"
          processorArchitecture="*"
          publicKeyToken="6595b64144ccf1df"
          language="*"
        />
    </dependentAssembly>
  </dependency>
  -->
</assembly>

Replace "asInvoker" with "requireAdministrator" as following

        <requestedExecutionLevel level="requireAdministrator" uiAccess="false" />

After doing the above steps, you will be asked to restart Visual Studio under different credentials.

[Linux Shell Script] A script for parsing file, writing logs, archiving logs and taking args

We need a shell script to execute HTTP request on demand. This script shall read/parse text file and write/archive logs.

• 3 sub functions

1. get_script_dir : Get current folder path of this script
2. echos : Write message to STDOut and log file
3. archive_logs : Archive old logs to .tar

#!/bin/bash

# Functions
get_script_dir () 
{
 SOURCE="${BASH_SOURCE[0]}"
# While $SOURCE is a symlink, resolve it
 while [ -h "$SOURCE" ]; do
  DIR="$( cd -P "$( dirname "$SOURCE" )" && pwd )"
  SOURCE="$( readlink "$SOURCE" )"
# If $SOURCE was a relative symlink (so no "/" as prefix, need to resolve it relative to the symlink base directory
  [[ $SOURCE != /* ]] && SOURCE="$DIR/$SOURCE"
 done
 DIR="$( cd -P "$( dirname "$SOURCE" )" && pwd )"
 echo "$DIR/"
}

# DateFormat in log file : "[YYYY/MM/DD H:M:S]"
logDate=`date "+%Y%m%d"`
logFolder="$(get_script_dir)Logs/"

if [ ! -d $logFolder ]; then
 mkdir $logFolder
fi
logFile="${logFolder}CGI_Controller_${logDate}.log"

# Append STD out to file additionaly
echos ()
{
 logTime=`date "+%H:%M:%S"`
 builtin echo "[$logTime] $@" | tee -a "$logFile"
}

archive_logs()
{
 currentDate=`date "+%Y%m%d"`
 array=( $( ls ${logFolder} ) )
# Explicitly report array content.
 let i=0
 while (( ${#array[@]} > i )); do
 # echos "${i} ${array[i++]}"
 if [[ ${array[i]} == CGI_Controller_$currentDate* ]] || [[ ${array[i]} == zips ]]; then
  echos "The log is in the Current month, skipped it!"
 else
# Get target year and month for sufix of archived file name
  targetDate=`cut -d"_" -f 3 <<< "${array[i]}"`;
  targetDate=`cut -d"." -f 1 <<< $targetDate`;
  targetMonth="${targetDate:0:6}";
  zipFolder="$(get_script_dir)Logs/zips/"
  archivedFile="${zipFolder}Archived_${targetMonth}.tar.gz"
  if [ ! -d $zipFolder ]; then
   mkdir $zipFolder
  fi
  if [ -f "$archivedFile" ];then
   tar -rf $archivedFile --directory="${logFolder}" "${array[i]}" 
  else
   tar -cf $archivedFile --directory="${logFolder}" "${array[i]}" 
  fi
  echos "Archive file ${array[i]} to ${archivedFile}";
  rm "${logFolder}${array[i]}"
 fi
 ((i++))
 done
}

Main function

Check if necessary files exist
Check if parameter is valid
Load setting file into an array
Parsing settings and assemble CGI link
Check server IP is UP
Execute commands

# Main
# Check parameter number
if [ $# -ne 0 ]; then
 cmdI=$1
else
 echos "Parameter not found";
 exit 3
fi
# CMD array for each NVR
declare -a cmdArray

# Check if setting file existed
file="$(get_script_dir)config.txt"
if [ -f "$file" ];then
 echos "$file found";
else
 echos "$file not found, please create one";
 exit 4
fi

# Load file into array.
let i=0
while IFS=$'\n' read -r line_data; do
 cmdArray[i]="${line_data}"
((++i))
done < $file

# Roll back to item number
((--i))

if [ $i -le 0 ];then
 echos "No settings found in $file, please add CGI settings in this file. ([IP],[Path for Open],[Path for Close])";
 exit 5
fi

# Explicitly report array content.
#let i=0
#while (( ${#cmdArray[@]} > i )); do
# printf "${i} ${cmdArray[i++]}\n"
#done

# Make sure command number is less than settings number
if [ $cmdI -lt $i ]; then
 ip="$(cut -d';' -f1 <<<"${cmdArray[cmdI]}")"
 openPath="$(cut -d';' -f2 <<<"${cmdArray[cmdI]}")"
 closePath="$(cut -d';' -f3 <<<"${cmdArray[cmdI]}")"

 printf "${cmdI} ${cmdArray[cmdI]}\n"

 # Ping IP before executing it
 ping -c 1 -W 1 ${ip} &> /dev/null && result=0 || result=1

 if [ "${result}" == 0 ]; then
  echos "Server ${ip} is UP.";
 else
  echos "Server ${ip} is DOWN.";
  exit 6
 fi

 res="${logFolder}res"
 echos "http://${ip}${openPath}";
 wget -O $res "http://${ip}${openPath}";
 echos `cat $res`;
 # Delay 1 sec and then execute 2nd CGI command
 sleep 1
 echos "http://${ip}${closePath}";
 wget -O $res "http://${ip}${closePath}";
 echos `cat $res`;
 rm $res;
else
 echos "Parameter value is bigger than settings";
 exit 7
fi
archive_logs

[C#] HTTP host service in windows desktop app

We need to create HTTP host service in a windows desktop app to receive HTTP POST request. (Only one request will be handled at the same time.)

Use HttpListener and add necessary prefixes and start it

HttpListener server = new HttpListener();
string url = $"http://localhost:{port}/";
server.Prefixes.Add(url);

url = $"http://127.0.0.1:{port}/";
server.Prefixes.Add(url);

IPHostEntry ipHostInfo = Dns.Resolve(Dns.GetHostName());
url = $"http://{ipHostInfo.AddressList[0].ToString()}:{port}/";
server.Prefixes.Add(url);

server.Start();
logger.Info($"Listening to [{url}]...");

Use loop process each HTTP request

while( true )
{
  if( isStopping )
     break;
  HttpListenerContext context = server.GetContext();
  HttpListenerResponse response = context.Response;
  ...
}

Retrieve API path to determine which command is called

StringBuilder builder;
switch( context.Request.Url.LocalPath )
{
    case "/api/command1":
        builder = new StringBuilder("OK");
        break;
    default:
        builder = new StringBuilder("Unrecognized API");
        failureFlag = true;
        break;
}

Retrieve Body and convert it into JSON Object

try
{
    using( var reader = new StreamReader(context.Request.InputStream,
                                         context.Request.ContentEncoding) )
    {
        bodyText = reader.ReadToEnd();
        logger.Info($"HTTP POST body\n{bodyText}");

        var boardMsg = jsonSerializationUtil.DeserializeFromString(bodyText);
        if( boardMsg == null )
            builder = new StringBuilder("JSON format deserialization failure.");
        else
        {
           // Process boardMsg...
        }
    }
}
catch( Exception ex )
{
    logger.Warn(ex);
    builder = new StringBuilder("Body loading failure.");
}

Convert response string into bytes and send it back

string something = builder.ToString();
byte[] buffer = Encoding.UTF8.GetBytes(something);
response.ContentLength64 = buffer.Length;
System.IO.Stream st = response.OutputStream;
st.Write(buffer, 0, buffer.Length);

context.Response.Close();

[C#] DB data model for MySQL (Code First from database)

Create DB data model from MySQL (code first)

1. Install NuGet packages 'MySql.Data' v.8.0.16 and 'MySql.Data.Entity' v. 6.10.8
2. Install MySQL connector Net 6.10.8
3. INstall MySQL for Visual Studio 1.2.8
4. Add Entity Data Model (Code First from database)
5. New Connection (MySQL Data Provider)
6. Modify the file 'App.config' for DB connection string

<connectionStrings>
<add name="MySqlModel" connectionString="server=redmine2.gorilla-technology.com;user id=root;password=linuxyes;persistsecurityinfo=True;database=redmine" providerName="MySql.Data.MySqlClient" />
<add name="Default" connectionString="host=192.168.60.162;port=5432;database=redminedb;user id=redmine;password=MYSQL-TEL#53519427" providerName="Npgsql" />
</connectionStrings>

All needed works are done and then DB operations are ready to go.

using( var context = new MySqlModel() )
using( var transaction = context.Database.BeginTransaction() )
{
...
}

Syntax highlight for blogger

Syntax highlight in blogger

使用Prism幫程式碼上色

Follow steps in the above link will make your blogger support syntax highlight function

[Powershell] Auto packaging files by Powershell scripts

This is a script for packaging files and calculating MD5Sum as following steps.

1. Get 3 parameters working folder, zip file name, and target file name
2. Check if archive tool(7zip/WinRAR) existed in the working folder
3. Get 7zip command from my FTP server
4. Check if file/folder existed
5. Archive target file into a zip file
6. Calculate MD5Sum of the zip file

#Installer package

$DebugPreference = "Continue" # Default is SilentlyContinue

if($args.Count -ne 3){
  Write-Error "args.count=$($args.Count). It should be 3"
  return
}

$zip7FilePath="$PSScriptRoot\7zip\7za.exe"
$winrarFilePath="C:\Program Files\WinRAR\WinRAR.exe"
$solutionDir=$args[0] -replace "`n","" 
$archivedFilePath=$args[1] -replace "`n",""
$installerFilePath=$args[2] -replace "`n",""

# Return true if all required data exist
function checkRequiredData ($solutionDir) {
  Write-Debug "winrarFilePath=`"$winrarFilePath`""
  Write-Debug "7zipFilePath=`"$zip7FilePath`""
  if (!(Test-Path $winrarFilePath)){
    # WinRAR does not exist
    Write-Debug "`"$winrarFilePath`" doesn't exist, try to detect 7zip"
    if (!(Test-Path $zip7FilePath)){
      # 7za.exe does not exist
      Write-Debug "`"$zip7FilePath`" doesn't exist, try to extract it from zip"
      $url = "ftp://hanping-lab-pc/7zip.zip"
      $output = "$PSScriptRoot\7zip.zip"
      $start_time = Get-Date

      if( !(Test-Path $output)){
        # WinRAR does not exist
        Write-Debug "`"$output`" doesn't exist, try to download it from hanping-lab-pc ftp"
        Invoke-WebRequest -Uri $url -OutFile $output
        Write-Debug "Time taken: $((Get-Date).Subtract($start_time).Seconds) second(s)"
      }
      Expand-Archive -Path "$PSScriptRoot\7zip.zip" -DestinationPath "$PSScriptRoot"
      
      if (!(Test-Path $zip7FilePath)){
        Write-Error "`"$zip7FilePath`" doesn't exist"
        return $false
      }
    }
  }

  Write-Debug "solutionDir=`"$solutionDir`""
  # Check solution dir
  if(!(Test-Path $solutionDir)){
    Write-Error "solutionDir `"$solutionDir`" doesn't exist"
    return $false
  }

  # Check Installer
  Write-Debug "installerFilePath=`"$global:installerFilePath`""
  if(!(Test-Path $global:installerFilePath)){
    Write-Error "installerFilePath `"$global:installerFilePath`" doesn't exist"
    return $false
  }
  return $true
}

function getMD5Hash($fileName) {
  if([System.IO.File]::Exists($fileName)){
    $fileStream = New-Object System.IO.FileStream($fileName,[System.IO.FileMode]::Open,[System.IO.FileAccess]::Read,[System.IO.FileShare]::ReadWrite)
    $md5Hash = New-Object System.Security.Cryptography.MD5CryptoServiceProvider
    [byte[]]$fileByteChecksum = $md5Hash.ComputeHash($fileStream)
    $fileChecksum = ([System.Bitconverter]::ToString($fileByteChecksum)).Replace("-","")
    $fileStream.Close()
  } else {
    $fileChecksum = "ERROR: $fileName Not Found"
  }
  return $fileChecksum
}

function archive () {
  # Compress by WinRAR
  $version = [System.Diagnostics.FileVersionInfo]::GetVersionInfo($global:installerFilePath).FileVersion
  $archivedFilePath = [System.IO.Path]::GetDirectoryName($archivedFilePath) + "\\" + [System.IO.Path]::GetFileNameWithoutExtension($archivedFilePath) + "_$($version).zip"
    # Remove existing archivedFile
  Write-Debug "archivedFilePath=`"$archivedFilePath`""
  if(Test-Path $archivedFilePath){
    Remove-Item $archivedFilePath -Recurse
  }
    if ((Test-Path $winrarFilePath)){
    # WinRAR does exist
      $process = [System.Diagnostics.Process]::Start("$winrarFilePath", "a -m5 -afzip -ep1` `"$archivedFilePath`" `"$global:installerFilePath`"") 
      $process.WaitForExit()
    }
    elseif ((Test-Path $zip7FilePath)){
    # 7-zip does exist
      $process = [System.Diagnostics.Process]::Start("$zip7FilePath", "a -tzip `"$archivedFilePath`" `"$global:installerFilePath`"") 
      $process.WaitForExit()
    }

  # Save MD5 of archivedFilePath as text file
  $md5Hash = (getMD5Hash($archivedFilePath)).toLower()
  Write-Debug "MD5=`"$md5Hash`""
  $dirName = [System.IO.Path]::GetDirectoryName($archivedFilePath)
  $fileNameWithoutExt = [System.IO.Path]::GetFileNameWithoutExtension($archivedFilePath)
  $md5FilePath = Join-Path -Path $dirName -ChildPath ("$($fileNameWithoutExt)_MD5.txt")
  Write-Debug "md5FilePath=`"$md5FilePath`""
  $md5Hash | Set-Content $md5FilePath
}

#main region

Write-Debug "solutionDir=`"$solutionDir`""

if(checkRequiredData($solutionDir) == $true){
  archive
}

[.NET] How to build .NET NuGet package

Steps to build NuGet package for 'RFID.dll'

1. nuget.exe spec RFID.dll (nuget.exe download page)
2. Edit file [RFID.dll.nuspec]
3. Create Folder [lib\Uart\Bridge\RFIDLibNet\\] and put RFID.dll in it
    Note: The folder structure is like the value of tag 'id' in RFID.dll.nuspec.
4. nuget.exe pack RFID.dll.nuspec and then Uart.Bridge.RFIDLibNet.15.6.3.2.nupkg is created

The following folder tree is my build environment

│  CreateNuGetPackages.bat
│  README.md
│
├─outputs
│      Gorilla.Uart.Bridge.RFIDLibNet.15.6.3.2.nupkg
│
├─package
│  │  Package.nuspec
│  │
│  └─lib
│      └─Gorilla
│          └─Uart
│              └─Bridge
│                  └─RFIDLibNet
│                          RFID.dll

I write a script for auto-executing some commands

@echo off
set NUGET_PATH="C:\nugetPool\nuget.exe"
nuget.exe pack "package\Package.nuspec"
echo ============================================================
echo  Create NuGet Packages
echo ============================================================
"%NUGET_PATH%" pack "package\Package.nuspec"
pause

P.S.
 Files in the folder 'lib' will be referenced and files in the folder 'build' will be copied to the output (build) folder (ex. /bin/). Furthermore, you can customize some operations for different target platform in file 'xxx.xxx.xxx.targets'.

<!--Start Code-->
<?xml version="1.0"?>
<package >
<metadata>
<id>XXXX.Uart.Bridge.RFIDLibNet</id>
<version>15.06.03.2</version>
<authors>XXXX Technology Group</authors>
<owners>XXXX Technology Group</owners>
<requireLicenseAcceptance>false</requireLicenseAcceptance>
<description>Silicon Uart Bridge RFID Reader lib</description>
<releaseNotes>Official DLL version</releaseNotes>
<copyright>Copyright 2017</copyright>
<tags>XXXX Silicon UartBridge RFID</tags>
</metadata>
</package>
<!--End Code-->