从 XSD 生成 SQL Server DB

Generating SQL Server DB from XSD(从 XSD 生成 SQL Server DB)
本文介绍了从 XSD 生成 SQL Server DB的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

重复:从 XML 生成 SQL 架构

在我正在进行的项目中,我需要支持强类型数据集以将数据存储为 XML,或将数据存储在 sql server 中.现在我已经创建了 XSD 架构,我希望能够使用 XSD 中定义的表和关系创建一个 sql server 数据库.

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 通过代码执行上述操作的方法.这个可以吗?

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();
    }

}
}

它尚未经过严格测试,需要映射出更多 SQL 到 CLR 类型,但它确实创建了一个新数据库,包含所有表、列、主键和外键.

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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持编程学习网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

DispatcherQueue null when trying to update Ui property in ViewModel(尝试更新ViewModel中的Ui属性时DispatcherQueue为空)
Drawing over all windows on multiple monitors(在多个监视器上绘制所有窗口)
Programmatically show the desktop(以编程方式显示桌面)
c# Generic Setlt;Tgt; implementation to access objects by type(按类型访问对象的C#泛型集实现)
InvalidOperationException When using Context Injection in ASP.Net Core(在ASP.NET核心中使用上下文注入时发生InvalidOperationException)
LINQ many-to-many relationship, how to write a correct WHERE clause?(LINQ多对多关系,如何写一个正确的WHERE子句?)