Results 1 to 3 of 3

Thread: [RESOLVED] Correct Method for TableAdapter Query

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Resolved [RESOLVED] Correct Method for TableAdapter Query

    Hi,

    I have a typical Customer and Orders example. One customer can have many orders.

    I want to display only the records for the customerID that I have store in Globalvariable on the userform.

    So for CustomersTableAdaper I have initial SQL query:

    Fill, GetData()

    Code:
    SELECT CustomerID, CustomerName
    FROM tblCustomers
    I want to load only customerID # 1

    Code:
    SELECT CustomerID, CustomerName
    FROM tblCustomers
    WHERE CustomerID=@CustomerID
    My question is: What is the correct way between changing the 'original query' or 'add query'? which one? If I add a query does it first run the original query and then the added query? I want the method that shall be the most fast. So I think to just include WHERE statement in original query...

    thanks

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Correct Method for TableAdapter Query

    I'm sure I've been through this with you before months ago. The correct option is to add a new query. You then call Fill if you want all the data and FillByCustomerID if you want just the data for one Customer. That's all there is to it. You're trying to complicate something that is very simple.

    Now, you could edit the original query if you wanted to but that would not help you in any way whatsoever. It would simply mean that the Fill method worked differently to every other Fill method and that you now have no way to get all the Customer data.

    I suggest that you add the query and then actually look at the code for the Fill and FillByCustomerID methods for yourself. You'll see that there's no magic there and it's basically the same ADO.NET code that you'd write for yourself if you weren't using a typed DataSet. Here, I just did it for you. It took me five minutes from scratch to get to this code:
    vb.net Code:
    1. Public Overloads Overridable Function Fill(ByVal dataTable As Database1DataSet.CustomerDataTable) As Integer
    2.     Me.Adapter.SelectCommand = Me.CommandCollection(0)
    3.     If (Me.ClearBeforeFill = true) Then
    4.         dataTable.Clear
    5.     End If
    6.     Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
    7.     Return returnValue
    8. End Function
    9.  
    10. Public Overloads Overridable Function FillByCustomerId(ByVal dataTable As Database1DataSet.CustomerDataTable, ByVal CustomerId As Integer) As Integer
    11.     Me.Adapter.SelectCommand = Me.CommandCollection(1)
    12.     Me.Adapter.SelectCommand.Parameters(0).Value = CType(CustomerId,Integer)
    13.     If (Me.ClearBeforeFill = true) Then
    14.         dataTable.Clear
    15.     End If
    16.     Dim returnValue As Integer = Me.Adapter.Fill(dataTable)
    17.     Return returnValue
    18. End Function
    As you can see, virtually no difference at all. Which option is faster is basically irrelevant because, if there's any difference at all, it will be a matter of milliseconds.

  3. #3

    Thread Starter
    Frenzied Member
    Join Date
    Apr 2016
    Posts
    1,415

    Re: Correct Method for TableAdapter Query

    Okay Thanks JMC, yes we did briefly talk about this before. I just wanted to clarify.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width