Walkthrough: Extend Database Project Build to Generate Model Statistics

  • Article

You can create a build contributor to perform custom actions when you build a database project. In this walkthrough, you create a build contributor named ModelStatistics that outputs statistics from the database model when you build a database project. Because this build contributor takes parameters when you build, some additional steps are required.

In this walkthrough, you will accomplish the following major tasks:

  • Create a build contributor

  • Install the build contributor

  • Test your build contributor

Prerequisites

You need the following components to complete this walkthrough:

  • You must have installed Visual Studio 2010 Premium or Visual Studio 2010 Ultimate.

  • You must have a database project that contains database objects

Note

This walkthrough is intended for users who are already familiar with the database features of Visual Studio Premium. You are also expected to be familiar with basic Visual Studio concepts, such as how to create a class library and how to use the code editor to add code to a class.

Create a Build Contributor

To create a build contributor, you must perform the following tasks:

  • Create a class library project and add required references

  • Define a class named ModelStatistics that inherits from BuildContributor

  • Override the OnPopulateArguments and OnExecute methods

  • Add a few private helper methods

  • Build the resulting assembly

Note

This contributor will only output when a database project is built by using MSBuild. The report is off by default, but you can override it by providing a property on the MSBuild command line. For an example of how to enable output in the Output Window, see Walkthrough: Extend Database Project Deployment to Analyze the Deployment Plan.

To create a class library project

  1. Create a Visual Basic or Visual C#, class library project named MyBuildContributor.

  2. In Solution Explorer, right-click the project node and then click Add Reference.

  3. Click the .NET tab.

  4. Highlight the Microsoft.Data.Schema and Microsoft.Data.Schema.Sql entries and click OK.

    Next, you start to add code to the class.

To define the ModelStatistics class

  1. In the code editor, update the class1.cs file to match the following using or Imports statements:

    using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Xml.Linq;
using Microsoft.Data.Schema;
using Microsoft.Data.Schema.Build;
using Microsoft.Data.Schema.Extensibility;
using Microsoft.Data.Schema.SchemaModel;
using Microsoft.Data.Schema.Sql;

    Imports System
Imports System.Collections.Generic
Imports System.IO
Imports System.Linq
Imports System.Xml.Linq
Imports Microsoft.Data.Schema
Imports Microsoft.Data.Schema.Build
Imports Microsoft.Data.Schema.Extensibility
Imports Microsoft.Data.Schema.SchemaModel
Imports Microsoft.Data.Schema.Sql

  2. Update the class definition to match the following:

    /// <summary>
/// The ModelStatistics class demonstrates
/// how you can create a class that inherits BuildContributor
/// to perform actions when you build a database project.
/// </summary>
[DatabaseSchemaProviderCompatibility(typeof(SqlDatabaseSchemaProvider))]
    public sealed class ModelStatistics : BuildContributor
    {
    }

    ''' <summary>
''' The ModelStatistics class demonstrates
''' how you can create a class that inherits BuildContributor
''' to perform actions when you build a database project.
''' </summary>
<DatabaseSchemaProviderCompatibility(GetType(SqlDatabaseSchemaProvider))> _
Public NotInheritable Class ModelStatistics
    Inherits BuildContributor
End Class

    Now you have defined your build contributor and used the attribute to indicate that this contributor is compatible with any database schema provider that inherits from SqlDatabaseSchemaProvider.

  3. Next, add the following members. You will use the members to enable this provider to accept command-line build parameters:

            private const string GenerateModelStatistics = "GenerateModelStatistics";
        private const string SortModelStatisticsBy = "SortModelStatisticsBy";
        private const string GenerateModelStatisticsVariable = "$(" + GenerateModelStatistics + ")";
        private const string SortModelStatisticsByVariable = "$(" + SortModelStatisticsBy + ")";

        private enum SortBy { None, Name, Value };
        private static Dictionary<string, SortBy> SortByMap = new Dictionary<string, SortBy>(StringComparer.OrdinalIgnoreCase)
        {
            { "none", SortBy.None },
            { "name", SortBy.Name },
            { "value", SortBy.Value },
        };

        private SortBy _sortBy = SortBy.None;

        Private Const GenerateModelStatistics As String = "GenerateModelStatistics"
    Private Const SortModelStatisticsBy As String = "SortModelStatisticsBy"
    Private Const GenerateModelStatisticsVariable As String = "$(" & GenerateModelStatistics & ")"
    Private Const SortModelStatisticsByVariable As String = "$(" & SortModelStatisticsBy & ")"

    Private Enum SortBy
        None
        Name
        Value
    End Enum
    '' SortByMap maps the command-line parameter string values to the enumeration values
    Private Shared SortByMap As New Dictionary(Of String, SortBy)(StringComparer.OrdinalIgnoreCase)

    Private _sortBy As SortBy = SortBy.None

    These members enable the user to specify whether statistics should be generated by using the GenerateModelStatistics option and to specify how the statistics should be ordered by specifying the SortModelStatisticsBy option.

    Next, you override the OnPopulateArguments method to build the list of arguments to pass to the build contributor.

To override OnPopulateArguments

  • Add the following override method to the ModelStatistics class:

        /// <summary>
    /// Override the OnPopulateArgument method to build a list of arguments from the input
    /// configuration information.
    /// </summary>
        protected override IList<ContributorArgumentConfiguration> OnPopulateArguments()
        {
            List<ContributorArgumentConfiguration> args = new List<ContributorArgumentConfiguration>();

            args.Add(new ContributorArgumentConfiguration(
                name: GenerateModelStatistics,
                value: GenerateModelStatisticsVariable,
                condition: null));

            args.Add(new ContributorArgumentConfiguration(
                name: SortModelStatisticsBy,
                value: SortModelStatisticsByVariable,
                condition: null));

            return args;
        }

        ''' <summary>
    ''' Override the OnPopulateArgument method to build a list of arguments from the input
    ''' configuration information.
    ''' </summary>
    Protected Overrides Function OnPopulateArguments() As System.Collections.Generic.IList(Of Microsoft.Data.Schema.Build.ContributorArgumentConfiguration)
        Dim args As List(Of ContributorArgumentConfiguration) = New List(Of ContributorArgumentConfiguration)

        args.Add(New ContributorArgumentConfiguration(name:=GenerateModelStatistics, _
                                                       value:=GenerateModelStatisticsVariable, _
                                                       condition:=Nothing))
        args.Add(New ContributorArgumentConfiguration(name:=SortModelStatisticsBy, _
                                                       value:=SortModelStatisticsByVariable, _
                                                       condition:=Nothing))
        Return MyBase.OnPopulateArguments()
    End Function

    You build two ContributorArgumentConfiguration objects, and add them to the arguments list.

    Next, you override the OnExecute method to add the code that you want to run when a database project is built.

To override OnExecute

  • Add the following method to your ModelStatistics class:

        /// <summary>
    /// Override the OnExecute method to perform actions when you build a database project.
    /// </summary>
        protected override void OnExecute(BuildContributorContext context, ErrorManager errorsContainer)
        {
            // handle related arguments, passed in as part of
            // the context information.
            bool generateModelStatistics;
            ParseArguments(context.Arguments, errorsContainer, out generateModelStatistics);

            // Only generate statistics if requested to do so
            if (generateModelStatistics)
            {
                // First, output model-wide information, such
                // as the type of database schema provider (DSP)
                // and the collation.
                List<DataSchemaError> args = new List<DataSchemaError>();
                args.Add(new DataSchemaError(" ", ErrorSeverity.Message));
                args.Add(new DataSchemaError("Model Statistics:", ErrorSeverity.Message));
                args.Add(new DataSchemaError("=================", ErrorSeverity.Message));
                args.Add(new DataSchemaError(" ", ErrorSeverity.Message));
                errorsContainer.Add(args, ErrorManager.DefaultCategory);

                var model = context.Model;

                // Start building up the XML that will later
                // be serialized.
                var xRoot = new XElement("ModelStatistics");

                SummarizeModelInfo(model, xRoot, errorsContainer);

                // First, count the elements that are contained 
                // in this model.
                var elements = model.GetElements(typeof(IModelElement), ModelElementQueryFilter.Internal);
                Summarize(elements, element => element.ElementClass.ClassName, "Internal Elements", xRoot, errorsContainer);

                // Now, count the elements that are defined in
                // another model. Examples include built-in types,
                // roles, filegroups, assemblies, and any 
                // referenced objects from another database.
                elements = model.GetElements(typeof(IModelElement), ModelElementQueryFilter.External);
                Summarize(elements, element => element.ElementClass.ClassName, "External Elements", xRoot, errorsContainer);

                // Now, count the number of each type
                // of relationship in the model.
                SurveyRelationships(model, xRoot, errorsContainer);

                // Count the various types of annotations
                // in the model.
                var annotations = model.GetAllAnnotations();
                Summarize(annotations, anno => anno.AnnotationClass.ClassName, "Annotations", xRoot, errorsContainer);

                // finally, count any types of custom data
                // defined for the model.
                var customData = model.GetCustomData();
                Summarize(customData, custom => custom.Category, "Custom Data", xRoot, errorsContainer);

                // Determine where the user wants to save
                // the serialized XML file.
                string outDir;
                if (context.Arguments.TryGetValue("OutDir", out outDir) == false)
                {
                    outDir = ".";
                }
                var filePath = Path.Combine(outDir, "ModelStatistics.xml");
                // Save the XML file and tell the user
                // where it was saved.
                xRoot.Save(filePath);
                DataSchemaError resultArg = new DataSchemaError("Result was saved to " + filePath, ErrorSeverity.Message);
                errorsContainer.Add(resultArg, ErrorManager.BuildCategory);
            }
        }

    ''' <summary>
''' Override the OnExecute method to perform actions when you build a database project.
''' </summary>
Protected Overloads Overrides Sub OnExecute(ByVal context As BuildContributorContext, ByVal errorsContainer As ErrorManager)
    ' handle related arguments, passed in as part of
    ' the context information.
    Dim generateModelStatistics As Boolean
    ParseArguments(context.Arguments, errorsContainer, generateModelStatistics)

    ' Only generate statistics if requested to do so
    If generateModelStatistics Then
        ' First, output model-wide information, such
        ' as the type of database schema provider (DSP)
        ' and the collation.
        Dim args As New List(Of DataSchemaError)()
        args.Add(New DataSchemaError(" ", ErrorSeverity.Message))
        args.Add(New DataSchemaError("Model Statistics:", ErrorSeverity.Message))
        args.Add(New DataSchemaError("=================", ErrorSeverity.Message))
        args.Add(New DataSchemaError(" ", ErrorSeverity.Message))
        errorsContainer.Add(args, ErrorManager.DefaultCategory)

        Dim model = context.Model

        ' Start building up the XML that will later
        ' be serialized.
        Dim xRoot = New XElement("ModelStatistics")

        SummarizeModelInfo(model, xRoot, errorsContainer)

        ' First, count the elements that are contained 
        ' in this model.
        Dim elements = model.GetElements(GetType(IModelElement), ModelElementQueryFilter.Internal)
        Summarize(elements, Function(element) element.ElementClass.ClassName, "Internal Elements", xRoot, errorsContainer)

        ' Now, count the elements that are defined in
        ' another model. Examples include built-in types,
        ' roles, filegroups, assemblies, and any 
        ' referenced objects from another database.
        elements = model.GetElements(GetType(IModelElement), ModelElementQueryFilter.External)
        Summarize(elements, Function(element) element.ElementClass.ClassName, "External Elements", xRoot, errorsContainer)

        ' Now, count the number of each type
        ' of relationship in the model.
        SurveyRelationships(model, xRoot, errorsContainer)

        ' Count the various types of annotations
        ' in the model.
        Dim annotations = model.GetAllAnnotations()
        Summarize(annotations, Function(anno) anno.AnnotationClass.ClassName, "Annotations", xRoot, errorsContainer)

        ' finally, count any types of custom data
        ' defined for the model.
        Dim customData = model.GetCustomData()
        Summarize(customData, Function([custom]) [custom].Category, "Custom Data", xRoot, errorsContainer)

        ' Determine where the user wants to save
        ' the serialized XML file.
        Dim outDir As String
        If context.Arguments.TryGetValue("OutDir", outDir) = False Then
            outDir = "."
        End If
        Dim filePath = Path.Combine(outDir, "ModelStatistics.xml")
        ' Save the XML file and tell the user
        ' where it was saved.
        xRoot.Save(filePath)
        Dim resultArg As New DataSchemaError("Result was saved to " & filePath, ErrorSeverity.Message)
        errorsContainer.Add(resultArg, ErrorManager.BuildCategory)
    End If
End Sub

    The OnExecute method is passed a BuildContributorContext object that provides access to any specified arguments, the database model, build properties, and extension files. In this example, we retrieve the model, and then call helper functions to output information about the model. The method is also passed an ErrorManager to use to report any errors that occur.

    Additional types and methods of interest include the following: DataSchemaModel, ModelStore, GetElements, GetAllAnnotations, GetCustomData , and ModelElement.

    Next, you define the helper methods that examine the details of the model.

To add helper methods that generate the statistics

  • First, add the skeletons of the four helper methods by adding the following code:

            /// <summary>
        /// Examine the arguments provided by the user
        /// to determine if model statistics should be generated
        /// and, if so, how the results should be sorted.
        /// </summary>
        private void ParseArguments(IDictionary<string, string> arguments, ErrorManager errorsContainer, out bool generateModelStatistics)
        {
        }

        /// <summary>
        /// Retrieve the database schema provider for the
        /// model and the collation of that model.
        /// Results are output to the console and added to the XML
        /// being constructed.
        /// </summary>
        private static void SummarizeModelInfo(DataSchemaModel model, XElement xContainer, ErrorManager errorsContainer)
        {
        }

        /// <summary>
        /// For a provided list of model elements, count the number
        /// of elements for each class name, sorted as specified
        /// by the user.
        /// Results are output to the console and added to the XML
        /// being constructed.
        /// </summary>
        private void Summarize<T>(IList<T> set, Func<T, string> groupValue, string category, XElement xContainer, ErrorManager errorsContainer)
        {
        }

        /// <summary>
        /// Iterate over all model elements, counting the
        /// styles and types for relationships that reference each 
        /// element
        /// Results are output to the console and added to the XML
        /// being constructed.
        /// </summary>
        private static void SurveyRelationships(DataSchemaModel model, XElement xContainer, ErrorManager errorsContainer)
        {
        }

        /// <summary>
        /// Performs the actual output for this contributor,
        /// writing the specified set of statistics, and adding any 
        /// output information to the XML being constructed.
        /// </summary>
        private static void OutputResult<T>(string category, Dictionary<string, T> statistics, XElement xContainer, ErrorManager errorsContainer)
        {
        }

        ''' <summary> 
    ''' This method goes through the provided arguments to the contributor and determines what 
    ''' parameters and parameter values were provided by the user.
    ''' </summary> 
    Private Sub ParseArguments(ByVal arguments As IDictionary(Of String, String), ByVal errorsContainer As ErrorManager, ByRef generateModelStatistics__1 As Boolean)
    End Sub

    ''' <summary> 
    ''' This method collects and outputs information about the model itself. 
    ''' </summary> 
Private Shared Sub SummarizeModelInfo(ByVal model As DataSchemaModel, ByVal xContainer As XElement, ByVal errorsContainer As ErrorManager)
    End Sub

    ''' <summary> 
    ''' This method goes counts the number of elements in specific categories from the provided element list.
    ''' </summary> 
Private Sub Summarize(Of T)(ByVal [set] As IList(Of T), ByVal groupValue As Func(Of T, String), ByVal category As String, ByVal xContainer As XElement, ByVal errorsContainer As ErrorManager)
    End Sub

    ''' <summary> 
    ''' This method counts the number of relationships of each type that reference elements in the model 
    ''' </summary> 
Private Shared Sub SurveyRelationships(ByVal model As DataSchemaModel, ByVal xContainer As XElement, ByVal errorsContainer As ErrorManager)
    End Sub

    ''' <summary> 
    ''' This method processes the provided data, outputting it to the console and adding the information  
    ''' to the provided XML.
    ''' </summary> 
Private Shared Sub OutputResult(Of T)(ByVal category As String, ByVal statistics As Dictionary(Of String, T), ByVal xContainer As XElement, ByVal errorsContainer As ErrorManager)
    End Sub

To define the body of the ParseArguments method

  • Add the following code to the body of the ParseArguments method:

                // By default, we don't generate model statistics
            generateModelStatistics = false;

            // see if the user provided the GenerateModelStatistics 
            // option and if so, what value was it given.
            string valueString;
            arguments.TryGetValue(GenerateModelStatistics, out valueString);
            if (string.IsNullOrWhiteSpace(valueString) == false)
            {
                if (bool.TryParse(valueString, out generateModelStatistics) == false)
                {
                    generateModelStatistics = false;

                    // The value was not valid from the end user
                    DataSchemaError invalidArg = new DataSchemaError(
                        GenerateModelStatistics + "=" + valueString + " was not valid.  It can be true or false", ErrorSeverity.Error);
                    errorsContainer.Add(invalidArg, ErrorManager.BuildCategory);
                    return;
                }
            }

            // Only worry about sort order if the user requested
            // that we generate model statistics.
            if (generateModelStatistics)
            {
                // see if the user provided the sort option and
                // if so, what value was provided.
                arguments.TryGetValue(SortModelStatisticsBy, out valueString);
                if (string.IsNullOrWhiteSpace(valueString) == false)
                {
                    SortBy sortBy;
                    if (SortByMap.TryGetValue(valueString, out sortBy))
                    {
                        _sortBy = sortBy;
                    }
                    else
                    {
                        // The value was not valid from the end user
                        DataSchemaError invalidArg = new DataSchemaError(
                            SortModelStatisticsBy + "=" + valueString + " was not valid.  It can be none, name, or value", ErrorSeverity.Error);
                        errorsContainer.Add(invalidArg, ErrorManager.BuildCategory);
                    }
                }
            }

            ' By default, we don't generate model statistics 
        generateModelStatistics__1 = False

        ' see if the user provided the GenerateModelStatistics 
        ' option and if so, what value was it given. 
        Dim valueString As String = ""
        arguments.TryGetValue(GenerateModelStatistics, valueString)
        If String.IsNullOrWhiteSpace(valueString) = False Then
            If Boolean.TryParse(valueString, generateModelStatistics__1) = False Then
                generateModelStatistics__1 = False

                ' The value was not valid from the end user 
                Dim invalidArg As New DataSchemaError((GenerateModelStatistics & "=") + valueString & " was not valid. It can be true or false", ErrorSeverity.[Error])
                errorsContainer.Add(invalidArg, ErrorManager.BuildCategory)
                Exit Sub
            End If
        End If

        If SortByMap.Count = 0 Then
            '' haven't populated the map yet
            SortByMap.Add("none", SortBy.None)
            SortByMap.Add("name", SortBy.Name)
            SortByMap.Add("value", SortBy.Value)
        End If

        ' Only worry about sort order if the user requested 
        ' that we generate model statistics. 
        If generateModelStatistics__1 Then
            ' see if the user provided the sort option and 
            ' if so, what value was provided. 
            arguments.TryGetValue(SortModelStatisticsBy, valueString)
            If String.IsNullOrWhiteSpace(valueString) = False Then
                Dim localSortBy As SortBy
                If SortByMap.TryGetValue(valueString, localSortBy) Then
                    _sortBy = localSortBy
                Else
                    ' The value was not valid from the end user 
                    Dim invalidArg As New DataSchemaError((SortModelStatisticsBy & "=") + valueString & " was not valid. It can be none, name, or value", ErrorSeverity.[Error])
                    errorsContainer.Add(invalidArg, ErrorManager.BuildCategory)
                End If
            End If
        End If

    Types and methods of interest include: ErrorManager and DataSchemaError.

To define the body of the SummarizeModelInfo method

  • Add the following code to the body of the SummarizeModelInfo method:

                // use a Dictionary to accumulate the information
            // that will later be output.
            var info = new Dictionary<string, string>();

            // Two things of interest: the database schema
            // provider for the model, and the language id and
            // case sensitivity of the collation of that
            // model
            info.Add("DSP", model.DatabaseSchemaProvider.GetType().Name);
            info.Add("Collation", string.Format("{0}({1})", model.Collation.Lcid, model.Collation.CaseSensitive ? "CS" : "CI"));

            // Output the accumulated information and add it to 
            // the XML.
            OutputResult("Basic model info", info, xContainer, errorsContainer);

        ' use a Dictionary to accumulate the information
    ' that will later be output.
    Dim info = New Dictionary(Of String, String)()

    ' Two things of interest: the database schema
    ' provider for the model, and the language id and
    ' case sensitivity of the collation of that
    ' model
    info.Add("DSP", model.DatabaseSchemaProvider.[GetType]().Name)
    info.Add("Collation", String.Format("{0}({1})", model.Collation.Lcid, If(model.Collation.CaseSensitive, "CS", "CI")))

    ' Output the accumulated information and add it to 
    ' the XML.
    OutputResult("Basic model info", info, xContainer, errorsContainer)

    Key types and members here include the following: DataSchemaModel, ModelStore, DatabaseSchemaProvider, and ModelCollation.

To add the body to the Summarize method

  • Add the following code to the body of the Summarize method:

                // Use a Dictionary to keep all summarized information
            var statistics = new Dictionary<string, int>();

            // For each element in the provided list,
            // count items based on the specified grouping
            var groups =
                from item in set
                group item by groupValue(item) into g
                select new { g.Key, Count = g.Count() };

            // order the groups as requested by the user
            if (this._sortBy == SortBy.Name)
            {
                groups = groups.OrderBy(group => group.Key);
            }
            else if (this._sortBy == SortBy.Value)
            {
                groups = groups.OrderBy(group => group.Count);
            }

            // build the Dictionary of accumulated statistics
            // that will be passed along to the OutputResult method.
            foreach (var item in groups)
            {
                statistics.Add(item.Key, item.Count);
            }

            statistics.Add("subtotal", set.Count);
            statistics.Add("total items", groups.Count());

            // output the results, and build up the XML
            OutputResult(category, statistics, xContainer, errorsContainer);

        ' Use a Dictionary to keep all summarized information
    Dim statistics = New Dictionary(Of String, Integer)()

    ' For each element in the provided list,
    ' count items based on the specified grouping
    Dim groups = From item In [set] _ 
        Group item By groupValue(item)Intog _ 
        Select New ()

    ' order the groups as requested by the user
    If Me._sortBy = SortBy.Name Then
        groups = groups.OrderBy(Function(group) group.Key)
    ElseIf Me._sortBy = SortBy.Value Then
        groups = groups.OrderBy(Function(group) group.Count)
    End If

    ' build the Dictionary of accumulated statistics
    ' that will be passed along to the OutputResult method.
    For Each item In groups
        statistics.Add(item.Key, item.Count)
    Next

    statistics.Add("subtotal", [set].Count)
    statistics.Add("total items", groups.Count())

    ' output the results, and build up the XML
    OutputResult(category, statistics, xContainer, errorsContainer)

    Again, the code comments provide the relevant information.

To add the body to the SurveyRelationships method

  • Add the following code to the body to the SurveyRelationships method:

                // get a list that contains all elements in the model
            var elements = model.GetElements(typeof(IModelElement), ModelElementQueryFilter.All);
            // We are interested in all relationships that
            // reference each element.
            var entries =
                from element in elements
                from entry in element.GetReferencedRelationshipEntries()
                select entry;

            // initialize our counting buckets
            var single = 0;
            var many = 0;
            var composing = 0;
            var hierachical = 0;
            var peer = 0;
            var reverse = 0;

            // process each relationship, adding to the 
            // appropriate bucket for style and type.
            foreach (var entry in entries)
            {
                switch (entry.RelationshipClass.ModelRelationshipCardinalityStyle)
                {
                    case ModelRelationshipCardinalityStyle.Many:
                        ++many;
                        break;
                    case ModelRelationshipCardinalityStyle.Single:
                        ++single;
                        break;
                    default:
                        break;
                }

                switch (entry.RelationshipClass.ModelRelationshipType)
                {
                    case ModelRelationshipType.Composing:
                        ++composing;
                        break;
                    case ModelRelationshipType.Hierarchical:
                        ++hierachical;
                        break;
                    case ModelRelationshipType.Peer:
                        ++peer;
                        break;
                    case ModelRelationshipType.Reverse:
                        // We count these, but reverse relationships
                        // are not actually stored*, so the count
                        // will always be zero.
                        // * - reverse relationships are generated
                        // dynamically when they are accessed.
                        ++reverse;
                        break;
                    default:
                        break;
                }
            }

            // build a dictionary of data to pass along
            // to the OutputResult method.
            var stat = new Dictionary<string, int>();
            stat.Add("Multiple", many);
            stat.Add("Single", single);
            stat.Add("Composing", composing);
            stat.Add("Hierarchical", hierachical);
            stat.Add("Peer", peer);
            // As noted, no need to output the count of reverse
            // relationships as it will always be zero.
            //stat.Add("Reverse", reverse);
            stat.Add("subtotal", entries.Count());

            OutputResult("Relationships", stat, xContainer, errorsContainer);

            ' get a list that contains all elements in the model 
        Dim elements = model.GetElements(GetType(IModelElement), ModelElementQueryFilter.All)
        ' We are interested in all relationships that 
        ' reference each element. 
        Dim entries = From element In elements _
            From entry In element.GetReferencedRelationshipEntries() _
            Select entry

        ' initialize our counting buckets 
        Dim [single] = 0
        Dim many = 0
        Dim composing = 0
        Dim hierachical = 0
        Dim peer = 0
        Dim reverse = 0

        ' process each relationship, adding to the 
        ' appropriate bucket for style and type. 
        For Each entry In entries
            Select Case entry.RelationshipClass.ModelRelationshipCardinalityStyle
                Case ModelRelationshipCardinalityStyle.Many
                    many += 1
                    Exit Select
                Case ModelRelationshipCardinalityStyle.[Single]
                    [single] += 1
                    Exit Select
                Case Else
                    Exit Select
            End Select

            Select Case entry.RelationshipClass.ModelRelationshipType
                Case ModelRelationshipType.Composing
                    composing += 1
                    Exit Select
                Case ModelRelationshipType.Hierarchical
                    hierachical += 1
                    Exit Select
                Case ModelRelationshipType.Peer
                    peer += 1
                    Exit Select
                Case ModelRelationshipType.Reverse
                    ' We count these, but reverse relationships 
                    ' are not actually stored*, so the count 
                    ' will always be zero. 
                    ' * - reverse relationships are generated 
                    ' dynamically when they are accessed. 
                    reverse += 1
                    Exit Select
                Case Else
                    Exit Select
            End Select
        Next

        ' build a dictionary of data to pass along 
        ' to the OutputResult method. 
        Dim stat = New Dictionary(Of String, Integer)()
        stat.Add("Multiple", many)
        stat.Add("Single", [single])
        stat.Add("Composing", composing)
        stat.Add("Hierarchical", hierachical)
        stat.Add("Peer", peer)
        ' As noted, no need to output the count of reverse 
        ' relationships as it will always be zero. 
        'stat.Add("Reverse", reverse); 
        stat.Add("subtotal", entries.Count())

    OutputResult("Relationships", stat, xContainer, errorsContainer)

    The code comments explain the key aspects of this method. Referenced types and methods of note include the following: DataSchemaModel, ModelStore, GetElements, and ModelElement.

To add the body of the OutputResult method

  • Add the following body to the OutputResult method:

                var maxLen = statistics.Max(stat => stat.Key.Length) + 2;
            var format = string.Format("{{0, {0}}}: {{1}}", maxLen);

            List<DataSchemaError> args = new List<DataSchemaError>();
            args.Add(new DataSchemaError(category, ErrorSeverity.Message));
            args.Add(new DataSchemaError("-----------------", ErrorSeverity.Message));

            // Remove any blank spaces from the category name
            var xCategory = new XElement(category.Replace(" ", ""));
            xContainer.Add(xCategory);

            foreach (var item in statistics)
            {
                //Console.WriteLine(format, item.Key, item.Value);
                var entry = string.Format(format, item.Key, item.Value);
                args.Add(new DataSchemaError(entry, ErrorSeverity.Message));
                // Replace any blank spaces in the element key with
                // underscores.
                xCategory.Add(new XElement(item.Key.Replace(' ', '_'), item.Value));
            }
            args.Add(new DataSchemaError(" ", ErrorSeverity.Message));
            errorsContainer.Add(args, ErrorManager.BuildCategory);

        Dim maxLen = statistics.Max(Function(stat) stat.Key.Length) + 2
    Dim format = String.Format("{{0, {0}}}: {{1}}", maxLen)

    Dim args As New List(Of DataSchemaError)()
    args.Add(New DataSchemaError(category, ErrorSeverity.Message))
    args.Add(New DataSchemaError("-----------------", ErrorSeverity.Message))

    ' Remove any blank spaces from the category name
    Dim xCategory = New XElement(category.Replace(" ", ""))
    xContainer.Add(xCategory)

    For Each item In statistics
        'Console.WriteLine(format, item.Key, item.Value);
        Dim entry = String.Format(format, item.Key, item.Value)
        args.Add(New DataSchemaError(entry, ErrorSeverity.Message))
        ' Replace any blank spaces in the element key with
        ' underscores.
        xCategory.Add(New XElement(item.Key.Replace(" "c, "_"c), item.Value))
    Next
    args.Add(New DataSchemaError(" ", ErrorSeverity.Message))
    errorsContainer.Add(args, ErrorManager.BuildCategory)

  • Save the changes to Class1.cs.

    Next, you build the class library.

To sign and build the assembly

  1. On the Project menu, click MyBuildContributor Properties.

  2. Click the Signing tab.

  3. Click Sign the assembly.

  4. In Choose a strong name key file, click <New>.

  5. In the Create Strong Name Key dialog box, in Key file name, type MyRefKey.

  6. (optional) You can specify a password for your strong name key file.

  7. Click OK.

  8. On the File menu, click Save All.

  9. On the Build menu, click Build Solution.

    Next, you must install and register the assembly so that it will be loaded when you build database projects.

Install a Build Contributor

To install a build contributor, you must perform the following tasks:

  • Copy the assembly and associated .pdb file to the Extensions folder

  • Create an Extensions.xml file to register the build contributor so it is loaded when you build database projects

To install the MyBuildContributor assembly

  1. Create a folder named MyExtensions in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions folder.

  2. Copy your signed assembly (MyBuildContributor.dll) to the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.

    Note

    We recommend that you do not copy your XML files directly into the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions folder. If you use a subfolder instead, you will prevent accidental changes to the other files that are provided with Visual Studio Premium.

    Next, you must register your assembly, a type of feature extension, so that it will appear in Visual Studio Premium.

To register the MyBuildContributor assembly

  1. On the View menu, click Other Windows, and then click Command Window to open the Command window.

  2. In the Command window, type the following code. For FilePath, substitute the path and file name of your compiled .dll file. Include the quotation marks around the path and file name.

    Note

    By default, the path of your compiled .dll file is YourSolutionPath\bin\Debug or YourSolutionPath\bin\Release.

    ? System.Reflection.Assembly.LoadFrom("FilePath").FullName

    ? System.Reflection.Assembly.LoadFrom(@"FilePath").FullName

  3. Press Enter.

  4. Copy the resultant line to the Clipboard. The line should resemble the following:

    "MyBuildContributor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=nnnnnnnnnnnnnnnn"

  5. Open a plain-text editor, such as Notepad.

    Important

    On Windows Vista and Microsoft Windows Server 2008, open the editor as an administrator so that you can save the file to your Program Files folder.

  6. Provide the following information. You can paste the information that you copied in step 4. Specify your own assembly name, public key token, and extension type:

    <?xml version="1.0" encoding="utf-8" ?> 
<extensions assembly="" version="1" xmlns="urn:Microsoft.Data.Schema.Extensions" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="urn:Microsoft.Data.Schema.Extensions Microsoft.Data.Schema.Extensions.xsd">
  <extension type="MyBuildContributor.ModelStatistics" 
assembly="MyBuildContributor, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<enter key here>" enabled="true" />
</extensions>

    You use this XML file to register the class that inherits from BuildContributor .

  7. Save the file as MyBuildContributor.extensions.xml in the %Program Files%\Microsoft Visual Studio 10.0\VSTSDB\Extensions\MyExtensions folder.

    Note

    In Save as type, verify that you specified All Files, or Notepad will ignore the extension and save the files with the .txt extension.

  8. Close Visual Studio.

    Next, you will build a database project to test your contributor.

Test your Build Contributor

To test your build contributor, you must perform the following tasks:

  • Add properties to the .dbproj file that you plan to build

  • Build the database project by using MSBuild and providing the appropriate parameters

Add Properties to the Database Project (.dbproj) File

Because this build contributor accepts command-line parameters from MSBuild, you must modify the database project to enable users to pass those parameters through MSBuild. You can do this in one of two ways. You can manually modify the .dbproj file to add the required arguments. You might choose to do this if you only build your database project by using MSBuild. If you choose this option, add the following statements to the .dbproj file between the last </ItemGroup> node in the file and the final </Project> node:

  <ItemGroup>
    <BuildContributorArgument Include="OutDir=$(OutDir)" />
    <BuildContributorArgument Include="GenerateModelStatistics=$(GenerateModelStatistics)" />
    <BuildContributorArgument Include="SortModelStatisticsBy=$(SortModelStatisticsBy)" />
  </ItemGroup>

The easier method is to load your database project into Visual Studio build the database project once, then exit Visual Studio. Save changes to your project when you exit. If you use this method, the additional arguments are added automatically to your database project file (.dbproj).

After you have followed one of these approaches, you can use MSBuild to pass in the parameters for command-line builds.

To add properties to the database project file

  1. Open the database project in Visual Studio. For more information, see How to: Open a Database or Server Project.

  2. Build your database project. For more information, see How to: Build a Database Project to Generate a Compiled Schema (.dbschema) File.

  3. Close Visual Studio. Save your solution and project if you are prompted to do so.

    Next you can build your database project by using MSBuild and specifying arguments to enable your build contributor to generate model statistics.

Build the Database Project

To rebuild your database project by using MSBuild and generate statistics

  1. Open a Visual Studio Command Prompt. On the Start menu, click All Programs, click Microsoft Visual Studio 2010, click Visual Studio Tools, and click Visual Studio Command Prompt (2010).

  2. At the command prompt, navigate to the folder that contains your database project.

  3. At the command prompt, type the following command line:

    MSBuild /t:Rebuild MyDatabaseProject.dbproj /p:GenerateModelStatistics=true /p:SortModelStatisticsBy=name /p:OutDir=.\

    Replace MyDatabaseProject with the name of the database project that you want to build. If you had changed the project after you last built it, you could use /t:Build instead of /t:Rebuild.

    Output such as the following appears:

Microsoft (R) Build Engine Version 4.0.20817.0
[Microsoft .NET Framework, Version 4.0.20817.0]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 8/19/2009 2:46:04 PM.
Project "C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\MyDatabaseProject.dbproj" on node 1 (Rebuild target(s)).
CoreClean:
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject_Script.PreDeployment.sql".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject_Script.PostDeployment.sql".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject_Database.sqlsettings".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject_Database.sqldeployment".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject_Database.sqlcmdvars".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject.deploymanifest".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject.dbschema".
  Deleting file "c:\users\UserName\documents\visual studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\obj\Debug\MyDatabaseProject.dbschema".
DspBuild:
  Creating a model to represent the project...
  Loading project files...
  Building the project model and resolving object interdependencies...
  Validating the project model...

Model Statistics:
=================

Basic model info
-----------------
        DSP: Sql100DatabaseSchemaProvider
  Collation: 1033(CI)

Internal Elements
-----------------
                    ISql100DatabaseOptions: 1
                          ISql100Filegroup: 1
                      ISql100FullTextIndex: 3
                              ISql100Index: 95
  ISql100MultiStatementTableValuedFunction: 1
               ISql100PrimaryKeyConstraint: 71
                          ISql100Procedure: 10
                     ISql100ScalarFunction: 10
                       ISql100SimpleColumn: 481
                ISql100SubroutineParameter: 41
                              ISql100Table: 71
                   ISql100UniqueConstraint: 1
                               ISql100View: 20
                           ISql100XmlIndex: 8
                     ISql90CheckConstraint: 89
                      ISql90ComputedColumn: 302
                  ISql90DatabaseDdlTrigger: 1
                   ISql90DefaultConstraint: 152
                          ISql90DmlTrigger: 10
                                ISql90File: 3
                ISql90ForeignKeyConstraint: 90
                     ISql90FullTextCatalog: 1
                               ISql90Route: 1
                              ISql90Schema: 5
           ISql90TriggerEventTypeSpecifier: 125
                       ISql90TypeSpecifier: 524
                 ISql90UserDefinedDataType: 6
                 ISql90XmlSchemaCollection: 6
                    ISql90XmlTypeSpecifier: 8
                   ISqlDynamicColumnSource: 5
                      ISqlExtendedProperty: 1161
          ISqlFullTextIndexColumnSpecifier: 4
            ISqlIndexedColumnSpecification: 220
          ISqlScriptFunctionImplementation: 11
                                  subtotal: 3538
                               total items: 34

External Elements
-----------------
           ISql100Filegroup: 1
               ISql100Queue: 3
             ISql100Service: 3
             ISql90Assembly: 1
       ISql90AssemblySource: 1
            ISql90ClrMethod: 151
   ISql90ClrMethodParameter: 138
          ISql90ClrProperty: 16
             ISql90Contract: 6
             ISql90Endpoint: 5
          ISql90MessageType: 14
                 ISql90Role: 10
               ISql90Schema: 13
        ISql90TypeSpecifier: 305
                 ISql90User: 4
  ISql90UserDefinedDataType: 1
      ISql90UserDefinedType: 3
            ISqlBuiltInType: 32
             ISqlServerRole: 9
                   subtotal: 716
                total items: 19

Relationships
-----------------
      Multiple: 3002
        Single: 4017
     Composing: 2332
  Hierarchical: 1812
          Peer: 2875
      subtotal: 7019

Annotations
-----------------
                         ExternalPropertyAnnotation: 1475
                        ExternalReferenceAnnotation: 187
                           ExternalSourceAnnotation: 2
                         ModuleInvocationAnnotation: 20
                      ParameterOrVariableAnnotation: 68
  ResolveTimeVerifiedDanglingRelationshipAnnotation: 119
                      SqlInlineConstraintAnnotation: 1
                SqlModelBuilderResolvableAnnotation: 7825
                        SysCommentsObjectAnnotation: 52
                                           subtotal: 9749
                                        total items: 9

Custom Data
-----------------
          AnsiNulls: 1
   ClrTypesDbSchema: 1
  CompatibilityMode: 1
    ModelCapability: 1
        Permissions: 1
   QuotedIdentifier: 1
           subtotal: 6
        total items: 6

Result was saved to .\ModelStatistics.xml

  Writing model to MyDatabaseProject.dbschema...
CopyFilesToOutputDirectory:
  Copying file from "obj\Debug\MyDatabaseProject.dbschema" to ".\sql\debug\MyDatabaseProject.dbschema".
  MyDatabaseProject -> C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\sql\debug\MyDatabaseProject.dbschema
Done Building Project "C:\Users\UserName\Documents\Visual Studio 2010\Projects\MyDatabaseProject\MyDatabaseProject\MyDatabaseProject.dbproj" (Rebuild target(s)).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:11.20

  1. Open ModelStatistics.xml and examine the contents.

    The results that were reported are also persisted to the XML file.

Next Steps

You could create additional tools to perform processing of the output XML file. This is just one example of a build contributor. You could, for example, create a build contributor to output a data dictionary file as part of your build.

See Also

Tasks

Walkthrough: Extend Database Project Deployment to Analyze the Deployment Plan

Concepts

Customize Database Build and Deployment by Using Build and Deployment Contributors

Extending the Database Features of Visual Studio