having progress bar while sql script gathers data

[budjo]

hello .. i have a problem .. I want to retrive data from a MS SQL server using stored procedure .. but the data is taking a long time to be fetched .. and on my program the program seems to hang waiting for the records to be returned back ..
is there a way for c# to run some other process like a progress bar or a animated gif ..while waiting for the records to be fetched and how would i know if the records are ready ???

thanks in advance ..

 

[dalchri]

Hopefully you are using the DataAdapter.Fill method on a table. If so, just hook to the RowChanged event of the DataTable. This will fire each time that a row is added from SQL as the records are read. You will need to query the SQL table up front for the total number of records. Then the ratio of DataTable.Rows.Count to your total record count will be the percentage complete.

MyDataTable.RowUpdated += new RowUpdatedEventHandler(this.RowUpdated);
MyDataAdapter.Fill(MyDataTable);

void RowUpdated(I forget what goes here) {
//Your total records will have to be at the class level
float fltPercent = (float) e.Row.Table.Count / m_intTotalRecords;
}

You may wish to start this on a separate thread creating a new System.Threading.Thread and passing it a void method that runs the download.

 

[budjo]

tnx for the reply .. but dalchri ..I used a stored procedure to get the records .. and a SQLDataReader to read the records as a result of a ExecuteReader command ... can it still be possible ??

 

[obislavu]

I think RowUpdated is not working when Fill() is called but RowChanged.
Here is an working example to populate a grid with data returned by a select typed in txtSQL textbox and a progress bar related to.

private int nTotal = 87106; // no total of records got at run time
private System.Windows.Forms.ProgressBar progressBar2;
private System.Windows.Forms.TextBox txtSQL;

private void btnSQLgo_Click(object sender, System.EventArgs e)
{
	System.Windows.Forms.Application.DoEvents();
	DataSet ds = new DataSet("ds");
	DataTable dt2 = new DataTable("DT2");
	ds.Tables.Add(dt2);
	dt2.RowChanged += new DataRowChangeEventHandler(RowUpdated);
	this.progressBar2.Visible = true;
	this.progressBar2.Minimum = 0;
	this.progressBar2.Maximum = nTotal;
	this.progressBar2.Value = 0;
	this.progressBar2.Step = 1000; //each 1000 records 

	string sSQLConnectionString = "";
	nTotal = GetCount(sSQLConnectionString, "select count(*) from ...");
	FillDataSet(ref dt2,txtSQL.Text.Trim(),sSQLConnectionString);
	dataGrid1.SetDataBinding(ds, "DT2");
	dataGrid1.CaptionText = dt2.Rows.Count + " Record(s) - " + txtSQL.Text;
	dataGrid1.Show();
	
}

private void RowUpdated (object sender, DataRowChangeEventArgs e)
{

	if (e.Action == System.Data.DataRowAction.Add)
	{
		if (e.Row.Table.Rows.Count > this.progressBar2.Step -1  && (e.Row.Table.Rows.Count%this.progressBar2.Step) == 0 || e.Row.Table.Rows.Count >= nTotal )
		{
			this.progressBar2.PerformStep();
			Application.DoEvents();

		}
	}
}

private int GetCount(string strCon, string strSelectCount)
{

	try
	{
		SqlConnection myConnection = new SqlConnection(strCon);
		myConnection.Open();
		SqlCommand CleanupCommand = new SqlCommand(strSelectCount);
		CleanupCommand.Connection = myConnection;
		CleanupCommand.ExecuteNonQuery();
		CleanupCommand = null;
		myConnection.Close();
		myConnection.Dispose();
	}
	catch(Exception e)
	{						
	  //		
	}						
	//
}
public void FillDataSet(ref DataTable dt, string sqlStatement, string sConnString)
{
	SqlConnection sConn			= null;
	SqlCommand WorkingCommand		= null;
	SqlDataAdapter WorkingDataAdapter	= null;
	try
	{
		sConn			= new SqlConnection(sConnString);
		WorkingCommand	= new SqlCommand();
		WorkingDataAdapter	= new SqlDataAdapter();
		sConn.Open();
		WorkingCommand.Connection			= sConn;
		WorkingCommand.CommandText = sqlStatement;
		WorkingDataAdapter.SelectCommand = WorkingCommand;
		WorkingDataAdapter.Fill(dt);

	}
	catch(Exception e)
	{						
		string sErr=e.GetType() + e.Message;
	}						
	finally 
	{
		if (sConn!=null)
		{
			sConn.Close();
			sConn.Dispose();
		}
	}

}
obislavu