Customize Applications with XML Fragments: Part 2

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data.Common;
using System.Data;

namespace Bulasoft.Common.SchemaGenerators
{
    internal class MSSQLSchemaGenerator : SchemaGeneratorBase
    {
        protected override string GenerateTableCommand(TableDescriptor name, FieldDescriptorList fields)
        {
            StringBuilder commandText;
            if (fields.Count == 0)
            {
                commandText = new StringBuilder("create table ");
                commandText.AppendFormat("[{0}] (", name.Name);
                commandText.Append(Environment.NewLine);
                foreach (FieldDescriptor field in name.Fields)
                    if (field.HasNulls)
                        commandText.AppendFormat(" [{0}] {1}," + Environment.NewLine, field.Name, FieldToSQL(field));
                    else
                    {
                        if (field.IsPK)
                            commandText.AppendFormat(" [{0}] {1} NOT NULL CONSTRAINT {2} PRIMARY KEY," + Environment.NewLine, field.Name, FieldToSQL(field), SchemaGenerator.PKPrefix + name.Name);
                        else
                            commandText.AppendFormat(" [{0}] {1} NOT NULL," + Environment.NewLine, field.Name, FieldToSQL(field));
                    }

                commandText.Remove(commandText.Length - 1 - Environment.NewLine.Length, 1 + Environment.NewLine.Length);
                commandText.Append(")");
            }
            else
            {
                bool someChange = false;
                commandText = new StringBuilder("alter table ");
                commandText.AppendFormat("[{0}] add", name.Name);
                commandText.Append(Environment.NewLine);
                foreach (FieldDescriptor field in name.Fields)
                    if (!fields.Contains(field.Name))
                    {
                        someChange = true;
                        if (field.HasNulls)
                            commandText.AppendFormat("[{0}] {1}," + Environment.NewLine, field.Name, FieldToSQL(field));
                        else
                            commandText.AppendFormat("[{0}] {1} NOT NULL," + Environment.NewLine, field.Name, FieldToSQL(field));
                    }

                if (!someChange)
                    return null;
                commandText.Remove(commandText.Length - 1 - Environment.NewLine.Length, 1 + Environment.NewLine.Length);
                commandText.Append("");
            }
            return commandText.ToString();
        }

        protected override string FieldToSQL(FieldDescriptor field)
        {
            switch (Type.GetTypeCode(field.FieldSystemType))
            {
                case TypeCode.DateTime:
                    return "datetime";
                case TypeCode.Int32:
                    return "int";
                case TypeCode.Int64:
                    return "bigint";
                case TypeCode.Double:
                    return "float";
                case TypeCode.String:
                    if (field.Length.HasValue)
                        return string.Format("nvarchar({0})", field.Length);
                    else
                        return "nvarchar(max)";
                case TypeCode.Boolean:
                    return "bit";
                case TypeCode.Decimal:
                    return "money";
                default:
                    {
                        if (field.FieldSystemType == typeof(Guid))
                            return "uniqueidentifier";

                        if (field.FieldSystemType == typeof(TimeSpan))
                            return "char(11)";

                        if (field.FieldSystemType == typeof(Enum))
                            return "int";

                        throw new ArgumentException(string.Format("Unsupported data type {0}", field.FieldSystemType.GetType().Name));
                    }
            }
        }

        protected override string GenerateFKCommand(TableDescriptor table, FieldDescriptor field)
        {
            return string.Format("ALTER TABLE {0} ADD CONSTRAINT {1} FOREIGN KEY ({2}) REFERENCES {3}",
                table.Name, table.GetFKName(field), field.Name, field.FKTableName);
        }

        protected override FieldDescriptorList GetFieldDescriptorList(string tableName)
        {
            FieldDescriptorList result = new FieldDescriptorList();
            DataColumn tableNameCol = allColumns.Columns["table_name"];
            DataColumn dbTypeCol = allColumns.Columns["data_type"];
            DataColumn columnNameCol = allColumns.Columns["column_name"];

            foreach (DataRow dr in allColumns.Rows)
            {
                if (dr[tableNameCol].ToString() == tableName)
                {
                    string dataType = dr[dbTypeCol].ToString();
                    FieldDescriptor descriptor = result.Add(dr[columnNameCol].ToString(), dataType);
                    descriptor.HasNulls = dr["is_nullable"].ToString().Equals("YES", StringComparison.OrdinalIgnoreCase);
                    if (dataType == "nvarchar")
                        descriptor.Length = (int)dr["character_maximum_length"];
                    if (dataType == "decimal")
                    {
                        descriptor.Precision = (byte)dr["numeric_precision"];
                        descriptor.Scale = (byte)dr["numeric_scale"];
                    }
                }
            }

            return result;
        }

        protected override string GenerateIndexCommand(TableDescriptor table, FieldDescriptor field)
        {
            return string.Format("CREATE INDEX {0} ON {1} ({2})", SchemaGenerator.IndexPrefix + table.Name + '_' + field.Name, table.Name, field.Name);
        }

    }
}