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!

Tuesday, April 20, 2010

Solutions to: “Server-generated keys and server-generated values are not supported by SQL Server Compact” with Entity Framework

SQL Compact does not support IDENTITY keys when used with Entity Framework, since the SQL Compact engine only supports a single statement per batch.

This can be solved in various ways:

You can have all your primary keys be of type uniqueidentifier, and assign the NewGuid() value to the object upon creation:

 Employee employee = new Employee();

employee.Id = Guid.NewGuid();

Or you can create an extension method as described here, noting the limitations of this approach.

Tuesday, April 13, 2010

SQL Server Compact 3.5 SP2 released!

SQL Server Compact 3.5 (build 3.5.8080.0) has been released to web.

SQL Server Compact 3.5 SP2 includes a host of new features including the following:

  • Support for the Transact-SQL Editor in Visual Studio 2010 that can be used to run free-text T-SQL queries and to view query show-plans for optimizing queries.
  • Exposes the Change Tracking feature as an application programming interface (API), namely, System.Data.SqlServerCe.SqlCeChangeTracking. The new Change Tracking API provides the ability to configure, enable, and disable change tracking on a table, and to access the change tracking data for the table.
  • New assembly versioning support and directory structure that enables the SQL Server Compact applications that use private deployment to load SQL Server Compact assemblies from within the application folder if they are of a higher version than that in the global assembly cache (GAC).
  • Support added for deploying the 64-bit version of SQL Server Compact using the ClickOnce deployment.
  • Synchronization Services for ADO.NET v1.0 providing the ability to synchronize data from disparate sources over two-tier, N-tier, and service-based architectures.

New feature details also available here: http://erikej.blogspot.com/2009/10/whats-new-in-sql-server-compact-35-sp2.html

- and the product team has more details here: http://blogs.msdn.com/sqlservercompact/archive/2010/04/14/microsoft-sql-server-compact-3-5-with-service-pack-2-released-with-visual-studio-2010-and-on-the-web.aspx

Downloads:

Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop 
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=e497988a-c93a-404c-b161-3a0b323dce24

Books Online
http://www.microsoft.com/downloads/details.aspx?FamilyID=746c3a6e-ffb1-4c92-93fa-b3ba41fde681&displaylang=en

Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Mobile
http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=5544c638-c532-48e3-871c-58b49c5d855c

Microsoft SQL Server Compact 3.5 Service Pack 2 Server Tools
http://www.microsoft.com/downloads/details.aspx?familyid=5DEEC9D5-C216-446E-A003-5D6026AD19B3&displaylang=en

Sunday, April 11, 2010

Using the ADO.NET Data Connection dialog in your own application – and limiting the choice of data sources

In version 3.0 of the ExportSqlCE SSMS add-in, I have used the newly released Visual Studio ADO.NET Add Data Connection dialog to prompt the user for connection information to the server. This dialog is now available in source code here.

This project consists of this well-know dialog (and others) – used in the Visual Studio Server Explorer:

image

For the purpose of the add-in, I was interested in on the Microsoft SQL Server data source, as this was the target of the scripting code.

This was achieved in the following way:

using Microsoft.Data.ConnectionUI;


DataSource sqlDataSource = new DataSource("MicrosoftSqlServer", "Microsoft SQL Server");
sqlDataSource.Providers.Add(DataProvider.SqlDataProvider);
DataConnectionDialog dcd = new DataConnectionDialog();
dcd.DataSources.Add(sqlDataSource);
dcd.SelectedDataProvider = DataProvider.SqlDataProvider;
dcd.SelectedDataSource = sqlDataSource;
if (DataConnectionDialog.Show(dcd) == DialogResult.OK)
{
string connectionString = dcd.ConnectionString;



 



The result is the following dialog, notice that the Change… button is disabled:



image

Tuesday, April 6, 2010

Version 3 of ExportSqlCE now available

After more than 1200 downloads of version 2.5 of my SQL Server Management Studio add-in for SQL Server Compact, version 3.0 is now available for download.

Many features and enhancements are by request from users – keep it coming!

Notable new features include (for a full list, see the release notes on the download page):

Script Server Database

image 3

- new menu item, that allows you to script a SQL Server database to SQL Compact compatible SQL script.

(With many improvements and fixes of the previously released command line utility)

 

Show table data

image

Various enhancements:

- Sorting of columns

- Reload, About and version check added

- Import/Export/Delete content of image columns