Replacement for DataTable/DataSet in .NET Core (NET Standard 2.1)

Question

.NET Core 3.1 supports DataTable/DataSet, DataRow, DataView, etc - which was not available when the OP created the following question

based on my research, it seems like .net core does not support DataTable/DataSet. I recently shifted to .net core for developing a new application, but failed to recognize that .net core does not have library for those in the first place. I used to use dt/ds for fetching the data via stored procedure and fill collection of classes. But now, I am quite lost with this new problem that I have to find replacement for dt/ds. To be more specific, I used to do this:

I have a stored procedure that takes four input parameters and returns a table.

ALTER PROCEDURE stp_Student

@Name nvarchar(450), 
@StudentIds tvp_ArrayInt READONLY,  
@StartDate date,
@EndDate date, 

AS

blah blah 

//returns student summary
SELECT  stu.StudentId, 
        stu.StudentName, 
        CASE WHEN (COUNT(DISTINCT course.Id) IS NOT NULL) THEN COUNT(DISTINCT course.Id) ELSE 0 END AS CourseCount, 
        CASE WHEN (SUM(course.TotalCourses) IS NOT NULL) THEN SUM(course.TotalCourses) ELSE 0 END AS TotalCourses, 
        CASE WHEN (SUM(course.Hours) IS NOT NULL) THEN SUM(course.Hours) ELSE 0 END AS Hours
FROM    #TempStudent AS #Temp 

and create a class with same fields as I have in my stored procedure.

public class StudentModel
{  
    public string StudentId { get; set; }
    public string StudentName { get; set; }
    public int CourseCount { get; set; }
    [Column(TypeName = "Money")]
    public decimal TotalCourses { get; set; }
    public double Hours { get; set; }
}

and fetch data and fill collection of class

using (SqlConnection conn = new SqlConnection(connectionString)) 
{
    SqlCommand cmd = new SqlCommand("sp_Student", conn);
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.Parameters.AddWithValue("@Name", name);
    cmd.Parameters.AddWithValue("@StudentIds", studentIds);
    cmd.Parameters.AddWithValue("@StartDate", startDate);
    cmd.Parameters.AddWithValue("@EndDate", endDate);
    conn.Open();

    SqlDataAdapter da = new SqlDataAdapter();
    da.SelectCommand = cmd;
    da.Fill(ds);
    conn.Close();
}

foreach (Datarow row in ds.Tables[0].Rows)
{
    var model = new StudentModel();
    model.StudentId = Convert.ToString(row["StudentId"]);
    //etc..
    studentList.Add(model);
}

My bad, I should have researched more before I initiated the project, but I really need anyone's help to find replacement for the above code. Would it be possible to use .net core's FromSql method as a replacement? Any suggestion or sample codes would be appreciated.

Edit

According to Peter's article, found a way to implement ds/dt in .net core version, but having a bit problem passing an int array(StudentIds) to my stored procedure.

public List<StudentModel> GetStudents(string name, IEnumerable<int> studentIds, DateTime startDate, DateTime endDate)
    {
        List<StudentModel> students = new List<StudentModel>();
        StudentModel = null;

        List<DbParameter> parameterList = new List<DbParameter>();

        parameterList.Add(base.GetParameter("Name", name));
        parameterList.Add(base.GetParameter("StudentIds", studentIds));           
        parameterList.Add(base.GetParameter("StartDate", startDate));
        parameterList.Add(base.GetParameter("EndDate", endDate));       

        using (DbDataReader dataReader = base.ExecuteReader("stp_Student", parameterList, CommandType.StoredProcedure))
        {
            if (dataReader != null)
            {
                while (dataReader.Read())
                {
                    model = new StudentModel();
                    model.StudentId= (int)dataReader["StudentId"];
                    ....

                    students .Add(model);
                }
            }
        }
        return students;
    }
}

I think the problem comes from passing IEnumerable? How can I pass an int array to stored procedure correctly?

Egeria

It will come in the next versio, see github.com/dotnet/corefx/pull/12426 – Fiscus

Also, take a look at social.technet.microsoft.com/wiki/contents/articles/… – Fiscus

I also have this problem, except that my stored procedure returns a number of tables. So in my code I just assign the result to a Dataset and access each table from there. Damn Microsoft is again forcing us to use half-baked products. – Denmark

Answer 1

I don't know much about .Net Core, but I think you can use IEnumerable<SqlDataRecord> instead.

Patriarchy

Answer 2

DataTable now exists in .NET Core 2.0. See my answer at https://blogs.msdn.microsoft.com/devfish/2017/05/15/exploring-datatable-and-sqldbadapter-in-asp-net-core-2-0/ .

public static DataTable ExecuteDataTableSqlDA(SqlConnection conn, CommandType cmdType, string cmdText, SqlParameter[] cmdParms)
 {
 System.Data.DataTable dt = new DataTable();
 System.Data.SqlClient.SqlDataAdapter da = new SqlDataAdapter(cmdText, conn);
 da.Fill(dt);
 return dt;
 }

Mastoid

And any compatibility for net standard 1.5? There is no DataSet avaliable. Now, 2.0 is in preview and not production ready. – Yetah

Answer 3

Here is the correct class to use for .NET Framework and .NET Core:

https://learn.microsoft.com/en-us/dotnet/api/system.data.dataset?view=net-8.0

It supports the following:

Product	Versions
.NET	Core 2.0, Core 2.1, Core 2.2, Core 3.0, Core 3.1, 5, 6, 7, 8, 9
.NET Framework	1.1, 2.0, 3.0, 3.5, 4.0, 4.5, 4.5.1, 4.5.2, 4.6, 4.6.1, 4.6.2, 4.7, 4.7.1, 4.7.2, 4.8, 4.8.1
.NET Standard	2.0, 2.1

Unpaid