Sending Data from Sql Data reader to grid and Excel sheet as a (Snapshot) without looping Statement

[Mohammad Abdullah]

Hi ,

I have a windows C#.Net application , this application want to send a data that get it from Sql data reader into a grid but without a looping e.g.:
int i=1;
Region.Parameters["@iRegion"].Value=i;
SqlDataReader Dr = Region.ExecuteReader();

//Loop process
//read the current record
//Put each field on the appropriate grid cell
//go to next record on the data reader
//End loop

I''m Using Sql server 2000 as a database and suppose the data reader gets about 10,000 records this is not accepted for the user because it will do more response time, so I want a method that sends the data to :
1-A grid As A snapshot without loop operations.
2-IF the looping is Must I heared that there is a methods called (Beginupdate,Endupdate) How I use this methods , explain by peace of C# code.

Note : There is another data bainding called(SqlDataAdapter) how I can use it to set the grid cells without Looping statement.

Thanks

[Mohammad Abdullah]

I forgit something:
I''m Using (Syncfusion.Windows.forms.Grid.Gridcontrol).

Thanks

>Hi ,
>
>I have a windows C#.Net application , this application want to send a data that get it from Sql data reader into a grid but without a looping e.g.:
>int i=1;
>Region.Parameters["@iRegion"].Value=i;
>SqlDataReader Dr = Region.ExecuteReader();
>
>//Loop process
>//read the current record
>//Put each field on the appropriate grid cell
>//go to next record on the data reader
>//End loop
>
>I''m Using Sql server 2000 as a database and suppose the data reader gets about 10,000 records this is not accepted for the user because it will do more response time, so I want a method that sends the data to :
>1-A grid As A snapshot without loop operations.
>2-IF the looping is Must I heared that there is a methods called (Beginupdate,Endupdate) How I use this methods , explain by peace of C# code.
>
>Note : There is another data bainding called(SqlDataAdapter) how I can use it to set the grid cells without Looping statement.
>
>Thanks
>

[Mohammad Abdullah]

Here Is A sample from the code that I have:
private private Syncfusion.Windows.Forms.Grid.GridControl gFind;

gFind.RowCount = 0;
SqlDataReader dr = ListBldg.ExecuteReader ();

if (dr.HasRows )
{
while (dr.Read ())
{
gFind.RowCount ++;
int i = gFind.RowCount;
int j = 1;
gFind[i,j++].Text = dr["BldgName"].ToString ();
gFind[i,j++].Text = dr["BldgType"].ToString ();
gFind[i,j++].Text = dr["RegionName"].ToString ();//dr["RegionType"].ToString () + " " + dr["RegionName"].ToString ();
gFind[i,j++].Text = Util.FmtLoc (Convert.ToDouble(dr["Lon"].ToString ()));
gFind[i,j++].Text = Util.FmtLoc (Convert.ToDouble(dr["Lat"].ToString ()));
gFind[i,j++].Text = dr["DeptName"].ToString ();
gFind[i,j++].Text = dr["Shifts"].ToString ();
gFind[i,j++].Text = dr["OutDept"].ToString ();
//gFind[i,j++].Text = dr["Notes"].ToString ();
gFind[i,j++].Text = dr["Updby"].ToString ();
gFind[i,j++].Text = dr["Updat"].ToString ();
gFind[i,j++].Text = dr["BldgNo"].ToString ();
}
}
dr.Close ();

As you see I''m Using SqldataReader that should go for each record then get its data and put it on the grid , which is not better for future performance issue.

[Administrator]

If you want to use a SqlDataReader, then you will have loop through things to populate the grid as you have to loop through the SqlDataReader to get the data sequentially.

But you can improve performance by a factor of 5 to 10 times by not using an indexer on the gridcontrol to set the values into the grid. Instead, you can access the GridControl.Data object directly, and this by passes a lot of code that raises mauy events (slowing things down).

Below is a little button handler snippet showing putting randon values in grid cells using this technique. You would user you SqlReader to provide the values at that point.

private void button1_Click(object sender, System.EventArgs e)
{
	int nRows = 8500;
	int nCols = 25;
	Random r = new Random();
	this.Cursor = Cursors.WaitCursor;
	int ticks = Environment.TickCount;
	this.gridControl1.BeginUpdate();
	this.gridControl1.RowCount = nRows;
	this.gridControl1.ColCount = nCols;
	GridData data = this.gridControl1.Data;
	for(int i = 1; i <= nRows; ++i)
	{
		for(int j = 1; j <= nCols; ++j)
		{
			GridStyleInfo style = new GridStyleInfo();
			style.CellValue = r.Next(100000);
			data[i, j] = style.Store;
		}
	}
	this.gridControl1.EndUpdate();
	this.gridControl1.Refresh();
	this.Cursor = Cursors.Default;
	this.label1.Text = string.Format("{0} ticks", Environment.TickCount - ticks);
}

[Mohammad Abdullah]

Thanks ,
Can you rewrite your code based on the my code on my previous message (include it with your code).
Next ,If I want to use (SqlDataAdapter) how I set it to the grid data source , give us an example.

[Administrator]

GridData data = gFind.Data;
int i = gFind.RowCount;
int j = 1;


GridStyleInfo style = new GridStyleInfo();
style.CellValue = dr["BldgName"].ToString ();
data[i, j++] = style.Store;

style = new GridStyleInfo();
style.CellValue = dr["BldgType"].ToString ();
data[i, j++] = style.Store;

style = new GridStyleInfo();
style.CellValue = dr["RegionName"].ToString ();
data[i, j++] = style.Store;

style = new GridStyleInfo();
style.CellValue =  Util.FmtLoc (Convert.ToDouble(dr["Lon"].ToString ()));
data[i, j++] = style.Store;

//  and so ...

If you are loading a DataTable using a dataadapter, then you can use the PopulateValues method to move the data into a GridControl. (If you are using a GridDataBoundGrid or a GridGroupingControl, you can just set the datatable as the grid.DataSource property.)

// dt is a populated DataTable object
this.gridControl1.RowCount = dt.Rows.Count; 
this.gridControl1.ColCount = dt.Columns.Count; 
this.gridControl1.Model.PopulateValues(GridRangeInfo.Cells(1, 1, dt.Rows.Count, dt.Columns.Count), dt); 
this.gridControl1.Refresh();// may not be needed...

[Mohammad Abdullah]

Thanks Clay Burch for your help , but still there is some small missing points :

1-If I decide to use Data reader ,the loop on my code like this:

while (dr.Read ())
{
gFind.RowCount ++;
int i = gFind.RowCount;
int j = 1;
gFind[i,j++].Text = dr["BldgName"].ToString ();
gFind[i,j++].Text = dr["BldgType"].ToString ();
gFind[i,j++].Text = dr["RegionName"].ToString ();//dr["RegionType"].ToString () + " " + dr["RegionName"].ToString ();

etc...
}
My point here where do I put (BeginUpdate(),EndUpdate())method for (gFind)is it inside the loop or outside it? Plus On your code you didn''t specify exactly the loop process method on your second message replay is it (for loop) or (while) and there working nature with dr.Read()?

2-If I choose (dataadapter), by code how I specify the fields position on the grid e.g.: suppose field name (bname) the index of it in the recordset is number (1) but I want to put it on position number 7 on the grid(gFind) , how I can do this ?

Thanks alot

[Mohammad Abdullah]

On your example for using dataadapter you write:
this.gridControl1.RowCount , (this)? is it grid (gfind) or Griddatabound or gridListcontrol?
and where is the statement responsible for setting the data source to the data adapter based on the following code :

SqlDataAdapter da = new SqlDataAdapter(ListBldg);
DataSet result = new DataSet();
da.Fill(result,"test");
///---> Here I want to ask the grid that his data source is (result), then setting all the fields to the customised grid positions on (gFind).

Thanks again

[Administrator]

What is the type of your gFind?

Is is a GridControl, or a GridDataBoundGrid, or a GridListControl, or a GridGroupingControl?

If it is one of the last three and "test" is the name of the DataTable you want to see in gFind, you write:

SqlDataAdapter da = new SqlDataAdapter(ListBldg);
DataSet result = new DataSet();
da.Fill(result,"test");

gFind.DataSource = result.Tables["test"];

If gFind is a GridControl, you write:

SqlDataAdapter da = new SqlDataAdapter(ListBldg);
DataSet result = new DataSet();
da.Fill(result,"test");

DataTable dt = result.Tables["test"];
gFind.RowCount = dt.Rows.Count; 
gFind.ColCount = dt.Columns.Count; 
gFind.Model.PopulateValues(GridRangeInfo.Cells(1, 1, dt.Rows.Count, dt.Columns.Count), dt); 
gFind.Refresh();// may not be needed...

[Mohammad Abdullah]

gFind is a grid control it gives me error (complile) on this line:
gFind.Model.PopulateValues(GridRangeInfo.Cells(1, 1, dt.Rows.Count, dt.Columns.Count), dt);
the error said ''Syncfusion.Windows.forms.grid.grid.Model'' doesnt contain for (PopulateValues).

All the Syncfusion.Grid Runtime version is : v1.0.3705 and version is : 2.1.0.9.

[Mohammad Abdullah]

Forgit About The error everey thing now is fine but I want small thing your example populate all the dataset columns, I dont want all the columns inside the grid control e.g.:
I want only: bname , bno ,lat , lon,enterby,enterat.

How I set the grid to only this fields.

[Mohammad Abdullah]

I did like this:

gFind.RowCount = 0;
//cnBldg.ConnectionString = Util.ConStr;
//cnBldg.Open ();
SqlDataAdapter da = new SqlDataAdapter(ListBldg);
DataSet result = new DataSet();
da.Fill(result,"test");
DataTable dt = result.Tables["test"];

int j = 1;
gFind.Model.PopulateValues(GridRangeInfo.Cells(1,1,dt.Rows.Count,dt.Columns.Count),dt);
gFind[i,j++].Text =dc1.ToString();
gFind[i,j++].Text = dc2.ToString();
gFind[i,j++].Text=dc3.ToString();//dr["RegionType"].ToString () + " " + dr["RegionName"].ToString ();
gFind[i,j++].Text = dc4.ToString();
gFind[i,j++].Text = dc5.ToString();
gFind[i,j++].Text = dc6.ToString();
gFind.RowCount=dt.Rows.Count;
gFind.ColCount = dt.Columns.Count;
System.Data.DataColumn dc1;
System.Data.DataColumn dc2;
System.Data.DataColumn dc3;
System.Data.DataColumn dc4;
System.Data.DataColumn dc5;
System.Data.DataColumn dc6;
int i = gFind.RowCount;
dc1 = result.Tables["test"].Columns["BldgName"];
dc2 = result.Tables["test"].Columns["BldgType"];
dc3 = result.Tables["test"].Columns["RegionName"];
dc4 = result.Tables["test"].Columns["Lon"];
dc5 = result.Tables["test"].Columns["Lat"];
dc6 = result.Tables["test"].Columns["DeptName"];
gFind.Refresh();

But still give me all the columns I want only (6) of them.

[Mohammad Abdullah]

Do you have An Idea about answering this question:
http://www.syncfusion.com/Support/Forums/message.aspx?MessageID=26872

I''m using IWoksheet (myWorkBook.WorkSheets[0])object.

[Administrator]

Not really. If you want to use a DataReader, then you will have to loop through things sequentially just like in the grid.

[Administrator]

Ok nevermind , lets continue with the grid control your example populate all the dataset columns, I dont want all the columns inside the grid control e.g.:
I want only: bname , bno ,lat , lon,enterby,enterat.

How I set the grid to only this fields.

[Mohammad Abdullah]

Ok nevermind , lets continue with the grid control your example populate all the dataset columns, I dont want all the columns inside the grid control e.g.:
I want only: bname , bno ,lat , lon,enterby,enterat.

How I set the grid to only this fields.

[Administrator]

You can of course change your SQL query to retrieve exactly what you want to see.

If you are using a GridDataBoundGrid and setting its DataSource to a DataTable, then you can control exactly what columns are seen in the grid by adding GridBoundColumn objects to the gridDataBoundGrid1.GridBoundColumns collection. Make sure you set the MappingName property of the GridBoundColumn to the ColumnName of teh DataColumn from the DataTable. This is how the grid knows what column''s you are specifying. Other GridBoundColumn properties you may or may not want to set include HeaderText and StyleInfo. The StyleInfo property is what you use to set the style properties for a column. For example, setting gridBoundColumn1.Styleinfo.BackColor = Color.Red will set the back color of the column to red.