Stored procedure for Insert and Update with XML and List of Object (Multiple ) From code C#

USE [CBI]

GO

/****** Object:  StoredProcedure [dbo].[IUSP_DocumentList]    Script Date: 1/22/2024 3:12:34 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[IUSP_DocumentList]

(

@xmlDoc xml

)

AS

BEGIN 

SET ARITHABORT ON 

DECLARE @TranName NVARCHAR(255)

DECLARE @ErrMsg NVARCHAR(2048)

DECLARE @ErrSeverity INT;

DECLARE @intPointer INT;

SET @ErrSeverity = 15; 

BEGIN TRY

EXEC sp_xml_preparedocument @intpointer OUTPUT,@xmlDoc

        DECLARE @DocumentId bigint

Select * Into #tmpDocument

        FROM OPENXML(@intpointer,'Document/DocumentList',2)

        WITH

        (

[DocumentId] [bigint],

[ParentDocumentId] [bigint],

[AssociatedDocumentId] [bigint],

[DocumentTypeId] [bigint],

[ObjectId] [bigint],

[ObjectType] [bigint],

[FileId] [bigint],

[EvidenceType] [bigint],

[DocumentClassification] [bigint],

[Date] [datetime],

[DirectoryId] [bigint],

[DocumentFilePath] [varchar](500),

[Remark] [varchar](1000),

[VersionNumber] [bigint],

[DocumentName] [nvarchar](500),

[Subject] [nvarchar](500),

[DocumentFile] [varbinary](max),

[FileType] [nvarchar](50),

[CoverPageId] [bigint],

[NumberOfPages] [bigint],

[Indexed] [bit],

[IsSplit] [bit],

[CurrentUserId] [bigint],

[CurrentRoleId] [bigint],

[OfficeID] [bigint],

[SectionId] [bigint],

[IsPDFGenerated] [bit],

[Status] [bigint],

[CurrentState] [bigint],

[PreviousState] [bigint],

[LinkedDate] [datetime],

[LinkedByUser] [bigint],

[LinkedByRole] [bigint],

[IsCopy] [bit],

[IsSentForLinking] [bit],

[IsActive] [bit],

[CreatedBy] [bigint],

[CreatedDate] [datetime],

[CreatedIPAddress] [nvarchar](15),

[UpdatedBy] [bigint],

[UpdatedDate] [datetime],

[UpdatedIPAddress] [nvarchar](15),

[MainDocumentId] [bigint],

[NoOfCopies] [bigint],

[Priority] [bigint],

[SecrecyLevel] [bigint],

[DateOfPublish] [datetime],

[DateOfUnPublish] [datetime],

[IsPublished] [bit],

[IsMigrated] [varchar](50),

[IsDraft] [bit],

[AttachmentType] [bigint]

        )tmp

UPDATE dbo.[Document] 

SET

        @DocumentId=tmp.DocumentId,

[AssociatedDocumentId]=tmp.AssociatedDocumentId,

        [DocumentTypeId]=tmp.DocumentTypeId ,

        [ObjectId]=tmp.ObjectId ,

[ObjectType]=tmp.ObjectType,

        [FileId]=tmp.FileId ,

[EvidenceType]=tmp.EvidenceType,

[DocumentClassification]=tmp.DocumentClassification,

        [DirectoryId]=tmp.DirectoryId ,

        [DocumentFilePath]=tmp.DocumentFilePath ,

        [Remark]=tmp.Remark ,

        [DocumentName]=tmp.DocumentName ,

[Subject]=tmp.Subject,

        [DocumentFile]=tmp.DocumentFile ,

        [FileType]=tmp.FileType ,

        [CoverPageId]=tmp.CoverPageId ,

        [NumberOfPages]=tmp.NumberOfPages ,

[Indexed] = tmp.Indexed,

[IsSplit] = tmp.IsSplit, 

[IsCopy]=tmp.IsCopy, 

[IsActive]=tmp.IsActive,     

        [UpdatedBy]=tmp.UpdatedBy ,

        [UpdatedDate]=getdate() ,       

        [UpdatedIPAddress]=tmp.UpdatedIPAddress,

[Priority] = tmp.[Priority],

[SecrecyLevel] = tmp.[SecrecyLevel]

        from #tmpDocument tmp 

where tmp.[DocumentId]=dbo.[Document].[DocumentId]

and tmp.[DocumentId]!=0

INSERT INTO [Document]

(

--[ParentDocumentId]

           [AssociatedDocumentId]

           ,[DocumentTypeId]

           ,[ObjectId]

           ,[ObjectType]

           ,[FileId]

           ,[EvidenceType]

           ,[DocumentClassification]

           ,[Date]

           ,[DirectoryId]

           ,[DocumentFilePath]

           ,[Remark]

           ,[VersionNumber]

           ,[DocumentName]

           ,[Subject]

           ,[DocumentFile]

           ,[FileType]

           ,[CoverPageId]

           ,[NumberOfPages]

           ,[Indexed]

           ,[IsSplit]

           ,[CurrentUserId]

           ,[CurrentRoleId]

           ,[OfficeID]

           ,[SectionId]

           ,[IsPDFGenerated]

           ,[Status]

           ,[CurrentState]

           ,[PreviousState]

           ,[LinkedDate]

           ,[LinkedByUser]

           ,[LinkedByRole]

           ,[IsCopy]

           ,[IsSentForLinking]

           ,[IsActive]

           ,[CreatedBy]

           ,[CreatedDate]

           ,[CreatedIPAddress]

           ,[UpdatedBy]

           ,[MainDocumentId]

           ,[NoOfCopies]

           ,[Priority]

           ,[SecrecyLevel]

           ,[DateOfPublish]

           ,[DateOfUnPublish]

           ,[IsPublished]

           ,[IsMigrated]

           ,[IsDraft]

           ,[AttachmentType]

)

SELECT 

--tmp.[ParentDocumentId]

           tmp.[AssociatedDocumentId]

           ,tmp.[DocumentTypeId]

           ,tmp.[ObjectId]

           ,tmp.[ObjectType]

           ,tmp.[FileId]

           ,tmp.[EvidenceType]

           ,tmp.[DocumentClassification]

           ,tmp.[Date]

           ,tmp.[DirectoryId]

           ,tmp.[DocumentFilePath]

           ,tmp.[Remark]

           ,tmp.[VersionNumber]

           ,tmp.[DocumentName]

           ,tmp.[Subject]

           ,tmp.[DocumentFile]

           ,tmp.[FileType]

           ,tmp.[CoverPageId]

           ,tmp.[NumberOfPages]

           ,tmp.[Indexed]

           ,tmp.[IsSplit]

           ,tmp.[CurrentUserId]

           ,tmp.[CurrentRoleId]

           ,tmp.[OfficeID]

           ,tmp.[SectionId]

           ,tmp.[IsPDFGenerated]

           ,tmp.[Status]

           ,tmp.[CurrentState]

           ,tmp.[PreviousState]

           ,tmp.[LinkedDate]

           ,tmp.[LinkedByUser]

           ,tmp.[LinkedByRole]

           ,tmp.[IsCopy]

           ,tmp.[IsSentForLinking]

           ,tmp.[IsActive]

           ,tmp.[CreatedBy]

           ,GETDATE()

           ,tmp.[CreatedIPAddress]

           ,tmp.[CreatedBy]

           ,tmp.[MainDocumentId]

           ,tmp.[NoOfCopies]

           ,tmp.[Priority]

           ,tmp.[SecrecyLevel]

           ,tmp.[DateOfPublish]

           ,tmp.[DateOfUnPublish]

           ,tmp.[IsPublished]

           ,tmp.[IsMigrated]

           ,tmp.[IsDraft]

           ,tmp.[AttachmentType]

FROM #tmpDocument tmp

where tmp.[DocumentId]=0

IF @DocumentId Is Null

BEGIN 

Set @DocumentId= @@IDENTITY

END

SELECT  @DocumentId

        exec sp_xml_removedocument @intPointer

END TRY

BEGIN CATCH

SELECT @ErrMsg = ERROR_MESSAGE();

RAISERROR(@ErrMsg, @ErrSeverity, 1);

RETURN; 

END CATCH

END