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
– Ralf, Commented Jun 1, 2015 at 11:48
You should import the XML directly into SQL Server to create new database. No need to use a C# application. Try this : stackoverflow.com/questions/2628327/… — jdweng
– jdweng, Commented Jun 1, 2015 at 12:08
you can get the data type, max length and probably a lot more information from the dataset columns... — Zohar Peled
– Zohar Peled, Commented Jun 4, 2015 at 12:17

I A Khan · Accepted Answer · 2015-06-12 08:43:17Z

4

+25

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, 2015 at 8:43

answered Jun 7, 2015 at 1:58

I A Khan

8,92716 gold badges57 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sel sel · Accepted Answer · 2015-06-09 19:43:16Z

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 9, 2015 at 19:43

sel sel

212 bronze badges

Comments

I A Khan · Accepted Answer · 2015-06-12 05:26:04Z

1

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.

edited Jun 12, 2015 at 5:26

I A Khan

8,92716 gold badges57 silver badges78 bronze badges

answered Jun 6, 2015 at 11:08

killer

6022 gold badges10 silver badges31 bronze badges

Collectives™ on Stack Overflow

Create SQL Server DB from DataSet

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related