Strong Typed Dataset set variable TOP value

Question

I've got a problem with a strong typed dataset query in my project.

I have a query like this:

SELECT * FROM dbo.PositionData WHERE ID_Tracker=@ID_Tracker

Which works fine and the datatable function is correctly generated.

Now I would like to add a TOP value to the query, which can be variable. This could look like this, but this doesn't work:

SELECT TOP @TopValue * FROM dbo.PositionData WHERE ID_Tracker=@ID_Tracker

Is there another way to accomplish this task?

I tried it somehow with a Partial class of my table adapter, but I don't know how to access the correct command (I see my custom command in the CommandCollection, but I don't know how I can evaluate the right command to replace something in the CommandText).

Is is possible to do that with Strong Typed Dataset?

Any help would be great. Thank you very much.

Best Regards, Silvan

Answer 1

You can put it in brackets:

SELECT TOP (@TopValue) * FROM dbo.PositionData WHERE ID_Tracker=@ID_Tracker

That's valid and generates correctly an int parameter for the table-adapter's SqlCommand.

---

According to your comment if its possible to to parametrize the sort-direction of an ORDER BY in a strongly typed DataSet. Yes, it is possible:

SELECT TOP @TopValue * FROM dbo.PositionData WHERE ID_Tracker=@ID_Tracker
ORDER BY 
    CASE WHEN @OrderDirection = 'ASC'  THEN [Time] END ASC, 
    CASE WHEN @OrderDirection = 'DESC' THEN [Time] END DESC

But you have to add the parameter to the command-collection manually since visual studio doesn't want. Therefore

• right-click on the command and
• click properties
• click parameters (at the bottom of the screenshot)

add the @OrderDirection parameter like here:

But maybe you should either use two different methods or a stored-procedure instead.