Introduction
This article will help to choose a proper SQL XML Documentation format for the future and help to convert these comments into Microsoft Help formats with minimal resources.
Background
Today there is still no useful XML Documentation standard for SQL which can stand time. I doubt that current solutions like "DBdoc" will be adopted by Microsoft. I haven't seen SQL 2008 but I can make a guess that all SQL objects will be commented through SQL extended properties and comments for SQL procedures will look something like this:
CREATE PROCEDURE [dbo].[solution_Category_InsertRecord] (
@RecordId Int,
@RecordGuid UniqueIdentifier,
@SomeValue NVarChar(200),
@RecordEnabled Bit
)
AS
Note: 'ref' attribute can be used to pull out comments from table column extended property.
... and XML Documentation file can look like this:
="1.0"
<doc>
<assembly>
<name>MyDbDocs</name>
</assembly>
<members>
<member name="M:Database.Procedures.solution_Category_InsertRecord
(Int,UniqueIdentifier,NVarChar,Bit)">
<summary>
Insert new record into table.
</summary>
<param name="RecordId" type="Int" size="4">Unique record Id.</param>
<param name="RecordGuid" type="UniqueIdentifier" size="16">
Set Global Unique Identifier.</param>
<param name="SomeValue" type="NVarChar" size="200">Set record value.</param>
<param name="RecordEnabled" type="Bit" size="1"
ref="dbo.Globalization_Countries.RecordEnabled">
Enable or disable record.</param>
<returns>Unique Id of new record.</returns>
<remarks>
History:
2007-11-02 - Created by Smith.
2007-11-23 - Modified by Neo.
</remarks>
</member>
<member>...
... so it can be easily compiled with tools Microsoft is using today.
Requirements
You need to have these 4 free tools installed:
- Microsoft SQL Server Native Client
It contains run-time support for applications using native-code APIs (ODBC, OLE DB and ADO) to connect to Microsoft SQL Server 7.0, 2000 or 2005. Redistributable installer for SQL Native Client installs the client components needed during run time to take advantage of new SQL Server 2005 features, and optionally installs the header files needed to develop an application that uses the SQL Native Client API.
- Microsoft SQL Server 2005 Management Objects Collection
Includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2000/2005.
- Microsoft SandCastle
Command Line Interface (CLI) Tool used for creating MSDN-style documentation from .NET assemblies and their associated XML comments files.
- CodePlex SandCastle Help File Builder
Graphical User Interface (GUI) for Microsoft SandCastle. Also contains command line based tools to build a help file in an automated fashion.
After installation of these tools, make sure that Visual Studio 2005 project references points to correct files. These are default locations:
Using the Code
Conversion from SQL XML Documentation to CHM Help file can be processed in these steps:
- Use official recommended tags (see example above) for documentation comments.
I hope these comments will be fully supported by Microsoft SQL Server IDE and tools like Microsoft SandCastle in the future. You can find recommended tags for .NET here. - Start "Visual Studio 2005 Command Prompt" and export SQL XML comments into XML documentation file with included
DatabaseToXml tool and C# compiler.
DatabaseToXml.exe -t -s localhost -d DatabaseName -c MyDbDocs.cs
csc /t:library /doc:MyDbDocs.xml MyDbDocs.cs /reference:
"JocysCom.Sql.XmlDocumentation.dll","%ProgramFiles%\Microsoft SQL Server\90\SDK\
Assemblies\Microsoft.SqlServer.Smo.dll"
- Compile Help file with CodePlex SandCastle Help File Builder.
- Run [Start] -> All Programs -> SandCastle Help File Builder -> SandCastle Help File Builder GUI.
- Press [Add] button and add MyDbDocs.xml file to the list.
- Click on: Project Properties \ Dependencies \ (Collection) [...] button
and add two files:
- JocysCom.Sql.XmlDocumentation.dll
- %ProgramFiles%\Microsoft SQL Server\90\SDK\Assemblies\Microsoft.SqlServer.Smo.dll
- Choose from menu: Documentation -> Build Project
You can save Help Builder project as MyDbDocs.shfb.
- Microsoft Help file Documentation.chm will be created and it will look like this:
Please note that results will be not ideal (C# mixed with SQL data) but you will have a clear point where its heading. Microsoft or someone else just needs to update XSLT templates and you can build proper SQL documentation by using only existing tools. This means that if Microsoft adopts same XML Documentation style as in C#, VB.NET, JavaScript... then your comments will be valid in the future and you will save time by not spending it on conversion.
DatabaseToXml Options
Options of Jocys.com Database XML Comments Exporter 1.x:
Jocys.com Database XML Comments Exporter 1.0.0.0
Creates XML Comments file and DLL Library from database.
Usage:
DatabaseToXml [-f <SqlScript> | -d <DataBaseName>]
[-t | -s <ServerName> -u <UserName> -p <Password>]
[-c <CsFile>] [-g <SqlScript>] [-r <Pattern>]
Source:
-f <SqlScript> Import from SQL Script through TempDatabase.
-d <DataBaseName> Database name.
Connection:
-t Use trusted connection.
-s <ServerName> SQL server name. Default: localhost
-u <UserName> Database username.
-p <Password> SQL password.
Filter:
-r <Pattern> Filter procedures with regular expression pattern.
Destination:
-g <SqlScript> Specifies SQL Script output file to generate.
-c <CsFile> Specifies C# code output file.
Examples:
:: Export from Database.
DatabaseToXml -d MyDatabase -t -c MyDatabase.cs
:: Script SQL Stored procedures with parsed XML Comments.
DatabaseToXml -d MyDatabase -t -g MyProcedures.sql
:: Export XML from SQL Script file.
:: Use trusted connection for 'TempDatabase'.
DatabaseToXml -f MyScripts.sql -t -c MyDatabase.cs
:: Export XML and exclude procedures with 'Globalization' inside name.
DatabaseToXml -d MyDatabase -t -c MyDatabase.cs -r "^((?!Globalization).)*$"
Points of Interest
- I will be happy to expand this article if you have some questions or suggestions.
- Please note that
DatabaseToXml tool is for comments of stored procedures only. - There is no guarantee that Microsoft will use this form of comments but I have a strong opinion that this is most logical way for Microsoft to go because unified comments format in all languages (C#, VB.NET, JavaScript, SQL...) will cut developing and support time. (As you see I can reuse existing tools to generate MSDN style help now.)
History
- 2007-11-12 - This article was created.
- 2007-11-13 -
DatabaseToXml tool was updated with "-g" option which allows to script SQL Stored procedures with parsed (recreated if missing) XML Comments from Database to *.SQL file. You can use this option if you want to generate XML comments automatically for stored procedures. - 2007-11-20 - Database tool to generate
INSERT/UPDATE/SELECT/DELETE procedures with SQL XML Documentation headers were added.
I use this tool to create stored procedures for .NET DataSets usually:
- 2007-11-23 -
DatabaseToXml tool was updated with "-a" option. This helps to export XML commented procedures from database with classic old headers (in case someone needs it for release process):
MyTemplate.txt contents:
/*******************************************************************
-- Project : $at
-- Version : $av
-- Process : $ap
-- Function : $af
-- Description : $DocSummary
-- Procedure Name : $ProcedureName
-- File Name : $ProcedureFile
********************************************************************
-- Change History
********************************************************************
** Date: By: Change Description:
** $ah
********************************************************************/
:: Export procedures with template header.
DatabaseToXml.exe -t -d MyDatabase -g MyDbScript.sql
-a "MyTemplate.txt" -at "Company ProductName" -av 1.0.0.0
-ah "2007-11-22 - Modified by John Smith" -ap "Process" -af "Function"
Note: You can use -a[a-z] CLI parameters for $a[a-z] template parameters
- 2007-11-23 -
DatabaseToXml tool was updated with "-fn" option. This will fix procedure names with [dbo].[ProcedureName] style when exporting. - 2007-12-04 - Lost screenshots were re-uploaded.
DatabaseToXml tool was updated with undocumented "-ru" option. This option will replace NOLOCK with READUNCOMMITTED when exporting procedures to SQL script. - 2008-02-29 - Added
server.SetDefaultInitFields(typeof(StoredProcedure), true); line in order to speed-up export process. Now all properties will be returned by default and there will be no extra round-trips to the database. - 2008-04-15 - Windows application connection provider was updated to
Microsoft.SqlServer.Management.Common.ServerConnection in order to work properly on Vista. Other bugs fixed. - 2008-04-17 - Encrypted procedures now are excluded (suggestion from schmallaria).
References
Note: I've modified and expanded Stephen Toub's XmlComments class in order to add SQL support. You can download original code from here.
