Databound DGV, detecting new records.

[jwcoleman87]

I have sub GetOrders which will basically refresh my datasource for my datagridview. It does this by simply querying the database and setting the datasource equal to the results of that query.

This causes weirdness when I would like to do something like figure out which records are new and which are old. In my mind there should be one datasource and it should be modified to be made accurate to real time, (not completely switched to another "new" datasource). If this was done the way I feel like it should work in my mind there would be no problem detecting which records were new. Unfortunately this way of refreshing data in a dgv is the only way I've learned thus far.

I've thought about copying all of the contents of the dgv into a dataset object, and then copying the new datasource to a different dataset object, and then comparing the two, but this seems clumsy.

I've also though about just taking a snapshot of the contents of the OrderID column, but this seems clumsy as well because the datagridview itself is not queryable.

Anyone already had to do this have some good tips on making this happen?

Again I'm just trying to determine which rows are new, not if there are new rows at all.

Private Sub GetOrders()
        Using db As New ProductionDataModelDataContext
            db.DeferredLoadingEnabled = False

            If dgvOrderQueue.SelectedRows.Count = 0 Then
                Dim orderqueue = From t In db.TTPartOrders _
                                 Join pi In db.TTPartInventories On t.PartNumber Equals pi.PartNumber _
                                 Join sku In db.SKUs On pi.PartNumber Equals sku.ManufacturerPart
                                 Where t.Closed = False _
                                 Select t.OrderID, t.UserID, t.PartNumber, sku.ProductType, sku.Description1, sku.StdCost, pi.Location

                dgvOrderQueue.DataSource = orderqueue
                dgvOrderQueue.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
            Else
                Dim selectedorder = dgvOrderQueue.SelectedRows(0).Index
                Dim count = dgvOrderQueue.Rows.Count
                Dim orderqueue = From t In db.TTPartOrders _
                                 Join pi In db.TTPartInventories On t.PartNumber Equals pi.PartNumber _
                                 Join sku In db.SKUs On pi.PartNumber Equals sku.ManufacturerPart
                                 Where t.Closed = False _
                                 Select t.OrderID, t.UserID, t.PartNumber, sku.ProductType, sku.Description1, sku.StdCost, pi.Location

                dgvOrderQueue.DataSource = orderqueue
                dgvOrderQueue.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
                If dgvOrderQueue.Rows.Count < count Then
                    Exit Sub
                Else
                    Dim printer As New ZBPrinter("10.101.2.241", 9100)
                    dgvOrderQueue.Rows(selectedorder).Selected = True
                End If

            End If

        End Using
    End Sub

 

[jwcoleman87]

   Private Sub GetOrders()
        Using db As New ProductionDataModelDataContext
            db.DeferredLoadingEnabled = False
            Dim printer As New ZBPrinter(My.Settings.PrinterIP, My.Settings.PrinterPort)
            If dgvOrderQueue.SelectedRows.Count = 0 Then 'this happens on first load or when all the orders are cleared out
                Dim orderqueue = From t In db.TTPartOrders _
                                 Join pi In db.TTPartInventories On t.PartNumber Equals pi.PartNumber _
                                 Join sku In db.SKUs On pi.PartNumber Equals sku.ManufacturerPart
                                 Where t.Closed = False _
                                 Select t.OrderID, t.UserID, t.PartNumber, sku.ProductType, sku.Description1, sku.StdCost, pi.Location

                dgvOrderQueue.DataSource = orderqueue
                dgvOrderQueue.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)

                'save results locally in an array, this will be used to determine any new orders
                NewOrders = orderqueue.ToArray()
                If PrintPickTicket = True Then
                    For Each row In NewOrders
                        MsgBox(row.ToString())
                        printer.PrintPickTicket(row.PartNumber.ToString().Trim(), row.Location.ToString().Trim(), row.ProductType.ToString().Trim(), row.UserID.ToString().Trim())
                    Next
                End If


            Else 'this happens when there are orders already in the table
                Dim selectedorder = dgvOrderQueue.SelectedRows(0).Index
                Dim count = dgvOrderQueue.Rows.Count
                Dim orderqueue = From t In db.TTPartOrders _
                                 Join pi In db.TTPartInventories On t.PartNumber Equals pi.PartNumber _
                                 Join sku In db.SKUs On pi.PartNumber Equals sku.ManufacturerPart
                                 Where t.Closed = False _
                                 Select t.OrderID, t.UserID, t.PartNumber, sku.ProductType, sku.Description1, sku.StdCost, pi.Location

                dgvOrderQueue.DataSource = orderqueue
                dgvOrderQueue.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.DisplayedCells)
                Dim theneworders = orderqueue.ToArray.Except(NewOrders)
                If PrintPickTicket = True Then
                    For Each r In theneworders
                        printer.PrintPickTicket(r.PartNumber.ToString().Trim(), r.Location.ToString().Trim(), r.ProductType.ToString().Trim(), r.UserID.ToString().Trim())
                    Next
                End If
                NewOrders = orderqueue.ToArray()

                'This conditional determines if the user cancelled or filled a part, therefore reducing the amount of data
                If dgvOrderQueue.Rows.Count < count Then
                    'we don't want to use a selectedorder that we removed from the list (will cause an exception)
                    Exit Sub
                Else
                    'set the selected order back to what it was before the refresh
                    dgvOrderQueue.Rows(selectedorder).Selected = True
                End If
            End If

        End Using
    End Sub

I was able to use this as a solution. I created a local array of the orders, then whenever I updated the datasource I used the except clause to create a new array which did not include any record which was in the last update, this left me with nothing but new records.
After doing this calculation I re set the local array to the most recent set of orders, and then when the function gets called again the process repeats.

Now for every new order the stock room clerk gets, he will have a pick ticket printed for him.

 

[jwcoleman87]

i need to rename some of those variables

 

[jmcilhinney]

The best way to achieve this is to design the database to support it in the first place. You can add a LastModifiedTime column to your table to store the time that the record was last modified and an IsActive column to indicate whether the record is consider active or inactive/deleted. This requires that you never actually delete a record but rather flag it as deleted in the database. Many people ascribe to that anyway, just because it means that you always know what data has been in your database at any time.

What you can do then is to populate your DataTable with all your data upfront, possibly excluding any records where IsActive is False. You would bind your data to a BindingSource and that to the grid, setting the Filter of the BindingSource to exclude any records where IsActive is False. When you want to refresh your local data, you first get the latest LastModifiedTime value from your DataTable.

You then query the database for all records where the LastModifiedTime is greater than that value. You can populate a new DataTable and merge it into the existing one or you should be able to simply populate the existing DataTable directly. Any new records will be added and any modified records should be overwritten with the new data. Note that modified records will all have been physically updated but some will have been logically deleted, i.e. had their IsActive flag set to False. Because of the Filter on the BindingSource, all the "deleted" rows will disappear from the grid, even though they are still in the DataTable. Not only have you not had to unbind and rebind the grid, the actual rows in the grid are still the same rows, so you won't lose the existing selection or anything like that.

 

[jwcoleman87]

Ah, I see that's where I got mixed up when I switched to Linq to SQL classes. I forgot how to use a binding source as an object in a programmatic way. Can you give me an example of how to use a binding source with linq to sql classes? I understand how using a binding source can eliminate unbinding and rebinding the grid, and this is pretty much exactly what I was after. Right now my Order table has columns for Filled and closed bits. I can eliminate orders I don't want the stock clerk to see by filtering by Closed orders.

I also understand how if I have a lastmodified column in my orders table I can run a query for any dates for parts that have been ordered after what is currently in the data grid, these orders I can print new tickets for and also display them on the grid. No fancy client side data storage magic necessary.

Please help me if you will, with some leads on using a binding source with linq to sql classes. I will post what I find on my own tomorrow some time.

 

[jwcoleman87]

I found this little snippet

Dim dataGrid1 As New DataGrid()
Dim dataGrid2 As New DataGrid()
Dim dataGrid3 As New DataGrid()

Dim custQuery = _
    From cust In db.Customers _
    Select cust

dataGrid1.DataSource = custQuery
dataGrid2.DataSource = custQuery
dataGrid2.DataMember = "Orders" 

Dim bs = _
    New BindingSource()
bs.DataSource = custQuery
dataGrid3.DataSource = bs

I see there he just initializes a binding source, and then sets the binding sources data source to the query he created with linq to sql. I see then that he uses that binding source as the datasource for his grid.

I see how this adds an intermediary binding source between the grid and the actual data, a binding source.

My question is, can I make live updates to the binding source, and will they automatically affect changes to what is seen on the datagrid. Another question is how is resetting updating the binding datasource different than directly changing the datagrids datasource?

EDIT

after rereading what you typed in light of this snippet, I see that there is yet another step involved in making the updates. The datatables!

You can populate a new DataTable and merge it into the existing one or you should be able to simply populate the existing DataTable directly.

How do I use the datatables to interact with the binding source? I understand how to query to get the information I need, it's the vb objects I'm unfamiliar with, and how they work together. It's time to search for another snippet

 

[jmcilhinney]

My advice wasn't entirely accurate before. I was talking about using a BindingSource that way on the assumption that your were using a DataTable as the data source, which is obviously not the case. Sometimes all the threads I post to blur and I forget the specifics of the current issue. In order to use the Filter property of a BindingSource that way, the data source must implement the IBindingListView interface, which it won't in your case. Unless you want to create your own class that does that and store your L2S objects in it, you'll have to resort to doing more manual work, like removing logically deleted records from your collection yourself.

 

[jwcoleman87]

I'm beginning to see how I got so discouraged with my production lead interface, because filtering data with linq queries was not easy to do dynamically. I just realized that I can actually still use the binding source .filter property as well as the linq queries.

EDIT: Or Not.


Wowsers

If only my work was not pushing me to create more and more modules for my application I would actually have time to refine what I've already made. I will get to it some time!

They're always like, jw we would like to be able to do this, can you do that? okay lets start....
Hey jw we really have a need here in this department to help reduce repair escalations, can you make something to remedy this issue?

I'm just a production lead lol.

 

[Herman]

Unless you want to create your own class that does that and store your L2S objects in it, you'll have to resort to doing more manual work, like removing logically deleted records from your collection yourself.

With Linq to SQL you can do two way databinding in a datagridview by defining your relationships directly in the database, and using navigation properties to set your filters and datasources like in the example you posted. As long as the datagridview can resolve the database field and table two-way binding will work. If you flatten the output of your queries with anonymous types, the datagridview cannot know what tables the fields belong to. In that case you can put the datagridview in VirtualMode and handle events to update the database, but that requires code.

 

[jwcoleman87]

With Linq to SQL you can do two way databinding in a datagridview by defining your relationships directly in the database, and using navigation properties to set your filters and datasources like in the example you posted. As long as the datagridview can resolve the database field and table two-way binding will work. If you flatten the output of your queries with anonymous types, the datagridview cannot know what tables the fields belong to. In that case you can put the datagridview in VirtualMode and handle events to update the database, but that requires code.

Can you explain this way to use the dgv in a "virtual" mode

 

[Herman]

Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control

 

[jwcoleman87]

Walkthrough: Implementing Virtual Mode in the Windows Forms DataGridView Control

Thank you, I'm going to give this a go, our asset recovery department needs a quicker way of removing mass quantities of parts from inventory without selecting individual parts. This could allow them to simply select multiple items and remove them as needed. Although I'd have to add some control to what they do.