从XSD生成SQL Server数据库

Question

重复问题: 从XML生成SQL架构

---

在我正在处理的项目中，我需要支持使用强类型数据集将数据存储为XML，或将数据存储在SQL Server中。现在我已经创建了XSD模式，并且希望能够使用在XSD中定义的表和关系创建SQL Server数据库。

这可能吗？如果可以，最好的方法是什么？

---

澄清: 我想做的是通过C#和SQL Server在运行时以编程方式执行上述操作。这可行吗？

- Jason Miesionczek

---

不是重复问题，因为其他问题涉及oracle和java；这个问题涉及SQL Server。 - devio

Answer 1

我成功地基于SQL Server Management Objects编写了以下类：

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Text;
using Microsoft.SqlServer.Management.Common;
using Microsoft.SqlServer.Management.Smo;
using Rule=System.Data.Rule;

namespace XSD2SQL
{
public class XSD2SQL
{
    private readonly Server _server;
    private readonly SqlConnection _connection;
    private Database _db;
    private DataSet _source;
    private string _databaseName;

    public XSD2SQL(string connectionString, DataSet source)
    {
        _connection = new SqlConnection(connectionString);
        _server = new Server(new ServerConnection(_connection));
        _source = source;
    }

    public void CreateDatabase(string databaseName)
    {
        _databaseName = databaseName;
        _db = _server.Databases[databaseName];
        if (_db != null) _db.Drop();
        _db = new Database(_server, _databaseName);
        _db.Create();
    }

    public void PopulateDatabase()
    {
        CreateTables(_source.Tables);
        CreateRelationships();
    }

    private void CreateRelationships()
    {
        foreach (DataTable table in _source.Tables)
        {
            foreach (DataRelation rel in table.ChildRelations)
                CreateRelation(rel);
        }
    }

    private void CreateRelation(DataRelation relation)
    {
        Table primaryTable = _db.Tables[relation.ParentTable.TableName];
        Table childTable = _db.Tables[relation.ChildTable.TableName];

        ForeignKey fkey = new ForeignKey(childTable, relation.RelationName);
        fkey.ReferencedTable = primaryTable.Name;

        fkey.DeleteAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.DeleteRule);
        fkey.UpdateAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.UpdateRule);


        for (int i = 0; i < relation.ChildColumns.Length; i++)
        {
            DataColumn col = relation.ChildColumns[i];
            ForeignKeyColumn fkc = new ForeignKeyColumn(fkey, col.ColumnName, relation.ParentColumns[i].ColumnName);

            fkey.Columns.Add(fkc);
        }

        fkey.Create();

    }

    private void CreateTables(DataTableCollection tables)
    {
        foreach (DataTable table in tables)
        {                
            DropExistingTable(table.TableName);
            Table newTable = new Table(_db, table.TableName);

            PopulateTable(ref newTable, table);                
            SetPrimaryKeys(ref newTable, table);
            newTable.Create();

        }
    }

    private void PopulateTable(ref Table outputTable, DataTable inputTable)
    {
        foreach (DataColumn column in inputTable.Columns)
        {
            CreateColumns(ref outputTable, column, inputTable);
        }
    }

    private void CreateColumns(ref Table outputTable, DataColumn inputColumn, DataTable inputTable)
    {
        Column newColumn = new Column(outputTable, inputColumn.ColumnName);
        newColumn.DataType = CLRTypeToSQLType(inputColumn.DataType);
        newColumn.Identity = inputColumn.AutoIncrement;
        newColumn.IdentityIncrement = inputColumn.AutoIncrementStep;
        newColumn.IdentitySeed = inputColumn.AutoIncrementSeed;
        newColumn.Nullable = inputColumn.AllowDBNull;
        newColumn.UserData = inputColumn.DefaultValue;

        outputTable.Columns.Add(newColumn);
    }

    private void SetPrimaryKeys(ref Table outputTable, DataTable inputTable)
    {
        Index newIndex = new Index(outputTable, "PK_" + outputTable.Name);
        newIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
        newIndex.IsClustered = false;

        foreach (DataColumn keyColumn in inputTable.PrimaryKey)
        {                                
            newIndex.IndexedColumns.Add(new IndexedColumn(newIndex, keyColumn.ColumnName, true));                
        }
        if (newIndex.IndexedColumns.Count > 0)
            outputTable.Indexes.Add(newIndex);
    }



    private DataType CLRTypeToSQLType(Type type)
    {
        switch (type.Name)
        {
            case "String":
                return DataType.NVarCharMax;

            case "Int32":
                return DataType.Int;

            case "Boolean":
                return DataType.Bit;

            case "DateTime":
                return DataType.DateTime;

            case "Byte[]":
                return DataType.VarBinaryMax;


        }

        return DataType.NVarCharMax;
    }

    private ForeignKeyAction SQLActionTypeToSMO(Rule rule)
    {
        string ruleStr = rule.ToString();

        return (ForeignKeyAction)Enum.Parse(typeof (ForeignKeyAction), ruleStr);
    }

    private void DropExistingTable(string tableName)
    {
        Table table = _db.Tables[tableName];
        if (table != null) table.Drop();
    }

}
}

目前尚未进行严格测试，还需要更多SQL到CLR类型的映射，但它确实可以创建一个新的数据库，包括所有表、列、主键和外键。

为了使此代码正常工作，需要引用几个程序集：

Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SqlEnum

希望这能帮助其他人。

- Jason Miesionczek

---

帮了我很多。感谢您发布这个。非常有用。 - spender

我是C#的新手...你能否给个使用这个类的例子？我想用SQLite做类似的事情...你认为这可能吗？ - Salvatore

不确定这是否适用于SQLite，因为它使用SQL Server管理对象。 - Jason Miesionczek

@JasonMiesionczek，我认为你的类恰好可以解决我今天遇到的问题，但我需要更进一步：数据行。我有一个XML文件，我使用ds.ReadXml()来导出完整的DataSet，而你的类在创建结构方面做得非常好。是否有一种方法可以扩展该类以将数据也放入其中？ - DonBoitnott

@JasonMiesionczek 我同意，如果能够扩展到读取XML并填充数据库，那将非常有帮助。 - Hancs

Answer 2

我会编写一些XSLT代码，将XSD转换为SQL创建语句。

- Chris Nava

Answer 3

如果您正在使用SQL2005，您可以创建一个具有强类型XML列的表，以便每个值都根据XML模式集合（即XSD）进行验证。但是我无法告诉您任何关于性能、可扩展性等方面的信息。

如果您尝试将XSD转换为一组关系表，您会发现XSD元素和SQL表之间没有唯一的映射：

XSD子元素可以作为详细表实现，也可以作为表示元素的一组列（如果只允许1个子元素），或者作为强制/可选的1:1/1:n关系。

XSD子元素的集合可以是主从关系，也可以是存储在单独表中的n:m关系以及属性。

我IRC在XSD中没有主键和唯一约束的定义，这在自动模式生成中提出了另一个问题。

这并不意味着没有人费心开发这样的工具。但这肯定意味着任务不能完全自动化。

- devio