{"id":1137,"date":"2015-03-30T08:58:22","date_gmt":"2015-03-30T08:58:22","guid":{"rendered":"http:\/\/mindfusion.eu\/blog\/?p=1137"},"modified":"2021-01-15T15:33:59","modified_gmt":"2021-01-15T15:33:59","slug":"database-schema-diagram","status":"publish","type":"post","link":"https:\/\/mindfusion.dev\/blog\/database-schema-diagram\/","title":{"rendered":"Database schema diagram"},"content":{"rendered":"

In this post we’ll show how to use TableNode objects to display tabular data, more specifically database schema information. A Visual Studio sample project containing the code from this post is available for download here:<\/p>\n

DatabaseSchema.zip<\/a><\/p>\n

To start, create a new Windows Forms application, and place a text field for connection string, a button and a DiagramView on the form. In the code-behind file, add following field to map table name to respective TableNode objects:<\/p>\n

Dictionary<string, tablenode=\"\"> tables = new Dictionary<string, tablenode=\"\">();\n<\/string,><\/string,><\/pre>\n
Add a RectangleF that stores default size passed to CreateTableNode method:<\/p>\n
RectangleF defaultSize = new RectangleF(0, 0, 30, 30);\n<\/pre>\n
Create a ReadTables method, which provided an SqlConnection, parses its schema information and creates diagram nodes:<\/p>\n
void ReadTables(SqlConnection connection)\n{\n\t\/\/ get table schema definitions from connection\n\tvar schema = connection.GetSchema(\"Tables\");\n\tforeach (DataRow row in schema.Rows)\n\t{\n\t\t\/\/ fetch table name\n\t\tvar name = row[\"TABLE_NAME\"].ToString();\n\n\t\t\/\/ create respective node\n\t\tvar table = diagram.Factory.CreateTableNode(defaultSize);\n\t\ttable.Caption = name;\n\t\ttable.Shape = SimpleShape.RoundedRectangle;\n\t\ttable.Brush = new MindFusion.Drawing.SolidBrush(Color.LightGray);\n\n\t\t\/\/ register node in dictionary for future foreign key reference\n\t\ttables[name.Replace(\" \", \"_\")] = table;\n\t\tReadFields(table, connection,\n\t\t\trow[\"TABLE_CATALOG\"].ToString(), null, name);\n\t}\n\n\tReadForeignKeys(connection);\n}\n<\/pre>\n
The ReadFields method takes table node and name parameters and creates node cells that will show information for the column name and type of database tables:<\/p>\n
void ReadFields(TableNode node,\n\tSqlConnection connection, string db, string owner, string tableName)\n{\n\t\/\/ remove default cells\n\tnode.RowCount = 0;\n\n\t\/\/ reserve one column for name and one for data type\n\tnode.ColumnCount = 2;\n\n\t\/\/ read column definitions of specified table\n\tvar schema = connection.GetSchema(\"Columns\", new[] { db, owner, tableName });\n\tforeach (DataRow row in schema.Rows)\n\t{\n\t\t\/\/ add a new row to the node\n\t\tint r = node.AddRow();\n\n\t\t\/\/ set cells' text to the column name and type\n\t\tnode[0, r].Text = row[\"COLUMN_NAME\"].ToString();\n\t\tnode[1, r].Text = row[\"DATA_TYPE\"].ToString();\n\n\t}\n\n\t\/\/ make table cells big enough to show all text\n\tnode.ResizeToFitText(false);\n}\n<\/pre>\n
The ReadForeignKeys method creates DiagramLink connectors between table nodes to show the relationships between database tables:<\/p>\n
void ReadForeignKeys(SqlConnection connection)\n{\n\tvar schema = connection.GetSchema(\"ForeignKeys\");\n\tforeach (DataRow row in schema.Rows)\n\t{\n\t\t\/\/ read foreign key information\n\t\tstring fkName = row[\"CONSTRAINT_NAME\"].ToString();\n\t\tstring tableName = row[\"TABLE_NAME\"].ToString().Replace(\" \", \"_\");\n\t\tstring prefix = \"FK_\" + tableName + \"_\";\n\t\tif (fkName.StartsWith(prefix))\n\t\t{\n\t\t\tstring targetName = fkName.Substring(prefix.Length);\n\n\t\t\t\/\/ get table nodes registered for specified names\n\t\t\tif (tables.ContainsKey(targetName) && tables.ContainsKey(tableName))\n\t\t\t{\n\t\t\t\tvar table = tables[tableName];\n\t\t\t\tvar targetTable = tables[targetName];\n\n\t\t\t\t\/\/ create a link between the nodes to show relationship\n\t\t\t\tdiagram.Factory.CreateDiagramLink(table, targetTable);\n\t\t\t}\n\t\t}\n\t}\n}\n<\/pre>\n
Finally handle the button’s click event to open specified connection and call ReadTables. Apply AnnealLayout to arrange the tables so that they do not overlap:<\/p>\n
private void btnOpen_Click(object sender, System.EventArgs e)\n{\n\tdiagram.ClearAll();\n\n\ttry\n\t{\n\t\tvar connection = new SqlConnection(tbConnection.Text);\n\t\tconnection.Open();\n\n\t\t\/\/ read schema and create corresponding diagram items\n\t\tReadTables(connection);\n\n\t\tconnection.Close();\n\t}\n\tcatch (Exception exception)\n\t{\n\t\tMessageBox.Show(exception.Message);\n\t\tdiagram.ClearAll();\n\t}\n\n\t\/\/ arrange the tables to remove overlaps\n\tvar layout = new AnnealLayout();\n\tlayout.SplitGraph = true;\n\tlayout.Randomize = false;\n\tlayout.MultipleGraphsPlacement = MultipleGraphsPlacement.MinimalArea;\n\tlayout.Margins = new SizeF(10, 10);\n\tlayout.Arrange(diagram);\n}\n<\/pre>\n
If you run the project and open the Northwind sample database by Microsoft, you should see this diagram:<\/p>\n
\"database<\/p>\n
The code above uses MindFusion\u2019s .NET API and can be used with Windows Forms, WPF, Silverlight and ASP.NET diagramming components. The Java API for Android and desktop Swing application will look similar, with setter method calls instead of property assignments.<\/p>\n
You can download the trial version of any MindFusion.Diagramming component from this page<\/a>.<\/p>\n
Enjoy!<\/p>\n","protected":false},"excerpt":{"rendered":"
In this post we’ll show how to use TableNode objects to display tabular data, more specifically database schema information. A Visual Studio sample project containing the code from this post is available for download here: DatabaseSchema.zip To start, create a … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false},"version":2}},"categories":[95,74],"tags":[110,264,193,3,298,19],"class_list":["post-1137","post","type-post","status-publish","format-standard","hentry","category-diagramming-2","category-sample-code","tag-net","tag-c","tag-database","tag-diagram","tag-schema","tag-table"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/p3RlKs-il","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/posts\/1137","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/comments?post=1137"}],"version-history":[{"count":2,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/posts\/1137\/revisions"}],"predecessor-version":[{"id":2535,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/posts\/1137\/revisions\/2535"}],"wp:attachment":[{"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/media?parent=1137"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/categories?post=1137"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mindfusion.dev\/blog\/wp-json\/wp\/v2\/tags?post=1137"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}