Using ADO.NET DataTable.Load() method
The ADO.NET DataTable supports a Load() method that will fill a datatable from a datasource using the IDataReader.The good thing about this is that if your datatable already contains data, the new data can be merged with the existing tables datarows.
The Load option supports the following 3 enumerations:
PreserveChanges is the default and states that it will update the existing value of the row with the new data being loaded (if it already exists).
OverwriteChanges – Updates the current and original versions of the row with the value of the incoming row.
Upsert – Updates the current and original versions of the row with the value of the incoming row.
I have a method within a .aspx page that checks the Cache for a datatable. If the table is not in the Cache, it will be loaded and put into the Cache.. In it’s simplest terms, the Load() method looks as follows:
private DataTable CheckCacheForCustomers()
{
DataTable _customers = null;
_customers = (DataTable)Cache["Customers"];
if (_customers != null)
return _customers;
using(SqlConnection conn = new SqlConnection(connectstring))
{
try
{
conn.Open();
string _sql = “Select * from Customers”;
using (SqlCommand command = new SqlCommand(_sql, conn))
{
IDataReader reader = command.ExecuteReader();
_customers = new DataTable();
_customers.Load(reader);
Cache["Customers"] = _customers;
}
}
catch (Exception ex)
{
Response.Write(ex.Message.ToString());
}
}
return _customers;
}
Notice the code:
IDataReader reader = command.ExecuteReader();
_customers = new DataTable();
_customers.Load(reader);
