SQL Server Modeling Services Data Access
[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.]
The SQL Server Modeling Services supports a wide array of mature data access technologies to interact with Modeling Services data. Users and applications have the flexibility to query the database using ADO.NET, Entity Data Model (EDM), LINQ, ODBC, and many other data access technologies, including non-Microsoft platforms. At the same time, Modeling Services administrators can secure data in the Modeling Services database, ensuring that only users with proper permissions can read or update data. This topic explains the underlying Modeling Services architecture that maintains security while enabling ease of access to Modeling Services data.
Updatable Security Views
The Modeling Services database uses updatable SQL Server views to protect base tables. A typical SQL Server view acts as a named read-only query of one or more tables that can subsequently be used in other queries as if it were a table. A view becomes updatable by applying INSTEAD OF triggers to the view. When INSERT, UPDATE, and DELETE statements are run against the view, the associated triggers are called to perform the work. In addition to intelligently updating the correct set of base tables, these triggers also have the ability to perform other tasks. The Modeling Services database security model leverages updatable views, performing access checks in the triggers to verify that a user has appropriate permissions to modify the data in the underlying tables. A Modeling Services administrator assigns permissions to users for accessing and modifying specific table data based on Modeling Services Folders. For more information, see Security Tasks (Modeling Services) and How to: Create and Use SQL Server Modeling Services Folders.
After a user or application has been assigned permissions to the views and to the appropriate Modeling Services data, they have the ability to use any data access technology or technique to manipulate Modeling Services data. Using updatable views is a more flexible data access strategy than the alternative approach of providing a set of stored procedures to securely update the base tables, because many data access technologies work best with set-based operations against tables or views. Stored procedures often fail to anticipate all of the possible data manipulations that users and applications demand. Stored procedures also require a more manual process of preparing parameters and processing the results of the procedure calls. Queries and updates on the Modeling Services’s updatable views avoid these complications. This enables tools to more easily automate database operations to the views. For more information about updatable views, see SQL Server Modeling Services Security, which includes samples of both the views and the triggers. For more information about guidelines for creating updatable views, see View Design Patterns.
Data Access Example
After populating Modeling Services schemas with items, you can compose useful queries against the views for that particular schema or model instance. For example, the System_Runtime domain has an Assemblies view that describes assembly instances. With the proper permissions, any data access technology could be used to query the [System_Runtime].[Assemblies] view, which queries the information in the [System_Runtime].[AssembliesTable] base table. The following T-SQL query demonstrates one way of accessing this data.
Note
This example could just as easily be rewritten as an ADO.NET code sample. For various examples of programmatically interacting with the Modeling Services database, see Loading Domain Models with Data.
use Repository
go
select Id, Folder, Name from [System_Runtime].[Assemblies]
The preceding query returns a list of all modeled .NET Framework assemblies that the user is permitted to see, including multiple versions of the same assembly if installed. The Id field is the unique identifier for the assembly. The Folder field is a reference to the [Repository.Item].[Folders] view that describes the Folder to which each instance belongs. The Name field is the fully qualified name of the assembly. As views are joined and filtered in more complex queries, the potential power of having this data in a relational storage format becomes clear. The preceding example can become more specific, as in the following T-SQL query.
use Repository
go
select A.Id, A.Folder as FolderId,
F2.Name as ParentFolder, F.Name as Folder,
A.Name as AssemblyName
from [System_Runtime].[Assemblies] A
inner join [Repository.Item].[Folders] F ON F.Id = A.Folder
left outer join [Repository.Item].[Folders] F2 ON F2.Id = F.Folder
where A.Name like N'System.Data%'
The preceding example makes two assumptions. First, it assumes that assembly information for one or more assemblies have been added that start with the name System.Data. It also assumes that these assemblies have been added to specific Folders, such as 2.0, that have been named to correspond to the .NET Framework version of the assemblies. The following table shows sample output from the preceding query.
| Id | FolderId | ParentFolder | Folder | AssemblyName |
|---|---|---|---|---|
1 |
105 |
.NET Framework |
1.1 |
System.Data, Version=1.0.5000.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 |
14 |
108 |
.NET Framework |
2.0 |
System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089 |
This more complex query on the [System_Runtime].[Assemblies] view selects only those assemblies whose names begin with the string System.Data. There are two joins to the [Repository.Item].[Folders] view to get the names of the immediate parent Folder, 1.1 or 2.0, as well as the Folder parent, .NET Framework. This same query could be further extended to join with other tables to provide further information about these assemblies. For example, adding joins to the [System_Runtime].[Modules] and [System_Runtime].[Files] views would provide the ability to explore the modules and files associated with these assemblies.
Summary
The data in Modeling Services creates a foundation for data-driven applications, and it also enables users and applications to mine model information through rich queries. The data access technologies for SQL Server are mature, ranging from simple T-SQL queries to LINQ and EDM queries with ADO.NET. The Modeling Services's data access model using views provides the best compatibility with these technologies and also implements a finer-grained security model.
See Also
Concepts
SQL Server Modeling Services Architecture
SQL Server Modeling Services Design Patterns