Friday, 28 August 2009

Capturing PRINT statements from Stored Procedure in C#

public class StoredProcedure
{
private static ILog log = LogManager.GetLogger(MethodInfo.GetCurrentMethod().DeclaringType);

static public void Execute(DBSqlPath path, int connectionTimeout,
int commandTimeout, string storedProcedureName, SqlParameter[] parameters)
{
SqlInfoMessageHandler handler = new SqlInfoMessageHandler();

using (DBConn conn = new DBConn(path, connectionTimeout))
{
conn.OpenConnection();

conn.SqlConnection.InfoMessage += handler.connection_InfoMessage;

SqlCommand command = new SqlCommand(storedProcedureName, conn.SqlConnection);
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = commandTimeout;
foreach (SqlParameter param in parameters)
{
command.Parameters.Add(param);
}

try
{
command.ExecuteNonQuery();
}
catch (SqlException ex)
{
throw new GeneralException(string.Format("Error executing stored procedure {0}", storedProcedureName), ex);
}
finally
{
conn.SqlConnection.InfoMessage -= handler.connection_InfoMessage;
log.Debug(handler.ErrorMessages);

conn.CloseConnection();
}
}
log.DebugFormat("Completed execution of {0}", storedProcedureName);
}

internal class SqlInfoMessageHandler
{
private readonly List<string> errorMessages = new List<string>();
public void connection_InfoMessage(object sender, SqlInfoMessageEventArgs e)
{
if (e.Errors != null)
{
for (int i = 0; i < e.Errors.Count; i++)
{
errorMessages.Add(e.Errors[i].Message);
}
}
}

public string[] ErrorMessages
{
get { return errorMessages.ToArray(); }
}
}
}

No comments:

Post a Comment