Introduction

Reading SQL table data into a C# DataTable is a standard ADO.NET task when you want a disconnected, in-memory representation of tabular data. The most common approach uses SqlDataAdapter.Fill, though SqlDataReader plus DataTable.Load is also valid. The right choice depends on how much control you want over execution and whether you need only a quick fill or a more streaming-style read path first.

Use SqlDataAdapter.Fill

For many applications, SqlDataAdapter is the simplest answer. You provide a connection, a query, and an empty DataTable, then call Fill.

1using System;
2using System.Data;
3using System.Data.SqlClient;
4
5class Program
6{
7    static void Main()
8    {
9        string connectionString = "Server=.;Database=AppDb;Trusted_Connection=True;";
10        string sql = "SELECT Id, Name, Email FROM Customers";
11
12        DataTable table = new DataTable();
13
14        using (SqlConnection connection = new SqlConnection(connectionString))
15        using (SqlDataAdapter adapter = new SqlDataAdapter(sql, connection))
16        {
17            adapter.Fill(table);
18        }
19
20        foreach (DataRow row in table.Rows)
21        {
22            Console.WriteLine($"{row["Id"]} {row["Name"]} {row["Email"]}");
23        }
24    }
25}

This is the usual starting point because it is compact and handles the connection lifecycle cleanly through using.

Use Parameters for Filtered Queries

If the query includes user-controlled values, use parameters instead of concatenating SQL strings.

1using System;
2using System.Data;
3using System.Data.SqlClient;
4
5class Program
6{
7    static void Main()
8    {
9        string connectionString = "Server=.;Database=AppDb;Trusted_Connection=True;";
10        DataTable table = new DataTable();
11
12        using (SqlConnection connection = new SqlConnection(connectionString))
13        using (SqlCommand command = new SqlCommand(
14            "SELECT Id, Name FROM Customers WHERE City = @City", connection))
15        using (SqlDataAdapter adapter = new SqlDataAdapter(command))
16        {
17            command.Parameters.AddWithValue("@City", "Toronto");
18            adapter.Fill(table);
19        }
20
21        Console.WriteLine(table.Rows.Count);
22    }
23}

This avoids SQL injection risks and also handles proper quoting and typing for the database provider.

SqlDataReader Plus DataTable.Load

If you prefer to execute the command yourself and then load the result into a DataTable, use a reader.

1using System;
2using System.Data;
3using System.Data.SqlClient;
4
5class Program
6{
7    static void Main()
8    {
9        string connectionString = "Server=.;Database=AppDb;Trusted_Connection=True;";
10        DataTable table = new DataTable();
11
12        using (SqlConnection connection = new SqlConnection(connectionString))
13        using (SqlCommand command = new SqlCommand(
14            "SELECT Id, Name, Email FROM Customers", connection))
15        {
16            connection.Open();
17
18            using (SqlDataReader reader = command.ExecuteReader())
19            {
20                table.Load(reader);
21            }
22        }
23
24        Console.WriteLine(table.Rows.Count);
25    }
26}

This version is useful when you want more explicit control over the command execution path before the data lands in the table.

Why Use a DataTable at All

A DataTable is most useful when you want:

• disconnected access after the query finishes
• flexible row and column inspection
• binding to older UI components or reporting tools

If the application really wants typed domain objects, direct mapping to C# classes may be a better fit. But for quick in-memory tabular work, DataTable is still practical.

Handle Nulls and Types Carefully

Database columns may contain NULL, and DataTable stores values as object. That means code reading values should check for DBNull.Value.

1foreach (DataRow row in table.Rows)
2{
3    string email = row["Email"] == DBNull.Value
4        ? "(missing)"
5        : row["Email"].ToString();
6
7    Console.WriteLine(email);
8}

Assuming every field is present and strongly typed is a common source of runtime errors.

Keep the Query Narrow

When filling a DataTable, it is tempting to select everything. In practice, selecting only the needed columns reduces memory use and makes downstream code clearer.

This matters especially when the table is large or the query is used in high-traffic code paths. SELECT * is convenient early on but usually a poor long-term habit.

Common Pitfalls

• Concatenating SQL strings instead of using parameters.
• Loading an entire table when only a few columns or rows are needed.
• Forgetting that DataTable values are object-based and may contain DBNull.Value.
• Skipping using blocks and leaving connections or commands open longer than necessary.
• Using DataTable automatically even when typed models would fit the code better.

Summary

• 'SqlDataAdapter.Fill is the simplest common way to load SQL data into a DataTable.'
• 'SqlDataReader plus DataTable.Load is a good alternative when you want more explicit control.'
• Use parameters for filtered queries.
• Check for DBNull.Value when reading nullable columns.
• 'DataTable is best when you want disconnected, flexible tabular access rather than strongly typed domain objects.'