How to Deserialize datareader or data table to c# class

Question

i have populated data reader from db table and i have class like

public class CandidateApplication
{
                public string EmailID { get; set; }
                public string Name { get; set; }
                public string PhoneNo { get; set; }
                public string CurrentLocation { get; set; }
                public string PreferredWorkLocation { get; set; }
                public int RoleApplingFor { get; set; }
                public string CurrentJobTitle { get; set; }
                public int EducationLevel { get; set; }
                public decimal SalaryExpected { get; set; }
                public string AvailableTime { get; set; }
                public int AdvertID { get; set; }
                public bool SignForAlert { get; set; }
                public string CVInText { get; set; }
                public string CVFileName { get; set; }
                public bool IsDownloaded { get; set; }
                public string specialization { get; set; }
                public bool isallocated { get; set; }
                public int id { get; set; }
                public string AdvertAdditionalInfo { get; set; }
}

i can populate the above class in loop. we can iterate in data reader and populate class but i want to know is there any short cut way to populate class from data reader.

if data deserialization is possible from data reader to class then also tell me if few fields are there in class which are not there in data reader then how to handle the situation.

This seems to be answered here: http://stackoverflow.com/questions/1464883/how-can-i-easily-convert-datareader-to-listt — markoo, Feb 28 '13 at 12:10

Alex Filipovici · Answer 1 · 2013-03-01T07:52:46.880

Although not an answer to your question, I would suggest you to consider the following workaround, which uses a SqlDataAdapter instead of a data reader:

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Xml.Serialization;

class Program
{
    static void Main(string[] args)
    {

        var cs = "YourConnectionString";
        var xml = "";
        using (var con = new SqlConnection(cs))
        using (var c = new SqlCommand("SELECT * FROM CandidateApplication", con))
        {
            con.Open();
            using (var adapter = new SqlDataAdapter(c))
            {
                var ds = new DataSet("CandidateApplications");
                ds.Tables.Add("CandidateApplication");
                adapter.Fill(ds, ds.Tables[0].TableName);
                xml = ds.GetXml();
            }
        }

        // We need to specify the root element
        var rootAttribute = new XmlRootAttribute();

        // The class to use as the XML root element (should match the name of 
        // the DataTable in the DataSet above)
        rootAttribute.ElementName = "CandidateApplications";

        // Initializes a new instance of the XmlSerializer class that can 
        // serialize objects of the specified type into XML documents, and 
        // deserialize an XML document into object of the specified type. 
        // It also specifies the class to use as the XML root element.
        // I chose List<CandidateApplication> as the type because I find it
        // easier to work with (but CandidateApplication[] will also work)
        var xs = new XmlSerializer(typeof(List<CandidateApplication>), rootAttribute);

        // Deserialize the XML document contained by the specified TextReader, 
        // in our case, a StringReader instance constructed with xml as a parameter.
        List<CandidateApplication> results = xs.Deserialize(new StringReader(xml));
    }
}

For those properties that are missing in the retrieved data, you could declare a private field with a default value:

string _advertAdditionalInfo = "default";
public string AdvertAdditionalInfo
{
    get
    {
        return _advertAdditionalInfo;
    }
    set
    {
        _advertAdditionalInfo = value;
    }
}

If you would like to enforce that the retrieved data will not fill in a specific property, use:

[XmlIgnoreAttribute]
public string AdvertAdditionalInfo { get; set; }

would u plzz explain last 5 line from main method. just tell me what each line is doing in details. thanks — Thomas, Mar 01 '13 at 07:36
how null will be handle in ur case? suppose when u retrieve data from db then there could be null and that null will be assign in dataset. so when u deserialize dataset to CandidateApplication then how null will be handle? or how to assign any value to any property when null found at the time of deserialization to CandidateApplication instance ? my two question is very important. please answer in details if possible. thanks — Thomas, Mar 01 '13 at 07:40
I've updated my answer with some inline comments. `rootAttribute.IsNullable = true;` is not really needed, so I removed that line. — Alex Filipovici, Mar 01 '13 at 07:53
What could be null? One/more properties? Please take a look at the `AdvertAdditionalInfo` and `_advertAdditionalInfo` usage in my answer. When `AdvertAdditionalInfo` is `null` in the DataSet, `"default"` will be used (or the default value you want to use). The same logic can be applied to all other properties in your class. — Alex Filipovici, Mar 01 '13 at 07:58

Derek · Accepted Answer · 2013-02-28T13:54:23.597

You don't need to use a Data Reader, You could just Populate the Data into a DataTable, and use the below method to create a List of your CandidateApplication Class.

The Call :-

List<CandidateApplication> CandidateList = GetCandidateInformation();

The Method that generates the list :-

public List<CandidateApplication> GetCandidateInformation()
        {
            DataTable dt = new DataTable();

            using (OleDbConnection con = new OleDbConnection(ConfigurationManager.AppSettings["con"]))
            {
                using (OleDbCommand cmd = new OleDbCommand("SELECT * FROM [TableName]", con))
                {
                    var adapter = new OleDbDataAdapter();
                    adapter.SelectCommand = cmd;

                    con.Open();
                    adapter.Fill(dt);

                    var CandApp = (from row in dt.AsEnumerable()

                    select new CandidateApplication
                    {

                    EmailID = row.Field<string>("EmailID"),
                    Name  = row.Field<string>("Name"),
                    PhoneNo = row.Field<string>("PhoneNo"),
                    CurrentLocation = row.Field<string>("CurrentLocation"),
                    PreferredWorkLocation = row.Field<string>("PreferredWorkLocation"),
                    RoleApplingFor = row.Field<int>("RoleApplingFor"),
                    CurrentJobTitle = row.Field<string>("CurrentJobTitle"),
                    EducationLevel = row.Field<int>("EducationLevel "),
                    SalaryExpected = row.Field<decimal>("SalaryExpected"),
                    AvailableTime = row.Field<string>("AvailableTime"),
                    AdvertID = row.Field<int>("AdvertID"),
                    SignForAlert = row.Field<bool>("SignForAlert"),
                    CVInText = row.Field<string>("CVInText"),
                    CVFileName = row.Field<string>("CVFileName"),
                    IsDownloaded = row.Field<bool>("IsDownloaded"),
                    Specialization = row.Field<string>("Specialization"),
                    Isallocated = row.Field<bool>("Isallocated"),
                    Id = row.Field<int>("Id"),
                    AdvertAdditionalInfo = row.Field<string>("AdvertAdditionalInfo")


                    }).ToList();

                    return CandApp;
                }
            }
        }

i have one question that when we extract data and assign to class property like EmailID = row.Field("EmailID") if email id or any integer or date field have null then how to handle...please guide me with code. another issue that...can't we assign null to int,bool & datetime type of variable? — Thomas, Mar 01 '13 at 07:33
i have not encounter any issue becoz there was no null in my data. so i want to know if null is there in data then what will happen...any error occur. if yes then how to handle error. — Thomas, Mar 01 '13 at 18:27

score 0 · Answer 3 · answered Oct 07 '22 at 17:50

I made a generic function for converting the SELECT result from an OleDbCommand to a list of classes.

Let's say that I have a class that looks like this, which maps to the columns in the database:

internal class EconEstate
{
    [Column(Name = "basemasterdata_id")]
    public Guid BaseMasterDataId { get; set; }

    [Column(Name = "basemasterdata_realestate")]
    public Guid? BaseMasterDataRealEstate { get; set; }

    [Column(Name = "business_area")]
    public string BusinessArea { get; set; }

    [Column(Name = "profit_centre")]
    public int ProfitCentre { get; set; }

    [Column(Name = "rentable_area")]
    public decimal RentableArea { get; set; }
}

Then I can get a list of those EconEstate objects using this code:

public void Main()
{
    var connectionString = "my connection string";
    var objects = ReadObjects<EconEstate>(connectionString, "EMBLA.EconEstates").ToList();
}

private static IEnumerable<T> ReadObjects<T>(string connectionString, string tableName) where T : new()
{
    using (var connection = new OleDbConnection(connectionString))
    {
        connection.Open();
        using (var command = new OleDbCommand($"SELECT * FROM {tableName};", connection))
        {
            var adapter = new OleDbDataAdapter
            {
                SelectCommand = command
            };
            var dataTable = new DataTable();
            adapter.Fill(dataTable);
            foreach (DataRow row in dataTable.Rows)
            {
                var obj = new T();
                foreach (var propertyInfo in typeof(T).GetProperties())
                {
                    var columnAttribute = propertyInfo.GetCustomAttributes().OfType<ColumnAttribute>().First();
                    var value = row[columnAttribute.Name];
                    var convertedValue = ConvertValue(value, propertyInfo.PropertyType);
                    propertyInfo.SetValue(obj, convertedValue);
                }
                yield return obj;
            }
        }
    }
}

private static object ConvertValue(object value, Type targetType)
{
    if (value == null || value.GetType() == typeof(DBNull))
    {
        return null;
    }
    if (value.GetType() == targetType)
    {
        return value;
    }
    var underlyingTargetType = Nullable.GetUnderlyingType(targetType) ?? targetType;
    if (value is string stringValue)
    {
        if (underlyingTargetType == typeof(int))
        {
            return int.Parse(stringValue);
        }
        else if (underlyingTargetType == typeof(decimal))
        {
            return decimal.Parse(stringValue);
        }
    }
    var valueType = value.GetType();
    var constructor = underlyingTargetType.GetConstructor(new[] { valueType });
    var instance = constructor.Invoke(new object[] { value });
    return instance;
}

As you can see, the code is generic, making it easy to handle different tables and classes.

How to Deserialize datareader or data table to c# class

3 Answers3