.NET Core 2.0 Strongly Typed DataRows

Question

Is there a way to strongly type a DataRow in C# .NET Core 2.0 without using EF nor any ORM? I used datasets on vb.net, but it looks like it's not yet available. Also, it looks like SqlCommandBuilder isn't available either.

I used something like this in vb.net:

Public Sub EntityToDataSet(ByRef entity As User, ByRef dataset As MyDataSet)
Dim dr As MyDataSet.UsersRow
dr = dataset.Users.NewUsersRow()
dr.Address = entity.Address
dr.Name = entity.Name
_dataAdapterUsers.Update(dataSet)
End Sub

As you can see, the dataset provides strongly typed rows. I want to be able to do something like this on .NET Core.

Answer 1

Even if strongly typed DataSets, DataRows etc. were available using a .NET Core Stack and ADO.NET which they are not, I think you will find in the .NET Core community today that there would be very little use of DataRow or DataSet at all and certainly not as a means to enforce strong typing, for example. That concept of "strongly typed" that ADO.NET provided out of the box for some of its key objects like DataTable, DataRow, Dataset is an old school mentality. If you want a strongly typed solution in this day and age, you simply define your types and throw exceptions when incorrect type casting is attempted, or do not even throw exceptions, C# will throw invalid cast exceptions for you.

I think it also makes sense to say that using a strongly typed DataRow/DataSet implementation is reminiscent of an implementation you might do with ASP.NET Web Forms stack circa 1999-2005. If I remember correctly ADO.NET DataTable and DataSet had these out of the box strongly typed features developers would utilize. But today, the best practices are always to keep things very simple and clean, no out of the box magic that Microsoft used to provide, that magic almost always led to monolithic, hard to maintain software architecture.

I believe the code below is a "strongly typed" solution, using .NET Core 2.0, ADO.NET and not using EF or any other ORM, if you just do not focus on a strongly typed "DataRow" requirement which confuses me a bit that is a requirement from the University, I am guessing that using a DataRow is something you hope to achieve because it is familiar to you from past code you have written.

And keep in mind that MySQL, SQL Server, Oracle database can be designed "strongly typed", and really cannot be easily designed any other way because columns have types like: varchar, int, decimal, money, byte, guid, nvarchar etc.

You could make every column in every database a blob or byte and then argue the database is dynamically typed but it is a bit of silly scenario.

C# is a "strongly typed" language.

So with all that said, and as long as the University does not specifically ask that you use DataRow or DataSet to enforce strong typing, then this solution might fit the bill as it still uses ADO.NET:

public class AddressModel {
    public string Address1 { get; set; } //this is a strongly typed property
    public int Zip { get; set; } //this is a strongly typed property
}

public List<AddressModel> GetAddress(int addressId) {
    List<AddressModel> addressList = new List<AddressModel>();
    cmd.CommandText = @"SELECT Address1, Zip FROM [dbo].[Address]";
    using (SqlDataReader data = cmd.ExecuteReader())
    {
        while (data.Read())
        {
            AddressModel temp = new AddressModel();
            temp.Address1 = Sql.Read<String>(data, "Address1");
            temp.Zip = Sql.Read<Int32>(data, "Zip");
            addressList.Add(temp);
        }
    }

    return addressList;
}

public static class Sql
{
    public static T Read<T>(DbDataReader DataReader, string FieldName)
    {
        int FieldIndex;
        try { FieldIndex = DataReader.GetOrdinal(FieldName); }
        catch { return default(T); }

        if (DataReader.IsDBNull(FieldIndex))
        {
            return default(T);
        }
        else
        {
            object readData = DataReader.GetValue(FieldIndex);
            if (readData is T)
            {
                return (T)readData;
            }
            else
            {
                try
                {
                    return (T)Convert.ChangeType(readData, typeof(T));
                }
                catch (InvalidCastException)
                {
                    return default(T);
                }
            }
        }
    }
}

Credit goes to this answer for the nice Read extension method that will throw Invalid Type Cast Exceptions