翼前緣渦流

設想的情境是有一些工作需要人工去執行一些特定的 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  看有沒有找到檔案。

參考

SQL Server 公用程式陳述式 - GO

Installing SQLCMD