Use DataTableReader instead of SqlDataReader

For our project, we've been using SqlDataReader as our main way of reading data from the db.

However, we found that using DataTableReader present in .NET framework 2.0 is so much faster when doing computations of any sort since the data is resigned in memory.


public DataTable getDataTable()
{
dbConnection.Open();
if (DTR != null && !DTR.IsClosed)
{
DTR.Close();
}
DataTable table = new DataTable();
dbCmd.CommandText = queryBuilder;
SqlDataAdapter da = new SqlDataAdapter(dbCmd);
da.Fill(table);
dbConnection.Close();
return table;
}

here, queryBuilder is basically a string which holds the CommandText.

Now, to get the DataTableReader.

public DataTableReader getDTReader()
{
//DTR is protected variable of type DataTableReader
if (DTR != null && !DTR.IsClosed) //Checking if it is closed or null for error prevention
{
DTR.Close();
}
DataTable DT = getDataTable();
DTR = DT.CreateDataReader();
return DTR;
}

It is also crucial to connect to the database only when needed. Connect and disconnect. If you leave the connection open for the program to use, you will notice that the connection will double and triple as the program keeps running on the background.