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. <\/p>\n
I was writing some code to read a SQL Server error log and eventually found an undocumented stored procedure sp_readerrorlog<\/strong> (amazing how so much useful stuff for SQL Server seems to be “undocumented”).<\/p>\n 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 <\/p>\n RAISERROR( ’foo’, 20, 1 ) WITH LOG <\/p>\n the log shows.<\/p>\n 2007-10-30 15:11:36.07 spid52 Error: 50000, Severity: 20, State: 1 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. <\/p>\n The 2005 function is by far the simpler as it 2005 has pre-parsed the log into separate columns:<\/p>\n \/\/ Struct to hold a line of the logfile 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><\/strong> andIEnumerator<T><\/strong> 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><\/strong> orIEnumerator<T><\/strong> (or the non-generic variants if you really want) and it will work it out. Here I chose IEnumerable<T><\/strong> so I can use it in a foreach statement <\/p>\n IEnumerable<LogLine> logreader; private struct LogLine string maybedate = line.Substring(0, 22); 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……<\/p>\n","protected":false},"author":143472,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[],"coauthors":[],"class_list":["post-2565","post","type-post","status-publish","format-standard","hentry","category-blogs"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2565","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/143472"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=2565"}],"version-history":[{"count":2,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2565\/revisions"}],"predecessor-version":[{"id":41575,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/2565\/revisions\/41575"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=2565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=2565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=2565"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=2565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
2007-10-30 15:11:36.07 spid52 foo. <\/p>\n
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;
}
<\/p>\n
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: <\/p>\n
{
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 <\/p>\n
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;
}
}<\/p>\n","protected":false},"excerpt":{"rendered":"