Executing SQL query on Windows Form using Background Worker

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

Visual C#14

Using DataReader is not ideal but if you want to have a progress maybe it is. DataReader reads the records one by one from database, instead of DataAdapter that gets all of them.
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.

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 datareader

dr = 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.Rows Idea ;

string columnName = (string)dataRow["ColumnName"];

Type colType = (Type)dataRow["DataType"];

DataColumn dc = new DataColumn(columnName, colType);

dt.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 Idea = 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.