Introduction
CatFactory provides a mechanism for importing existing databases to a CLR structure, in that way we can use all objects definitions from CatFactory as we want. In this guide, I'll show you how to generate a data dictionary from an existing database.
Background
There are a large number of tools for generating data dictionaries from existing databases but with the preview release for CatFactory, we can generate data dictionary in DotNet Core.
Usually in SQL Server, we can store the description for objects in extended properties, we add a description in MS_Description extended property's value and then we can import those values to get information about database objects.
Using the Code
Step 01 - Create Console Project
Create a new console project with Visual Studio.
Step 02 - Add Packages for Console Project
Add the following package in project.json file:
| Name | Version | Description |
CatFactory.SqlServer | 1.0.0-alpha-build11 | Provides import database feature for SQL Server |
Step 03 - Add Class for Console Project
Add the following files for console project:
- Extensions.cs
- DataDictionaryCodeBuilder.cs
Now modify the code for each file:
Code for Extensions.cs file:
using System;
using System.Collections.Generic;
using System.Reflection;
namespace ConsoleApp4
{
public static class Extensions
{
public static String GetAttributes(this Object attributes)
{
var items = new List<String>();
foreach (var property in attributes.GetType().GetProperties())
{
items.Add(String.Format("{0}=\"{1}\"",
property.Name.Replace("-", "_"),
property.GetValue(attributes, null)));
}
return String.Join(" ", items);
}
}
}
Code for DataDictionaryCodeBuilder.cs file:
using System;
using System.Text;
using CatFactory.CodeFactory;
using CatFactory.Mapping;
namespace ConsoleApp4
{
public class DataDictionaryCodeBuilder : CodeBuilder
{
public DataDictionaryCodeBuilder(Database db)
{
Database = db;
}
public Database Database { get; }
public override String FileName
{
get
{
return String.Format("{0}.DataDictionary", Database.Name);
}
}
public override String FileExtension
{
get
{
return "html";
}
}
public String OpenTag(String name, Object attributes = null)
{
if (attributes == null)
{
return String.Format("<{0}>", name);
}
else
{
return String.Format("<{0} {1}>", name, attributes.GetAttributes());
}
}
public String CloseTag(String name)
{
return String.Format("</{0}>", name);
}
public String GetTag(String name, Object content, Object attributes = null)
{
if (attributes == null)
{
return String.Format("<{0}>{1}</{0}>", name, content);
}
else
{
return String.Format("<{0} {1}>{2}</{0}>",
name, attributes.GetAttributes(), content);
}
}
public String GetTag(String name)
{
return String.Format("<{0} />", name);
}
public override String Code
{
get
{
var output = new StringBuilder();
output.AppendFormat("{0}", OpenTag("html"));
output.AppendFormat("{0}", OpenTag("head"));
output.AppendFormat("{0}", OpenTag("style"));
output.AppendFormat
("body {{ font-family: Verdana; font-size: 12px; }}");
output.AppendFormat
(".table
{{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");
output.AppendFormat(".view
{{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");
output.AppendFormat(".storedProcedure
{{ border: 1px solid black; font-family: Verdana; font-size: 14px; }}");
output.AppendFormat(".columns
{{ border: 1px solid black; font-family: Verdana; font-size: 12px; }}");
output.AppendFormat(".parameters
{{ border: 1px solid black; font-family: Verdana; font-size: 12px; }}");
output.AppendFormat(".alternateRow {{ background-color: #C0C0C0; }}");
output.AppendFormat("{0}", CloseTag("style"));
output.AppendFormat("{0}", CloseTag("head"));
output.AppendFormat("{0}", OpenTag("body"));
output.AppendFormat("{0}", GetTag("h1",
$"Data Dictionary for {Database.Name}",
new { id = "top", style = "text-align: center;" }));
output.AppendFormat("{0}", GetTag("h2", "Tables"));
output.AppendFormat("{0}", OpenTag("ol"));
foreach (var table in Database.Tables)
{
output.AppendFormat("{0}", GetTag("li",
GetTag("a", table.FullName, new { href = $"#{table.FullName}" })));
}
output.AppendFormat("{0}", CloseTag("ol"));
output.AppendFormat("{0}", GetTag("h2", "Views"));
output.AppendFormat("{0}", OpenTag("ol"));
foreach (var view in Database.Views)
{
output.AppendFormat("{0}", GetTag("li",
GetTag("a", view.FullName, new { href = $"#{view.FullName}" })));
}
output.AppendFormat("{0}", CloseTag("ol"));
output.AppendFormat("{0}", GetTag("h2", "Stored Procedures"));
output.AppendFormat("{0}", OpenTag("ol"));
foreach (var procedure in Database.Procedures)
{
output.AppendFormat("{0}", GetTag("li",
GetTag("a", procedure.FullName, new { href = $"#{procedure.FullName}" })));
}
output.AppendFormat("{0}", CloseTag("ol"));
foreach (var table in Database.Tables)
{
output.AppendFormat("{0}", OpenTag("table",
new { id = table.FullName, @class = "table" }));
output.AppendFormat("{0}", OpenTag("tbody"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Type"));
output.AppendFormat("{0}", GetTag("td",
String.Format("User Table {0}", GetTag("a",
"Go top", new { href = "#top" }))));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Schema"));
output.AppendFormat("{0}", GetTag("td",
GetTag("strong", table.Schema)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Name"));
output.AppendFormat("{0}", GetTag("td",
GetTag("strong", table.Name)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Description"));
output.AppendFormat("{0}", GetTag("td",
GetTag("strong", table.Description)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Columns"));
output.AppendFormat("{0}", GetTag("td", table.Columns.Count));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("br"));
output.AppendFormat("{0}",
OpenTag("table", new { @class = "columns" }));
output.AppendFormat("{0}",
GetTag("caption", "Columns list"));
output.AppendFormat("{0}", OpenTag("thead"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("th", "Name"));
output.AppendFormat("{0}", GetTag("th", "Type"));
output.AppendFormat("{0}", GetTag("th", "Length"));
output.AppendFormat("{0}", GetTag("th", "Prec"));
output.AppendFormat("{0}", GetTag("th", "Scale"));
output.AppendFormat("{0}", GetTag("th", "Nullable"));
output.AppendFormat("{0}", GetTag("th", "Collation"));
output.AppendFormat("{0}", GetTag("th", "Description"));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("thead"));
output.AppendFormat("{0}", OpenTag("tbody"));
for (var i = 0; i < table.Columns.Count; i++)
{
var column = table.Columns[i];
if (i % 2 == 0)
{
output.AppendFormat("{0}", OpenTag("tr"));
}
else
{
output.AppendFormat("{0}",
OpenTag("tr", new { @class = "alternateRow" }));
}
output.AppendFormat("{0}", GetTag("td", column.Name));
output.AppendFormat("{0}", GetTag("td", column.Type));
output.AppendFormat("{0}", GetTag("td", column.Length));
output.AppendFormat("{0}", GetTag("td", column.Prec));
output.AppendFormat("{0}", GetTag("td", column.Scale));
output.AppendFormat("{0}",
GetTag("td", column.Nullable ? "Yes" : "No"));
output.AppendFormat("{0}", GetTag("td", column.Collation));
output.AppendFormat("{0}", GetTag("td", column.Description));
output.AppendFormat("{0}", CloseTag("tr"));
}
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("hr"));
output.AppendFormat("{0}", GetTag("br"));
}
foreach (var view in Database.Views)
{
output.AppendFormat("{0}", OpenTag("table",
new { id = view.FullName, @class = "view" }));
output.AppendFormat("{0}", OpenTag("tbody"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Type"));
output.AppendFormat("{0}", GetTag("td",
String.Format("View {0}", GetTag("a",
"Go top", new { href = "#top" }))));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Schema"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", view.Schema)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Name"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", view.Name)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Description"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", view.Description)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Columns"));
output.AppendFormat("{0}", GetTag("td", view.Columns.Count));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("br"));
output.AppendFormat("{0}",
OpenTag("table", new { @class = "columns" }));
output.AppendFormat("{0}",
GetTag("caption", "Columns list"));
output.AppendFormat("{0}", OpenTag("thead"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("th", "Name"));
output.AppendFormat("{0}", GetTag("th", "Type"));
output.AppendFormat("{0}", GetTag("th", "Length"));
output.AppendFormat("{0}", GetTag("th", "Prec"));
output.AppendFormat("{0}", GetTag("th", "Scale"));
output.AppendFormat("{0}", GetTag("th", "Nullable"));
output.AppendFormat("{0}", GetTag("th", "Collation"));
output.AppendFormat("{0}", GetTag("th", "Description"));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("thead"));
output.AppendFormat("{0}", OpenTag("tbody"));
for (var i = 0; i < view.Columns.Count; i++)
{
var column = view.Columns[i];
if (i % 2 == 0)
{
output.AppendFormat("{0}", OpenTag("tr"));
}
else
{
output.AppendFormat("{0}",
OpenTag("tr", new { @class = "alternateRow" }));
}
output.AppendFormat("{0}", GetTag("td", column.Name));
output.AppendFormat("{0}", GetTag("td", column.Type));
output.AppendFormat("{0}", GetTag("td", column.Length));
output.AppendFormat("{0}", GetTag("td", column.Prec));
output.AppendFormat("{0}", GetTag("td", column.Scale));
output.AppendFormat("{0}",
GetTag("td", column.Nullable ? "Yes" : "No"));
output.AppendFormat("{0}", GetTag("td", column.Collation));
output.AppendFormat("{0}", GetTag("td", column.Description));
output.AppendFormat("{0}", CloseTag("tr"));
}
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("hr"));
output.AppendFormat("{0}", GetTag("br"));
}
foreach (var storedProcedure in Database.Procedures)
{
output.AppendFormat("{0}", OpenTag("table",
new { id = storedProcedure.FullName, @class = "storedProcedure" }));
output.AppendFormat("{0}", OpenTag("tbody"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Type"));
output.AppendFormat("{0}", GetTag("td",
String.Format("Stored Procedure {0}",
GetTag("a", "Go top", new { href = "#top" }))));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Schema"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", storedProcedure.Schema)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Name"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", storedProcedure.Name)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Description"));
output.AppendFormat("{0}",
GetTag("td", GetTag("strong", storedProcedure.Description)));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("td", "Parameters"));
output.AppendFormat("{0}",
GetTag("td", storedProcedure.Parameters.Count));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("br"));
output.AppendFormat("{0}",
OpenTag("table", new { @class = "parameters" }));
output.AppendFormat("{0}",
GetTag("caption", "Parameters list"));
output.AppendFormat("{0}", OpenTag("thead"));
output.AppendFormat("{0}", OpenTag("tr"));
output.AppendFormat("{0}", GetTag("th", "Name"));
output.AppendFormat("{0}", GetTag("th", "Type"));
output.AppendFormat("{0}", GetTag("th", "Length"));
output.AppendFormat("{0}", GetTag("th", "Prec"));
output.AppendFormat("{0}", GetTag("th", "Scale"));
output.AppendFormat("{0}", GetTag("th", "Collation"));
output.AppendFormat("{0}", GetTag("th", "Order"));
output.AppendFormat("{0}", CloseTag("tr"));
output.AppendFormat("{0}", CloseTag("thead"));
output.AppendFormat("{0}", OpenTag("tbody"));
for (var i = 0; i < storedProcedure.Parameters.Count; i++)
{
var parameter = storedProcedure.Parameters[i];
if (i % 2 == 0)
{
output.AppendFormat("{0}", OpenTag("tr"));
}
else
{
output.AppendFormat("{0}",
OpenTag("tr", new { @class = "alternateRow" }));
}
output.AppendFormat("{0}", GetTag("td", parameter.Name));
output.AppendFormat("{0}", GetTag("td", parameter.Type));
output.AppendFormat("{0}", GetTag("td", parameter.Length));
output.AppendFormat("{0}", GetTag("td", parameter.Prec));
output.AppendFormat("{0}", GetTag("td", parameter.Scale));
output.AppendFormat("{0}", GetTag("td", parameter.Collation));
output.AppendFormat("{0}", GetTag("td", parameter.ParamOrder));
output.AppendFormat("{0}", CloseTag("tr"));
}
output.AppendFormat("{0}", CloseTag("tbody"));
output.AppendFormat("{0}", CloseTag("table"));
output.AppendFormat("{0}", GetTag("hr"));
output.AppendFormat("{0}", GetTag("br"));
}
output.AppendFormat("{0}", CloseTag("body"));
output.AppendFormat("{0}", CloseTag("html"));
return output.ToString();
}
}
}
}
using System;
using CatFactory.SqlServer;
namespace ConsoleApp1
{
public class Program
{
public static void Main(String[] args)
{
var dbFactory = new SqlServerDatabaseFactory
{
ConnectionString =
"server=(local);database=AdventureWorks2012;integrated security=yes;"
};
var db = dbFactory.Import();
var dataDictionary = new DataDictionaryCodeBuilder(db)
{
OutputDirectory = "C:\\Temp"
};
dataDictionary.CreateFile();
}
}
}
For this example, I'm targeting an existing database on my local SQL Server instance and a local directory. Obviously, you can change those values but before, please make sure you have the rights to read/write database and directory, please make sure about that to avoid common execution errors.
Save all changes and run your console program and check on your output directory the generated file or check the sample file in download list.
How it works? We import the definition for existing database with CatFactory and then we read all tables and views. Please make sure the flag ImportMSDescription has "true" value and in this case, build a couple of HTML tables to represent the information.
I know the style for HTML output file. It's a crap but this guide is not about how to get a pretty HTML, it's about how to generate a data dictionary from an existing database. If you want to apply a pretty style to the output file, you can modify the code for DataDictionaryCodeBuilder class.
Output:
Points of Interest
- Unfortunately, there isn't compatibility with DocX for DotNet Core, I know it's better to generate a Word document instead of simple HTML file but for now we generate an HTML file.
Code Improvements
- Add functions
- Add custom data types
Related Links
- Generating Code for EF Core with CatFactory
History
- 31st December, 2016: Initial version
CatFactory Creator.
Full Stack Developer with Experience in C#, Entity Framework Core, ASP.NET Core and Angular.
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
