Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Transactions

Public Class MyVNC

    Private _numero As String
    Private _path As String
    Private _tipo As String
    Private WithEvents _adpCounter As SqlDataAdapter
    Private WithEvents _adpArts As SqlDataAdapter
    Private WithEvents _adpDetails As SqlDataAdapter
    Private _ds As MyVncData
    Private tmpDS As DataSet

    Public ReadOnly Property DB() As MyVncData
        Get
            Return _ds
        End Get
    End Property

    Public Property numero() As String
        Get
            Return _numero
        End Get
        Set(ByVal value As String)
            _numero = value
        End Set
    End Property

    Public ReadOnly Property percorso() As String
        Get
            Return _path
        End Get
    End Property

    Public Property tipo() As String
        Get
            Return _tipo
        End Get
        Set(ByVal value As String)

        End Set
    End Property

    Private Sub PrepareDB()

        Dim cmd As SqlCommand
        Dim cntIns As SqlCommand
        Dim artIns As SqlCommand
        Dim artUpd As SqlCommand
        Dim detIns As SqlCommand
        Dim detUpd As SqlCommand

        cmd = New SqlCommand("USR_SP_SEL_VNCCOUNTER", oConnection)
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add("@VNCNUM", SqlDbType.VarChar, 8).Value = _numero

        _adpCounter = New SqlDataAdapter(cmd)

        cntIns = New SqlCommand("USR_SP_INS_VNCCOUNTER", oConnection)
        cntIns.CommandType = CommandType.StoredProcedure

        cntIns.Parameters.Add("@VNCTYPE", SqlDbType.VarChar, 2, "VNCTYPE")
        cntIns.Parameters.Add("@USRID", SqlDbType.Int, 0, "USRID")

        _adpCounter.InsertCommand = cntIns

        _adpCounter.Fill(_ds, "VNCCOUNTER")

        cmd.CommandText = "USR_SP_SEL_VNCARTS"

        _adpArts = New SqlDataAdapter(cmd)

        artIns = New SqlCommand("USR_SP_INS_VNCARTS", oConnection)
        artIns.CommandType = CommandType.StoredProcedure
        artIns.Parameters.Add("@VNCNUM", SqlDbType.VarChar, 8, "VNCNUM")
        artIns.Parameters.Add("@ARTNAME", SqlDbType.VarChar, 24, "ARTNAME")
        artIns.Parameters.Add("@ARTDISE", SqlDbType.VarChar, 24, "ARTDISE")
        artIns.Parameters.Add("@ARTDESC", SqlDbType.VarChar, 30, "ARTDESC")
        artIns.Parameters.Add("@ARTQTA", SqlDbType.Decimal, 18, "ARTQTA")

        artUpd = New SqlCommand("USR_SP_UPD_VNCARTS", oConnection)
        artUpd.CommandType = CommandType.StoredProcedure
        artUpd.Parameters.Add("@VNCNUM", SqlDbType.VarChar, 8, "VNCNUM")
        artUpd.Parameters.Add("@ARTID", SqlDbType.Int, 0, "ARTID")
        artUpd.Parameters.Add("@ARTNAME", SqlDbType.VarChar, 24, "ARTNAME")
        artUpd.Parameters.Add("@ARTDISE", SqlDbType.VarChar, 24, "ARTDISE")
        artUpd.Parameters.Add("@ARTDESC", SqlDbType.VarChar, 30, "ARTDESC")
        artUpd.Parameters.Add("@ARTQTA", SqlDbType.Decimal, 18, "ARTQTA")

        _adpArts.InsertCommand = artIns
        _adpArts.UpdateCommand = artUpd

        _adpArts.Fill(_ds, "VNCARTS")

        cmd.CommandText = "USR_SP_SEL_VNCDETAILS"

        _adpDetails = New SqlDataAdapter(cmd)

        detIns = New SqlCommand("USR_SP_INS_VNCDETAILS", oConnection)
        detIns.CommandType = CommandType.StoredProcedure

        detIns.Parameters.Add("@VNCNUM", SqlDbType.VarChar, 8, "VNCNUM")
        detIns.Parameters.Add("@DETDESC", SqlDbType.VarChar, 2147483647, "DETDESC")
        detIns.Parameters.Add("@DETNOTE", SqlDbType.VarChar, 2147483647, "DETNOTE")
        detIns.Parameters.Add("@DETFOR", SqlDbType.VarChar, 4, "DETFOR")
        detIns.Parameters.Add("@DETRIF", SqlDbType.Int, 0, "DETRIF")

        detUpd = New SqlCommand("USR_SP_UPD_VNCDETAILS", oConnection)
        detUpd.CommandType = CommandType.StoredProcedure
        detUpd.Parameters.Add("@VNCNUM", SqlDbType.VarChar, 8, "VNCNUM")
        detUpd.Parameters.Add("@DETID", SqlDbType.Int, 0, "DETID")
        detUpd.Parameters.Add("@DETDESC", SqlDbType.VarChar, 2147483647, "DETDESC")
        detUpd.Parameters.Add("@DETNOTE", SqlDbType.VarChar, 2147483647, "DETNOTE")
        detUpd.Parameters.Add("@DETFOR", SqlDbType.VarChar, 4, "DETFOR")
        detUpd.Parameters.Add("@DETRIF", SqlDbType.Int, 0, "DETRIF")

        _adpDetails.InsertCommand = detIns
        _adpDetails.UpdateCommand = detUpd

        _adpDetails.Fill(_ds, "VNCDETAILS")

        _ds.AcceptChanges()

        cntIns.Dispose()
        artIns.Dispose()
        artUpd.Dispose()
        cmd.Dispose()

    End Sub

    Private Sub CreateDB()

        Dim cntRow As DataRow
        Dim artRow As DataRow


            cntRow = _ds.VNCCOUNTER.NewVNCCOUNTERRow
            cntRow.BeginEdit()
            cntRow("USRID") = curUser.UserId
            cntRow("VNCTYPE") = _tipo

            artRow = _ds.VNCARTS.NewVNCARTSRow
            artRow.BeginEdit()

            artRow.SetParentRow(cntRow)

            _ds.VNCCOUNTER.Rows.Add(cntRow)
            _ds.VNCARTS.Rows.Add(artRow)

    End Sub

    Public Sub New(ByVal vncCnt As String)

        _ds = New MyVncData
        _numero = vncCnt.Split(".")(0)
        _tipo = vncCnt.Split(".")(1)

        PrepareDB()

        If _numero = 0 Then
            CreateDB()
        End If

        tmpDS = _ds.Copy

    End Sub

    Public Sub Save()

        Try

            For Each rw As DataRow In _ds.VNCARTS.Rows
                rw.EndEdit()
            Next

            Using scope As New TransactionScope

                _adpCounter.Update(_ds, "VNCCOUNTER")
                _adpArts.Update(_ds, "VNCARTS")
                _adpDetails.Update(_ds, "VNCDETAILS")

                _ds.WriteXml(CurDir() & "\data.xml")
                _ds.AcceptChanges()

                scope.Complete()

            End Using

            For Each rw As DataRow In _ds.VNCARTS.Rows
                rw.BeginEdit()
            Next

        Catch ex As Exception
            Throw ex
        End Try

    End Sub

    'Private Sub _adpArts_RowUpdating(ByVal sender As Object, ByVal e As System.Data.SqlClient.SqlRowUpdatingEventArgs) Handles _adpArts.RowUpdating

    '    If e.Row("ARTNAME").ToString.Length = 0 Then
    '        Throw New Exception("Inserire il nome articolo")
    '    End If

    'End Sub

End Class