Creating a Dynamic TableAdapter

Hello,

I have the following form which use the following combo boxes to filter the dataversionid listbox
1. Filter by Budget Year
2. Filter by Input Code
3. Filter by Technology Set

I want to avoid creating nine different methods in  the underlying table adapter.  I would need to create nine getdata methods in order to take into account all possible selections of comboboxes that user may make selection from.  Below is the type of code I originally had in mind.  But it seems to me, there should be a better way.  Thanks
If cboBudgetYear.SelectedIndex > -1 Then
            Dim intSelectedYear As Integer = Convert.ToInt32(cboBudgetYear.Text.Substring(0, 4))
            Me.CommentsTableAdapter.FillByBY(Me.BADSDataSet.Comments, intSelectedYear)
            MessageBox.Show(intSelectedYear.ToString)
        Else
            Me.CommentsTableAdapter.Fill(Me.BADSDataSet.Comments)
            MessageBox.Show("No Budget Year Selected")

        End If

Open in new window

GraphDesigner.JPG
chtullu135Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
CodeCruiserConnect With a Mentor Commented:
You have 3 options

1) Create a untyped tableadapter based on selection (or even just change the selectcommand)
2) Use stored procedure
3) Combination of above

1)Example
Dim dbadp as new SQLDataAdapter("", connectionstring)
If condition1 Then
   dbadp.SelectCommand.CommandText = "Select ... Where ..."
Elseif condition2 Then
   dbadp.SelectCommand.CommandText = "Select ... Where ..."
...
End If
dbadp.Fill(dTable)

2)Example
Dim dbadp As New SQLDataAdapter("", connectionstring)
If condition1 Then
   dbadp.SelectCommand.CommandText = "LoadAssignedContacts"
Elseif condition2 Then
   dbadp.SelectCommand.CommandText = "LoadUnassignedContacts"
...
End If

3)Example
Dim dbadp as new SQLDataAdapter("LoadContacts", connectionstring)
If Condition1 Then
   dbadp.SelectCommand.Parameters.AddWithValue("LoadAssigned", 1)
Else
   dbadp.SelectCommand.Parameters.AddWithValue("LoadAssigned, 0)
End If
0
 
chtullu135Author Commented:
I just realized I can create a stored procedure to do what I want that has conditional statement to return the correct dataset
0
 
chtullu135Author Commented:
Hello Code Cruiser.  I went ahead and went the stored procedure route.  I created a procedure that takes the user selections as optional variables and via a series of conditional statements returns the correct set of records. I'll will use this stored procedure to filter the dataversionld listbox via the fill method of the underlying data adapter.  
0
 
CodeCruiserCommented:
That's great. Stored procedures are the best option in speed, performance, network traffic, and security aspects.
0
All Courses

From novice to tech pro — start learning today.