how to update data adapters?

[Michael]

Hi,

I have a large table. Normally, the user will need to see only the data
from the past two years, but sometimes, they will need to go back further.

I have a form with a datagrid, a DateTimePicker and a command button.
What I'd like is when the user selects a new date and clicks the button,
the grid would display data from the value in the datapicker through today.

I have it working, but only if I select everything in the data adapter,
then I fill a dataset with all the records (this takes 20-30 seconds),
then I create a dataview that is the datasource for the datagrid.

When the user picks a new date, I get the value and use the dataview's
rowfilter to update the range displayed in the datagrid.

This works well, but it is slow and I read here that it is best to work
with small sets of data and then update when necessary.

There must be a better way to somehow update the data adapter and then
update the dataset, dataview and datagrid but I can't seem to get it.

Can someone please give me some ideas to test out?

Thanks

Michael

[W.G. Ryan eMVP]

Re: how to update data adapters?

Micheal:

When the user selects a new date range, are you requerying for data that you
already have? If so, you are eating up a lot of resources for no benefit.
So if the date range is smaller for instance, or includes data that you
already have, you can just change your SQL Statement to get the new data
that you want and then just fill the existing datatable. Each time, you can
just use the Adapter to fill rows that you don't have. Since you are
binding to a dataview, you can filter there. So if you had rows 1-10000 and
needed 5000-15000.. .you could just querty the db for 10001 to 15000 filling
the datatable that's already filled, This will give you a table with 15000
records. Now in your grid, you can specify the rowfilter of the dataview to
show only what you want so you don't have to requery. If for instance the
next selection was rows 1-3000 you wouldn't even need to go to the db again.

Does this sound like a viable solution?

--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
"Michael" <nocrap@aol.com > wrote in message
news:eQBvxzfaFH A.604@TK2MSFTNG P10.phx.gbl...[color=blue]
> Hi,
>
> I have a large table. Normally, the user will need to see only the data
> from the past two years, but sometimes, they will need to go back further.
>
> I have a form with a datagrid, a DateTimePicker and a command button.
> What I'd like is when the user selects a new date and clicks the button,
> the grid would display data from the value in the datapicker through
> today.
>
> I have it working, but only if I select everything in the data adapter,
> then I fill a dataset with all the records (this takes 20-30 seconds),
> then I create a dataview that is the datasource for the datagrid.
>
> When the user picks a new date, I get the value and use the dataview's
> rowfilter to update the range displayed in the datagrid.
>
> This works well, but it is slow and I read here that it is best to work
> with small sets of data and then update when necessary.
>
> There must be a better way to somehow update the data adapter and then
> update the dataset, dataview and datagrid but I can't seem to get it.
>
> Can someone please give me some ideas to test out?
>
> Thanks
>
> Michael[/color]

[Cor Ligthert]

Re: how to update data adapters?

Michael,

It are users so you can not force them to do something except if you tell
that you will not return more than let say a page full of data from your
datagrid let say 30 rows.

You can do that using the Select Top with a Where clause

http://msdn.microsoft.com/library/de...a-ses_9sfo.asp

When you can than use the data from your last selected row for the next
Where, than you can do paging through your database using buttons by storing
the proper data for that (by instance the data from the first row everytime)

Another method when you cannot get the exact starting points is of course
much easier by doing this paging date by date.

Just some idea's I hope they help,

Cor

[Cor Ligthert]

Re: how to update data adapters?

Bill,
[color=blue]
>
> Does this sound like a viable solution?
>
> --[/color]

Bill,

I was reading full interest your solutions. However I see some problems. How
do you recoginize the gabs. If you give a "when" it will load all the rows
newly so you have to merge those. I don't think that it than will be a
solution however creates more overhead.

However, the biggest disadvantage from your solution is for me, that you
don't know if the already retrieved data in the datatable is complete.

Or do I oversee something?

Cor

[Michael]

Re: how to update data adapters?

Hi Ryan,

I like your solution, but I'm not sure how to implement 'changing you
sql command' part. I'm not sure how to use the data adapter and dataset
to update what is being displayed in the datagrid.

I thought I'd have to requery everything with the new ranges. You mean
I can add more rows to what is already in the dataset?

This is what I am doing now...
.....
sqlConn = New sqlConnection(" ...")
sqlCmd = New SqlCommand("Sel ect * from SALES where date>'2002/1/1'",
sqlConn)
daSales = New SqlDataAdapter( sqlCmd)
daSales.Fill(ds , "SALES")
dvSales = New DataView(ds.Tab les("SALES"))
dgSales.DataSou rce = dvSales
.....

'User selects a new date range below
d = DateTimePicker1 .Value.Date 'gets date user selected
s = CStr(d)
daSales.SelectC ommand.CommandT ext = "select * from SALES where date > " & s
daSales.Fill(ds , "SALES")
dvSales = New DataView(ds.Tab les("SALES"))
dgSales.DataSou rce = dvSales

'Note this doesn't work :(
What does work is having this initial data adapter query ALL the rows,
then later use the dataview's rowfilter to narrow the range. The only
problem is that initial query of gettting ALL the rows can take 20-30sec
and it will only get worse as they enter more data. I thought it best
to limit the default to 2yrs of data and then if needed, I can requery
and get more rows, but I am not sure how to do this.

Thanks for you idea.

Michael


W.G. Ryan eMVP wrote:[color=blue]
> Micheal:
>
> When the user selects a new date range, are you requerying for data that you
> already have? If so, you are eating up a lot of resources for no benefit.
> So if the date range is smaller for instance, or includes data that you
> already have, you can just change your SQL Statement to get the new data
> that you want and then just fill the existing datatable. Each time, you can
> just use the Adapter to fill rows that you don't have. Since you are
> binding to a dataview, you can filter there. So if you had rows 1-10000 and
> needed 5000-15000.. .you could just querty the db for 10001 to 15000 filling
> the datatable that's already filled, This will give you a table with 15000
> records. Now in your grid, you can specify the rowfilter of the dataview to
> show only what you want so you don't have to requery. If for instance the
> next selection was rows 1-3000 you wouldn't even need to go to the db again.
>
> Does this sound like a viable solution?
>[/color]

[Michael]

Re: how to update data adapters?

Hi Cor,

Yes, that gives me a few ideas to try out. My only problem is I am not
sure how to re-query a dataset. I thought I would update the sqlcommand
in the data adapter and then refill the dataset. I tried this but the
grid doesn't update.

Am I missing some refresh() method somewhere?

If someone could please show me the steps (data adapter -> dataset ->
dataview -> datagrid) I'd really appreciate it.

What I do now is: (in the form load event)
1) define a data adapter, where clause with the last two years data
2) data adapter fill method fills a dataset
3) create a dataview using the above dataset
4) have the datagrid's datasource set to the dataview above

This works, but I don't know how to update (or re-query) the rows I need

Thanks

Richard

Cor Ligthert wrote:
[color=blue]
> Michael,
>
> It are users so you can not force them to do something except if you tell
> that you will not return more than let say a page full of data from your
> datagrid let say 30 rows.
>
> You can do that using the Select Top with a Where clause
>
> http://msdn.microsoft.com/library/de...a-ses_9sfo.asp
>
> When you can than use the data from your last selected row for the next
> Where, than you can do paging through your database using buttons by storing
> the proper data for that (by instance the data from the first row everytime)
>
> Another method when you cannot get the exact starting points is of course
> much easier by doing this paging date by date.
>
> Just some idea's I hope they help,
>
> Cor
>
>[/color]

[Cor Ligthert]

Re: how to update data adapters?

Michael,
[color=blue]
> What I do now is: (in the form load event)[/color]

This sound strange for me, I assumed that a datatimepicker event did do
this.
And than clear the dataset everytime in advance.

And than another event with a button to let the user page back in future
until now.

Cor

[Michael]

Re: how to update data adapters?

Hi Cor,

Sorry for the mixup. I didn't want to cut and paste a lot of code.

I simplified my form so that it has just 3 controls:
- DateTimePicker (used to specify range of rows to show in datagrid)
- Button (when clicked, I'd like the datagrid rows updated)
- DataGrid (initially set with 2 years rows)

At the top of the form (below the 'Windows Form Designer generated
code'), I declared some public variables:

Dim sqlConn As New SqlConnection
Dim daSales As New SqlDataAdapter
Dim ds As New DataSet
Dim sqlCmd As New SqlCommand
Dim dvSales As New DataView

Then in the form load event, I defined the SqlConnection, the Data
Adapter (with select choosing the last two year's rows), the DataSet,
the DataView and then I set the DataGrid's source to the DataView.

sqlConn = New SqlConnection(" server=...")
sqlCmd = New SqlCommand("Sel ect * from SALES where date>'2003/1/1'",
sqlConn)
daSales = New SqlDataAdapter( sqlCmd)
daSales.Fill(ds , "SALES")
dvSales = New DataView(ds.Tab les("SALES"))
dgSales.DataSou rce = dvSales


Later in the code, I have a button_click event that gets the value of a
DateTimePicker control that the user changes. It is here that I am
having trouble re-querying. If I use a DataView.rowfil ter in this
event, it works fine, but I must have the data adapter select ALL (that
takes a long time) defined in the form load event.

' Button click event

Dim d As Date
Dim s As String
Dim strSql As String

d = DateTimePicker1 .Value.Date
s = CStr(d)

' The commented lines below don't work?
'strSql = daSales.SelectC ommand.CommandT ext
'daSales.Select Command.Command Text = "select * from SALES where date > " & s
'daSales.Fill(d s, "SALES")
'dvSales = New DataView(ds.Tab les("SALES"))
'dgSales.DataSo urce = dvSales

dvSales.RowFilt er = "date> '" & s & "'" '<- this works


I hope this makes it clearer what I am trying to do.

Richard


Cor Ligthert wrote:
[color=blue]
> Michael,
>
>[color=green]
>>What I do now is: (in the form load event)[/color]
>
> This sound strange for me, I assumed that a datatimepicker event did do
> this.
> And than clear the dataset everytime in advance.
>
> And than another event with a button to let the user page back in future
> until now.
>
> Cor[/color]

[Cor Ligthert]

Re: how to update data adapters?

Michael,

I made a sample how I would probably do your problem. I have not completly
tested yet however I thought that it was working as I expected and otherwise
it gives a good idea how you could do it.

\\\needs a datetimepicker, a button and a datagrid and the NorthWind
database
Private Sub Form1_Load(ByVa l sender As System.Object, _
ByVal e As System.EventArg s) Handles MyBase.Load
Me.DateTimePick er1.Value = New DateTime(1996, 7, 1)
End Sub
Private Sub DateTimePicker1 _ValueChanged(B yVal sender _
As System.Object, ByVal e As System.EventArg s) Handles
DateTimePicker1 .ValueChanged
FillGrid(DateTi mePicker1.Value )
End Sub
Private Sub Button1_Click(B yVal sender As System.Object, _
ByVal e As System.EventArg s) Handles Button1.Click
FillGrid(Nothin g)
End Sub
Private Sub FillGrid(ByVal dt As DateTime)
Dim conn As New SqlClient.SqlCo nnection("Serve r=(Local);" & _
"DataBase=North wind; Integrated Security=SSPI")
Static lastDate As DateTime = New DateTime(1996, 7, 1)
Dim ds As New DataSet
If dt = Nothing Then
dt = lastDate.AddDay s(1)
End If
Dim da As New SqlClient.SqlDa taAdapter _
("Select * from Orders Where orderdate = @orderdate", conn)
da.SelectComman d.Parameters.Ad d("@orderdate ", CDate(dt))
da.Fill(ds)
Do While ds.Tables(0).Ro ws.Count = 0
dt = dt.AddDays(1)
da.SelectComman d.Parameters("@ orderdate").Val ue = dt
da.Fill(ds)
If dt.Equals(New DateTime(1998, 6, 30)) Then
Exit Do
End If
Loop
DataGrid1.DataS ource = Nothing
DataGrid1.DataS ource = ds.Tables(0)
lastDate = dt
End Sub
///
I hope this helps a little bit?

Cor

[Michael]

Re: how to update data adapters?

Hi Cor,

Thanks so much for taking the time to show me how to do it. This helps
A LOT!

Your example also showed me how to do other things on my list such at
public functions and parameter passing to SQL select statements.

I copied your code into a new example and it finally changes the grid!
I will slowly go over everything and this will definitely teach me what
I need to know.

Thanks again!

Michael


Cor Ligthert wrote:[color=blue]
> Michael,
>
> I made a sample how I would probably do your problem. I have not completly
> tested yet however I thought that it was working as I expected and otherwise
> it gives a good idea how you could do it.
>
> \\\needs a datetimepicker, a button and a datagrid and the NorthWind
> database
> Private Sub Form1_Load(ByVa l sender As System.Object, _
> ByVal e As System.EventArg s) Handles MyBase.Load
> Me.DateTimePick er1.Value = New DateTime(1996, 7, 1)
> End Sub
> Private Sub DateTimePicker1 _ValueChanged(B yVal sender _
> As System.Object, ByVal e As System.EventArg s) Handles
> DateTimePicker1 .ValueChanged
> FillGrid(DateTi mePicker1.Value )
> End Sub
> Private Sub Button1_Click(B yVal sender As System.Object, _
> ByVal e As System.EventArg s) Handles Button1.Click
> FillGrid(Nothin g)
> End Sub
> Private Sub FillGrid(ByVal dt As DateTime)
> Dim conn As New SqlClient.SqlCo nnection("Serve r=(Local);" & _
> "DataBase=North wind; Integrated Security=SSPI")
> Static lastDate As DateTime = New DateTime(1996, 7, 1)
> Dim ds As New DataSet
> If dt = Nothing Then
> dt = lastDate.AddDay s(1)
> End If
> Dim da As New SqlClient.SqlDa taAdapter _
> ("Select * from Orders Where orderdate = @orderdate", conn)
> da.SelectComman d.Parameters.Ad d("@orderdate ", CDate(dt))
> da.Fill(ds)
> Do While ds.Tables(0).Ro ws.Count = 0
> dt = dt.AddDays(1)
> da.SelectComman d.Parameters("@ orderdate").Val ue = dt
> da.Fill(ds)
> If dt.Equals(New DateTime(1998, 6, 30)) Then
> Exit Do
> End If
> Loop
> DataGrid1.DataS ource = Nothing
> DataGrid1.DataS ource = ds.Tables(0)
> lastDate = dt
> End Sub
> ///
> I hope this helps a little bit?
>
> Cor
>
>[/color]