Dbdoc_ automated chm documentation for a sql server 2000 database – codeproject

Do you know the Visual Studio .NET capability of extracting – at compile time – some formatted comments (you included in your source code) in the form of XML descriptive files, that you can later process with programs like NDoc in order to produce a CHM documentation of your assemblies and classes? Well, if your answer is “yes”, and if you ever felt the lack of a similar feature in the Microsoft SQL Server 2000 suite, then you will easily understand the goal of DBdoc, the tool presented in this article. Database graph It arises from the need of producing, in a similar automated way, the documentation for a SQL Server 2000 database, by extracting some properly formatted comments included in the tables, stored procedures, user-defined functions and triggers of the database, and by combining them with some SQL Server metadata in order to produce a collection of descriptive HTML files useful to build the CHM documentation of your database. If you’re interested in the XML code documentation for .NET programming languages, keep in mind that in some versions of Microsoft Visual Studio this feature is present in the C# language editor context only; in order to have a similar feature in VB.NET language, you can use third-party tools like the free Visual Studio add-in named “VBCommenter” (see http://www.codeproject.com/vb/net/VbCommenter.asp). Database naming conventions A first look to DBdoc The main idea behind DBdoc is to make easy the CHM documentation preparation for your Microsoft SQL Server 2000 database through collecting some comments and descriptions you left into the database itself. Database entity Being your database and its description indivisible, you’ll mantain the documentation updated easily, while modifying the database objects, their structure and the T-SQL code. The input for DBdoc is a properly commented SQL Server 2000 database (in the following, we’ll call it for simplicity “ToBeDoc database”, the “database to be documented”); its output is a CHM help project ready to be compiled with the “Microsoft HTML Help Workshop” in order to produce the actual CHM file. Database developer For information about “HTML Help Workshop”, normally shipped with Visual Studio .NET, visit http://www.microsoft.com/downloads/details.aspx?FamilyID=00535334-c8a6-452f-9aa0-d597d16580cc&DisplayLang=en (I used the version 4.74.8702.0 of “HTML Help Workshop”, that comes with Microsoft Visual Studio .NET 2003). In order to extract the needed information from the ToBeDoc database, DBdoc executes some queries on the database metadata (i.e. Data recovery plan system tables such as sysobjects and syscolumns, INFORMATION_SCHEMA views and so on). The extraction of XML comments from stored procedures, user-defined functions and triggers (see below for a description about XML commenting guidelines) is done through a syntax parsing of the T-SQL code that implements them. Data recovery kansas city The T-SQL parser has been built using the wonderful Spart library by Jonathan de Halleux (that I wish here to thank for this great work). N k database How DBdoc works The support database DBdoc makes use of a simple support database where it stores temporary data (needed for internal computation) collected from the database to be documented (“ToBeDoc database”). Data recovery 2016 This support database (we’ll call it simply “DBdoc database”) is made up of the following tables: • RawRoutines and RawParameters: containing raw information about the T-SQL “routines” (that is: stored procedures, user-defined functions and triggers) hosted in the ToBeDoc database, along with their parameters; • Routines, Params, Tables and Columns: containing similar (but computed, not raw) information about the objects hosted in the ToBeDoc database; • ExcludedRoutines and ExcludedTables: eventually containing the list of the objects (routines and tables) you want to omit in the final resulting CHM documentation. The difference between the Routines, Params, Tables and Columns tables and their Raw counterparts is that Raw tables collect a sort of raw “dump” of ToBeDoc database metadata, while not- Raw tables contain similar data enriched and pre-computed to be ready for the CHM project files generation. To make it clear, for example the RawTables table contains the fields TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME and TABLE_TYPE (directly coming from a ” SELECT * FROM INFORMATION_SCHEMA.TABLES” command executed against the ToBeDoc database), while the Tables table contains fields like Comment (computed later by inspecting the extended properties of each specific table object listed). The filename to be assigned to the HTML Help Workshop project file produced by DBdoc (that will have an .HHP extension) and – after compilation – to the final documentation file (that, of course, will have a .CHM extension). The folder where you want the HTML Help Workshop project files will be saved (the folder is created if it doesn’t exist). 510 k database fda You can either specify a full, absolute folder path (such as ” C:\Northwind”) or a relative folder path (that will refer a folder under the directory where DBdoc is currently running). This content document normally introduces the CHM document content as a whole, and has to be supplied manually, because it is not inferable from the ToBeDoc database metadata or comments. You can either simply specify a filename (such as “Introduction.htm”) or a full, absolute file path (such as “C:\MyFiles\Introduction.htm”): in the first case, you will have to ensure that the file actually exists on the output folder before compiling the CHM project with HTML Help Workshop; in the latter case, DBdoc will automatically copy the file you specified from its original location to the output folder. The connection string details (server name, database name, username and password of a valid SQL Server login) to access the ToBeDoc database and its metadata. The connection string details (server name, username and password of a valid SQL Server login) to access the DBdoc support database (always named “DBdoc”, by design). This option allows you to specify the source for comments related to the tables to be documented and their fields. Database programmer Currently, DBdoc supports the retrieval of such information either from the SQL Server standard extended properties (see below) or from a DDL file (containing the ToBeDoc database data model) generated with Microsoft Visio. A flag indicating if you want (or don’t want) to detect cross-reference info in the ToBeDoc database objects set and to include them in the final CHM documentation. A flag indicating if you want (or don’t want) to log unespected errors encountered during the execution of DBdoc. Data recovery osx Whit this option enabled, in the folder where DBdoc runs, an HTML file named LogFile.htm will be created to collect any exception raised during DBdoc computations; this could be very useful to diagnose the presence of something wrong in the comments you included in the ToBeDoc database. Database integrity The log file is in HTML format just to be easily read in a browser. All the information you supply in the UI form are persisted for your future DBdoc session through the well-known ConfigOpt class. Database backup Processing steps to produce your database documentation • describe your database objects by inserting descriptions and comments, following the guidelines in the paragraph “How to comment your database” below; DBdoc makes use of the msdn.css cascading stylesheet, in order to produce a standard MSDN-like documentation. Hollywood u database The actual HTML pages generated by DBdoc and then used by the “HTML Help Workshop” to build the final CHM documentation file are produced by applying some XSLT transformation stylesheets (look for *.XSLT files in the bin folder) to the actual data extracted from the ToBeDoc database. Data recovery ipad This means that the final layout of the produced CHM documentation file is fully customizable by modifying these XSLT and CSS files. Database vs server How to comment your database As stated earlier, DBdoc builds the documentation files by r
etrieving the descriptive comments you wrote inside your SQL Server 2000 database. The best way for supplying comments for tables and fields to DBdoc is through SQL Server extended properties; this is also the recommended way, because it keeps the documentation close to the database objects. Database is in transition SQL Server extended properties for a table and its fields are simply editable by using Microsoft Enterprise Manager: just put the comment of each field in the Description textbox shown in the design view of a table, and put the comment for the table itself in the Description textbox of its Properties dialog box. DBdoc also supports the retrieval of table and fields comments from a DDL Microsoft Visio-generated file, but I won’t describe this feature in details (also because it is just sketched and it is not designed to work with “any” version of Visio files). Data recovery ios Commenting Stored Procedures In order to supply comments for the stored procedures hosted in the ToBeDoc database, you have to include in the code implementing each of them a properly formatted XML comment, following this example: CREATE PROCEDURE CustOrderHist @CustomerID nchar( 5) The comment must describe your code in detail. Database data types Because stored procedures not only can receive input parameters and give out a single resultset (like in the above example) but also can return output parameters, integer return values, multiple resultsets or XML streams, the nodes you have to include in the descriptive XML comment will vary. Data recovery johannesburg The following table lists all the node types you can put in the descriptive comment of a stored procedure in order to feed DBdoc with the proper information (see the SP_comment.xml file in the downloadable ZIP for an example): the “path” (with parts delimited by backslashes) that describes where, in the CHM topics hierarchy (the “Table of contents” of the final CHM document), the description of this stored procedure will appear. Iphone 5 data recovery software For example, by specifying a of “FRC\Tree” for the xnetFRCInit stored procedure, and a of “FRC” for the stored procedures xnetRFCMenuGet and xnetFRCTreeMenuCreate, you obtain this organization of topics in the final CHM: To supply a descriptive comment for a trigger, you have to proceed in the same way you did for stored procedures. Database operations Put the XML comment in the code implementing the trigger as shown here: CREATE TRIGGER [TriggerName] ON [TableName] The node types you can put in the descriptive comment of a trigger are listed here (see the TR_comment.xml file in the downloadable ZIP for an example): the “path” (with parts delimited by backslashes) that describes where, in the CHM topics hierarchy (the “Table of contents” of the final CHM document), the description of this trigger will appear Again, to supply a descriptive comment for an UDF, you have to proceed in the same way you did for stored procedures and triggers. Database index Even if the syntax for the various types of UDF differs (for Scalar Functions, Inline Table-valued Functions, Multi-statement Table-valued Functions), you have to put the XML descriptive comment in the code implementing the UDF, just after the AS keyword.


Node types you can put in the descriptive comment of a Scalar Function (see the UDFscalar_comment.xml file in the downloadable ZIP for an example): the “path” (with parts delimited by backslashes) that describes where, in the CHM topics hierarchy (the “Table of contents” of the final CHM document), the description of this UDF will appear Node types you can put in the descriptive comment of an Inline Table-valued Function or of a Multi-statement Table-valued Function (respectively, see the UDFinline_comment.xml and the UDFmultistat_comment.xml files in the downloadable ZIP for an example): the “path” (with parts delimited by backslashes) that describes where, in the CHM topics hierarchy (the “Table of contents” of the final CHM document), the description of this UDF will appear The position where to put the XML descriptive comment in your T-SQL code is important: I recommend to put it after the AS keyword because if you use some source control tools, such as SqlXPress, comments outside the CREATE statement could be cut off during saving/retrieval (checkin/checkout) operations. Don’t use “<" and ">” inside the descriptive text of your comment (or you will break the correctness of the XML comment fragment): use instead “<” and “>”. I suggest to keep the

node text very short (as inside MSDN documentation): you can go in more details in the text. Database crud Points of interest The two main points of interest in DBdoc are the T-SQL parser implementation with the Spart library, and the advanced use of SQL Server metadata access. DBdoc is quite far to be a perfect and complete tool. Drupal 8 database It has to be improved in various areas and extended in many features. Data recovery disk Some of them could be the subject for future enhancements. • Currently, for example, there is no support for documenting database VIEWS. Database 3 tier architecture Also, DBdoc doesn’t manage any T-SQL code created with the ENCRYPTION option. Data recovery orlando Another weakness of DBdoc is in the cross-reference detection algorithm; it simply looks for known object names inside the T-SQL code of other objects (see the FindObjectNameInScript procedure): this approach is very simple and is not fully reliable, especially when T-SQL code contains dynamic SQL (then, some cross-references could be missing). • Currently, DBdoc doesn’t check about correspondance between actual parameters in the routines and the ones you documented in the XML comments. • Currently, you have to manually invoke the “HTML Help Workshop” compiler in order to produce the final CHM file: DBdoc could perform this step automatically, as other products like NDoc do. • Finally, of course, DBdoc is designed for SQL Server 2000 and it doesn’t support SQL Server 2005; modifying DBdoc to support the new version of Microsoft SQL Server is not so simple, because of new syntax, new metadata storage, and so on… Database cardinality Do you accept the challenge? 🙂 The main point is however to simply create the Sql Server 2000 exported sys table spt_datatype_info into DbDoc database and then fix the table reference at line 675 in frmMain.vb to ” JOIN DBdoc.dbo.spt_datatype_info spt_dtp” * The content of a multiline “

” is now converted to html friendly. Database unit testing Conversion is by linefeed and tabs, or, if comment contains several “:”, it a table will be created columns seperated by “:”. see the topic “System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint ‘PK_RoutinesXref’. I data recovery software free download Cannot insert duplicate key in object ‘RoutinesXref’.”. … O review database and some other minor stuff I don’t recall right now. Database in recovery All the stuff I’ve with it is anyway quick and ugly, but lets hope that it will get you started!

banner