Network Programming in .NET

In C# / .NET a DataSet is easy to use, you can iterate over it using a simple For Loop, however, if you are using a large query like “select * from hugeTable” then you’ll find that the memory footprint, plus the time taken to read the whole dataset into memory is prohibitive.

Using a DataReader is the alternative, where you explicitly specify when you want the next row, so hopefully, the first row of your query will return faster. But, client code can’t use a simple for loop to iterate over the data.

So, here is my hybrid solution;

public static IEnumerable<Dictionary<string, object>> PopulateReader(string command)
{
	var sqlConnection = "---READ DSN FROM CONFIG---";
	var connection = new SqlConnection(sqlConnection);
	connection.Open();
	var sqlCommand = new SqlCommand(command, connection);
	var reader = sqlCommand.ExecuteReader();
	while (reader.Read())
	{
	    var result = new Dictionary<string, object>();
	    for (var column = 0; column < reader.FieldCount; column++)
		result.Add(reader.GetName(column), reader.GetValue(column));
	    yield return result;
	}
	connection.Close();
}

And your client can call this, in a very similar way to how a DataReader would be called.

var reader = PopulateReader(sql);
foreach (var dr in reader)
{
	var id = dr["id"].ToString();
	... 
}

Hope this helps, or acts as inspiration for someone!