Home
> Uncategorized > Making a DataReader behave like a DataSet #sql #dotNET
Making a DataReader behave like a DataSet #sql #dotNET

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!
Categories: Uncategorized
Comments (0)
Trackbacks (0)
Leave a comment
Trackback