問題描述

重復:從 XML 生成 SQL 架構

在我正在進行的項目中，我需要支持強類型數(shù)據(jù)集以將數(shù)據(jù)存儲為 XML，或?qū)?shù)據(jù)存儲在 sql server 中.現(xiàn)在我已經(jīng)創(chuàng)建了 XSD 架構，我希望能夠使用 XSD 中定義的表和關系創(chuàng)建一個 sql server 數(shù)據(jù)庫.

In a project i am working on, i have a need to support either a strongly-typed dataset for storing the data as XML, or storing the data in sql server. Now i already have the XSD schema created and i would like to be able to create a sql server database using the tables and relationships defined in the XSD.

這可能嗎?如果是這樣，解決這個問題的最佳方法是什么?

Is this possible? and if so, what is the best way to approach this problem?

說明:我正在尋找的是一種在運行時使用 C# 和 SQL Server 通過代碼執(zhí)行上述操作的方法.這個可以嗎?

Clarification: What i'm looking for is a way to do the above via code at runtime with C# and SQL Server. Can this be done?

推薦答案

我設法提出了以下基于 SQL Server 管理對象的類:

I managed to come up with the following class based on the 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();
    }

}
}

它尚未經(jīng)過嚴格測試，需要映射出更多 SQL 到 CLR 類型，但它確實創(chuàng)建了一個新數(shù)據(jù)庫，包含所有表、列、主鍵和外鍵.

It hasn't been rigorously tested yet, and there needs to be more SQL to CLR types mapped out, but it does create a new database, all the tables, columns, primary keys, and foreign keys.

要使此代碼正常工作，需要引用一些程序集:

For this code to work, a few assemblies need to be referenced:

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

希望這對其他人有所幫助.

Hope this helps someone else out.

這篇關于從 XSD 生成 SQL Server DB的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！