DataSet - Dynamic SQL

Add queries to the dataset tableAdapter by right clicking and adding query. Just add a name to the fillby and get by for new query.

The tableAdapter fills the dataset by using a predefined query. The dataset can have more than one query. In the dataset designer one can add queries using different select statements or stored procedures. One query could be SELECT * FROM Tmesheet. Another query could be SELECT* FROM TimeSheet WHERE dateWorked = @Date. When adding another query give it a distinct name such as FillByDate.



Namespace dsTimesheet2TableAdapters

Partial Public Class TimeSheetTableAdapter
Public Overloads Sub FillbyNoWhere(ByVal sqlFilter As String)
Me.CommandCollection(0).CommandText += " " + sqlFilter
End Sub

Public Function GetByNoWhere() As String
Return Me.CommandCollection(0).CommandText
End Function
End Class

Partial Public Class TimeSheetTableAdapter
Public Overloads Sub FillbyDateWorked(ByVal frmDate As String)
Me.CommandCollection(2).CommandText += " " + frmDate
End Sub

Public Function GetByDateWorked() As String
Return Me.CommandCollection(2).CommandText
End Function
End Class

End Namespace
The code above is called in form Load event using the date to limit the rows retrieved:
 rc = Me.TimeSheetTableAdapter.FillByDateWorked(Me.DsTimesheet2.TimeSheet, FrmDate) 

Called from btn_Filter:

 ta.FillByNoWhere(strFilter.ToString)