Document SQL Server Databases with CatFactory.UI

• GitHub repository

Introduction

In enterprise it's a good practice to have documentation, usually we have documentation for source code, at least we add comments in source code to explain some details about the implementation, but isn't the same case for databases.

Some companies have a directory in enterprise portal with documents (Word) with all documentation for databases.

In this guide we'll work using CatFactory.SqlServer package to add or update description for tables, table's columns, views and view's columns.

There are another options to document SQL Server databases, for example: Dataedo, but this project it's a good chance to show CatFactory posibilities.

Background

CatFactory provides the schema for a database: tables, views and another objects; adding improvements for CatFactory.SqlServer package development team added import extendend properties from database, usually developers use MS_Description extendend property to store descriptions.

So, CatFactory retrieve all values for MS_Description extended property and add those values in Description property for CatFactory definitions.

There is a tip about to generate a data dictionary with CatFactory, soon there will an update for tip in order to use the latest version of CatFactory.SqlServer package.

Database

Take a look for sample database to understand each component in architecture. In this database there are 4 schemas: Dbo, HumanResources, Warehouse and Sales.

Each schema represents a division on store company, keep this in mind because all code is designed following this aspect; at this moment this code only implements features for Production and Sales schemas.

All tables have a primary key with one column and have columns for creation, last update and concurrency token.

You can found the scripts for database in this link: OnLine Store Database Scripts on GitHub.

Please remember: This is a sample database, only for demonstration of concepts.

Schema	Name
dbo	ChangeLog
dbo	ChangeLogExclusion
dbo	Country
dbo	CountryCurrency
dbo	Currency
dbo	EventLog
HumanResources	Employee
HumanResources	EmployeeAddress
HumanResources	EmployeeEmail
Sales	Customer
Sales	OrderDetail
Sales	OrderHeader
Sales	OrderStatus
Sales	PaymentMethod
Sales	Shipper
Warehouse	Location
Warehouse	Product
Warehouse	ProductCategory
Warehouse	ProductInventory

Using the code

The first concept with CatFactory is import an existing database.

In order to add documentation, we need to install CatFactory.SqlServer package:

Name	Version
CatFactory.SqlServer	1.0.0-beta-sun-build16	Allows import an existing database from SQL Server instance

To start with documentation, import database from SQL Server instance:

// Create instance for database factory
var databaseFactory = new SqlServerDatabaseFactory
{
	DatabaseImportSettings = new DatabaseImportSettings
	{
		ConnectionString = "server=(local);database=Store;integrated security=yes;",
		ExtendedProperties =
		{
			"MS_Description"
		}
	}
};

// Import database
var database = databaseFactory.Import();

Import method extracts extended properties for tables, views and columns in tables and views, invokes fn_listextendedproperty table function that allows to retrieve extended properties.

This is the definition for ExtendedProperty class:

using System.Diagnostics;

namespace CatFactory.Mapping
{
    [DebuggerDisplay("Name={Name}, Value={Value}")]
    public class ExtendedProperty
    {
        public ExtendedProperty();

        public string Name { get; set; }
        
        public string Value { get; set; }
        
        public string Level0Type { get; set; }
        
        public string Level0Name { get; set; }
        
        public string Level1Type { get; set; }
        
        public string Level1Name { get; set; }
        
        public string Level2Type { get; set; }
        
        public string Level2Name { get; set; }
    }
}

Read Extended Properties

// Get table from database
var table = database.FindTable("Production.Product");

// Get extended properties for table
foreach (var extendedProperty in table.ExtendedProperties)
{
	Console.WriteLine("Name: '{0}', Value: '{1}'", extendedProperty.Name, extendedProperty.Value);
}

Extension Methods for SqlServerDatabaseFactory

Name	Description
AddExtendedProperty	Adds extended property for database object
UpdateExtendedProperty	Updates extended property for database object
AddOrUpdateExtendedProperty	Adds or updates extended property for database object
DropExtendedProperty	Drops extended property for database object

These methods allow to manage extended properties for tables, views and columns in tables and views.

Working with Tables

Add Extended Property for Table

// Get table from database
var table = database.FindTable("Production.Product");

// Add extended property
databaseFactory.AddExtendedProperty(table, "MS_Description", "Products catalog");

Add Extended Property for Column in Table

// Get table from database
var table = database.FindTable("Production.Product");

// Get column from table
var column = table.GetColumn("ProductName");

// Add extended property
databaseFactory.AddExtendedProperty(table, column, "MS_Description", "Name for product");

AddExtendedProperty method invokes sp_addextendedproperty stored procedure.

Update Extended Property for Table

// Get table from database
var table = database.FindTable("Production.Product");

// Update extended property
databaseFactory.UpdateExtendedProperty(table, "MS_Description", "Products catalog for store");

Update Extended Property for Column in Table

// Get table from database
var table = database.FindTable("Production.Product");

// Get column from table
var column = table.GetColumn("UnitPrice");

// Update extended property
databaseFactory.UpdateExtendedProperty(table, column, "MS_Description", "Unit price for product in USD");

UpdateExtendedProperty method invokes sp_updateextendedproperty stored procedure.

Add or Update Extended Property for Table

// Get table from database
var table = database.FindTable("Production.Product");

// Update extended property
databaseFactory.AddOrUpdateExtendedProperty(table, "MS_Description", "Products catalog for store");

Add or Update Extended Property for Column in Table

// Get table from database
var table = database.FindTable("Production.Product");

// Get column from table
var column = table.GetColumn("Description");

// Update extended property
databaseFactory.AddOrUpdateExtendedProperty(table, "MS_Description", "Description for product");

AddOrUpdateExtendedProperty method invokes sp_addextendedproperty or sp_updateextendedproperty stored procedures.

Drop Extended Property for Table

// Get table from database
var table = database.FindTable("Production.Product");

// Update extended property
databaseFactory.DropExtendedProperty(table, "MS_Description");

Drop Extended Property for Column in Table

// Get table from database
var table = database.FindTable("Production.Product");

// Get column from table
var column = table.GetColumn("UnitPrice");

// Update extended property
databaseFactory.DropExtendedProperty(table, column, "MS_Description");

DropExtendedProperty method invokes sp_dropextendedproperty stored procedure.

Working with Views

Add Extended Property for View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Add extended property
databaseFactory.AddExtendedProperty(view, "MS_Description", "Summary for orders");

Add Extended Property for Column in View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Get column from view
var column = view.GetColumn("ShipperName");

// Add extended property
databaseFactory.AddExtendedProperty(view, column, "MS_Description", "Name for shipper");

AddExtendedProperty method invokes sp_addextendedproperty stored procedure.

Update Extended Property for View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Update extended property
databaseFactory.UpdateExtendedProperty(view, "MS_Description", "Details information for orders");

Update Extended Property for Column in View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Get column from view
var column = view.GetColumn("Total");

// Update extended property
databaseFactory.UpdateExtendedProperty(view, column, "MS_Description", "Total for order in USD");

UpdateExtendedProperty method invokes sp_updateextendedproperty stored procedure.

Add or Update Extended Property for View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Update extended property
databaseFactory.AddOrUpdateExtendedProperty(view, "MS_Description", "Details information for orders in store");

Add or Update Extended Property for Column in View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Get column from view
var column = view.GetColumn("Total");

// Update extended property
databaseFactory.AddOrUpdateExtendedProperty(view, "MS_Description", "Total for order in USD");

AddOrUpdateExtendedProperty method invokes sp_addextendedproperty or sp_updateextendedproperty stored procedures.

Drop Extended Property for View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Update extended property
databaseFactory.DropExtendedProperty(view, "MS_Description");

Drop Extended Property for Column in View

// Get view from database
var view = database.FindView("Sales.OrderSummary");

// Get column from view
var column = view.GetColumn("CurrencyName");

// Update extended property
databaseFactory.DropExtendedProperty(view, column, "MS_Description");

DropExtendedProperty method invokes sp_dropextendedproperty stored procedure.

Code above works with MS_Description but in code we can work with another extended properties.

Using the UI

UI for CatFactory allows edit extended properties with a graphical interface.

This client invokes the same methods from CatFactory.SqlServer package.

Setting Up

Back-end

Prerequisites: .NET Core 2

With Visual Studio:

1. Open CatFactory.UI.API solution located in Back-end directory
2. Set CatFactory.UI.API as startup project
3. Rebuild solution
4. Set port number to 8400 in launchSettings.json file (for iisSettings and CatFactory.UI.API profiles)
5. Run CatFactory.UI.API project

Make sure API runs in port number 8400

With Command Line

1. Open a command line terminal for CatFactory.UI.API directory located in Back-end directory
2. Run "dotnet restore" command to restore all nuget packages
3. Run "dotnet run" command to run CatFactory.UI.API project

Back-end has the reference for CatFactory.SqlServer package.

Front-end

Prerequisites: NodeJS, Angular CLI

1. Open a command line for CatFactoryUI directory located in Front-end directory
2. Run "npm install" command to install all nodejs packages
3. Run "ng serve" to start project in port number 4200

Document An Existing Database

This client contains the following views:

• Dashboard: Shows imported databases.
• Import: Allows import an existing database from SQL Server instance.
• Database details: Shows details for an imported database.
• Table details: Shows details for selected table.
• View details: Shows details for selected view.
• Edit description: Allows edit description for database object.

This client works only with MS_Description but we can change the extended property in C# code.

Go to dashboard, initially there isn't any database:

Go to import, this view allows to import an existing database:

Set name and connection string to import an existing database, additionally we can disable tables and views from import.

Now we can see databases in dashboard view:

Once we select a database from list, we can see details for database such as tables, views and database types mappings:

We can select a table to see details:

In details view, we can edit description for table:

Also we can edit descriptions for columns:

The same process applies for views and view's columns.

Code Improvements

• Allow add description for database
• Allow add description for indexes
• Allow add description for table functions
• Allow add description for stored procedures
• Add export feature (Word, PDF, HTML)

Points of Interest

• Description field is UI isn't required, because in some cases we want to clear a description for object

History

• 15^th July, 2018: Initial version
• 4^th October, 2018: Addition of code section and how to for tables and views