Create SQL Server DB from DataSet

Question

I read xsd and xml file in DataSet, now I want create db from this DataSet

foreach (DataTable dt in temp.Tables) {
    foreach (DataColumn dc in dt.Columns) {
        //example for one column
        SqlCommand createtable = new SqlCommand(
            "create table " + dt.TableName + " (" 
            + dc.ColumnName + "  varchar(max))", conn);
        createtable.ExecuteNonQuery();
    }
}

But I have some problem, when I create db table I need column type and size from XSD (in example use varchar(max)). How to fix this?

For example in xsd I have

<xs:restriction base="xs:string">
<xs:maxLength value="36"/>
<xs:minLength value="1"/>
</xs:restriction>

or

<xs:restriction base="xs:string">
<xs:maxLength value="40"/>
</xs:restriction>

or

<xs:restriction base="xs:decimal">
<xs:totalDigits value="19"/>
<xs:fractionDigits value="2"/>
</xs:restriction>

In the end I need script to create db tables with size of columns (like in xsd)

UPD: Maybe use XmlSchemaSet to parse XSD?

Walk over the DataColumn(s) of the Columns Property of the DataTable and create the approriate column definitions. — Ralf, Jun 01 '15 at 11:48
Check [this MSDN article](https://msdn.microsoft.com/en-us/library/system.data.datacolumn.datatype%28v=vs.110%29.aspx) ;). — shA.t, Jun 01 '15 at 11:56
You should iterate over the DataColumns (as you do now) and simply read the needed info from the DataColumn. A DataColumn has a DataType and a MaxLength Property. Map DataType (thats a .Net Type) to the Sql DataType you want and attch the Length if needed. — Ralf, Jun 01 '15 at 11:57
You should import the XML directly into SQL Server to create new database. No need to use a C# application. Try this : http://stackoverflow.com/questions/2628327/how-to-build-a-database-from-an-xsd-schema-and-import-xml-data — jdweng, Jun 01 '15 at 12:08
MaxLength -1 means (max) - i.e varchar(max), varbinady(max) etc`. Didn't the answers in the links you've got in the comments helped you? if you already have a dataset, you don't need the xsd anymore, you can simply use the dataset. However, restrictions like minlength might be tricky to handle, and I'm not sure they are even tralslated to the dataset. — Zohar Peled, Jun 04 '15 at 11:48
@ZoharPeled when i create colunm i need use informstion from xsd, for example `string` `max lenght = 10` - so in sql i created this column with this size — e1s, Jun 04 '15 at 12:15
you can get the data type, max length and probably a lot more information from the dataset columns... — Zohar Peled, Jun 04 '15 at 12:17
As @ZoharPeled: said: look at the other properties of the `DataColumn` type. They will get you all of the per-column information that the `DataSet` accepted from the XSD. — John Saunders, Jun 07 '15 at 02:09
@JohnSaunders, for examle in xsd I have `minLenght`, but in `DataColumn` I can't find this property. — e1s, Jun 07 '15 at 10:23
That's one of the examples of the difference between XSD and most relational databases - most databases always have a minLength of 0 (NULL) or 1 (NOT NULL). What's your reason for creating this database? Are you going to store the data from XML files which validate against this schema? Then you may want to consider storing the XML _as_ XML, in a database. SQL Server has this capability, for instance. — John Saunders, Jun 07 '15 at 17:05

Imran Ali Khan · Accepted Answer · 2015-06-12T08:43:17.500

4

use XSD2DB

XSD2DB is a command line tool written in C#, that will read a Microsoft ADO.NET compatible DataSet Schema File (XSD) and generate a database.

link

edited Jun 12 '15 at 08:43

answered Jun 07 '15 at 01:58

Imran Ali Khan

8,469
16
52
77

score 1 · Answer 2 · edited Jun 12 '15 at 05:26

I guess there's no general solution to this. An XSD can easily describe something that does not map to a relational database.While you can try to "automate" this, your XSD's must be designed with a relational database in mind, or it won't work out well.

However you can transfer your datatables in memory to sql server database using SQLServer Management Objects and SqlBulkCopy.

Please refer to this link for more information. Hope this may help you.

score 1 · Answer 3 · answered Jun 09 '15 at 19:43

1

Would scaffolding be able to help you in this situation?

Maybe take a look at that, I know there was a way of doing a reverse engeneering of the database first with scaffolding

answered Jun 09 '15 at 19:43

sel sel

21
2

Create SQL Server DB from DataSet

3 Answers3

Linked