Insert and update data Using Xml file in Sql Server

'Making Xml 
 Dim sb As New System.Text.StringBuilder

        sb.Append("")


        For i = 0 To dt.Rows.Count - 1 

            sb.Append("
            sb.Append("PQty=""" & dt.Rows(i).Item("PQty") & """")

            sb.Append("PWeight=""" & ClearWhiteSpace(dt.Rows(i).Item("PWeight")) & """")            


            sb.Append("/>")

        Next

        sb.Append("")

-- Making Stored Procedure
CREATE PROCEDURE [dbo].[TestMasterInsert]    
    @MId Int,    
    @GId Int,
    @PId Int,
    @Name varchar(50),
    @Data text,
    @TranType  Char(1)
AS
BEGIN    
    SET NOCOUNT ON;    
    
    Declare @id As Int 
        SET @id = 0
        
        DECLARE @hDoc int exec sp_xml_preparedocument @hDoc OUTPUT, @Data
        
    IF @TranType = 'I'    
        BEGIN        
            Insert Into TestMaster Values (@GId,@PId,@Name)
        
            Select @id = SCOPE_IDENTITY()  
        
            INSERT INTO TestDetail
                SELECT @id,PQty,PWeight
                FROM   OPENXML (@hDoc, '/ROOT/SI',2)          
                WITH (
                       PQty       Decimal(18,3) '@PQty',
                       PWeight    Decimal(18,3) '@PWeight'
                     )   
          
             EXEC sp_xml_removedocument @hDoc
        END
    ELSE IF @TranType = 'U'        
        BEGIN
            
            UPDATE TestDetail     
            SET     
                  TestDetail.PQty = XMLTEST.PQty,    
                  TestDetail.PWeight = XMLTEST.PWeight
            FROM OPENXML(@hDoc, '/ROOT/SI',2)
                        WITH (DId Int '@DId',PQty Decimal(18,3) '@PQty',PWeight Decimal(18,3) '@PWeight') XMLTEST
            WHERE  TestDetail.DId = XMLTEST.DId
            
            EXEC sp_xml_removedocument @hDoc        
        END    
END

Imports System.ServiceProcess
Imports System.IO
Public Class Service1
    Inherits System.ServiceProcess.ServiceBase
    Public Shared DailyTime As String = "DailyTime.txt"
    Public Shared errlogfile As String = "Error.err"


    Protected Overrides Sub OnStart(ByVal args() As String)
        Try


        Catch ex As Exception
            [Error]("OnStart:-" + ex.Message + DateTime.Now.ToString())
        End Try
    End Sub


    Protected Overrides Sub OnStop()
        ' Add code here to perform any tear-down necessary to stop your service.
    End Sub


    Protected Overrides Sub OnSessionChange(ByVal changeDescription As SessionChangeDescription)
        Try


            Select Case changeDescription.Reason
                Case SessionChangeReason.SessionLogon


                    [InsertDailyTime]("Session Logon:-" + DateTime.Now.ToString())


                    Exit Select
                Case SessionChangeReason.SessionLogoff
                    [InsertDailyTime]("Session Logoff:-" + DateTime.Now.ToString())
                    Exit Select
                Case SessionChangeReason.RemoteConnect
                    'Remote Connect 
                    Exit Select
                Case SessionChangeReason.RemoteDisconnect
                    'Remote Disconnect 
                    Exit Select
                Case SessionChangeReason.SessionLock
                    [InsertDailyTime]("Session Lock:-" + DateTime.Now.ToString())
                    Exit Select
                Case SessionChangeReason.SessionUnlock
                    [InsertDailyTime]("Session Unlock:-" + DateTime.Now.ToString())
                    Exit Select
                Case Else
                    Exit Select
            End Select


        Catch ex As Exception
            [Error]("OnSessionChange:-" + ex.Message + DateTime.Now.ToString())
        End Try
    End Sub


    Public Shared Sub [InsertDailyTime](ByVal message As String)
        Dim datewisefile As String = ""
        Dim dt As DateTime = DateTime.Now
        Dim fs As System.IO.FileStream = Nothing
        Dim info As Byte() = Nothing
        Try
            If Not System.IO.Directory.Exists(Path.GetDirectoryName("D:\Projects\TimeManager")) Then
                System.IO.Directory.CreateDirectory(Path.GetDirectoryName("D:\Projects\TimeManager"))
            End If
            'logfile = logfile.Split('.')[0] + System.DateTime.Now.ToString("yyyyMMddHHmm") + '.' + logfile.Split('.')[1]; 
            datewisefile = ((dt.Year & "") + "-" + (If(dt.Month <= 9, "0" & dt.Month & "", dt.Month & "")) & "") + "-" + (If(dt.Day <= 9, "0" & dt.Day & "", dt.Day & ""))
            DailyTime = "\Nik's_" & datewisefile & ".txt"
            fs = System.IO.File.Open(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs" + DailyTime, System.IO.FileMode.Append)
            info = New System.Text.UTF8Encoding(True).GetBytes((System.DateTime.Now.ToString() & ":") + message & vbLf)
            ' fs.Close(); 
            fs.Write(info, 0, info.Length)
            fs.Flush()
            fs.Close()
            fs = Nothing
            info = Nothing
        Catch ex As Exception
            [Error]("InsertDailyTime:-" + ex.Message + DateTime.Now.ToString())
        Finally
            'GC.SuppressFinalize(Me)
        End Try
    End Sub


    Public Shared Sub [Error](ByVal message As String)
        Dim datewisefile As String = ""
        Dim dt As DateTime = DateTime.Now
        Dim fs As System.IO.FileStream = Nothing
        Dim info As Byte() = Nothing
        Try
            If Not System.IO.Directory.Exists(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs") Then
                System.IO.Directory.CreateDirectory(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs")
            End If
            'logfile = logfile.Split('.')[0] + System.DateTime.Now.ToString("yyyyMMddHHmm") + '.' + logfile.Split('.')[1]; 
            datewisefile = ((dt.Year & "") + "-" + (If(dt.Month <= 9, "0" & dt.Month & "", dt.Month & "")) & "") + "-" + (If(dt.Day <= 9, "0" & dt.Day & "", dt.Day & ""))
            errlogfile = "\TimeManager_" & datewisefile & ".Log"
            fs = System.IO.File.Open(Path.GetDirectoryName("D:\Projects\TimeManager") & "\logs" + errlogfile, System.IO.FileMode.Append)
            info = New System.Text.UTF8Encoding(True).GetBytes((System.DateTime.Now.ToString() & ":") + message & vbLf)
            ' fs.Close(); 
            fs.Write(info, 0, info.Length)
            fs.Flush()
            fs.Close()
            fs = Nothing
            info = Nothing
        Catch ex As Exception
            'Write("Error:"+ex.Message); 
        Finally
            'GC.SuppressFinalize(Me)
        End Try
    End Sub


End Class