SQL and C# Data Access
Two alternate approaches to access data from an Sql query
I haven’t written in a while. I tend to write when something intrigues me and I believe it might have value to someone else.
I was tasked to query a database and return the results in JSON format using C#. A pretty common task for a backend but I had never wrote this is C# before.
Several examples I found involved using the SqlDataReader.GetOrdinal(string columnName) method and then using the SqlDataReader.GetString(int ordinal) method. WHAT!?! I have to first determine the ordinal position of my column by searching through the columns with a string name AND then get the data out of the row by calling GetString() with the ordinal position of the column?
Of course I had no guarantee that the data was actually in the table so my code ended up looking like this:
Other than being ugly, this code failed. Occasionally, in the not so perfect database table, a database null value would crop up and throw an exception. I was confused by this until one of my mentors and senior engineers pointed out that a database null is different from the null in C#. So he pointed me in the direction of the DataTable class.
DataTable is a wonderful class that I haven’t even scratched the surface. But my code ended up being cleaner and more robust.
My requirements were to put the results in a HashSet so the implementation is slightly different than above. The key differences are iterating over the dataTable.Rows and using the indexing feature of DataRow to access the column name and convert it to a string before inserting it into my HashSet.
I wrote an additional HashSet extension to check for Null or Empty before inserting it into my HashSet as I had no use for those values.
