dataadapter.fill

[MDB]

Hello All,

I have a data grid that I fill using a dataset. The results of the query
has around 15 columns and 500 rows (and growing). The reason I am using the
datagrid is so the end users can auto sort and page so I would like to keep
using the datagrid. My problem is that when I call DataAdapter.Fill, it
takes a very long time and is even causing the web page to time out. Does
anyone know why the fill is taking so long (15-20 sec) or have any better
suggestions to display the records?

SQL Sent To Function
Select user_id, password, emailaddress, firstname, lastname, address, city,
state, zip, phonenumber, marketing, admin, status, last_order from users
where status = 'A'

Primary Key on user_id
Index on Status

public DataSet LoadDataset(string sql, string tableName, DataSet ds)
{
SqlCeCommand dc = new SqlCeCommand(sql, conn, trans);

if (ds == null) {ds = new DataSet();}
if(ds.Tables[tableName]!=null){ds.Tables[tableName].Clear();}
if (tableName == null) {tableName = "Table";}
SqlCeDataAdapter da = new SqlCeDataAdapter(dc);
da.Fill(ds, tableName);

da.Dispose();
dc.Dispose();

return ds;
}

 

[tomodati]

Hi,
First of all, searching by text fields is very very slow and not efficient.
If You have to make selection on status ( which could be also numeric
field ) do this way, searching by numeric value is faster.


Your Code:

SqlCeCommand dc = new SqlCeCommand(sql, conn, trans);

if (ds == null) {ds = new DataSet();}
if(ds.Tables[tableName]!=null){ds.Tables[tableName].Clear();}
if (tableName == null) {tableName = "Table";}
SqlCeDataAdapter da = new SqlCeDataAdapter(dc);
da.Fill(ds, tableName);

try do this way:
ds = null;
ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(dt);
ds.Tables.add(dt)

this code is good when You use Dataset as a storage of one table, there is
no problem to create new table and fill it by dataadapter;-)

Adas

Hello All,

I have a data grid that I fill using a dataset. The results of the query
has around 15 columns and 500 rows (and growing). The reason I am using
the datagrid is so the end users can auto sort and page so I would like to
keep using the datagrid. My problem is that when I call DataAdapter.Fill,
it takes a very long time and is even causing the web page to time out.
Does anyone know why the fill is taking so long (15-20 sec) or have any
better suggestions to display the records?

SQL Sent To Function
Select user_id, password, emailaddress, firstname, lastname, address,
city, state, zip, phonenumber, marketing, admin, status, last_order from
users where status = 'A'

Primary Key on user_id
Index on Status

public DataSet LoadDataset(string sql, string tableName, DataSet ds)
{
SqlCeCommand dc = new SqlCeCommand(sql, conn, trans);

if (ds == null) {ds = new DataSet();}
if(ds.Tables[tableName]!=null){ds.Tables[tableName].Clear();}
if (tableName == null) {tableName = "Table";}
SqlCeDataAdapter da = new SqlCeDataAdapter(dc);
da.Fill(ds, tableName);

da.Dispose();
dc.Dispose();

return ds;
}

 

[MDB]

Thank you for your response. I will try switching the status to a numeric
value and using a datatable.

Hi,
First of all, searching by text fields is very very slow and not
efficient. If You have to make selection on status ( which could be also
numeric field ) do this way, searching by numeric value is faster.


Your Code:

SqlCeCommand dc = new SqlCeCommand(sql, conn, trans);

if (ds == null) {ds = new DataSet();}
if(ds.Tables[tableName]!=null){ds.Tables[tableName].Clear();}
if (tableName == null) {tableName = "Table";}
SqlCeDataAdapter da = new SqlCeDataAdapter(dc);
da.Fill(ds, tableName);

try do this way:
ds = null;
ds = new DataSet();
DataTable dt = new DataTable();
da.Fill(dt);
ds.Tables.add(dt)

this code is good when You use Dataset as a storage of one table, there is
no problem to create new table and fill it by dataadapter;-)

Adas

Hello All,

I have a data grid that I fill using a dataset. The results of the query
has around 15 columns and 500 rows (and growing). The reason I am using
the datagrid is so the end users can auto sort and page so I would like
to keep using the datagrid. My problem is that when I call
DataAdapter.Fill, it takes a very long time and is even causing the web
page to time out. Does anyone know why the fill is taking so long (15-20
sec) or have any better suggestions to display the records?

SQL Sent To Function
Select user_id, password, emailaddress, firstname, lastname, address,
city, state, zip, phonenumber, marketing, admin, status, last_order from
users where status = 'A'

Primary Key on user_id
Index on Status

public DataSet LoadDataset(string sql, string tableName, DataSet ds)
{
SqlCeCommand dc = new SqlCeCommand(sql, conn, trans);

if (ds == null) {ds = new DataSet();}
if(ds.Tables[tableName]!=null){ds.Tables[tableName].Clear();}
if (tableName == null) {tableName = "Table";}
SqlCeDataAdapter da = new SqlCeDataAdapter(dc);
da.Fill(ds, tableName);

da.Dispose();
dc.Dispose();

return ds;
}