C#将SQL Server消息输出到文本文件
本文关键字:文本 文件 输出 消息 SQL Server | 更新日期: 2023-09-27 18:05:11
我是C#的新手,所以请耐心等待,因为我继承了一个正在尝试调整的脚本。
我想让SQL PRINT/RRAISEROR语句的输出显示在脚本另一部分中声明的日志文件中。
这是我调用的方法:
public void ProcessData(string StoredProcedure, int StartDate, int EndDate, string Directory, string LogFileNameAndPath)
{
SqlConnection sqlConnection = null;
SqlCommand sqlCommand = null;
SqlParameter sqlParameter = null;
// String outputText = null;
try
{
sqlConnection = new SqlConnection(_ConnectionString);
sqlConnection.Open();
sqlCommand = new SqlCommand();
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandText = StoredProcedure;
sqlCommand.Connection = sqlConnection;
sqlCommand.CommandTimeout = 0;
sqlParameter = new SqlParameter("@StartDt", SqlDbType.Int);
sqlParameter.Value = StartDate;
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@EndDt", SqlDbType.Int);
sqlParameter.Value = EndDate;
sqlCommand.Parameters.Add(sqlParameter);
sqlParameter = new SqlParameter("@stringDirs", SqlDbType.VarChar);
sqlParameter.Value = Directory;
sqlCommand.Parameters.Add(sqlParameter);
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
sqlCommand.ExecuteNonQuery();
}
catch (SqlException sqlEx)
{
throw sqlEx;
}
catch (Exception ex)
{
throw new Exception(ex.ToString());
}
finally
{
if (sqlConnection != null)
{
if (sqlConnection.State != ConnectionState.Closed)
{
sqlConnection.Close();
}
}
}
}
这是信息处理程序方法:
public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)//, String LogFileNameAndPath)
{
foreach (SqlError err in args.Errors)
{
//File.AppendAllText(LogFileNameAndPath, err.Message);
Console.WriteLine("{0}", err.Message);
//return err.Message;
//"The {0} has received a severity {1}, state {2} error number {3}'n" +
// "on line {4} of procedure {5} on server {6}:'n{7}",
// err.Source, err.Class, err.State, err.Number, err.LineNumber,
// err.Procedure, err.Server, err.Message);
}
}
我不想输出到控制台,而是想通过"File.AppendAllText(LogFileNameAndPath,err.Message("将其写入LogFileNameAndPath变量;然而,我在网上看了很多帖子,没有人提供解决方案。有办法做到这一点吗?请友善一点。谢谢
=================================================
【增加2015-07-27 1606 EDT】如果我将此行从:更改
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
到
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage(LogFileNameAndPath));
它无法编译这是如何传递给方法的
这是一种新方法:
public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args, String LogFileNameAndPath)
{
foreach (SqlError err in args.Errors)
{
File.AppendAllText(@LogFileNameAndPath, err.Message);
//Console.WriteLine("{0}", err.Message);
}
}
虽然直接写入文件可能对您有用(请参阅@FirebladeDan的答案(,但出于性能原因,我强烈建议使用现有的日志库。
Log4Net是一个常见的建议,这个链接对你来说应该是一个好的开始。Log4Net有一些不错的功能可以帮助你,比如通过应用程序的功能。配置/Web。配置文件以在运行时配置日志记录,这样您就可以根据配置更改日志记录级别(详细程度(,而无需重新编译。
希望这能让你走上正确的道路,祝你好运!
编辑:
根据您更新的声明,您可能需要此选项。
// use a variable to store the path.
private string logFileNameAndPath;
public void ProcessData(string StoredProcedure, int StartDate, int EndDate, string Directory, string LogFileNameAndPath)
{
// Store the parameter that was passed in as the variable.
this.logFileNameAndPath = LogFileNameAndPath;
/* Do query setup work here*/
sqlConnection.InfoMessage += new SqlInfoMessageEventHandler(OnInfoMessage);
sqlCommand.ExecuteNonQuery();
}
public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach (SqlError err in args.Errors)
{
// Use the variable to indicate where to log to.
File.AppendAllText(this.logFileNameAndPath, err.Message);
}
}
希望这能帮助你冲过终点线。
File.AppendAllText(@"pathgoeshere", err.Message);
添加:以下是如何使用AppConfig 中的密钥获取值
String LogFileNameandPath = ConfigurationManager.AppSettings["keynamegoeshere"]);
将LogFileName和Path传递到您的方法中**不要忘记添加引用
与其尝试将日志文件名传递到事件处理程序中,不如在其他地方定义日志文件名,并在事件处理程序内部可见。例如:
public class SqlHandler
{
private readonly string m_LogFileNameAndPath = @"C:'log.txt";
// or get it from AppConfig, as noted by FirebladeDan
public void ProcessData() { /*...*/ }
public void OnInfoMessage(object sender, SqlInfoMessageEventArgs args)
{
foreach(var err in args.Errors)
{
File.AppendAllText(m_LogFileNameAndPath, err.Message);
}
}
}
还值得注意的是,您仍然可以将日志库(Log4Net、NLog等(与自定义文件一起使用,方法是告诉库要登录到哪个文件。