Share via

How to: Use Folders to Organize Data

  • Article

[This content is no longer valid. For the latest information on "M", "Quadrant", SQL Server Modeling Services, and the Repository, see the Model Citizen blog.]

In the Microsoft code name “Quadrant” Explorer, below the Database node you might see several folder icons. These Folders are used to organize the content of a SQL Server Modeling Services database. Folders are a concept in the Microsoft code name “M” language that are implemented in Modeling Services. A Folder can serve as a container for any kind of data. That is, you can insert any kind of record into a Folder. This allows you to group heterogeneous data together, such as all items that are associated with a particular person.

For more information about Folders, see Base Domain Library (BDL) and "Oslo" Repository Folder Design Patterns.

For more information about how to use Folders in “Quadrant”, see How to: Add Items to a Folder, How to: Create a New and Nested Folder, and How to: Update and Delete a Folder.

This tutorial assumes that you have already completed the How to: Create a Database from "M" in "Quadrant" tutorial, which creates the PetShop database in an instance of SQL Server by using Microsoft code name “M”.

To add Modeling Services support to a database

  1. On the Start menu, click All Programs, then Microsoft SQL Server Modeling CTP, and click Microsoft SQL Server Modeling CTP Command Prompt to open a SQL Server Modeling CTP command prompt.

  2. At the command prompt, paste and run the following command to install Modeling Services patterns to the MSPetShop4 database.

    Mx.exe install repository.mx -d:MSPetShop4

    For more information about using Mx.exe, see How to: Update an Existing Model by Recreating the SQL Server Modeling CTP Database.

  3. If the PetShop Explorer is already open, press F5 to refresh it. If it is not open and you have connected to the PetShop database before, on the View menu, click Explorer, and then click MSPetShop4.

    If you have not connected to the PetShop database before, use the steps outlined in How to: Connect to a SQL Server Database with "Quadrant" to connect to the PetShop database.

  4. Modeling Services databases l always have a Repository folder, which serves as a container for any kind of data. The following sections will show you how to add additional folders to the database.

  5. Expand the Database node. The Repository and Repository.Item schemas are now visible. For more information about these schemas, see "Oslo" Repository Folder Design Patterns.

To add a new column to a table for Folder organization

  1. We are now going to use T-SQL to create a column called Folder for the Product table in the PetShop database. This column will be used to store folder information for records in the Product table. By doing so, each record will have its own Folder field that indicates the folder it is associated with. For more information on For more information about using the T-SQL console in “Quadrant”, see How to: Use the T-SQL Console to Explore and Edit Data.

    On the File menu, click New, and then click T-SQL Console.

  2. Type the following command at the prompt and press ENTER.

    ALTER TABLE dbo2.Product
 ADD Folder [Repository.Item].[FolderId]

  3. At the next prompt, type GO and press ENTER to execute the Transact-SQL command, which inserts a new column called Folder to the Product table, with a data type defined by FolderId in the Repository.Item schema.

  4. Next we are going to create a FOREIGN KEY constraint for this new Folder column, that references the Id column of the [Repository.Item].[FoldersTable] table. For more information about why this must be done, see the “3. Add a Folder Column to Support the “Oslo” Repository Folder Design Pattern” section of Table Design Patterns.

    ALTER TABLE [dbo2].[Product] with check 
ADD constraint [FK_Stocks_Folder_Repository.Item.Folders] foreign key ([Folder])
   references [Repository.Item].[FoldersTable] ([Id])
   on delete cascade

  5. At the next prompt, type GO and press ENTER to execute the Transact-SQL command.

  6. You can now use the steps described in How to: Add Items to a Folder, How to: Create a New and Nested Folder, and How to: Update and Delete a Folder to manipulate folders in the PetShop database.

To create a new folder for a record type

  1. Right-click the Database node and click Insert Folder. A new folder named New Folder is added as a sibling node to the Database node. Note the + character to the right of the folder name, which indicates a newly added item.

  2. Double-click New Folder in the Explorer to open a new workpad titled New FoldersTable Record +. By default, the workpad opens by using a Folder Tree viewer.

  3. In the upper-right corner of the workpad, click the viewer switch icon (the triangle) and click Properties to view it in a properties viewer.

  4. Click the Name field and change it from New Folder to Birds.

  5. On the File menu, click Save Changes to commit this change to the database.

  6. Confirm that the Birds folder now shows up in the Explorer.

This procedure creates a folder without any implicit security support. To enable permission control on a folder (so that you can grant or deny access to a folder and its content based on user identity), see the How to: Grant Users Access to SQL Server Modeling Services topic in the Security Tasks section.

To assign records to folders

  1. Return to the MSPetShop4 Explorer.

  2. Expand the Database icon and expand the dbo2 subnode.

  3. Drag the Product icon to the workspace. This opens a workpad that displays all Product records in a table.

  4. Press F5 to refresh the tableThe Folder column appears in the table, which was created by the Transact-SQL commands in the second procedure of this topic.

  5. Click the Folder cell for the Owl row. In the drop-down menu, click Birds. Do the same for the Duck row.

  6. On the File menu, click Save Changes to commit these changes. Alternatively, press CTRL+S.

To browse folders for records

  1. Return to the MSPetShop4 Explorer.

  2. Double-click the Birds folder.

  3. In the upper-right corner of the new workpad, click the viewer switch icon (the triangle) and select Tree Master/Detail.

  4. Expand the Birds node and click its Product subnode. Notice that the Birds records (Owl and Duck) appear in a table on the right pane.

  5. From the right pane, double-click one of the records. A new workpad appears with the Bird data in a Properties viewer.

See Also

Tasks

How to: Create a New and Nested Folder
How to: Add Items to a Folder
How to: Update and Delete a Folder

Other Resources

"Oslo" Repository Folder Design Patterns
Table Design Patterns