Stored proc (xmldoc XML) calling methods

Public Function CreateXml() As String

        Dim xml As New StringBuilder()

        Try

            xml.Append(“<root><Tasks TasksId=”"”)

            xml.Append(TasksId)

 

            xml.Append(“”" Name=”"”)

            xml.Append(Name)

 

            xml.Append(“”" Priority=”"”)

            xml.Append(Priority)

 

            xml.Append(“”" IsNotificationOnly=”"”)

            xml.Append(IsNotificationOnly)

 

            xml.Append(“”" IsRetain=”"”)

            xml.Append(IsRetain)

 

            xml.Append(“”" EngagementID=”"”)

            xml.Append(EngagementID)

 

            xml.Append(“”" Assignor=”"”)

            xml.Append(Assignor)

 

            xml.Append(“”" AssignedDate=”"”)

            xml.Append(AssignedDate)

 

            xml.Append(“”" Desc=”"”)

            xml.Append(Desc)

 

            xml.Append(“”" DueDate=”"”)

            xml.Append(DueDate)

 

            xml.Append(“”" HasIssues=”"”)

            xml.Append(HasIssues)

 

            xml.Append(“”" HasAttachment=”"”)

            xml.Append(HasAttachment)

 

            xml.Append(“”" Requester=”"”)

            xml.Append(Requester)

 

            xml.Append(“”" HasServiceExtension=”"”)

            xml.Append(HasServiceExtension)

 

            xml.Append(“”" ReviewStatus=”"”)

            xml.Append(ReviewStatus)

 

            xml.Append(“”" ImportSrc=”"”)

            xml.Append(ImportSrc)

 

            xml.Append(“”" ImportSourceInfo=”"”)

            xml.Append(ImportSourceInfo)

 

            xml.Append(“”" ImportSourceItem=”"”)

            xml.Append(ImportSourceItem)

 

            xml.Append(“”" ImportSourceVersion=”"”)

            xml.Append(ImportSourceVersion)

 

            xml.Append(“”" ImportSourceType=”"”)

            xml.Append(ImportSourceType)

 

            xml.Append(“”" CreatedBy=”"”)

            xml.Append(CreatedBy)

 

            xml.Append(“”" CreatedOn=”"”)

            xml.Append(CreatedOn)

 

            xml.Append(“”" rowguid=”"”)

            xml.Append(rowguid)

 

 

 

            xml.Append(“”"/></root>”)

            Return (xml.ToString())

        Catch err As Exception

            Throw err

        End Try

    End Function

 

    Public Function saveTask(ByVal xmlTasks As String, ByVal StrTaskid As String) As Boolean

 

        Dim _sqlConnection As New SqlConnection()

        Dim cmdInsert As New SqlCommand()

        Dim retVal As Boolean

 

        Dim strConn As String = ConfigurationManager.ConnectionStrings(“sqlConnectionString”).ToString()

 

        Try

            _sqlConnection.ConnectionString = strConn

            _sqlConnection.Open()

 

            cmdInsert.Connection = _sqlConnection

            cmdInsert.CommandType = CommandType.StoredProcedure

 

            cmdInsert.CommandText = “Proc_SaveTask”

 

            cmdInsert.Parameters.Add(“@xmldoc”, SqlDbType.Xml)

            cmdInsert.Parameters(0).Value = xmlTasks

 

            cmdInsert.Parameters.Add(“@Id”, SqlDbType.VarChar)

            cmdInsert.Parameters(1).Value = StrTaskid

 

            cmdInsert.ExecuteNonQuery()

 

            retVal = True

 

        Catch ex As Exception

 

            retVal = False

 

        End Try

 

 

        Return retVal

 

 

    End Function

 

===============================================

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[Proc_SaveTask](@xmldoc XML,@Id varchar(50))

AS
DECLARE @idoc int

BEGIN TRAN
if isnull(@Id,”)<>”  delete from Task where TasksId=@Id
 

–Create an internal representation of the XML document.
EXEC sp_xml_preparedocument @idoc OUTPUT, @xmldoc

INSERT INTO Task (TasksId,[Name], Priority,IsNotificationOnly,IsRetain,
 EngagementID,Assignor, AssignedDate,[Desc],DueDate,
 HasIssues,HasAttachment,Requester,HasServiceExtension,
 ReviewStatus,ImportSrc,ImportSourceInfo,ImportSourceItem,
 ImportSourceVersion,ImportSourceType,CreatedBy,CreatedOn,rowguid)

SELECT   
 
 TasksId ,  
 CASE WHEN [Name] <>” THEN [Name] ELSE NULL END,
 CASE WHEN Priority <>” THEN Priority ELSE NULL END,
 CASE WHEN IsNotificationOnly <>” THEN IsNotificationOnly ELSE NULL END,
 CASE WHEN IsRetain <>” THEN IsRetain ELSE NULL END,
 CASE WHEN EngagementID <>” THEN EngagementID ELSE NULL END,
 CASE WHEN Assignor <>” THEN Assignor ELSE NULL END,
 CASE WHEN AssignedDate <>” THEN AssignedDate ELSE NULL END,  
 CASE WHEN [Desc] <>” THEN [Desc] ELSE NULL END,
 CASE WHEN DueDate <>” THEN DueDate ELSE NULL END,
 CASE WHEN HasIssues <>” THEN HasIssues ELSE NULL END,
 CASE WHEN HasAttachment <>” THEN HasAttachment ELSE NULL END,
 CASE WHEN Requester <>” THEN Requester ELSE NULL END,
 CASE WHEN HasServiceExtension <>” THEN HasServiceExtension ELSE NULL END,
 CASE WHEN ReviewStatus <>” THEN ReviewStatus ELSE NULL END,
 CASE WHEN ImportSrc <>” THEN ImportSrc ELSE NULL END,
 CASE WHEN ImportSourceInfo <>” THEN ImportSourceInfo ELSE NULL END,
 CASE WHEN ImportSourceItem <>” THEN ImportSourceItem ELSE NULL END,
 CASE WHEN ImportSourceVersion <>” THEN ImportSourceVersion ELSE NULL END,
 CASE WHEN ImportSourceType <>” THEN ImportSourceType ELSE NULL END,
 CASE WHEN CreatedBy <>” THEN CreatedBy ELSE NULL END,
 CASE WHEN CreatedOn <>” THEN CreatedOn ELSE NULL END,  
 rowguid
 
FROM       OPENXML (@idoc, ‘/root/Tasks’,1)
            WITH
    (TasksId varchar(50), [Name] varchar(200),Priority varchar(200),IsNotificationOnly varchar(200),
    IsRetain varchar(200),EngagementID varchar(50), Assignor varchar(200),  AssignedDate varchar(25),
    [Desc] varchar(200), DueDate varchar(25), HasIssues varchar(50), HasAttachment varchar(50),
    Requester varchar(50), HasServiceExtension varchar(200), ReviewStatus varchar(200),
    ImportSrc varchar(200), ImportSourceInfo varchar(200),  ImportSourceItem varchar(200),
    ImportSourceVersion varchar(200), ImportSourceType varchar(200),
    CreatedBy varchar(200), CreatedOn varchar(25), rowguid uniqueidentifier )
IF @@ERROR>0
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
COMMIT TRAN
END
EXEC SP_XML_REMOVEDOCUMENT @xmldoc

——————————————————————————-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
create  PROCEDURE [dbo].[Proc_SelectTask](@TaskId varchar(50))

AS
select * from task Where TasksId=@TaskId For XML Auto  , ELEMENTS

——————————————————————————-