sqldoc.cs

是一个把MSSERVICE数据库表结构导出成一个HTML文档

// sqldoc
// coded by Jose A. Gonzalvo
// JAG Software 2004
// 09/06/04

using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
using System.Xml.Xsl;

namespace JAGSoftware.SqlDoc
{
    /// <summary>
    /// class to extract tables and fields info from a Sql Server database and to a xml/html file
    /// </summary>
    public class SqlDoc
    {

        private const string SQL_GETTABLES = "SELECT TABLE_NAME FROM INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE'";
        //private const string SQL_GETFIELDS = "SELECT TABLE_NAME, COLUMN_NAME, COLUMN_DEFAULT, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME IN (" + SQL_GETTABLES + ")";

        //		private const string SQL_GETTABLES = "SELECT id, name AS TABLE_NAME FROM SYSOBJECTS WHERE xtype = 'U'";
        //private const string SQL_GETFIELDS = "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME, systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, sysproperties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as COLUMN_DEFAULT, syscolumns.isnullable as IS_NULLABLE " +
        //    "FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype " +
        //    "LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id " + 
        //                        "LEFT OUTER JOIN sysproperties ON (sysproperties.smallid = syscolumns.colid AND sysproperties.id = syscolumns.id) " +
        //                            "LEFT OUTER JOIN	syscomments ON syscolumns.cdefault = syscomments.id " +
        //    "WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')" +
        //    "ORDER BY syscolumns.colid";
        //TUPUNCO
        private const string SQL_GETFIELDS = "SELECT Sysobjects.name AS TABLE_NAME, syscolumns.Id, syscolumns.name AS COLUMN_NAME, systypes.name AS DATA_TYPE, syscolumns.length as CHARACTER_MAXIMUM_LENGTH, sysproperties.[value] AS COLUMN_DESCRIPTION,  syscomments.text as COLUMN_DEFAULT, syscolumns.isnullable as IS_NULLABLE, " +
            " CASE WHEN SysColumns.colid in (SELECT a.colid FROM syscolumns a INNER join sysobjects b ON a.id=b.id and b.xtype='U' and b.name<>'dtproperties' WHERE exists(SELECT 1 FROM sysobjects WHERE xtype='PK' and name in (SELECT name FROM sysindexes WHERE indid in(SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) and b.id=SysObjects.id) then 1 else 0 end as IS_KEY " +
            "FROM syscolumns INNER JOIN systypes ON syscolumns.xtype = systypes.xtype " +
            "LEFT JOIN sysobjects ON syscolumns.id = sysobjects.id " +
                                "LEFT OUTER JOIN sysproperties ON (sysproperties.smallid = syscolumns.colid AND sysproperties.id = syscolumns.id) " +
                                    "LEFT OUTER JOIN	syscomments ON syscolumns.cdefault = syscomments.id " +
            "WHERE syscolumns.id IN (SELECT id FROM SYSOBJECTS WHERE xtype = 'U') AND (systypes.name <> 'sysname')" +
            "ORDER BY syscolumns.colid";

        public static void Document(string connectionString, string xslFile, string outputFile)
        {
            if (xslFile.Trim().Length == 0)
                throw new ArgumentException("xslFile parameter missing", "xslFile");

            if (outputFile.Trim().Length == 0)
                throw new ArgumentException("outputFile parameter missing", "outputFile");

            DataSet ds = QueryDatabase(connectionString);
            ProcessDataSet(ds, xslFile, outputFile);
        }

        private static void ProcessDataSet(DataSet ds, string xslFile, string outputFile)
        {
            XmlDataDocument xmlDoc = new XmlDataDocument(ds);

            XslTransform xslTran = new XslTransform();
            xslTran.Load(xslFile);

            XmlTextWriter writer = new XmlTextWriter(outputFile, System.Text.Encoding.UTF8);

            xslTran.Transform(xmlDoc, null, writer, null);
            writer.Close();
        }

        private static DataSet QueryDatabase(string connectionString)
        {

            DataSet ds = new DataSet("MyDatabase");
            // add a new table with general info

            DataTable dt = ds.Tables.Add("Database");
            dt.Columns.Add("Name");
            dt.Columns.Add("Date");
            DataRow dr = dt.NewRow();
            string searchPattern = "Initial Catalog=";
            int index1 = connectionString.IndexOf(searchPattern) + searchPattern.Length;
            int index2 = connectionString.IndexOf(";", index1);
            string dbName = connectionString.Substring(index1, index2 - index1);
            dr.ItemArray = new object[] { dbName, DateTime.Now.ToShortDateString() };
            dt.Rows.Add(dr);

            SqlConnection cnx = new SqlConnection(connectionString);
            cnx.Open();

            SqlDataAdapter tablesDA = new SqlDataAdapter(SQL_GETTABLES, cnx);
            tablesDA.Fill(ds, "Tables");

            SqlDataAdapter fieldsDA = new SqlDataAdapter(SQL_GETFIELDS, cnx);
            fieldsDA.Fill(ds, "Fields");

            cnx.Close();

            ds.Relations.Add("TablesFields",
                ds.Tables["Tables"].Columns["TABLE_NAME"],
                ds.Tables["Fields"].Columns["TABLE_NAME"]).Nested = true;

            ds.WriteXml("test.xml");
            return ds;
        }

    }
}