Using the AddStandardPatterns Stored Procedure Home
Using the AddStandardPatterns Stored Procedure
[This is prerelease documentation and is subject to change in future releases. Blank topics are included as placeholders.]

The [Repository.Item].[AddStandardPatterns] stored procedure adds multiple “Oslo” repository design patterns to existing database tables and views. For more information about specific design patterns, see "Oslo" Repository Design Patterns. The [Repository.Item].[AddStandardPatterns] stored procedure is a convenience to users that want to apply the most common design patterns with a single call. Note that it is not required to call this procedure to add the design patterns. In some scenarios, a user might want to apply only a subset of the standard patterns to a data model. This can be done by calling the specific stored procedures related to each pattern. This topic discusses the requirements for using the [Repository.Item].[AddStandardPatterns] stored procedure and the patterns that this procedure applies.

Requirements

The [Repository.Item].[AddStandardPatterns] stored procedure operates on an individual table and view pair. The table and view must have the following characteristics:

  • The table's primary key must be integer-based (int or bigint).

  • The table's primary key must not be an IDENTITY column.

  • The table must have a column named Folder of type int.

  • The table name must follow the convention of using a plural base name appended by the word "Table".

  • The view name must use the plural base name.

  • The view should filter rows based on a join with the [Repository.Item].[ReadableFolders] function.

Many of these requirements are basic design patterns for tables and views. For more information, see Table Design Patterns and View Design Patterns. The following T-SQL example provides definitions for a [Contact].[AddressesTable] table and a [Contact].[Addresses] view that meet these basic requirements.

Dd819161.Tip(en-us,VS.85).gifTip:
Note that if this schema has been previously added to the database, these statements may fail. You can completely remove the Contact schema and its associated objects using a custom stored procedure. For more information, see  Dropping an "Oslo" Repository SQL Schema.

use Repository
go
create schema [Contact] authorization [RepositoryOwner]
go
create table [Contact].[AddressesTable](
   [Id] bigint NOT NULL,
   [Folder] [Repository.Item].[FolderId] NOT NULL,
   [Street] nvarchar(max) NOT NULL,
   [City] nvarchar(max) NOT NULL,
   [State] nvarchar(max) NOT NULL,
   [ZipCode] int NOT NULL,
   constraint [PK_Addresses] primary key clustered 
   (
      [Id] asc
   ) on [PRIMARY]
) on [PRIMARY]
go

create view [Contact].[Addresses] 
    ([Id], [Folder], [Street], [City], [State], [ZipCode])
as
select top (9223372036854775807) [AT].[Id], [AT].[Folder], [AT].[Street], 
    [AT].[City], [AT].[State], [AT].[ZipCode]
from [Contact].[AddressesTable] as [AT] with (readcommitted)
inner join [Repository.Item].[ReadableFolders]() as [RCV]  on [AT].[Folder] = [RCV].[Folder];
go

Usage

The [Repository.Item].[AddStandardPatterns] stored procedure accepts the following parameters.

ParameterDescription

@schema

The schema containing the target table and view.

@baseName

The base name for the target table and view.

@sequenceSchema

The schema that owns the sequence object. This parameter is optional and defaults to the value of the @schema parameter.

@sequence

The sequence object used to create new unique identifiers. This parameter is optional and defaults to the value of the @baseName parameter.

The following T-SQL example adds the standard design patterns to the previously created [Contact].[AddressesTable] table and [Contact].[Addresses] view.

exec [Repository.Item].[AddStandardPatterns] @schema = [Contact], @baseName = [Addresses]

Standard Design Patterns

This section describes the patterns applied by the [Repository.Item].[AddStandardPatterns] stored procedure. The following changes are applied to the target table and view:

See Also

Fill out a survey about this topic for Microsoft.
© 2009 Microsoft Corporation. All rights reserved.   Terms of Use | Trademarks | Privacy Statement
Page view tracker
Rate the Lightweight library
x
Lightweight builds on ScriptFree (loband) by adding features you've requested: a SearchBox and default code language selection.
Do you like the SearchBox?
Do you like the tabbed code blocks?
How useful is this topic?
Tell us more.
Thanks
x
You're helping to improve MSDN Online.
Feedback
Switch View
Classic
Lightweight Beta
ScriptFree
Switch View