How to Use STRING_AGG() for Concatenating Rows into a Single Column in SQL Server

Closing a SqlDataReader correctly prevents memory leaks, connection issues, and unclosed resources. Here’s the best way to do it.

Use 'using' to Auto-Close

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.

⚡ Alternative: Manually Close in finally Block

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.

Reading a file line by line is useful when handling large files without loading everything into memory at once.

✅ Best Practice: Use File.ReadLines() which is more memory efficient.

Example

foreach (string line in File.ReadLines("file.txt"))
{
    Console.WriteLine(line);
}

Why use ReadLines()?

Reads one line at a time, reducing overall memory usage. Ideal for large files (e.g., logs, CSVs).

Alternative: Use StreamReader (More Control)

For scenarios where you need custom processing while reading the contents of the file:

using (StreamReader reader = new StreamReader("file.txt"))
{
    string? line;
    while ((line = reader.ReadLine()) != null)
    {
        Console.WriteLine(line);
    }
}

Why use StreamReader?

Lets you handle exceptions, encoding, and buffering. Supports custom processing (e.g., search for a keyword while reading).

When to Use ReadAllLines()? If you need all lines at once, use:

string[] lines = File.ReadAllLines("file.txt");

Caution: Loads the entire file into memory—avoid for large files!

XML (Extensible Markup Language) is a widely used format for storing and transporting data.

In C#, you can create XML files efficiently using the XmlWriter and XDocument classes. This guide covers both methods with practical examples.

Writing XML Using XmlWriter

XmlWriter provides a fast and memory-efficient way to generate XML files by writing elements sequentially.

Example:

using System;
using System.Xml;

class Program
{
    static void Main()
    {
        using (XmlWriter writer = XmlWriter.Create("person.xml"))
        {
            writer.WriteStartDocument();
            writer.WriteStartElement("Person");

            writer.WriteElementString("FirstName", "John");
            writer.WriteElementString("LastName", "Doe");
            writer.WriteElementString("Age", "30");

            writer.WriteEndElement();
            writer.WriteEndDocument();
        }
        Console.WriteLine("XML file created successfully.");
    }
}

Output (person.xml):

<?xml version="1.0" encoding="utf-8"?>
<Person>
    <FirstName>John</FirstName>
    <LastName>Doe</LastName>
    <Age>30</Age>
</Person>

Writing XML Using XDocument

The XDocument class from LINQ to XML provides a more readable and flexible way to create XML files.

Example:

using System;
using System.Xml.Linq;

class Program
{
    static void Main()
    {
        XDocument doc = new XDocument(
            new XElement("Person",
                new XElement("FirstName", "John"),
                new XElement("LastName", "Doe"),
                new XElement("Age", "30")
            )
        );
        doc.Save("person.xml");
        Console.WriteLine("XML file created successfully.");
    }
}

This approach is ideal for working with complex XML structures and integrating LINQ queries.

When to Use Each Method

• Use XmlWriter when performance is critical and you need to write XML sequentially.
• Use XDocument when you need a more readable, maintainable, and flexible way to manipulate XML.

Conclusion

Writing XML files in C# is straightforward with XmlWriter and XDocument. Choose the method that best suits your needs for performance, readability, and maintainability.