create tables from xsd to sql server database c 23

1using System;
2using System.Collections.Generic;
3using System.Data;
4using System.Data.SqlClient;
5using System.IO;
6using System.Text;
7using Microsoft.SqlServer.Management.Common;
8using Microsoft.SqlServer.Management.Smo;
9using Rule=System.Data.Rule;
10
11namespace XSD2SQL
12{
13public class XSD2SQL
14{
15    private readonly Server _server;
16    private readonly SqlConnection _connection;
17    private Database _db;
18    private DataSet _source;
19    private string _databaseName;
20
21    public XSD2SQL(string connectionString, DataSet source)
22    {
23        _connection = new SqlConnection(connectionString);
24        _server = new Server(new ServerConnection(_connection));
25        _source = source;
26    }
27
28    public void CreateDatabase(string databaseName)
29    {
30        _databaseName = databaseName;
31        _db = _server.Databases[databaseName];
32        if (_db != null) _db.Drop();
33        _db = new Database(_server, _databaseName);
34        _db.Create();
35    }
36
37    public void PopulateDatabase()
38    {
39        CreateTables(_source.Tables);
40        CreateRelationships();
41    }
42
43    private void CreateRelationships()
44    {
45        foreach (DataTable table in _source.Tables)
46        {
47            foreach (DataRelation rel in table.ChildRelations)
48                CreateRelation(rel);
49        }
50    }
51
52    private void CreateRelation(DataRelation relation)
53    {
54        Table primaryTable = _db.Tables[relation.ParentTable.TableName];
55        Table childTable = _db.Tables[relation.ChildTable.TableName];
56
57        ForeignKey fkey = new ForeignKey(childTable, relation.RelationName);
58        fkey.ReferencedTable = primaryTable.Name;
59
60        fkey.DeleteAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.DeleteRule);
61        fkey.UpdateAction = SQLActionTypeToSMO(relation.ChildKeyConstraint.UpdateRule);
62
63
64        for (int i = 0; i < relation.ChildColumns.Length; i++)
65        {
66            DataColumn col = relation.ChildColumns[i];
67            ForeignKeyColumn fkc = new ForeignKeyColumn(fkey, col.ColumnName, relation.ParentColumns[i].ColumnName);
68
69            fkey.Columns.Add(fkc);
70        }
71
72        fkey.Create();
73
74    }
75
76    private void CreateTables(DataTableCollection tables)
77    {
78        foreach (DataTable table in tables)
79        {                
80            DropExistingTable(table.TableName);
81            Table newTable = new Table(_db, table.TableName);
82
83            PopulateTable(ref newTable, table);                
84            SetPrimaryKeys(ref newTable, table);
85            newTable.Create();
86
87        }
88    }
89
90    private void PopulateTable(ref Table outputTable, DataTable inputTable)
91    {
92        foreach (DataColumn column in inputTable.Columns)
93        {
94            CreateColumns(ref outputTable, column, inputTable);
95        }
96    }
97
98    private void CreateColumns(ref Table outputTable, DataColumn inputColumn, DataTable inputTable)
99    {
100        Column newColumn = new Column(outputTable, inputColumn.ColumnName);
101        newColumn.DataType = CLRTypeToSQLType(inputColumn.DataType);
102        newColumn.Identity = inputColumn.AutoIncrement;
103        newColumn.IdentityIncrement = inputColumn.AutoIncrementStep;
104        newColumn.IdentitySeed = inputColumn.AutoIncrementSeed;
105        newColumn.Nullable = inputColumn.AllowDBNull;
106        newColumn.UserData = inputColumn.DefaultValue;
107
108        outputTable.Columns.Add(newColumn);
109    }
110
111    private void SetPrimaryKeys(ref Table outputTable, DataTable inputTable)
112    {
113        Index newIndex = new Index(outputTable, "PK_" + outputTable.Name);
114        newIndex.IndexKeyType = IndexKeyType.DriPrimaryKey;
115        newIndex.IsClustered = false;
116
117        foreach (DataColumn keyColumn in inputTable.PrimaryKey)
118        {                                
119            newIndex.IndexedColumns.Add(new IndexedColumn(newIndex, keyColumn.ColumnName, true));                
120        }
121        if (newIndex.IndexedColumns.Count > 0)
122            outputTable.Indexes.Add(newIndex);
123    }
124
125
126
127    private DataType CLRTypeToSQLType(Type type)
128    {
129        switch (type.Name)
130        {
131            case "String":
132                return DataType.NVarCharMax;
133
134            case "Int32":
135                return DataType.Int;
136
137            case "Boolean":
138                return DataType.Bit;
139
140            case "DateTime":
141                return DataType.DateTime;
142
143            case "Byte[]":
144                return DataType.VarBinaryMax;
145
146
147        }
148
149        return DataType.NVarCharMax;
150    }
151
152    private ForeignKeyAction SQLActionTypeToSMO(Rule rule)
153    {
154        string ruleStr = rule.ToString();
155
156        return (ForeignKeyAction)Enum.Parse(typeof (ForeignKeyAction), ruleStr);
157    }
158
159    private void DropExistingTable(string tableName)
160    {
161        Table table = _db.Tables[tableName];
162        if (table != null) table.Drop();
163    }
164
165}
166}
167