Required to know the performance overhead associated with DataSet and DataTable.
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
DataSet
is a collection ofDataTable
. Whenever you need to process multiple differentDataTable
s for one database, you can useDataSet
to take advantage of its capabilities likeDataRelation
, Data Integrity and Data Serialization. But if you just need only work on one table to process its data (like usingDataRow
) then DataTable would be enough. \$\endgroup\$DataSet
example should be returningdsProctorUserDetails
not anArrayList
? For that matter neither of your example methods is actually returning a value at all. Can you please fix these examples so that they should compile. \$\endgroup\$try
,catch
,finally
. As it would grow huge.. @ChrisSchaller \$\endgroup\$