Create DataBase using DataSet using Vb.NET

Create DataBase using DataSet using Vb.NET

Step 1)  Import XML file into DataSet

              ds.ReadXml("c:\data\books.xml")

Once you import books.xml into DataSet. It will create multiple tables based on xml data.
In our case, Book has many authors,  so 1-many relationship will be created between book(one)-authors(many).
 The above dataset has 3 tables.
               1.book table
               2.author table
               3.authors table(relationship between table 1 & 2).

Step 2)  Create Database using DataSet(i.e SqlServer Management Objects SMO)

Creating  Database using   SQLServer Management Objects(SMO).
I am naming database as "BooksList", Creating 3 tables that exists in above dataset.

Using SQLServer Management Objects in C#/ASP.NET/WPF

Add following references located in C:\Program Files\Microsoft SQL Server\110\SDK\Assemblies

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

        void CreateDataBase()
        {
            if (ds.Tables.Count = 0) LoadXmlIntoDataSet()
            Try
            Microsoft.SqlServer.Management.Smo.Server server =
                New Microsoft.SqlServer.Management.Smo.Server(New ServerConnection()
                {
                ConnectAsUser = False,
                ServerInstance = "machiname\sqlexpress2012",
                DatabaseName = "master",
                ConnectAsUserName = "sa",
                ConnectAsUserPassword = "pwd&"

                }
)
            Dim db As Database =  New Database(server,"master")
            db.Name = "BooksList"
            'db.Drop();

            AddDBTables(db)
            CreateDatabse_and_tables(db)
            CreateForeignKeysForTables(db)

            db.AutoClose = True
            Catch ex As Exception
                 Response.Write(ex.Message)

            End Try

        }
        void CreateForeignKeysForTables(Database db)
        {
            Dim fKeybook As ForeignKey =  New ForeignKey(db.Tables("authors"),"book_table_book_id")
            fKeybook.IsEnabled = True
            Dim fkbookidcol As ForeignKeyColumn =  New ForeignKeyColumn(fKeybook,"book_id","book_id")
            fKeybook.Columns.Add(fkbookidcol)
            fKeybook.ReferencedTable = "book"

            fKeybook.Create()

        }
 //First Create New Database called "BooksList"
//Second Create DB tables similar to DataSet tables
        void CreateDatabse_and_tables(Database db)
        {
        db.Create()
        Dim table As Microsoft.SqlServer.Management.Smo.Table
        For Each table In db.Tables
        table.Create()
        Next

        }
'This method adds Database tables and columns associated with each table
'including primary keys         void AddDBTables(Database db)
        {
            Dim tbl As DataTable
            For Each tbl In ds.Tables
             Microsoft.SqlServer.Management.Smo.Table table =
                 New Microsoft.SqlServer.Management.Smo.Table(db, tbl.TableName)
             AddDBColumns(table, tbl)
             db.Tables.Add(table)
            Next
         }
        void AddDBColumns(Microsoft.SqlServer.Management.Smo.Table table, DataTable datatable)
        {
            Dim col As DataColumn
            For Each col In datatable.Columns

                Dim tblcol As Column =  New Column()
                tblcol.Name = col.ColumnName
                'tblcol.Create();
                table.Columns.Add(tblcol)
              'Find out better logic
                if(col.DataType.Name="String")
                {
                    tblcol.DataType = DataType.NVarChar(col.MaxLength)

                }
                if (col.DataType.Name = "Int32" Or col.DataType.Name = "Int64")
                {
                    tblcol.DataType = DataType.Int
                }
                tblcol.Default = col.DefaultValue.ToString()

                tblcol.Nullable = col.AllowDBNull
                if (col.AutoIncrement)
                {
                    tblcol.Nullable = False
                    Dim idx As Index =  New Index(table,"pk_" + col.ColumnName)
                    idx.IndexedColumns.Add(New IndexedColumn(idx,tblcol.Name))
                    idx.IsClustered = True
                    idx.IsUnique = True
                    idx.IndexKeyType = IndexKeyType.DriPrimaryKey
                    table.Indexes.Add(idx)

                    tblcol.Identity = col.AutoIncrement
                    tblcol.IdentityIncrement = col.AutoIncrementStep
                    tblcol.IdentitySeed = col.AutoIncrementSeed
                }

            Next


        }
         'Loading XML File into dataSet

        void LoadXmlIntoDataSet()
        {
            ds.ReadXml(Server.MapPath("/data/books.xml"))
        }

//Once Database is Created with table objects.
//we can populate Database  tables (just now created step2) using ADO.NET Bulk Insert

Step 3) Bulk Insert Datatable into SqlServer

      'Batach Insert using BulkCopy in ADO.NET
         void BulkInsertData()
        {
        SqlBulkCopy blkCopy = New SqlBulkCopy("server=(local)\SqlExpress2012;uid=sa;pwd=pwd&;database=BooksList",
            SqlBulkCopyOptions.KeepIdentity)
          Dim table As DataTable
          For Each table In ds.Tables
              blkCopy.DestinationTableName = table.TableName
              blkCopy.WriteToServer(table)
          Next
            blkCopy.Close()
        }
Here is the Database called BooksList and Tables in Dataset with Data.

books.xml

<?xml version="1.0" encoding="utf-8" ?>

<bookslist>

  <book id="ISBN-220-244">

    <title>LINQ Unleased</title>

    <price>$40.99</price>

    <authors>

      <author>

        <firstname>Kimmel </firstname>

        <lastname>Paul</lastname>

      </author>

    </authors>

    <publisher>Sams</publisher>

  </book>

  <book id="ISBN-456-344">

    <title>C# Design Patterns</title>

    <price>$30.99</price>

    <authors>

      <author>

        <firstname>john</firstname>

        <lastname>peter</lastname>

      </author>

      <author>

        <firstname>mary</firstname>

        <lastname>kurl</lastname>

      </author>

    </authors>

    <publisher>Tata Mcgraw Hill</publisher>

  </book>

  <book id="ISBN-434-233">

    <title>Pro Entity Framework 4.0</title>

    <price>29.99</price>

    <authors>

      <author>

        <firstname>Scott</firstname>

        <lastname>Klein</lastname>

      </author>

    </authors>

    <publisher>APress</publisher>

  </book>

  <book id="ISBN-433-234">

    <title>Beginning PHP and MySQL E-Commerce</title>

    <price>$35.99</price>

    <authors>

      <author>

        <firstname>Cristian</firstname>

        <lastname>Darie</lastname>

      </author>

      <author>

        <firstname>Emilian</firstname>

        <lastname>Balanescu</lastname>

      </author>

    </authors>

    <publisher>APress</publisher>

  </book>

</bookslist>

Source Code

Imports System
Imports System.Collections.Generic
Imports System.Linq
Imports System.Web
Imports System.Web.UI
Imports System.Data
Imports System.Web.UI.WebControls
Imports System.Data.SqlClient
Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common
Namespace WebApplication1
    Public partial Class xmltodatabase
     Inherits System.Web.UI.Page
        Dim ds As DataSet =  New DataSet("XmlImport")
        protected void Page_Load(Object sender, EventArgs e)
        {

            CreateDataBase()
            BulkInsertData()
        }

        void CreateDataBase()
        {
            if (ds.Tables.Count = 0) LoadXmlIntoDataSet()
            Try
            Microsoft.SqlServer.Management.Smo.Server server =
                New Microsoft.SqlServer.Management.Smo.Server(New ServerConnection()
                {
                ConnectAsUser = False,
                ServerInstance = "seethapathi-PC\sqlexpress2012",
                DatabaseName = "master",
                ConnectAsUserName = "sa",
                ConnectAsUserPassword = "lordsiva2030&"

                }
)
            Dim db As Database =  New Database(server,"master")
            db.Name = "BooksList"
            'db.Drop();

            AddDBTables(db)
            CreateDatabse_and_tables(db)
            CreateForeignKeysForTables(db)

            db.AutoClose = True
            Catch ex As Exception
                 Response.Write(ex.Message)

            End Try

        }
        void CreateForeignKeysForTables(Database db)
        {
            Dim fKeybook As ForeignKey =  New ForeignKey(db.Tables("authors"),"book_table_book_id")
            fKeybook.IsEnabled = True
            Dim fkbookidcol As ForeignKeyColumn =  New ForeignKeyColumn(fKeybook,"book_id","book_id")
            fKeybook.Columns.Add(fkbookidcol)
            fKeybook.ReferencedTable = "book"

            fKeybook.Create()

        }
        void CreateDatabse_and_tables(Database db)
        {
        db.Create()
        Dim table As Microsoft.SqlServer.Management.Smo.Table
        For Each table In db.Tables
        table.Create()
        Next

        }
        void AddDBTables(Database db)
        {
            Dim tbl As DataTable
            For Each tbl In ds.Tables
             Microsoft.SqlServer.Management.Smo.Table table =
                 New Microsoft.SqlServer.Management.Smo.Table(db, tbl.TableName)
            'Microsoft.SqlServer.Management.Smo.Table tableAuthor = new Microsoft.SqlServer.Management.Smo.Table(db, "author");
            'Microsoft.SqlServer.Management.Smo.Table tableAuthors = new Microsoft.SqlServer.Management.Smo.Table(db, "authors");

             AddDBColumns(table, tbl)
             db.Tables.Add(table)
            Next
         }
        void AddDBColumns(Microsoft.SqlServer.Management.Smo.Table table, DataTable datatable)
        {
            Dim col As DataColumn
            For Each col In datatable.Columns

                Dim tblcol As Column =  New Column()
                tblcol.Name = col.ColumnName
                'tblcol.Create();
                table.Columns.Add(tblcol)
                if(col.DataType.Name="String")
                {
                    tblcol.DataType = DataType.NVarChar(col.MaxLength)

                }
                if (col.DataType.Name = "Int32" Or col.DataType.Name = "Int64")
                {
                    tblcol.DataType = DataType.Int
                }
                tblcol.Default = col.DefaultValue.ToString()

                tblcol.Nullable = col.AllowDBNull
                if (col.AutoIncrement)
                {
                    tblcol.Nullable = False
                    Dim idx As Index =  New Index(table,"pk_" + col.ColumnName)
                    idx.IndexedColumns.Add(New IndexedColumn(idx,tblcol.Name))
                    idx.IsClustered = True
                    idx.IsUnique = True
                    idx.IndexKeyType = IndexKeyType.DriPrimaryKey
                    table.Indexes.Add(idx)

                    tblcol.Identity = col.AutoIncrement
                    tblcol.IdentityIncrement = col.AutoIncrementStep
                    tblcol.IdentitySeed = col.AutoIncrementSeed
                }

            Next


        }
        void LoadXmlIntoDataSet()
        {
            ds.ReadXml(Server.MapPath("/data/books.xml"))
        }

        void BulkInsertData()
        {
        SqlBulkCopy blkCopy = New SqlBulkCopy("server=(local)\SqlExpress2012;uid=sa;pwd=lordsiva2030&;database=BooksList",
            SqlBulkCopyOptions.KeepIdentity)
          Dim table As DataTable
          For Each table In ds.Tables
              blkCopy.DestinationTableName = table.TableName
              blkCopy.WriteToServer(table)
          Next
            blkCopy.Close()
        }
    End Class
End Namespace

Tags:Create DataBase using DataSet using Vb.NET,Create Database using SMO VB.NET, Create Database using SqlServer Management Objects VB.NET, Bulkcopy in ADO.NET VB.NET,Create DB Tables using SqlServer Management Objects VB.NET