如果您沒有為 SQLCMD 傳遞任何密碼，它將嘗試從標準輸入讀取它（我可以手動完成它并且它可以作業）。
我啟動了 cmd 行程，我將帶有密碼的 streamWriter 作為標準輸入傳遞給該行程。
行程正確啟動，我可以列印傳遞的流，但我得到了
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login failed for user 'sa'
有沒有辦法讓 sqlcmd 讀取標準輸入？
C# 代碼
ProcessStartInfo processStartInfo = new ProcessStartInfo
{
FileName = @"path_to_my_script",
UseShellExecute = false,
CreateNoWindow = false,
RedirectStandardInput = true
};
var process = Process.Start( processStartInfo );
StreamWriter myStreamWriter = process.StandardInput;
string pass = "123";
myStreamWriter.WriteLine( pass );
myStreamWriter.Close();
myScript（修改后的查詢）
SqlCmd -S DESKTOP-UR7LHEE -U sa -Q "SELECT * FROM myDb"
uj5u.com熱心網友回復：
下面展示了如何使用System.Diagnostics.Process通過sqlcmd運行 SQL 腳本。但是，您可能想查看服務器管理物件 (SMO)是否滿足您的需求。
以下內容已經過測驗：
添加以下 using 陳述句：
- 使用 Microsoft.Win32；
- 使用 System.IO；
- 使用 System.Diagnostics；
首先，我們將創建一個方法來查詢注冊表以查找 sqlcmd.exe 的路徑：
注意：如果 sqlcmd.exe 所在的目錄/檔案夾在您的 PATH 環境變數中，則不需要以下方法 - 可以使用“sqlcmd.exe”而不是完全限定的檔案名。
GetSqlCmdPath：
private string GetSqlCmdPath()
{
//get the fully-qualified path for sqlcmd.exe
string sqlCmdPath = string.Empty;
using (RegistryKey key = RegistryKey.OpenBaseKey(Microsoft.Win32.RegistryHive.LocalMachine, RegistryView.Registry64))
{
using (RegistryKey subkey = key.OpenSubKey(@"SOFTWARE\Microsoft\Microsoft SQL Server"))
{
if (subkey != null)
{
string[] subkeyNames = subkey.GetSubKeyNames();
if (subkeyNames != null)
{
foreach (string name in subkeyNames)
{
string clientSetupSubkey = Path.Combine(name, "Tools", "ClientSetup");
using (RegistryKey subkeyClientSetup = subkey.OpenSubKey(clientSetupSubkey))
{
if (subkeyClientSetup != null)
{
string[] valueNames = subkeyClientSetup.GetValueNames();
if (valueNames != null)
{
foreach (string vName in valueNames)
{
if (vName == "Path" || vName == "ODBCToolsPath")
{
//get value
string valPath = subkeyClientSetup.GetValue(vName)?.ToString();
//check if sqlcmd.exe exists
if (File.Exists(Path.Combine(valPath, "sqlcmd.exe")))
{
sqlCmdPath = Path.Combine(valPath, "sqlcmd.exe");
}
}
}
}
}
}
}
}
}
}
}
return sqlCmdPath;
}
接下來，創建一個使用 Process 實體執行腳本的方法：
RunProcessSqlCmd：
private void RunProcessSqlCmd(string scriptFilename, string logFilename, string password)
{
string sqlCmdPath = GetSqlCmdPath();
if (String.IsNullOrEmpty(sqlCmdPath))
throw new Exception("Error: Fully-qualified path to 'sqlcmd.exe' could not be determined.");
ProcessStartInfo startInfo = new ProcessStartInfo ()
{
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),
//Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),
CreateNoWindow = true,
FileName = sqlCmdPath,
RedirectStandardError = true,
RedirectStandardInput = true,
RedirectStandardOutput = true,
UseShellExecute = false,
WindowStyle = ProcessWindowStyle.Hidden
};
using (Process p = new Process () { StartInfo = startInfo, EnableRaisingEvents = true})
{
//subscribe to event and add event handler code
p.ErrorDataReceived = (sender, e) =>
{
if (!String.IsNullOrEmpty(e.Data))
{
//ToDo: add desired code
Debug.WriteLine("Error: " e.Data);
}
};
//subscribe to event and add event handler code
p.OutputDataReceived = (sender, e) =>
{
if (!String.IsNullOrEmpty(e.Data))
{
//ToDo: add desired code
Debug.WriteLine("Output: " e.Data);
}
};
//start
p.Start();
p.BeginErrorReadLine(); //begin async reading for standard error
p.BeginOutputReadLine(); //begin async reading for standard output
using (StreamWriter sw = p.StandardInput)
{
//provide values for each input prompt
//ToDo: add values for each input prompt - changing the for loop as necessary
//Note: Since we only have 1 prompt, using a loop is unnecessary - a single 'WriteLine' statement would suffice
for (int i = 0; i < 1; i )
{
if (i == 0)
sw.WriteLine(password); //1st prompt
else
break; //exit
}
}
//waits until the process is finished before continuing
p.WaitForExit();
}
}
注意：上面的代碼將腳本的輸出寫入日志檔案。如果您希望將輸出寫入 StandardOutput，請更改以下內容：
來自：
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0} -o {1}", scriptFilename, logFilename),
至：
Arguments = String.Format(@"-S .\SQLExpress -U appAdmin -i {0}", scriptFilename),
此外，由于該logFilename引數將不再使用，您可以將其洗掉。
用法：
//create test script
string logFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name "_ScriptLog.txt");
string scriptFilename = Path.Combine(Path.GetTempPath(), System.Reflection.Assembly.GetExecutingAssembly().GetName().Name "_Script.sql");
Debug.WriteLine($"scriptFilename: {scriptFilename} logFilename: {logFilename}");
//string scriptText = "use master;" System.Environment.NewLine;
//scriptText = "SELECT name, database_id from sys.databases;";
string scriptText = "SELECT name, database_id from sys.databases;";
File.WriteAllText(scriptFilename, scriptText);
RunProcessSqlCmd(scriptFilename, logFilename, "myPassword123");
資源：
- System.Diagnostics.Process
- 下載 sqlcmd 實用程式
- sqlcmd - 使用該實用程式
- View list of databases on SQL Server
Server Management Objects (SMO) Resources:
- Server Management Objects (SMO) - Overview
- Installing Server Management Objects (SMO)
- Programming Specific Tasks
- Backing Up and Restoring Databases and Transaction Logs
- Getting Started in SMO
- Creating SMO Programs
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/444435.html
上一篇：Windows批處理檔案-包含模式但不以相同模式結尾的匹配檔案
下一篇：如何正確使用多個“for”命令