关于.NET(C#)无EF的ADO.NET数据访问层数据传递优化问询

Great question! Let's walk through your options here—you're on the right track wanting to avoid exposing DataReaders (and the connection management headaches that come with them) while keeping your DAL clean with ADO.NET (no EF required).

First: Your Dictionary Idea (Valid, But Context-Dependent) #

Your initial thought of returning a List<Dictionary<string, object>> is totally workable, especially for dynamic scenarios where you don't know the schema upfront (like ad-hoc reports or flexible data queries). Here's a polished implementation that handles DBNull properly to avoid runtime errors:

public List<Dictionary<string, object>> ExecuteQueryToDictionary(string connectionString, SqlCommand command)
{
    var results = new List<Dictionary<string, object>>();
    
    using (var connection = new SqlConnection(connectionString))
    {
        command.Connection = connection;
        connection.Open();
        
        using (var reader = command.ExecuteReader())
        {
            // Grab all column names upfront to avoid repeated calls
            var columnNames = Enumerable.Range(0, reader.FieldCount)
                                       .Select(reader.GetName)
                                       .ToList();
            
            while (reader.Read())
            {
                var rowDict = new Dictionary<string, object>();
                foreach (var column in columnNames)
                {
                    // Handle DBNull to prevent invalid cast issues
                    rowDict[column] = reader[column] == DBNull.Value ? null : reader[column];
                }
                results.Add(rowDict);
            }
        }
    }
    return results;
}

Pros: #

• No need to define model classes upfront
• Flexible for dynamic data shapes
• Fully hides connection/reader management from callers

Cons: #

• No type safety—callers have to cast values manually (risk of runtime errors)
• Less readable than working with named properties
• Slightly worse performance than strongly-typed objects due to dictionary lookups

The Better Approach: Strongly-Typed Object Mapping #

For most production scenarios, returning strongly-typed model objects is far superior. It gives you type safety, better readability, and cleaner code. The key is to use a generic method that accepts a mapping delegate to convert IDataReader rows into your model.

Here's how to implement this:

Step 1: Create Your Model Class #

First, define a class that matches your database schema (nullable properties for optional columns):

public class Customer
{
    public int Id { get; set; }
    public string FullName { get; set; }
    public DateTime SignupDate { get; set; }
    public decimal? AccountBalance { get; set; } // Nullable for optional columns
}

Step 2: Generic Execution Method #

This method handles all connection/command/reader cleanup, and lets callers define how to map rows to objects:

public List<T> ExecuteQuery<T>(string connectionString, SqlCommand command, Func<IDataReader, T> rowMapper)
{
    var results = new List<T>();
    
    using (var connection = new SqlConnection(connectionString))
    {
        command.Connection = connection;
        connection.Open();
        
        using (var reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // Delegate the mapping logic to the caller
                results.Add(rowMapper(reader));
            }
        }
    }
    return results;
}

Step 3: Call the Method #

When you need to run a query, pass in your command (with parameters to avoid SQL injection!) and a lambda to map the reader to your model:

// Build your parameterized command
var getCustomerCommand = new SqlCommand(@"
    SELECT Id, FullName, SignupDate, AccountBalance 
    FROM Customers 
    WHERE Id = @CustomerId");
getCustomerCommand.Parameters.AddWithValue("@CustomerId", 123);

// Execute and map to Customer objects
var customers = ExecuteQuery(connectionString, getCustomerCommand, reader => new Customer
{
    Id = reader.GetInt32(reader.GetOrdinal("Id")),
    FullName = reader.GetString(reader.GetOrdinal("FullName")),
    SignupDate = reader.GetDateTime(reader.GetOrdinal("SignupDate")),
    AccountBalance = reader.IsDBNull(reader.GetOrdinal("AccountBalance")) 
        ? null 
        : reader.GetDecimal(reader.GetOrdinal("AccountBalance"))
});

Pros: #

• Type safety: Compile-time checks catch errors before runtime
• Readable code: Callers work with named properties instead of string keys
• Better performance: Direct property assignment is faster than dictionary lookups
• Clean separation of concerns: DAL handles data access, caller handles object mapping

Bonus: Simplify Mapping with Helper Methods #

To reduce boilerplate in the mapping lambda, add extension methods for IDataReader:

public static class DataReaderExtensions
{
    public static T GetValueOrDefault<T>(this IDataReader reader, string columnName)
    {
        var ordinal = reader.GetOrdinal(columnName);
        return reader.IsDBNull(ordinal) ? default(T) : (T)reader.GetValue(ordinal);
    }
}

Now your mapping becomes much cleaner:

var customers = ExecuteQuery(connectionString, getCustomerCommand, reader => new Customer
{
    Id = reader.GetValueOrDefault<int>("Id"),
    FullName = reader.GetValueOrDefault<string>("FullName"),
    SignupDate = reader.GetValueOrDefault<DateTime>("SignupDate"),
    AccountBalance = reader.GetValueOrDefault<decimal?>("AccountBalance")
});

Honorable Mention: DataTable #

If you need a middle ground (structured data without defining models), you could return a DataTable. But be aware that DataTable is heavier in terms of memory usage and less performant than strongly-typed lists. It's fine for quick prototypes, but not ideal for large datasets:

public DataTable ExecuteQueryToDataTable(string connectionString, SqlCommand command)
{
    var dataTable = new DataTable();
    using (var connection = new SqlConnection(connectionString))
    {
        command.Connection = connection;
        connection.Open();
        using (var adapter = new SqlDataAdapter(command))
        {
            adapter.Fill(dataTable);
        }
    }
    return dataTable;
}

Final Recommendation #

• Use strongly-typed objects for most production code (type safety, readability, performance)
• Use List<Dictionary<string, object>> only when you need dynamic schema support
• Avoid DataTable unless you have a specific legacy or prototyping need

内容的提问来源于stack exchange，提问作者user2921909