Click here to Skip to main content
15,867,835 members
Articles / Database Development / SQL Server

Document SQL Server Databases with CatFactory.UI

Rate me:
Please Sign up or sign in to vote.
2.31/5 (6 votes)
2 Jan 2019CPOL5 min read 7.2K   3   4
Document SQL Server Databases with CatFactory.UI

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:

C#
// 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:

C#
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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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

C#
// 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:

Dashboard

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

Import

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

Add Information to Import

Now we can see databases in dashboard view:

Dashboard with databases

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

Database details

We can select a table to see details:

Table details

In details view, we can edit description for table:

Edit Table Description Save Table Description

Also we can edit descriptions for columns:

Description for Columns Edit Column Description Save Column Description

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

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

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
El Salvador El Salvador
CatFactory Creator.

Full Stack Developer with Experience in C#, Entity Framework Core, ASP.NET Core and Angular.

Comments and Discussions

 
GeneralDoesn't work on Azure though Pin
pt14016-Oct-18 22:58
pt14016-Oct-18 22:58 
GeneralRe: Doesn't work on Azure though Pin
HHerzl7-Oct-18 21:10
HHerzl7-Oct-18 21:10 
GeneralMy vote of 2 Pin
Member 1236439022-Jul-18 21:25
Member 1236439022-Jul-18 21:25 
QuestionNot enough Pin
#realJSOP16-Jul-18 6:31
mve#realJSOP16-Jul-18 6:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.