[VBA] Read Write UTF-8 File

原始碼

1. Dim reader As Object 
2. Set reader = CreateObject("ADODB.Stream") 
3. reader.CharSet = "UTF-8" 
4. reader.LineSeparator = 10 
5. reader.Open 
6. reader.LoadFromFile "C:\test.txt" 
7.  
8. Dim textLine As String 
9. Do Until reader.EOS 
10.    textLine = reader.ReadText(-2) 
11. Loop 
12. reader.Close 

Dim reader As Object
Set reader = CreateObject("ADODB.Stream")
reader.CharSet = "UTF-8"
reader.LineSeparator = 10
reader.Open
reader.LoadFromFile "C:\test.txt"

Dim textLine As String
Do Until reader.EOS
    textLine = reader.ReadText(-2)
Loop
reader.Close

原始碼

1. Dim writer As Object 
2. Set writer = CreateObject("ADODB.Stream") 
3. writer.CharSet = "UTF-8" 
4. writer.Open 
5. writer.WriteText "xxxxxxx" & Chr(10) 
6.  
7. writer.SaveToFile "C:\test.txt", 2 
8. writer.Close 

Dim writer As Object
Set writer = CreateObject("ADODB.Stream")
writer.CharSet = "UTF-8"
writer.Open
writer.WriteText "xxxxxxx" & Chr(10)

writer.SaveToFile "C:\test.txt", 2
writer.Close

擴充 LinqToSql 的 Left Join 關連 Table

Linq 在處理 Left Join 的語法有點煩人，想採用 EntityRef 的方式進行查詢，而建立 Entity 的關連表的方式很多，但 DBML 都是透過 Visual Studio 的工具去維護的，手動去修改他實在是不優啊！利用 partial class 的方式就可以避開這種問題。

在 AssociationAttribute 中的 IsForeignKey = false 就會採用 Left Join 進行關連查詢。

原始碼

1. using System.Data.Linq; 
2. using System.Data.Linq.Mapping; 
3.  
4. namespace MyConsoleApp.Database 
5. { 
6.    public partial class JobCommandMaterial 
7.    { 
8.        private Link<Material> _Material; 
9.  
10.        [Association(Storage = "_Material", ThisKey = "MaterialCode", OtherKey = "MaterialCode")] 
11.        public Material Material { get { return _Material.Value; } } 
12.    } 
13. } 

using System.Data.Linq;
using System.Data.Linq.Mapping;

namespace MyConsoleApp.Database
{
    public partial class JobCommandMaterial
    {
        private Link<Material> _Material;

        [Association(Storage = "_Material", ThisKey = "MaterialCode", OtherKey = "MaterialCode")]
        public Material Material { get { return _Material.Value; } }
    }
}

參考：
AssociationAttribute 類別 (System.Data.Linq.Mapping)
EntityRef(TEntity) 結構 (System.Data.Linq)
Link(T) 結構 (System.Data.Linq)

LinqToSql 替換查詢的 Table

因為封存的 Table 是基於 Base Table 複製出來的，DBML 也不可能一開始就建立這些封存的 Table，但又想用 LinqToSql 去處理資料存取，所以想到用程式去換掉 SQL 裡的 Table Name。

原始碼

1. var query = _dc.CtrlCommandRecord.Where(x => x.CreateDate > DateTime.Today); 
2.  
3. DbCommand command = _dc.GetCommand(query); 
4. command.CommandText = command.CommandText.Replace("CtrlCommandRecord", "CtrlCommandRecord_2017"); 
5. command.Connection = _dc.Connection; 
6. command.Connection.Open(); 
7.  
8. DbDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection); 
9. var list =  _dc.Translate<CtrlCommandRecord>(reader); 
10.  
11. list.Dump(); 

var query = _dc.CtrlCommandRecord.Where(x => x.CreateDate > DateTime.Today);

DbCommand command = _dc.GetCommand(query);
command.CommandText = command.CommandText.Replace("CtrlCommandRecord", "CtrlCommandRecord_2017");
command.Connection = _dc.Connection;
command.Connection.Open();

DbDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
var list =  _dc.Translate<CtrlCommandRecord>(reader);

list.Dump();

用 Visual Studio 自動壓縮發佈的程式

針對只能用隨身碟安裝程式，又不想用 Web Deploy 這麼複雜工具，簡單的用壓縮檔來是處理，利用 Visual Studio 的檔案發佈再進行壓縮封裝，在原本的 pubxml 檔的後面加上 ZipPublishOutput Target 來接續發佈處理，這樣在 Visual Studio 在執行發佈時就可以產生壓縮封裝。

原始碼

1. <?xml version="1.0" encoding="utf-8"?> 
2. <!-- 
3. "C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe" MySolution.sln /t:Rebuild /p:VisualStudioVersion=14.0;Configuration=Release;DeployOnBuild=true;PublishProfile=Release.pubxml 
4. --> 
5. <Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003"> 
6.  <PropertyGroup> 
7.    <WebPublishMethod>FileSystem</WebPublishMethod> 
8.    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration> 
9.    <LastUsedPlatform>Any CPU</LastUsedPlatform> 
10.    <SiteUrlToLaunchAfterPublish /> 
11.    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish> 
12.    <ExcludeApp_Data>True</ExcludeApp_Data> 
13.    <publishUrl>obj\Publish</publishUrl> 
14.    <DeleteExistingFiles>True</DeleteExistingFiles> 
15.    <AutoParameterizationWebConfigConnectionStrings>False</AutoParameterizationWebConfigConnectionStrings> 
16.  </PropertyGroup> 
17.  
18.  <Target Name="ZipPublishOutput" AfterTargets="GatherAllFilesToPublish;WebFileSystemPublish"> 
19.    <Message Text="== 封裝 7-Zip 安裝包 ===============================" Importance="high" /> 
20.    <PropertyGroup> 
21.      <Today>$([System.DateTime]::Today.ToString("yyyyMMdd"))</Today> 
22.      <zip>"C:\Program Files\7-Zip\7z.exe"</zip> 
23.      <ReleasePath>$(ProjectDir)obj\Release\Package\PackageTmp</ReleasePath> 
24.      <PackagePath>D:\MySolution_$(Today).exe</PackagePath> 
25.    </PropertyGroup> 
26.  
27.    <Exec Command='DEL $(PackagePath)' /> 
28.    <Exec Command='$(zip) a -t7z -mx9 -sfx7z.sfx -bd $(PackagePath) * | findstr /v "^$"' WorkingDirectory="$(ReleasePath)" /> 
29.  </Target> 
30. </Project> 

<?xml version="1.0" encoding="utf-8"?>
<!--
"C:\Program Files (x86)\MSBuild\14.0\Bin\MSBuild.exe" MySolution.sln /t:Rebuild /p:VisualStudioVersion=14.0;Configuration=Release;DeployOnBuild=true;PublishProfile=Release.pubxml
-->
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <WebPublishMethod>FileSystem</WebPublishMethod>
    <LastUsedBuildConfiguration>Release</LastUsedBuildConfiguration>
    <LastUsedPlatform>Any CPU</LastUsedPlatform>
    <SiteUrlToLaunchAfterPublish />
    <LaunchSiteAfterPublish>True</LaunchSiteAfterPublish>
    <ExcludeApp_Data>True</ExcludeApp_Data>
    <publishUrl>obj\Publish</publishUrl>
    <DeleteExistingFiles>True</DeleteExistingFiles>
    <AutoParameterizationWebConfigConnectionStrings>False</AutoParameterizationWebConfigConnectionStrings>
  </PropertyGroup>

  <Target Name="ZipPublishOutput" AfterTargets="GatherAllFilesToPublish;WebFileSystemPublish">
    <Message Text="== 封裝 7-Zip 安裝包 ===============================" Importance="high" />
    <PropertyGroup>
      <Today>$([System.DateTime]::Today.ToString("yyyyMMdd"))</Today>
      <zip>"C:\Program Files\7-Zip\7z.exe"</zip>
      <ReleasePath>$(ProjectDir)obj\Release\Package\PackageTmp</ReleasePath>
      <PackagePath>D:\MySolution_$(Today).exe</PackagePath>
    </PropertyGroup>

    <Exec Command='DEL $(PackagePath)' />
    <Exec Command='$(zip) a -t7z -mx9 -sfx7z.sfx -bd $(PackagePath) * | findstr /v "^$"' WorkingDirectory="$(ReleasePath)" />
  </Target>
</Project>