Partial Class help with designer TableAdapters

Hello,
I would like to be able to set the WHERE clause of a select statement on the
fly. I have a DataAccess layer designed using the DataSet designer and a
BusinessLogic layer using classes. The business logic layer is decorated
using the

attributes and they are used to bind to controls and Object datasets on web
forms.
Is there a resonable way to set the WHERE clause on the fly with a partial
class from a TableAdapter? Some example code maybe?

For instance, I have a table adapter that has a query like the following:
SELECT AssetsId, AssetNumber, AssetReviewed, AssetNotTracked,
NoLongerAsset, DateAssetDropped
FROM dbo.Assets

How can I change that query using partial classes of the Table Adapter...?

SAL

RE: Partial Class help with designer TableAdapters

Hi SAL,

When you said "and a BusinessLogic layer using classes", do you mean the
class generated by the DataSet designer or your own class? I'm asking this
since the generated TableAdapter partial class also has this attribute:

Global.System.ComponentModel.DataObjectAttribute(true),

If you defined your own custom BusinessLogic class and applied the
DataObjectAttribute, could you please post some of your code to show how
you're using the TableAdapter?

Based on my understanding so far, you can create a partial class to the
TableAdapter and expose a public method or property to change the
CommandText of the select query to include your where clause. For example:

Namespace DataSet1TableAdapters
Partial Public Class authorsTableAdapter

Public Sub SetWhereClause(ByVal clause As String)
Me.InitCommandCollection()
Me._commandCollection(0).CommandText += " where " + clause
End Sub

End Class
End Namespace


Dim x As New DataSet1TableAdapters.authorsTableAdapter
Dim ds As DataSet1.authorsDataTable
x.SetWhereClause("phone like '4%'")
ds = x.GetData()


Hope this helps.


Regards,
Walter Wang (wawang@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: Partial Class help with designer TableAdapters

Hi Walter and thanks for your reply. I used the code you posted as a
template for the partial class but I'm not getting any data to update even
though the correct values are getting set in a DataTable and being passed
into my UpdateBatchCntyAssetsAssetManagement function shown below. I'm also
posting the code for the partial class. The Update method being called in my
Business class was generated by the designer.

I'm posting my business logic layer class here:

Imports Microsoft.VisualBasic
Imports System.Data.SqlClient
Imports System.Data
Imports CatsDsTableAdapters

_
Public Class CntyAssetsAssetManagementBLL
Private caamTa As CntyAssetsAssetManagementTableAdapter

Protected ReadOnly Property Adapter() As
CntyAssetsAssetManagementTableAdapter
Get
If caamTa Is Nothing Then
caamTa = New CntyAssetsAssetManagementTableAdapter
End If
Return caamTa
End Get
End Property

True)> _
Public Function GetCntyAssetsAssetManagement() As
CatsDs.CntyAssetsAssetManagementDataTable
Return Adapter.GetCntyAssetsAssetManagement()
End Function

False)> _
Public Function GetCntyAssetsAssetManagement(ByVal whereClause As String)
As CatsDs.CntyAssetsAssetManagementDataTable
Adapter.UpdateWhere(whereClause)
Return Adapter.GetCntyAssetsAssetManagement()
End Function

False)> _
Public Function GetCntyAssetByAssetId(ByVal aid As Integer) As
CatsDs.CntyAssetsAssetManagementDataTable
Return Adapter.GetCntyAssetByAssetId(aid)
End Function

True)> _
Public Function UpdateBatchCntyAssetsAssetManagement(ByVal caamdt As
CatsDs.CntyAssetsAssetManagementDataTable) As Boolean
For Each dr As CatsDs.CntyAssetsAssetManagementRow In caamdt.Rows
If Not dr.IsAssetNumberNull Then
dr.AssetReviewed = True
End If
If Not dr.IsAssetNotTrackedNull AndAlso dr.AssetNotTracked = True
Then
dr.AssetReviewed = True
End If
If Not dr.IsNoLongerCntyAssetNull AndAlso dr.NoLongerCntyAsset =
True Then
dr.DateAssetDropped = Now
End If
Next
caamdt.AcceptChanges()
Adapter.Update(caamdt)
Return True
End Function

Public Sub UpdateWhere(ByVal where As String)
Adapter.UpdateWhere(where)
End Sub
End Class

Imports Microsoft.VisualBasic
Imports System.Data
Imports System.Data.SqlClient

Namespace CatsDsTableAdapters
Partial Public Class CntyAssetsAssetManagementTableAdapter

Private mSelectCommand As String

Public Sub UpdateWhere(ByVal where As String)
Me.InitCommandCollection()
If mSelectCommand = "" OrElse mSelectCommand = String.Empty Then
mSelectCommand = Me._commandCollection(0).CommandText
End If
Dim sql As String

If where = "" Then
sql = ""
Else
sql = " WHERE " & where
End If
Me._commandCollection(0).CommandText = mSelectCommand & sql
End Sub
End Class
End Namespace


""Walter Wang [MSFT]"" wrote in message
news:aN13v2VFIHA.360@TK2MSFTNGHUB02.phx.gbl...
> Hi SAL,
>
> When you said "and a BusinessLogic layer using classes", do you mean the
> class generated by the DataSet designer or your own class? I'm asking this
> since the generated TableAdapter partial class also has this attribute:
>
> Global.System.ComponentModel.DataObjectAttribute(true),
>
> If you defined your own custom BusinessLogic class and applied the
> DataObjectAttribute, could you please post some of your code to show how
> you're using the TableAdapter?
>
> Based on my understanding so far, you can create a partial class to the
> TableAdapter and expose a public method or property to change the
> CommandText of the select query to include your where clause. For example:
>
> Namespace DataSet1TableAdapters
> Partial Public Class authorsTableAdapter
>
> Public Sub SetWhereClause(ByVal clause As String)
> Me.InitCommandCollection()
> Me._commandCollection(0).CommandText += " where " + clause
> End Sub
>
> End Class
> End Namespace
>
>
> Dim x As New DataSet1TableAdapters.authorsTableAdapter
> Dim ds As DataSet1.authorsDataTable
> x.SetWhereClause("phone like '4%'")
> ds = x.GetData()
>
>
> Hope this helps.
>
>
> Regards,
> Walter Wang (wawang@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>

Re: Partial Class help with designer TableAdapters

Hi SAL,

Thanks for your quick reply.

I'm not very clear about your follow-up question, let me verify some points
first:

1) Our first question is talking about modifying the Select query to
return different data dynamically, is it working now?
2) Your follow-up question is about not being able to update the data,
right? If we remove the code that are adding the where clause, does it
work? Based on my understanding, what we have added is only to append some
where clause the the Select query only, it should not affect the updating
behavior.


Regards,
Walter Wang (wawang@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: Partial Class help with designer TableAdapters

Hi SAL,

Would you please post a reply here to let me know the status of this post?
Thanks.

Regards,
Walter Wang (wawang@online.microsoft.com, remove 'online.')
Microsoft Online Community Support

==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================

This posting is provided "AS IS" with no warranties, and confers no rights.

Re: Partial Class help with designer TableAdapters

I'm sorry Walter. I did get everything working fine. Thank you very much for
your help.


S

""Walter Wang [MSFT]"" wrote in message
news:gUubYGqGIHA.540@TK2MSFTNGHUB02.phx.gbl...
> Hi SAL,
>
> Would you please post a reply here to let me know the status of this post?
> Thanks.
>
> Regards,
> Walter Wang (wawang@online.microsoft.com, remove 'online.')
> Microsoft Online Community Support
>
> ==================================================
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ==================================================
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>