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]

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?

 

[Cor Ligthert]

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/default.asp?url=/library/en-us/tsqlref/ts_sa-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]

Bill,

Does this sound like a viable solution?

--

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]

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("Select * from SALES where date>'2002/1/1'",
sqlConn)
daSales = New SqlDataAdapter(sqlCmd)
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = dvSales
.....

'User selects a new date range below
d = DateTimePicker1.Value.Date 'gets date user selected
s = CStr(d)
daSales.SelectCommand.CommandText = "select * from SALES where date > " & s
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = 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

 

[Michael]

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]

Michael,

What I do now is: (in the form load event)

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]

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("Select * from SALES where date>'2003/1/1'",
sqlConn)
daSales = New SqlDataAdapter(sqlCmd)
daSales.Fill(ds, "SALES")
dvSales = New DataView(ds.Tables("SALES"))
dgSales.DataSource = 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.rowfilter 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.SelectCommand.CommandText
'daSales.SelectCommand.CommandText = "select * from SALES where date > " & s
'daSales.Fill(ds, "SALES")
'dvSales = New DataView(ds.Tables("SALES"))
'dgSales.DataSource = dvSales

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


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

Richard

 

[Cor Ligthert]

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(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles MyBase.Load
Me.DateTimePicker1.Value = New DateTime(1996, 7, 1)
End Sub
Private Sub DateTimePicker1_ValueChanged(ByVal sender _
As System.Object, ByVal e As System.EventArgs) Handles
DateTimePicker1.ValueChanged
FillGrid(DateTimePicker1.Value)
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles Button1.Click
FillGrid(Nothing)
End Sub
Private Sub FillGrid(ByVal dt As DateTime)
Dim conn As New SqlClient.SqlConnection("Server=(Local);" & _
"DataBase=Northwind; Integrated Security=SSPI")
Static lastDate As DateTime = New DateTime(1996, 7, 1)
Dim ds As New DataSet
If dt = Nothing Then
dt = lastDate.AddDays(1)
End If
Dim da As New SqlClient.SqlDataAdapter _
("Select * from Orders Where orderdate = @orderdate", conn)
da.SelectCommand.Parameters.Add("@orderdate", CDate(dt))
da.Fill(ds)
Do While ds.Tables(0).Rows.Count = 0
dt = dt.AddDays(1)
da.SelectCommand.Parameters("@orderdate").Value = dt
da.Fill(ds)
If dt.Equals(New DateTime(1998, 6, 30)) Then
Exit Do
End If
Loop
DataGrid1.DataSource = Nothing
DataGrid1.DataSource = ds.Tables(0)
lastDate = dt
End Sub
///
I hope this helps a little bit?

Cor

 

[Michael]

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