I am using 3 layer in my application
UI (WPF) ->BL->DAL
Business Objects is referred from UI,BL & DAL
DB is oracle and using "Oracle.ManagedDataAccess.Client"
My query is returning around 300000 records at a time.
After adding whole result from data reader to collection, returning that collection to BL, then to UI. it take a little long time.
I need to speed up or need to load as batches
by loading 100/1000 records to the grid So that user can avoid waiting long time to see the result.
Which is the best method to achieve it?
Any helpsss.. really appreciated... Please provide some sample code..or please change in the following sample code...
Number of records will be always above 300000 and its for some reports..
Sample code is given below to know how my coding goes....
Sample UI Code
gridEmployee.ItemsSource =new BLEmployee().GetEmployeeTransactions(startDate,endDate);
Sample Business Layer Code
public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
{
return new DLEmployee().GetEmployeeTransactions(startDate, endDate);
}
Sample Data Access LayerCode
public ObservableCollection<BOEmployeeTransaction> GetEmployeeTransactions(DateTime startDate, DateTime endDate)
{
ObservableCollection<BOEmployeeTransaction> empTransactions = new ObservableCollection<BOEmployeeTransaction>();
string query = "";
BOEmployeeTransaction transaction = null;
OracleConnection con = new OracleConnection();
con.ConnectionString = oracleConString;
con.Open();
OracleCommand cmd = new OracleCommand();
cmd.Connection = con;
cmd.CommandText = "SELECT Column1,Couln2... FROM TABLE1 WHERE TRUNC(A_DATE) BETWEEN TO_DATE('01-01-2015','MM-DD-YYYY') AND TO_DATE('02-02-2015','MM-DD-YYYY')"
cmd.CommandType = CommandType.Text;
using (OracleDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
transaction =new BOEmployeeTransaction();
transaction.Name=dr["Name"].ToString();
.......
.......
.......
empTransactions.Add(BOEmployeeTransaction);
}
}
con.Clone();
con.Dispose();
return transactions;
}