Yay! I’ve actually used the C# yield statement for the first time. I’ve wanted to use it ever since I read about it, but never really had a use for it until today.
I was writing some code to read a SQL Server error log and eventually found an undocumented stored procedure sp_readerrorlog (amazing how so much useful stuff for SQL Server seems to be “undocumented”).
This procedure works on 2000 and 2005 and returns the entire current error log. However, (probably partly due to its undocumented nature) the different versions return the results in a different format. I need to look for things in the log so my first thought was to create a Parse2000Log and a Parse2005Log and then have these call a 3rd method (e.g. ParseLogLine) for each line. The problem with this was that the messages can span multiple lines. For example if you do a
RAISERROR( 'foo', 20, 1 ) WITH LOG
the log shows.
2007-10-30 15:11:36.07 spid52 Error: 50000, Severity: 20, State: 1
2007-10-30 15:11:36.07 spid52 foo.
This would mean I need to keep state in my ParseLogLine function to track which line I expect next, which means I’d need to make some class variables which I’d really rather avoid. I’m more in favour of using local stack variables when I can. Obviously there are numerous ways to skin this (as with most) cats, but then I hit on the idea of using yield to provide an enumerator over the results and hand back a struct for each line to a calling function. This means I can separate out the differences between the servers into 2 functions but call them from a common function that can act on the actual message.
The 2005 function is by far the simpler as it 2005 has pre-parsed the log into separate columns:
// Struct to hold a line of the logfile
private struct LogLine
{
public DateTime date;
public string processInfo;
public string message;
}
private static IEnumerable <LogLine> Parse2005Log SqlDataReader reader )
{
LogLine ret;
while( reader.Read() )
{
ret.date = reader.GetDateTime( 0 );
ret.processInfo = reader.GetString( 1 );
ret.message = reader.IsDBNull( 2 ) ?
String.Empty : reader.GetString( 2 );
yield return ret;
}
And there is the yield in action. What actually happens is that the compiler generates a class for you which (in this case) inherits from IEnumerable<T> andIEnumerator<T> and has the methods necessary. This means you only have to write one method and not the whole class which I think makes for neat solution in this case. You can make the method return IEnumerable<T> orIEnumerator<T> (or the non-generic variants if you really want) and it will work it out. Here I chose IEnumerable<T> so I can use it in a foreach statement
IEnumerable<LogLine> logreader;
if( s_is2005Server )
logreader = Parse2005Log( reader );
else
logreader = Parse2000Log( reader );
foreach( LogLine line in logreader )
{
Console.WriteLine( "{0} : {1} : {2}", line.date,
line.processInfo, line.message );
}
Here’s the full example:
private struct LogLine
{
public DateTime date;
public string processInfo;
public string message;
}
static void Main(string[] args)
{
SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = s_machineName;
builder.IntegratedSecurity = true;
string connStr = builder.ConnectionString;
try
{
using (SqlConnection conn = new SqlConnection(connStr))
{
conn.Open();
using (SqlCommand comm = conn.CreateCommand())
{
comm.CommandType = CommandType.StoredProcedure;
comm.CommandText = "sp_readerrorlog";
SqlDataReader reader = comm.ExecuteReader();
IEnumerable<LogLine> logreader;
if (s_is2005Server)
logreader = Parse2005Log(reader);
else
logreader = Parse2000Log(reader);
foreach (LogLine line in logreader)
{
Console.WriteLine("{0} : {1} : {2}", line.date,
line.processInfo, line.message);
}
}
}
}
catch (SqlException ex)
{
Console.WriteLine("Exceptional! - " + ex.Message);
}
}
private static IEnumerable<LogLine> Parse2000Log(SqlDataReader reader)
{
LogLine ret;
while (reader.Read())
{
if (!reader.IsDBNull(0))
{
string line = reader.GetString(0);
// The format of the SQL 2000 log file is fairly
// contstrained to be a fixed length
// date, then fixed length process info field and then
// the message.
//
// .. or it can just be freeform
string maybedate = line.Substring(0, 22);
DateTime date;
if (DateTime.TryParse(maybedate,
CultureInfo.InvariantCulture,
DateTimeStyles.AssumeLocal,
out date)
&& !line.StartsWith("\t"))
{
ret.date = date;
ret.processInfo = line.Substring(23, 7).TrimEnd();
ret.message = line.Substring(33).TrimEnd();
}
else
{
ret.date = DateTime.MinValue;
ret.processInfo = string.Empty;
ret.message = line;
}
yield return ret;
}
}
}
private static IEnumerable<LogLine> Parse2005Log(SqlDataReader reader)
{
LogLine ret;
while (reader.Read())
{
ret.date = reader.GetDateTime(0);
ret.processInfo = reader.GetString(1);
ret.message = reader.IsDBNull(2)
? String.Empty : reader.GetString(2);
yield return ret;
}
}