Sunday, April 25, 2010

Diagram database table relationships using DGML, SMO and Visual Studio 2010

DGML (directed graph markup language) is a graphing XML language supported by the new modeling tools in Visual Studio 2010.

You can create various code analysis graphs using Visual Studio 2010 Ultimate, and Visual Studio 2010 Premium includes a DGML viewer for displaying DGML documents, but does not include tools for creating them (other than the XML editor!). Unfortunately, for this release of Visual Studio, the Express versions and Professional does not include a DGML viewer. You can, however, share  graphs by saving them as XPS documents directly from the DGML viewer. You can read more about the DGML format here.

The sample command line utility that comes with this blog demonstrates how you can very easily create a nice graph of the tables and table relationships in your database using DGML and SMO.

SQL Server Management Objects (SMO) are objects designed for programmatic management of Microsoft SQL Server. It allows you to inspect all objects that relate to SQL Server and manipulate these. For this sample, we only need access to the tables and foreign keys of the tables of a specific database.

var connectionString = @"Data Source=(local);Initial Catalog=AdventureWorksLT;Integrated Security=SSPI;";

using (var conn = new SqlConnection(connectionString))
{
conn.Open();
var server = new Server(new ServerConnection(conn));
var database = server.Databases[conn.Database];
foreach (Table table in database.Tables)
{
System.Diagnostics.Debug.WriteLine(table.Schema + "." + table.Name;

foreach (ForeignKey key in table.ForeignKeys)
{



The code above demonstrates how to get a collection of Table objects for a database using SMO. In addition, each table object has a collection of ForeignKeys, that allow you to get information about each foreign key for the table.



A simple DGML document looks like this:



<DirectedGraph xmlns="http://schemas.microsoft.com/vs/2009/dgml">
<
Nodes>
<
Node Id="0" Label="tblDoctor" />
<
Node Id="1" Label="tblDoctorLocation" />
<
Node Id="2" Label="tblFavoriteDoctor" />
</
Nodes>
<
Links>
<
Link Source="0" Target="1" Label="FK_tblDoctorLocation_tblDoctor" />
<
Link Source="1" Target="2" Label="FK_tblFavoriteDoctor_tblDoctorLocation" />
</
Links>
</DirectedGraph>



 



And looks like this in the DGML viewer:



 



image



 



This is graph for the AdventureWorksLT  database:



 



image



The complete code for this sample is available for download here.



Hope you find it useful!

4 comments:

Rob said...

Thanks!

a.palacio said...

Hi Erik. My name is Alejandro from Colombia SouthAmerica. We have a big issue to insert around 5.000 records in a table an only way we have found for doing it is your "ErikEJ.SqlCe.dll". Do u have any success stories? Is it to risky use it in a project?
Thanks in advance - Kind Regards.
Alejandro - from Colombia.

a.palacio said...

Another Question. "ErikEJ.SqlCe.dll" do accepts a text file as a source data to make a Bulk copy into sdf table? Thanks in advance.

ErikEJ said...

It has been downloaded 390 time and I have never had any "complaints". So I guess that counts as a success story? It is open source, so very low risk afaik. It accepts either a IDataReader or DataTable. There is a 3rd party product that allows you to load a text file in a DataReader: http://www.csvreader.com/code/cs/upload_csv.php