Opinion on best method - How to load a DGV and Filter via comboboxes
I have a WinForm that contains 15 comboboxes (lists of last name, first name, dob, etc.) and a DataGridView used for search results. On initial load the DGV is filled with ALL records from the DB and each of the CB's are filled with the distinct values from the records. Each time a user changes a search criteria (CB) the DGV will get reloaded with matching records. The CB's also get reloaded with new results to match. (Cascading combobox I believe it's called?)
I am not new to VB.net (2 years,) but not an expert. I AM new to Entity Framework and still trying to learn.
As far as I can tell I have several options when it comes to achieving my goal. I am looking for opinion, and maybe some reference material, to get the result I need and have it working efficiently.
Option 1 - Currently I am using SqlCommands to query the DB on initial load and each subsequent CB change. This works great, but it is slow as it has to do a full query to the DB, with search criteria, to fill the DGV and all the CB's.
Option 2 - Use Entity Framework and query against that for the DGV and all CB's. I have played around with EF enough to get this to work, partially. I don't have all the search criteria set up yet, and there's a few bugs.
Option 3 - Fill a DataTable/DataView, using EF, and use "filtering." This is what I'm thinking may be the "better" solution? Loading a DataTable on initial load and then doing a SELECT and passing the results to a DataView, which would be the datasource for the DGV.
What has made this difficult for me is the fact that I am searching on joined tables. The Query is nested because the actual results will have many more records than I want to display. (duplicates due to searching joined tables.) The nested query is searching on 16 columns, but I am only displaying the distinct values for 13 of those columns.
I hope all of this makes sense. I'm not sure if ANY of these options are valid or if there is a MUCH better method to follow.
Any help would be greatly appreciated!
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
I depends on what you are after.
EF is not the holy grail but you seem to be eager to use it.
In your case it would not matter as you don't seem to have large tables to fill.
So option 3 would be fine if you want to but it seems that it would reserve more resources by having everything on the fly (not 100% sure on how VS reserves resources tho).
Personally I would use a dataset without EF or straight SqlCommands (that are the fastest by the way) and trying to optimize the SQL query so I won't have to do a full query search. If you do a select * from table, please reconsider or post to the database forum for an advice on an optimized query. I'm saying this because you state: "On initial load the DGV is filled with ALL records from the DB" . That is not necessary. There are methods like paging that would avoid doing so and overload that poor DGV.
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
If you are initially loading all the records into a datatable then create a BindingSource and use the datatable as it's datasource, then use the BindingSource as the DGV datasource. You can then use the Bindingsource Filter method each time you want to change what''s displayed in the DGV.
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
While I do state that *ALL* records are pulled on initial load, there is still a pretty complex SQL command to get what I need. I'll post it below. I'm also not sure if I'll be able to optimize it as it is fairly complex - but by all means, if you see anything I can change, please let me know.
I'm not EAGER to use EF, it just seemed from other answers I've seen that it has a slight advantage since you are not dealing with the database directly. I does seem to be running faster for what little I have converted thus far.
I'm also not sure what you consider to be "large" tables. The First and Last name comboboxes could have thousands of entries. The DGV could have tens of thousands of entries - so to me that is a lot of data. Maybe too much for initial load on the DGV???
Code:
SQLcmd = New SqlCommand With {
.Connection = Vars.sqlConnACL,
.CommandText = String.Join(Environment.NewLine,
"SELECT DISTINCT ",
"data_Demog.[ID_Demog], ",
"data_Demog.[Last_Name], ",
"data_Demog.[First_Name], ",
"data_Demog_1.Last_Name + ', ' + data_Demog_1.First_Name AS [AKA_Name], ",
"data_Demog.[DOB], ",
"data_Demog.[Gender], ",
"data_Demog.[ST_Complete], ",
"data_Demog.[LT_Complete], ",
"data_Demog.[LT_Due_Date], ",
"tbl_Disease_Type.[Disease_Type_Abr], ",
"tbl_Reason_For_Call.[Reason_For_Call], ",
"IIF(IsNull(data_Demog.[ID_Physician],0)=0,NULL, ",
"(tbl_Physician.[Last_Name] + ",
"IIF(ISNULL(tbl_Physician.[First_Name],'')='','',', ' + tbl_Physician.[First_Name]) + ",
"IIF(ISNULL(tbl_Physician.[Middle_Initial],'')='','',' ' + tbl_Physician.[Middle_Initial]) + ",
"IIF(ISNULL(tbl_Title.[Title],'')='','',', ' + tbl_Title.[Title]))) AS [Physician_Name], ",
"data_Demog_Communication.[Date_Sent] ",
"FROM [ACL].[data_Demog] ",
"INNER JOIN( ",
"SELECT ",
"data_Demog.[ID_Demog], ",
"data_Demog.[Last_Name], ",
"data_Demog.[First_Name], ",
"data_Demog.[DOB], ",
"data_Demog.[ID_Physician], ",
"data_Demog.[ST_Complete], ",
"data_Demog.[LT_Complete], ",
"data_Demog.[LT_Due_Date], ",
"data_Demog.[ID_Reason_for_Call], ",
"data_Demog.[Intl_Patient], ",
"data_Demog.[Mayo_Patient], ",
"data_Demog_MedHist.[ID_Disease_Group], ",
"data_Demog_MedHist.[ID_Disease_Type], ",
"data_Demog_BGL.[ID_BGL_Assay], ",
"data_Demog.[ID_Demog_AKA], ",
"data_Demog_Communication.[Date_Sent] ",
"FROM [ACL].data_Demog ",
"LEFT JOIN [ACL].data_Demog_MedHist ON data_Demog_MedHist.[ID_Demog] = data_Demog.[ID_Demog] ",
"LEFT JOIN [ACL].data_Demog_BGL ON data_Demog_BGL.[ID_Demog] = data_Demog.[ID_Demog] ",
"LEFT JOIN [ACL].tbl_Disease_Type ON tbl_Disease_Type.[ID_Disease_Type] = data_Demog_MedHist.[ID_Disease_Type] ",
"LEFT JOIN [ACL].data_Demog_Communication ON data_Demog_Communication.[ID_Demog] = data_Demog.[ID_Demog] ",
"WHERE ( ",
"((@LTPD=-1) or (@LTPD=1 AND data_Demog.[LT_Due_Date]<=GetDate()) OR (@LTPD=0 AND data_Demog.[LT_Due_Date]>=GetDate())) AND ",
"(data_Demog.[Last_Name]=@LName Or @LName Is Null) And ",
"(data_Demog.[First_Name]=@FName Or @FName Is Null) And ",
"(data_Demog.[ID_Physician]=@Physician Or @Physician Is Null) And ",
"(data_Demog.[ID_Reason_for_Call]=@RFC Or @RFC Is Null) And ",
"(data_Demog.[DOB]=@DOB Or @DOB Is Null) And ",
"(data_Demog_MedHist.[ID_Disease_Group]=@DGroup Or @DGroup Is Null) And ",
"(data_Demog_MedHist.[ID_Disease_Type]=@DType Or @DType Is Null) And ",
"(data_Demog.[ST_Complete]=@STC Or @STC=-1) And ",
"(data_Demog.[LT_Complete]=@LTC Or @LTC=-1) And ",
"(data_Demog_BGL.[ID_BGL_Assay]=@Assay Or @Assay Is Null) And ",
"((tbl_Disease_Type.[Disease_Type_Owner] Like '%' + @GC + '%') OR @GC Is Null) And ",
"(data_Demog.[Mayo_Patient]=@MPat Or @MPat=-1) And ",
"(data_Demog.[Intl_Patient]=@IPat Or @IPat=-1)) ",
") AS SearchD On data_Demog.[ID_Demog] = SearchD.[ID_Demog] ",
"LEFT JOIN [ACL].tbl_Physician ON tbl_Physician.[ID_Physician] = data_Demog.[ID_Physician] ",
"LEFT JOIN [ACL].tbl_Title on tbl_Title.[ID_Title] = tbl_Physician.[ID_Title] ",
"LEFT JOIN [ACL].tbl_Reason_For_Call ON tbl_Reason_For_Call.[ID_Reason_For_Call] = SearchD.[ID_Reason_for_Call] ",
"LEFT JOIN [ACL].data_Demog_MedHist on data_Demog_MedHist.[ID_Demog] = SearchD.[ID_Demog] ",
"LEFT JOIN [ACL].tbl_Disease_Type ON tbl_Disease_Type.[ID_Disease_Type] = SearchD.[ID_Disease_Type] ",
"LEFT JOIN [ACL].data_Demog AS data_Demog_1 ON data_Demog.[ID_Demog_AKA] = data_Demog_1.[ID_Demog] ",
"LEFT JOIN [ACL].data_Demog_Communication ON data_Demog_Communication.[ID_Demog] = data_Demog.[ID_Demog] ",
"LEFT JOIN [BGL_Applications].[ACL].data_Demog_Molecular ON data_Demog_Molecular.[ID_Demog] = data_Demog.[ID_Demog] ",
"WHERE(data_Demog_Communication.[Date_Sent] = ( ",
"SELECT MAX([Date_Sent]) ",
"FROM [ACL].[data_Demog_Communication] ",
"WHERE [ACL].data_Demog_Communication.[ID_Demog] = [ACL].data_Demog.ID_Demog) Or ",
"[ACL].data_Demog_Communication.[Date_Sent] Is Null)")
}
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
That's not too much for loading a datatable. You can certainly time how long that takes and decide whether or not it is acceptable. If it isn't, then you might consider loading the table in a background thread if there is ANYTHING else the user could look at while it loads. That number of records may not take all that long, though, so timing the loading of the table is likely worthwhile.
Beyond that, I'd either use the DefaultView of the datatable (which can be filtered by setting the RowFilter), or do what wes4dbt said. I'm not sure that there's much of a performance difference between those two, and both should be pretty quick.
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
Yes it looks like there isn't a lot of data so again, as I've said, it will really make no difference. Just use what you feel more comfortable with.
I'm literately pulling my eyes out on the SQL. This is an excellent example why you don't write large statements in .net code and rather do it on SQL.
From an initial look tho, you could try to make any possible Left joins to inner joins. Also we can't be sure of the table sizes but this seems like a bad performance rule:
tbl_Disease_Type.[Disease_Type_Owner] Like '%' + @GC + '%' . The first '%' makes this non surgable .
...You could open a topic if you like on the database thread and ask about optimization on the database. I'm no expert so I don't know how much help I can give over there form my part.
But this seems like a relatively small database so even if you leave it at that, i don't think it will highly impact performance.
Re: Opinion on best method - How to load a DGV and Filter via comboboxes
Using Entity Framework would be a good option. For writing joins in Entity Framework first create the query in SSMS (SQL-Server Management Studio) or by creating the query by creating it from selecting new query from the database connection under server explorer in Visual Studio then model the joins from that query.
For sorting properly and filtering look at BindingListView, read the documentation especially “An Important Detail”. BindingListView is C#, simply download, add a reference to your project and use it.
Let’s say you have a class called CustomerEntity where an instance of CustomerEntity is populated from a SELECT JOIN while underlying classes might be Customer, CustomerType etc
Create a form level variable
Code:
Private _customersView As BindingListView(Of CustomerEntity)
Do one for a BindingSource
Private ReadOnly _customersBindingSource As New BindingSource()
In form load or shown where gridView is a DataGridView
Code:
_customersBindingSource.DataSource = _customersView
gridView.DataSource = _customersBindingSource
Apply and remove filter based on ComboBox
Code:
''' <summary>
''' Example of a filter on an exact type, in this case contact type
''' </summary>
''' <param name="sender"></param>
''' <param name="e"></param>
Private Sub FilterByContactTypeButton_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim selectedContactType = CType(ContactTypeComboBox.SelectedItem, ContactType)
If selectedContactType.ContactTypeIdentifier = 0 Then
_customersView.Filter = Nothing
Else
_customersView.ApplyFilter(Function(customer) customer.ContactTypeIdentifier = selectedContactType.ContactTypeIdentifier)
End If
End Sub
Like conditions
Code:
Private Sub FilterCompanyName()
If String.IsNullOrWhiteSpace(CompanyLikeToolStripTextBox.Text) Then
_customersView.Filter = Nothing
Else
If CompanyNameConditiontoolStripComboBox.Text = "Starts with" Then
_customersView.ApplyFilter(Function(customer) customer.CompanyName.StartsWith(CompanyLikeToolStripTextBox.Text))
ElseIf CompanyNameConditiontoolStripComboBox.Text = "Ends with" Then
_customersView.ApplyFilter(Function(customer) customer.CompanyName.EndsWith(CompanyLikeToolStripTextBox.Text))
ElseIf CompanyNameConditiontoolStripComboBox.Text = "Contains" Then
_customersView.ApplyFilter(Function(customer) customer.CompanyName.Contains(CompanyLikeToolStripTextBox.Text))
End If
End If
End Sub
I don't have a complete or nearly complete code sample in VB.NET but the above can from a code sample (a work in progress but all works) I did in C# on GitHub and the code should be easy to follow. Take note how I update data in the DataGridView.
But is Entity Framework right for you? It depends on your willingness to learn Entity Framework and interact with controls,
