Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

April 17, 2014

SSDT Data Editor Now Has Sorting and Filtering!

The SQL Server Data Tools (SSDT) provide access to many SQL Server features from within Visual Studio. One of those features, available from the SQL Server Object Explorer, is the visual Data Editor.

While the Data Editor has always been great for inserting, updating, and viewing data, it did not support any sorting or filtering … until now!

image

NOTE:

  • For an introduction to SSDT and the SQL Server Object Explorer, see this post.
  • For an introduction to the SSDT Data Editor, see this post.

The March 2014 release of SSDT added support for SQL Server 2014 databases. But it ALSO provided new features in VS 2012 and VS 2013 for sorting and filtering the data in the Data Editor!

If you are using VS 2012, you can use the update option to get this update (SQL | Check for Updates).

If you are using VS 2013, the update should appear in the Notification window when you click the notification flag:

image

If not, you can look for it under Tools | Extensions and Updates.

Once you have it installed, your Data Editor will have two additional buttons:

image

The first button disables sorting and filtering.

The second button displays a dialog for entry of the sorting and filtering criteria.

image

In this example, I set a sort on the FirstName column. Any number of sorts can be added, either ascending or descending. I also added a filter on the LastName so only customers with a last name that begins with B will be listed.

This dialog also allows you to uncheck columns to remove them from the Data Editor display. This helps you focus on only the columns you need to see.

Notice at the bottom of the dialog is the SQL expression SSDT will use to query the table. This expression changes as you modify the columns, sorting, sort order, or filter.

Clicking Apply immediately executes the query and re-populates the data in the Data Editor. This allows you to view the results without closing the Filter and Sort dialog.

image

When you have the results you need, click OK to close the dialog.

NOTE: When I attempted to edit the sorted and filtered data, I received an error message:

image

HOWEVER, the edits were actually made to the data.

Try out these new features any time you want more control over the data displayed in the Data Editor.

Enjoy!

2 Comments

  1.   Lukas — September 22, 2015 @ 6:44 am    Reply

    How to write an query using SSDT Data Editor (Filter and Sort) when need to list only the rows with NULL value e.g. “Select TOP 1000 * from [dbo].[TAB1] WHERE [TitleA] = N’Main’ AND [TitleB] is NULL” ?

    I allways get the finall (WRONG) query like this: “Select TOP 1000 * from [dbo].[TAB1] WHERE [TitleA] = N’Main’ AND [TitleB] =”

    Thanks.

  2.   Lukas — September 22, 2015 @ 7:18 am    Reply

    The same problem: http://stackoverflow.com/questions/32253657/visual-studio-2013-ssdt-edit-data-is-null-not-work-as-filter

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2022 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs