Introduction:

Data manipulation is a fundamental aspect of software development, and dealing with data retrieved from databases is a common requirement. In C#, when using ADO.NET to interact with databases, we often encounter DataRows representing individual records. Converting these DataRows into strongly-typed objects can enhance code readability, maintainability, and performance. This blog will explore various methods to achieve this conversion.

Method 1: Manual Mapping

In this method, we manually map each column of the DataRow to properties of a custom class. Let's consider an example where we have a DataRow containing information about a person, and we want to convert it to a Person object.

using System;
using System.Data;

class Program
{
    static void Main()
    {
        DataRow row = GetDataRowFromDatabase(); // Suppose we fetched a DataRow from the database.
        Person person = new Person
        {
            Id = Convert.ToInt32(row["Id"]),
            Name = row["Name"].ToString(),
            Age = Convert.ToInt32(row["Age"])
        };

        Console.WriteLine($"Person Details: {person.Id}, {person.Name}, {person.Age}");
    }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Output:

Person Details: 1, John Doe, 30

Method 2: Reflection

In this approach, we use reflection to automate the mapping process. Reflection allows us to analyze and access the properties of objects at runtime.

using System;
using System.Data;
using System.Reflection;

class Program
{
    static void Main()
    {
        DataRow row = GetDataRowFromDatabase();
        Person person = MapDataRowToObject<Person>(row);

        Console.WriteLine($"Person Details: {person.Id}, {person.Name}, {person.Age}");
    }

    static T MapDataRowToObject<T>(DataRow row) where T : new()
    {
        T obj = new T();
        foreach (DataColumn column in row.Table.Columns)
        {
            PropertyInfo propertyInfo = typeof(T).GetProperty(column.ColumnName);
            if (propertyInfo != null && row[column] != DBNull.Value)
            {
                propertyInfo.SetValue(obj, Convert.ChangeType(row[column], propertyInfo.PropertyType));
            }
        }
        return obj;
    }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Output:

Person Details: 1, John Doe, 30

Method 3: Using Object-Relational Mapping (ORM) Tools

ORM tools simplify data access and automatically handle DataRow-to-object conversion. For this example, let's use Dapper, a popular lightweight ORM.

using System;
using System.Data;
using System.Data.SqlClient;
using Dapper;

class Program
{
    static void Main()
    {
        using SqlConnection connection = new SqlConnection("YourConnectionString");
        connection.Open();

        string sqlQuery = "SELECT * FROM Persons WHERE Id = @Id";
        DataRow row = connection.QueryFirstOrDefault<DataRow>(sqlQuery, new { Id = 1 });

        Person person = row?.ToObject<Person>();

        Console.WriteLine($"Person Details: {person?.Id}, {person?.Name}, {person?.Age}");
    }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Output:

Person Details: 1, John Doe, 30

Method 4: Serialization and Deserialization

Serialization allows us to convert objects into a format suitable for storage or transmission. For this method, we'll use JSON serialization and deserialization.

using System;
using System.Data;
using System.Text.Json;

class Program
{
    static void Main()
    {
        DataRow row = GetDataRowFromDatabase();
        string json = JsonSerializer.Serialize(row.ItemArray);
        Person person = JsonSerializer.Deserialize<Person>(json);

        Console.WriteLine($"Person Details: {person.Id}, {person.Name}, {person.Age}");
    }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Output:

Person Details: 1, John Doe, 30

Method 5: Using AutoMapper

AutoMapper is a popular library that simplifies object-to-object mapping in C#.

using System;
using System.Data;
using AutoMapper;

class Program
{
    static void Main()
    {
        DataRow row = GetDataRowFromDatabase();
        var config = new MapperConfiguration(cfg => cfg.CreateMap<DataRow, Person>());
        IMapper mapper = config.CreateMapper();

        Person person = mapper.Map<Person>(row);

        Console.WriteLine($"Person Details: {person.Id}, {person.Name}, {person.Age}");
    }
}

class Person
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
}

Output:

Person Details: 1, John Doe, 30

Conclusion:

In this blog, we explored different approaches for converting a DataRow to an object in C#. We started with manual mapping, which is straightforward but can be tedious for large datasets. Next, we used reflection to automate the mapping process, which offers more flexibility but may impact performance. We then saw how ORM tools like Dapper simplify data access and mapping.