Using SqlDataReader asynchronously prevents blocking the main thread, improving performance in web apps and large queries. Here’s how to do it properly.
Use await with ExecuteReaderAsync()
using (SqlConnection conn = new SqlConnection(connectionString)) { await conn.OpenAsync(); using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn)) using (SqlDataReader reader = await cmd.ExecuteReaderAsync()) { while (await reader.ReadAsync()) { Console.WriteLine(reader["Username"]); } } // ✅ Auto-closes reader } // ✅ Auto-closes connection
Why use async?
A couple of reasons:
⚡ Alternative: ConfigureAwait(false) for ASP.NET
Use ConfigureAwait(false) in library code to avoid deadlocks in UI frameworks like ASP.NET.
using (SqlConnection conn = new SqlConnection(connectionString)) { await conn.OpenAsync().ConfigureAwait(false); using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn)) using (SqlDataReader reader = await cmd.ExecuteReaderAsync().ConfigureAwait(false)) { while (await reader.ReadAsync().ConfigureAwait(false)) { Console.WriteLine(reader["Username"]); } } }
String interpolation, introduced in C# 6.0, provides a more readable and concise way to format strings compared to traditional concatenation (+) or string.Format(). Instead of manually inserting variables or placeholders, you can use the $ symbol before a string to directly embed expressions inside brackets.
string name = "Walt"; string job = 'Software Engineer'; string message = $"Hello, my name is {name} and I am a {job}"; Console.WriteLine(message);
This would produce the final output of:
Hello, my name is Walt and I am a Software Engineer
String interpolation can also be chained together into a multiline string (@) for even cleaner more concise results:
string name = "Walt"; string html = $@" <div> <h1>Welcome, {name}!</h1> </div>";
When working with URLs in C#, encoding is essential to ensure that special characters (like spaces, ?, &, and =) don’t break the URL structure. The recommended way to encode a string for a URL is by using Uri.EscapeDataString(), which converts unsafe characters into their percent-encoded equivalents.
string rawText = "hello world!"; string encodedText = Uri.EscapeDataString(rawText); Console.WriteLine(encodedText); // Output: hello%20world%21
This method encodes spaces as %20, making it ideal for query parameters.
For ASP.NET applications, you can also use HttpUtility.UrlEncode() (from System.Web), which encodes spaces as +:
using System.Web; string encodedText = HttpUtility.UrlEncode("hello world!"); Console.WriteLine(encodedText); // Output: hello+world%21
For .NET Core and later, Uri.EscapeDataString() is the preferred choice.
Closing a SqlDataReader correctly prevents memory leaks, connection issues, and unclosed resources. Here’s the best way to do it.
Using using statements ensures SqlDataReader and SqlConnection are closed even if an exception occurs.
Example
using (SqlConnection conn = new SqlConnection(connectionString)) { conn.Open(); using (SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn)) using (SqlDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { Console.WriteLine(reader["Username"]); } } // ✅ Auto-closes reader here } // ✅ Auto-closes connection here
This approach auto-closes resources when done and it is cleaner and less error-prone than manual closing.
If you need explicit control, you can manually close it inside a finally block.
SqlDataReader? reader = null; try { using SqlConnection conn = new SqlConnection(connectionString); conn.Open(); using SqlCommand cmd = new SqlCommand("SELECT * FROM Users", conn); reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader["Username"]); } } finally { reader?.Close(); // ✅ Closes reader if it was opened }
This is slightly more error prone if you forget to add a finally block. But might make sense when you need to handle the reader separately from the command or connection.
Register for my free weekly newsletter.