Imports System.Xml
Imports System.Data.SqlClient

Imports System.IO

Public Class frm_loadxml
    Dim conn As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("SO_Conn"))
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim adpter As New SqlDataAdapter
        Dim sql As String
        Dim command As SqlCommand


        xmlFile = XmlReader.Create("C:\Service_Order_R2R\Set1_CEExport_Group2_1.xml", New XmlReaderSettings())
        ds.ReadXml(xmlFile)
        Dim i As Integer
        If conn.State = 0 Then
            conn.Open()
        End If
        For i = 0 To ds.Tables(2).Rows.Count - 1
            '  MessageBox.Show("insert into tbl_folders values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(6) & "','" & ds.Tables(3).Rows(i).Item(8) & "','" & ds.Tables(5).Rows(i).Item(0) & "','" & ds.Tables(5).Rows(i).Item(1) & "')")

            'sql = "insert into tbl_folders values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(6) & "','" & ds.Tables(3).Rows(i).Item(8) & "','" & ds.Tables(5).Rows(i).Item(0) & "','" & ds.Tables(5).Rows(i).Item(1) & "')"
            sql = "SO_Frmloadxml_insertfolder"
            command = New SqlCommand(sql, conn)
            adpter.InsertCommand = command
            adpter.InsertCommand.CommandType = CommandType.StoredProcedure
            adpter.InsertCommand.Parameters.AddWithValue("@var1", ds.Tables(3).Rows(i).Item(1))
            adpter.InsertCommand.Parameters.AddWithValue("@var2", ds.Tables(3).Rows(i).Item(4))
            adpter.InsertCommand.Parameters.AddWithValue("@var3", ds.Tables(3).Rows(i).Item(5))
            adpter.InsertCommand.Parameters.AddWithValue("@var4", ds.Tables(3).Rows(i).Item(6))
            adpter.InsertCommand.Parameters.AddWithValue("@var5", ds.Tables(3).Rows(i).Item(8))
            adpter.InsertCommand.Parameters.AddWithValue("@var6", ds.Tables(5).Rows(i).Item(0))
            adpter.InsertCommand.Parameters.AddWithValue("@var7", ds.Tables(5).Rows(i).Item(1))
           
            'adpter.InsertCommand.CommandType=CommandType.StoredProcedure
            adpter.InsertCommand.ExecuteNonQuery()



        Next

        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        MessageBox.Show("Data imported")
    End Sub

    Private Sub btn_relation_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_relation.Click
        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim adpter As New SqlDataAdapter
        Dim sql As String
        Dim command As SqlCommand


        xmlFile = XmlReader.Create("C:\Service_Order_R2R\Set1_CEExport_Group4_1.xml", New XmlReaderSettings())
        'xmlFile = XmlReader.Create(" E:\ServiceOrder\sos\Service_Order_R2R\Set1_CEExport_Group4_1.xml", New XmlReaderSettings())

        ds.ReadXml(xmlFile)
        Dim i As Integer
        Dim j As Integer
        If conn.State = 0 Then
            conn.Open()
        End If
        For i = 0 To ds.Tables(2).Rows.Count - 1
            j = i * 2
            'MessageBox.Show("insert into tbl_relations values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(5).Rows(j).Item(0) & "','" & ds.Tables(5).Rows(j).Item(1) & "','" & ds.Tables(3).Rows(i).Item(3) & "','" & ds.Tables(5).Rows(j + 1).Item(0) & "','" & ds.Tables(5).Rows(j + 1).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(7) & "')")
            'Sql = "insert into tbl_relations values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(5).Rows(j).Item(0) & "','" & ds.Tables(5).Rows(j).Item(1) & "','" & ds.Tables(3).Rows(i).Item(3) & "','" & ds.Tables(5).Rows(j + 1).Item(0) & "','" & ds.Tables(5).Rows(j + 1).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(7) & "')"
            sql = "SO_Frmloadxml_insertrelation"
            command = New SqlCommand(sql, conn)
            adpter.InsertCommand = command
            adpter.InsertCommand.CommandType = CommandType.StoredProcedure
            adpter.InsertCommand.Parameters.AddWithValue("@var1", ds.Tables(3).Rows(i).Item(1))
            adpter.InsertCommand.Parameters.AddWithValue("@var2", ds.Tables(5).Rows(j).Item(0))
            adpter.InsertCommand.Parameters.AddWithValue("@var3", ds.Tables(5).Rows(j).Item(1))
            adpter.InsertCommand.Parameters.AddWithValue("@var4", ds.Tables(3).Rows(i).Item(3))
            adpter.InsertCommand.Parameters.AddWithValue("@var5", ds.Tables(5).Rows(j + 1).Item(0))
            adpter.InsertCommand.Parameters.AddWithValue("@var6", ds.Tables(5).Rows(j + 1).Item(1))
            adpter.InsertCommand.Parameters.AddWithValue("@var7", ds.Tables(3).Rows(i).Item(4))
            adpter.InsertCommand.Parameters.AddWithValue("@var8", ds.Tables(3).Rows(i).Item(5))
            adpter.InsertCommand.Parameters.AddWithValue("@var9", ds.Tables(3).Rows(i).Item(7))

            adpter.InsertCommand.ExecuteNonQuery()
        Next
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        MessageBox.Show("Data imported")
    End Sub

    Private Sub btn_servicecards_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_servicecards.Click
        ''Dim ds As New DataSet
        ''Dim xmlFile As XmlReader
        ''Dim adpter As New SqlDataAdapter
        ' ''Dim sql As String
        ' ''Dim command As SqlCommand


        ''xmlFile = XmlReader.Create("C:\Service_Order_R2R\Set1_CEExport_Group3_1.xml", New XmlReaderSettings())
        ''ds.ReadXml(xmlFile)
        ''Dim i As Integer
        ''If conn.State = 0 Then
        ''    conn.Open()
        ''End If
        ''For i = 0 To ds.Tables(2).Rows.Count - 1
        ''    MessageBox.Show("insert into tbl_servicecards values ('" & ds.Tables(3).Rows(i).Item(2) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(6) & "')")

        ''    'sql = "insert into tbl_folders values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(6) & "','" & ds.Tables(3).Rows(i).Item(8) & "','" & ds.Tables(5).Rows(i).Item(0) & "','" & ds.Tables(5).Rows(i).Item(1) & "')"
        ''    'command = New SqlCommand(sql, conn)
        ''    'adpter.InsertCommand = command
        ''    'adpter.InsertCommand.ExecuteNonQuery()


        ''Next

        ''If conn.State = ConnectionState.Open Then
        ''    conn.Close()
        ''End If
        ''MessageBox.Show("Data imported")

        Dim adpter, adpter1 As New SqlDataAdapter
        Dim sql, sql1 As String
        Dim command, command1 As SqlCommand

        Dim xmldoc As New XmlDataDocument()
        Dim xmlnode As XmlNodeList
        Dim i, j As Integer

        Dim fs As New FileStream("C:\Service_Order_R2R\Set1_CEExport_Group3_1.xml", FileMode.Open, FileAccess.Read)
        xmldoc.Load(fs)
        xmlnode = xmldoc.GetElementsByTagName("ServiceCardsProperties")


        If conn.State = 0 Then
            conn.Open()
        End If

        For i = 0 To xmlnode.Count - 1
            ' xmlnode(i).ChildNodes.Item(0).InnerText.Trim()
            'Dim Str As String = xmlnode(i).ChildNodes.Item(27).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(19).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(33).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim() & " | " & xmlnode(i).ChildNodes.Item(9).InnerText.Trim()
            'MsgBox(Str)

            'MessageBox.Show("insert into tbl_servicecards values ('" & xmlnode(i).ChildNodes.Item(27).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(19).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(33).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(9).InnerText.Trim() & "')")

            'sql = "insert into tbl_servicecards values ('" & xmlnode(i).ChildNodes.Item(2).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(4).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(6).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(27).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(27).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(41).InnerText.Trim() & "', 'N')"
            'command = New SqlCommand(sql, conn)
            'adpter.InsertCommand = command
            'adpter.InsertCommand.ExecuteNonQuery()


            'sql = "insert into tbl_servicecards values ('" & xmlnode(i).ChildNodes.Item(27).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(19).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(33).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim() & "', ' " & xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(9).InnerText.Trim() & "', 'N')"
            sql = "SO_Frmloadxml_insertServicecards"
            command = New SqlCommand(sql, conn)
            adpter.InsertCommand = command
            adpter.InsertCommand.CommandType = CommandType.StoredProcedure
            

            adpter.InsertCommand.Parameters.AddWithValue("@var1", xmlnode(i).ChildNodes.Item(2).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var2", xmlnode(i).ChildNodes.Item(4).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var3", xmlnode(i).ChildNodes.Item(6).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var4", xmlnode(i).ChildNodes.Item(27).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var5", xmlnode(i).ChildNodes.Item(27).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var6", xmlnode(i).ChildNodes.Item(41).InnerText.Trim())
            adpter.InsertCommand.Parameters.AddWithValue("@var7", "N")
            'adpter.InsertCommand.Parameters.AddWithValue("@var1", xmlnode(i).ChildNodes.Item(27).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var2", xmlnode(i).ChildNodes.Item(19).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var3", xmlnode(i).ChildNodes.Item(33).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var4", xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(0).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var5", xmlnode(i).ChildNodes.Item(26).ChildNodes.Item(0).ChildNodes.Item(1).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var6", xmlnode(i).ChildNodes.Item(9).InnerText.Trim())
            'adpter.InsertCommand.Parameters.AddWithValue("@var7", "N")
            adpter.InsertCommand.ExecuteNonQuery()

            For j = 0 To xmlnode(i).ChildNodes.Item(22).ChildNodes.Count - 1
                ' MessageBox.Show("insert into tbl_so_imagefiles values ('" & xmlnode(i).ChildNodes.Item(33).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(9).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim() & "')")
                'sql1 = "insert into tbl_so_imagefiles values ('" & xmlnode(i).ChildNodes.Item(6).InnerText.Trim() & "','" & xmlnode(i).ChildNodes.Item(41).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(22).ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(22).ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim() & "')"
                'command1 = New SqlCommand(sql1, conn)
                'adpter1.InsertCommand = command1
                'adpter1.InsertCommand.ExecuteNonQuery()
                '               sql1 = "insert into tbl_so_imagefiles values ('" & xmlnode(i).ChildNodes.Item(33).InnerText.Trim() & "', '" &
                'xmlnode(i).ChildNodes.Item(9).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim() & "', '" & xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim() & "')"
                sql1 = "SO_Frmloadxml_insertsoimage"
                command1 = New SqlCommand(sql1, conn)
                adpter1.InsertCommand = command1
                adpter1.InsertCommand.CommandType = CommandType.StoredProcedure
                adpter1.InsertCommand.Parameters.AddWithValue("@var1", xmlnode(i).ChildNodes.Item(6).InnerText.Trim())
                adpter1.InsertCommand.Parameters.AddWithValue("@var2", xmlnode(i).ChildNodes.Item(41).InnerText.Trim())
                adpter1.InsertCommand.Parameters.AddWithValue("@var3", xmlnode(i).ChildNodes.Item(22).ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim())
                adpter1.InsertCommand.Parameters.AddWithValue("@var4", xmlnode(i).ChildNodes.Item(22).ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim())

                'adpter1.InsertCommand.Parameters.AddWithValue("@var1", xmlnode(i).ChildNodes.Item(33).InnerText.Trim())
                'adpter1.InsertCommand.Parameters.AddWithValue("@var2", xmlnode(i).ChildNodes.Item(9).InnerText.Trim())
                'adpter1.InsertCommand.Parameters.AddWithValue("@var3", xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(1).InnerText.Trim())
                'adpter1.InsertCommand.Parameters.AddWithValue("@var4", xmlnode(i).ChildNodes.Item(21).ChildNodes.Item(j).ChildNodes.Item(3).InnerText.Trim())
                adpter1.InsertCommand.ExecuteNonQuery()

            Next
        Next
        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        MessageBox.Show("Data imported")
    End Sub

    Private Sub Btn_folderrel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Btn_folderrel.Click

        Dim ds As New DataSet
        Dim xmlFile As XmlReader
        Dim adpter As New SqlDataAdapter
        Dim sql As String
        Dim command As SqlCommand


        xmlFile = XmlReader.Create("C:\Service_Order_R2R\Relationship.xml", New XmlReaderSettings())
        ds.ReadXml(xmlFile)
        Dim i As Integer
        If conn.State = 0 Then
            conn.Open()
        End If
        For i = 0 To ds.Tables(0).Rows.Count - 1
            '  MessageBox.Show("insert into tbl_folders values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(6) & "','" & ds.Tables(3).Rows(i).Item(8) & "','" & ds.Tables(5).Rows(i).Item(0) & "','" & ds.Tables(5).Rows(i).Item(1) & "')")

            'sql = "insert into tbl_folderrel values ('" & ds.Tables(0).Rows(i).Item(0) & "','" & ds.Tables(0).Rows(i).Item(1) & "')"
            sql = "SO_Frmloadxml_insertfolderrel"
            command = New SqlCommand(sql, conn)
            adpter.InsertCommand = command
            adpter.InsertCommand.CommandType = CommandType.StoredProcedure
            adpter.InsertCommand.Parameters.AddWithValue("@var1", ds.Tables(0).Rows(i).Item(0))
            adpter.InsertCommand.Parameters.AddWithValue("@var2", ds.Tables(0).Rows(i).Item(1))
            adpter.InsertCommand.ExecuteNonQuery()
        Next

        If conn.State = ConnectionState.Open Then
            conn.Close()
        End If
        MessageBox.Show("Data imported")


        'Dim ds As New DataSet
        'Dim xmlFile As XmlReader
        'Dim adpter As New SqlDataAdapter
        'Dim sql As String
        'Dim command As SqlCommand


        'xmlFile = XmlReader.Create("C:\Service_Order_R2R\FolderRelationships.xml", New XmlReaderSettings())
        'ds.ReadXml(xmlFile)
        'Dim i As Integer
        'If conn.State = 0 Then
        '    conn.Open()
        'End If
        'For i = 0 To ds.Tables(0).Rows.Count - 1

        '    'MessageBox.Show(ds.Tables(0).Rows(i).Item(0) & "','" & ds.Tables(0).Rows(i).Item(1))
        '    'MessageBox.Show("insert into tbl_folders values ('" & ds.Tables(3).Rows(i).Item(1) & "','" & ds.Tables(3).Rows(i).Item(4) & "','" & ds.Tables(3).Rows(i).Item(5) & "','" & ds.Tables(3).Rows(i).Item(6) & "','" & ds.Tables(3).Rows(i).Item(8) & "','" & ds.Tables(5).Rows(i).Item(0) & "','" & ds.Tables(5).Rows(i).Item(1) & "')")

        '    sql = "insert into tbl_folderrel values ('" & ds.Tables(0).Rows(i).Item(0) & "','" & ds.Tables(0).Rows(i).Item(1) & "')"
        '    command = New SqlCommand(sql, conn)
        '    adpter.InsertCommand = command()
        '    adpter.InsertCommand.ExecuteNonQuery()
        '    If i = 3 Then
        '        Exit For
        '    End If

        'Next

        'If conn.State = ConnectionState.Open Then
        '    conn.Close()
        'End If
        'MessageBox.Show("Data imported")
    End Sub
End Class