Ideal ways for Choosing DataSet or DataTable

Question

1. Required to know the performance overhead associated with DataSet and DataTable.

2. When it comes choosing which data structure might be good and performance oriented

   • what if single Result Set is returned from Stored Procedure
   • what if more than one Result Set is returned from Stored Procedure

Question may arise choosing whether which one should we need to go for?

Here is the coding snippet from Business Access Layer:

// Method 1: Using DataTable
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataTable dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    
    if (dtProctorUserDetails != null && dtProctorUserDetails.Rows.Count > 0)
    {
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("UserID") && dtProctorUserDetails.Rows[i]["UserID"] != DBNull.Value)
                proctorUserDetails.UserID = Convert.ToString(dtProctorUserDetails.Rows[i]["UserID"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("LoginName") && dtProctorUserDetails.Rows[i]["LoginName"] != DBNull.Value)
                proctorUserDetails.LoginName = Convert.ToString(dtProctorUserDetails.Rows[i]["LoginName"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("FirstName") && dtProctorUserDetails.Rows[i]["FirstName"] != DBNull.Value)
                proctorUserDetails.FirstName = Convert.ToString(dtProctorUserDetails.Rows[i]["FirstName"]);
            if (dtProctorUserDetails.Rows[i].Table.Columns.Contains("LastName") && dtProctorUserDetails.Rows[i]["LastName"] != DBNull.Value)
                proctorUserDetails.LastName = Convert.ToString(dtProctorUserDetails.Rows[i]["LastName"]);
    
            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

---

// Method 2: Using DataSet
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    
    if (dsProctorUserDetails != null && dsProctorUserDetails.Tables.Count > 0 && dsProctorUserDetails.Tables[0].Rows.Count > 0)
    {
        for (int i = 0; i < dsProctorUserDetails.Tables[0].Rows.Count; i++)
        {
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (dsProctorUserDetails.Tables[0].Columns.Contains("UserID") && dsProctorUserDetails.Tables[0].Rows[i]["UserID"] != DBNull.Value)
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["UserID"]));
            if (dsProctorUserDetails.Tables[0].Columns.Contains("LoginName") && dsProctorUserDetails.Tables[0].Rows[i]["LoginName"] != DBNull.Value)
                proctorUserDetails.LoginName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["LoginName"]);
            if (dsProctorUserDetails.Tables[0].Columns.Contains("FirstName") && dsProctorUserDetails.Tables[0].Rows[i]["FirstName"] != DBNull.Value)
                proctorUserDetails.FirstName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["FirstName"]);
            if (dsProctorUserDetails.Tables[0].Columns.Contains("LastName") && dsProctorUserDetails.Tables[0].Rows[i]["LastName"] != DBNull.Value)
                proctorUserDetails.LastName = Convert.ToString(dsProctorUserDetails.Tables[0].Rows[i]["LastName"]);
    
            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

---

// To call SP to whichever prototype/return type
public static <String/DataSet/DataTable> GetProctorsDetails(LoginUser sessionDetails)
{
    List<object> parameter = new List<object>();
    parameter.Add(SqlHelper.BuildSqlParameter("OrganizationID", SqlDbType.BigInt, sizeof(Int64), sessionDetails.OrganizationID));
    parameters.Add(SqlHelper.BuildSqlParameter("Status", SqlDbType.VarChar, 5, "Status", string.Empty, ParameterDirection.Output));
    
    using (DataTable dt = SqlHelper.ExecuteDataTable(CommandType.StoredProcedure, "UspGetUserOrgDetails", parameters.ToArray(), false))
    {
        statusCode = Convert.ToString(SqlHelper.GetParameterValue(parameters[1]));
    }
    
    using (DataSet ds = SqlHelper.ExecuteDataset(CommandType.StoredProcedure, "UspGetUserOrgDetails", parameters.ToArray(), false))
    {
        statusCode = Convert.ToString(SqlHelper.GetParameterValue(parameters[1]));
    }
    return <statusCode/ds/dt>;
}

How should be our choice of selection? Especially for .NET Framework or Core or some other programming language

Answer 1

As pointed out by @iSR5's comment, A DataSet is basically a wrapper around a collection or DataTable objects. It has some other helpful utility methods, but from a performance point of view the only performance issue with your logic is that in the DataSet solution you need to access the table to read from through the indexer that has assumed that there is a table at index 0.

• So there is not really a performance argument to choose one over the other.
• Given that both solutions for your Business layer GetProctorsDetails do not return either of the DataSet or DataTable objects, your ONLY consideration is actually the implementation of PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
  • Your code examples already demonstrate that you can manage both types of results from this method.

Focusing on the DAL Implementation

Specifically for SqlHelper there is no measurable difference between ExecuteDataTable and ExecuteDataSet, in fact if you have a look at the source code, most implementations or ExecuteDataTable use a DataSet internally:

var ds = new DataSet();
using (var dataAdapter = new SqlDataAdapter(command))
{
    dataAdapter.Fill(ds);
}
return ds.Tables[0];

DO NOT make the assumption that using DataSet is therefore more performant!

The only reason you should consider a DataSet over a DataTable is because you want to return multiple tables of records. If you are only going to return a single table of results, DataTable is the better choice because it simplifies the code, but also conveys the correct expectation to callers that there is only a single table of records in this result set.

Back to the Business layer mapping

The code smell to watch out for is when you have lots of code that is accessing an item in an array at index zero, in your second code example the following indexer is evaluated 5 times for every record:

dsProctorUserDetails.Tables[0]

It's not a big deal at runtime, but the use of DataSet in this scenario has added an unnecessary level of complexity and uncertainty into this code.

If we refactor your 1st two examples to reduce the repetitive column validations, (do you really expect or want to support the condition of only some if these columns being present in the result set?) you will see there is even less difference in the usage between DataTable and DataSet:

// Method 1: Using DataTable
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataTable dtProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);

    if (dtProctorUserDetails != null && dtProctorUserDetails.Rows.Count > 0)
    {
        var userIdOrd = dtProctorUserDetails.Columns.Contains("UserID") ? dtProctorUserDetails.Columns["UserID"].Ordinal : -1;
        var loginOrd = dtProctorUserDetails.Columns.Contains("LoginName") ? dtProctorUserDetails.Columns["LoginName"].Ordinal : -1;
        var firstNameOrd = dtProctorUserDetails.Columns.Contains("FirstName") ? dtProctorUserDetails.Columns["FirstName"].Ordinal : -1;
        var lastNameOrd = dtProctorUserDetails.Columns.Contains("LastName") ? dtProctorUserDetails.Columns["LastName"].Ordinal : -1;
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            var row = dtProctorUserDetails.Rows[i];
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (userIdOrd > 0 && !row.IsNull(userIdOrd))
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(row[userIdOrd]));
            if (loginOrd > 0 && !row.IsNull(loginOrd))
                proctorUserDetails.LoginName = Convert.ToString(row[loginOrd]);
            if (firstNameOrd > 0 && !row.IsNull(firstNameOrd))
                proctorUserDetails.FirstName = Convert.ToString(row[firstNameOrd]);
            if (lastNameOrd > 0 && !row.IsNull(lastNameOrd))
                proctorUserDetails.LastName = Convert.ToString(row[lastNameOrd]);

            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

For performance reasons, I have moved the column exists check out of the row indexer (all rows in the table will have the same columns), by storing and using the column ordinal we can squeeze out some additional CPU ticks by not having to compare strings.

See if you can spot the main difference between Method 1 and Method 2:

// Method 2: Using DataSet
public static ArrayList GetProctorsDetails(LoginUser sessionDetails)
{
    List<ProctorUserDetails> proctorUserDetailsList = new List<ProctorUserDetails>();
    DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
    if (dsProctorUserDetails != null && dsProctorUserDetails.Tables.Count > 0 && dsProctorUserDetails.Tables[0].Rows.Count > 0)
    {
        DataTable dtProctorUserDetails = dsProctorUserDetails.Tables[0];

        var userIdOrd = dtProctorUserDetails.Columns.Contains("UserID") ? dtProctorUserDetails.Columns["UserID"].Ordinal : -1;
        var loginOrd = dtProctorUserDetails.Columns.Contains("LoginName") ? dtProctorUserDetails.Columns["LoginName"].Ordinal : -1;
        var firstNameOrd = dtProctorUserDetails.Columns.Contains("FirstName") ? dtProctorUserDetails.Columns["FirstName"].Ordinal : -1;
        var lastNameOrd = dtProctorUserDetails.Columns.Contains("LastName") ? dtProctorUserDetails.Columns["LastName"].Ordinal : -1;
        for (int i = 0; i < dtProctorUserDetails.Rows.Count; i++)
        {
            var row = dtProctorUserDetails.Rows[i];
            ProctorUserDetails proctorUserDetails = new ProctorUserDetails();
            if (userIdOrd > 0 && !row.IsNull(userIdOrd))
                proctorUserDetails.UserID = AESCrypto.Encrypt(Convert.ToString(row[userIdOrd]));
            if (loginOrd > 0 && !row.IsNull(loginOrd))
                proctorUserDetails.LoginName = Convert.ToString(row[loginOrd]);
            if (firstNameOrd > 0 && !row.IsNull(firstNameOrd))
                proctorUserDetails.FirstName = Convert.ToString(row[firstNameOrd]);
            if (lastNameOrd > 0 && !row.IsNull(lastNameOrd))
                proctorUserDetails.LastName = Convert.ToString(row[lastNameOrd]);

            proctorUserDetailsList.Add(proctorUserDetails);
        }
    }
    return proctorUserDetailsList;
}

If you missed it, in the second example we get the table at index 0 from the dataset, from then on the processing is identical:

DataSet dsProctorUserDetails = PasswordGeneratorDAL.GetProctorsDetails(sessionDetails);
DataTable dtProctorUserDetails = dsProctorUserDetails.Tables[0];

Remember my point above:

DO NOT make the assumption that using DataSet is therefore more performant!

There is zero net difference between 1 and 2 in the refactored versions, in 1 the .Tables[0] is executed once inside the SqlHelper routine, in 2 it is executed in our code.

---

Additional Considerations

In General, using the DataTable solution is likely to have less long term maintenance issues especially when you want to support a stored procedure that might return multiple tables of result sets.

• When there are multiple tables in the response, only the calling context (the context that provides the SQL) is likely to know which one of the tables in the result set is the table that this method needs to interact with, unless you can guarantee that the proctor details table that you want to inspect is always the first table in the DataSet.
  • So DataTable is better than trying to blindly support DataSet, to properly support DataSet you should be iterating the Tables within it to identify which table matches the columns that you are expecting to read from.

How should be our choice of selection? Especially for .NET Framework or Core or some other programming languge

From a performance and maintenance perspective I would not advocate using DataTable or DataSet in a new project or any .Net Core projects moving forward. Instead I would recommend moving towards a typed data access layer using an ORM framework or tool (Entity Framework/nHibernate). To keep your DAL and DTO classes decoupled you can use Interfaces instead of DataSets/DataTables. There are also tools like AutoMapper that replace your first two code examples to map between your DAL and business layer or DTOs.

In legacy or simple .Net FX applications DataSet or DataTable can work, even moving forward, however there have been other significant advances in the IDE tooling and syntax and language runtimes that mean it is often easier and less error prone to work in typed data models, so avoiding DataTable and DataSet altogether.

The issue with DataTable and DataSets, from a performance perspective, is that code like this must search for and pre-validate every property on demand. When you move to a typed interface the property resolution and type validation is performed in an efficient manner when the data is loaded from the database, or when it is deserialized.

• Note, as you flagged performance as a driving factor, implementing an ORM and or a DTO mapper like AutoMapper itself is not likely to be more performant, in fact it can be perceived to be slower in some cases, especially for loading data records, however your code becomes more reliable saving you development time and they can award you performance and maintenance gains throughout the rest of your application to offset or trade off any losses you might be able to measure at data load.