使用 C# 執行 sql 檔，但不包含查詢
設想的情境是有一些工作需要人工去執行一些特定的 sql 檔，像是建置應用程式的資料庫及相關表等等，而現今這些要被自動化。
因為覺得 Dapper 的形式很簡潔(可以參黑大這篇 短小精悍的.NET ORM神器 -- Dapper)，我希望這個執行 sql 檔的方法，也可以是一個 IDbConnection 的擴充方法，像這樣呈現
conn.ExcuteFile(@"D:\InstallNorthwind.sql");
實作
大致的做法是建立一個擴充方法，內容是用 sqlcmd 去執行 sql 檔，帳號密碼等資訊則從 Connection 中取得。
在實際運用場景，要注意的是因為預設 Connectioin 在第一次 Open 後就會把 Password 擦去，需要把 Persist Security Info 設為 True，以保留 Password。
void Main()
{
var conn = new SqlConnection(@"Data Source=127.0.0.1\SQL2012;Initial Catalog=NorthwindTest;Persist Security Info=True;User ID=sa;Password=sapass");
conn.ExecuteFile(@"D:\InstallNorthwind.sql");
}
// Define other methods and classes here
public static class FileExtension
{
// sqlcmd -iD:\CreateNorthwind.sql -dNorthwindTest -S127.0.0.1\SQL2012 -Usa -Psapass
public static void ExecuteFile(this IDbConnection conn, string path)
{
var cs = new SqlConnectionStringBuilder(conn.ConnectionString);
var cmd = $"-i{path} -d{cs.InitialCatalog} -S{cs.DataSource}";
if (!cs.IntegratedSecurity)
{
cmd += $" -U{cs.UserID} -P{cs.Password}";
}
Process p = new Process();
p.StartInfo.FileName = @"sqlcmd";
p.StartInfo.Arguments = cmd;
p.StartInfo.RedirectStandardOutput = true;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.CreateNoWindow = true;
p.StartInfo.UseShellExecute = false;
p.Start();
var info = p.StandardOutput.ReadToEnd();
p.WaitForExit();
if (p.ExitCode != 0)
{
throw new Exception($"execute faild:{info}");
}
}
}
使用 sqlcmd 而不是透過 ADO.NET 執行內文，因為 sql 檔內可能包含 GO 語句，GO 是給 sqlcmd 或 SSMS 讀的，並不是 SQL 語法的一部分，所以 ADO.NET 執行包含 GO 的語法會出現錯誤。
後記
後來想到 sqlcmd 或許不是每個環境都有，就去查了一下，只要有安裝 SSMS(SQL Server Manager Studio) 或 SQL Server 就會有，所以會有執行 sql 檔需求的環境都會有就是了。
也可以打開命令提示字元執行 where sqlcmd 看有沒有找到檔案。
參考
