I have the following C# code that runs a SQL query:

var users = new List<Person>();
try
{
    conn.Open();                
    SqlCommand cmd = new SqlCommand(
        @$"
        WITH emp AS (
              SELECT *
              FROM {tableName}
              WHERE Id = {id}
 
              UNION ALL
   
              SELECT e.*
              FROM {tableName} e INNER JOIN emp
              ON e.ManagerId = emp.Id
        )
        SELECT e.*
        FROM emp e;                    
        ", conn);
    SqlDataReader reader = cmd.ExecuteReader();

    int id = reader.GetOrdinal("Id");
    int email = reader.GetOrdinal("Email");
    int managerId = reader.GetOrdinal("ManagerId");
    // 50 more                
    while (reader.Read())
    {
        var response = new Person
        {
            Id = reader.IsDBNull(id) ? null : reader.GetInt32(id).ToString(),
            Email = reader.IsDBNull(email) ? null : reader.GetString(email),
            ManagerId = reader.IsDBNull(managerId) ? 0 : reader.GetInt32(managerId)                        
            // 50 more 
        };
        users.Add(response);
    }

    reader.Close();
}
finally
{
    conn.Close();
}

Is there a different way to convert the response into Person data type as there > 50 columns?

CodePudding user response：

If you are just mapping a query to an object I would use a ORM to do it for you. Dapper is my choice. It's available via NuGet.

CodePudding user response：

When you're dealing with simple queries and result sets this sort of classic code is fine. Past a certain level of complexity though - as you're discovering - it's tedious as all hell. Fortunately there are a ton of libraries out there that are specifically designed to resolve this type of problem.

For a minimal change approach, try Automapper or Dapper... or any of several other mapping libraries that do similar things. They turn the reading code into one or two lines regardless of the number of fields.

On the other hand you can take the step into using a full-blown ORM (Object-Relational Mapper) like LINQ To SQL, Entity Framework, NHibernate, LINQ to DB (my favorite)... and so on. These tools give you a different way to look at database access from your .NET code, letting you do most of your queries (and other database operations) from direct C# code. For instance, your query above can be done with LINQ to DB's CTE extensions fairly quickly without having to manually manage connection, command and so on.

And on the gripping hand: learn reflection and write your own custom mapper. While this isn't the simplest way to go it can be quite rewarding, and it's the most lightweight option since there are zero dependencies. Add a little LINQ expression magic and you can do all sorts of fun things.