I have created a basic form that uses the background worker to execute a SQL query.
First I start my Background Worker which creates a DataReader(using my connection and command text), I then loop through the reader getting each row adding them to a DataTable. I use the ReportProgress method to update my screen with the current row count (this updates a label). On completion of the worker I fill a DataGridView with the Datatable.
The first time I ran a query using this method it ran very quickly(how it should work). The problem is when I run this again the DataReader appears to get the data significantly slower.
I have managed to successfully use a DataAdaptor to fill a DataTable directly(using a background worker) however I have no control over the retrieval of this data i.e I can’t cancel the executing query because it gets all the data at once.
Ideally I want to use the background worker to get data (row by row) using a DataReader, I will then store a collection of rows (in a DataTable or arraylist) this would then update my DataGridView using ReportProgress (report progress should run every 1000 rows), so every 1000 rows retrieved they will be added to the DataGridView.
For some reason my problem has only arisen when I started to use a DataReader. Is a Background Worker an appropriate way of achieving my goal
Any help would be appreciated, if you need to view my code I can post it here for you.
Many Thanks,
Andy
Executing SQL query on Windows Form using Background Worker
n0n4m3
yeah, reading the data one by one from the database is how I would like to do this.
I closed the DataReader on completion of the Background Worker, I have also tried to dispose of the DataReader but this didn't help.
this is the code I am using,
On click of my button.
private void btnGo_Click(object sender, EventArgs e){
btnClear_Click(
this, EventArgs.Empty); this.tssStart.Text = System.DateTime.Now.ToLongTimeString(); this.progressBar1.Visible = true; this.progressBar1.Value = 0;dgvMyData.DataSource =
null; this.backgroundWorker1.RunWorkerAsync();}
RunWorkerAsync() runs this.
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e){
BackgroundWorker bw = sender as BackgroundWorker;GetData(bw);
if (bw.CancellationPending){
e.Cancel =
true;}
}
GetData runs this.
private
void GetData(BackgroundWorker bw){
// get datareaderdr =
DataBuilder.BuildDataReader(sqlCon); //get column data DataTable schemaTable = dr.GetSchemaTable(); if (schemaTable != null){
for (int i = 0; i < schemaTable.Rows.Count; i++){
DataRow dataRow = schemaTable.Rowsdt.Columns.Add(dc);
}
}
while (!bw.CancellationPending && dr.Read()) //this will loop through rows unless cancel is clicked{
try{
DataRow drow = dt.NewRow(); for (int i = 0; i < dr.FieldCount; i++)drow
= dr.GetValue(i);
dt.Rows.Add(drow);
rowCount++;
totalRowCount++;
}
catch (Exception ex){
throw ex;}
if (rowCount == 5000){
rowCount = 0;
bw.ReportProgress(0);
}
}
}
For now the ReportProgress() just adds 20 to the progressBar value every 5000 rows.
private
void ReportProgress(object sender, ProgressChangedEventArgs e){
progressBar1.Value += 20;
lblCount.Text = totalRowCount.ToString();
if(progressBar1.Value == 200)progressBar1.Value =0;
}
if the cancel button is clicked CancelAsync() is called.
private void btnCancel_Click(object sender, EventArgs e)
{
this.backgroundWorker1.CancelAsync();}
After background worker method has finished the background worker completed method is called.
private
void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e){
if (e.Error != null){
string msg = String.Format("An error occurred :{0}", e.Error.Message); MessageBox.Show(msg);}
else{
dgvMyData.DataSource = dt;
lblCount.Text = totalRowCount.ToString();
totalRowCount = 0;
rowCount = 0;
this.tssEnd.Text = System.DateTime.Now.ToLongTimeString();}
dr.Close();
progressBar1.Visible =
false;}
variables used.
SqlConnection
sqlCon; DataTable dt = new DataTable(); SqlDataReader dr; int rowCount = 0; int totalRowCount = 0;databuilder Class, that has the dataReader return method BuildDataReader()
static
class DataBuilder{
public static SqlDataReader BuildDataReader(SqlConnection sc){
SqlCommand comm = new SqlCommand();comm.CommandText =
"Select top 4000 account_no, industrial_class from Agreements";comm.Connection = sc;
SqlDataReader dr = comm.ExecuteReader(); return dr;}
}
This is most of the code, so this works fine the first time but for some reason after that is runs very slowly(but still works).
Thanks for you helps.
crussting
Check to see did you closing dataReader when you finish reading.
Post the source for reading data if posible to see what is going on.