Create New Table :
CREATE TABLE [dbo].[tblCommentBox](
[commentBoxID] [int] IDENTITY(1,1) NOT NULL,
[commentBoxTitle] [varchar](max) NULL,
[commentBoxDesc] [nvarchar](max) NULL,
[created] [datetime] NULL,
[modified] [datetime] NULL,
[commentStatus] [bit] NULL,
[BoxPositionX] [int] NULL,
[BoxPositionY] [int] NULL,
[boxHeight] [int] NULL,
[boxWidth] [int] NULL
) ON [PRIMARY]
create table tblCommentNetwork (
commentNetworkId int identity(1,1),networkId int,
commentBoxID int )
Create Store Procedure to insert :
create proc [dbo].[bsp_commentBox_InsertUpdate_ByXML]
@RetrieveXMLDoc xml
AS
declare @docHandler int
exec sp_xml_preparedocument @docHandler output,@RetrieveXMLDoc
begin tran
declare @isError nvarchar(max)='0'
declare @isCount int=0
/* tblCommentBox */
declare @AddEdit nvarchar(1)
declare @commentBoxID int
declare @commentBoxTitle varchar(max)
declare @commentBoxDesc nvarchar(max)=''
declare @commentStatus bit
declare @BoxPositionX int
declare @BoxPositionY int
declare @boxHeight int
declare @boxWidth int
select @commentBoxID = _commentBoxID,
@commentBoxTitle = _commentBoxTitle,
@commentBoxDesc = _commentBoxDesc,
@commentStatus = _commentStatus,
@BoxPositionX = _BoxPositionX,
@BoxPositionY = _BoxPositionY,
@boxHeight = _boxHeight,
@boxWidth = _boxWidth
from openxml (@docHandler,'NewDataSet/CommentBox',2)
with(_commentBoxID int,_commentBoxTitle varchar(max),_commentBoxDesc nvarchar(max), _commentStatus int,
_BoxPositionX int, _BoxPositionY int, _boxHeight int, _boxWidth int)
if (@commentBoxID = 0)
begin
insert into tblCommentBox (commentBoxTitle,commentBoxDesc,created,modified,commentStatus,BoxPositionX,BoxPositionY,boxHeight,boxWidth)
values (@commentBoxTitle,@commentBoxDesc,getdate(),getdate(),@commentStatus,@BoxPositionX,@BoxPositionY,@boxHeight,@boxWidth)
set @commentBoxID = @@identity
set @isError = @@ERROR
end
else
begin
update tblCommentBox set commentBoxTitle = @commentBoxTitle, commentBoxDesc = @commentBoxDesc, commentStatus = @commentStatus,
BoxPositionX = @BoxPositionX, BoxPositionY = @BoxPositionY, boxHeight = @boxHeight, boxWidth = @boxWidth
where commentBoxID = @commentBoxID
set @isError = @@ERROR
end
if @isError = '0'
begin /* Comment-Network */
delete from tblCommentNetwork where commentBoxID = @commentBoxID
declare @networkId int
select identity(int,1,1)SNo,_networkId into #tempCommentNetwork
from openxml (@docHandler,'NewDataSet/CommentBox/networkCommentBox',2) with(_networkId int)
declare @NewSNo int
set @NewSNo =1
select @networkId = _networkId from #tempCommentNetwork where SNo = @NewSNo
while @@rowcount > 0
begin
Insert into tblCommentNetwork(networkId,commentBoxID) Values(@networkId,@commentBoxID)
set @isError = @@error
if @isError <> '0'
begin
break
end
set @NewSNo = @NewSNo + 1
select @networkId = _networkId from #tempCommentNetwork where SNo = @NewSNo
end /* While End */
end /* Comment-Network End */
if @isError = '0'
begin
select 'DS102'
commit tran
end
else
begin
select @isError
rollback tran
end
XML :
bsp_commentBox_InsertUpdate_ByXML
'<NewDataSet>
<CommentBox>
<_commentBoxID>0</_commentBoxID>
<_commentBoxTitle>Frontech Mouse</_commentBoxTitle>
<_commentStatus>1</_commentStatus>
<_boxHeight>100</_boxHeight>
<_boxWidth>100</_boxWidth>
<_BoxPositionX>0</_BoxPositionX>
<_BoxPositionY>0</_BoxPositionY>
<_commentBoxDesc>Test Description</_commentBoxDesc>
<networkCommentBox>
<_commentNetworkId>0</_commentNetworkId>
<_networkId>11</_networkId>
<_commentBoxID>0</_commentBoxID>
</networkCommentBox>
<networkCommentBox>
<_commentNetworkId>0</_commentNetworkId>
<_networkId>22</_networkId>
<_commentBoxID>0</_commentBoxID>
</networkCommentBox>
</CommentBox>
</NewDataSet>'
CREATE TABLE [dbo].[tblCommentBox](
[commentBoxID] [int] IDENTITY(1,1) NOT NULL,
[commentBoxTitle] [varchar](max) NULL,
[commentBoxDesc] [nvarchar](max) NULL,
[created] [datetime] NULL,
[modified] [datetime] NULL,
[commentStatus] [bit] NULL,
[BoxPositionX] [int] NULL,
[BoxPositionY] [int] NULL,
[boxHeight] [int] NULL,
[boxWidth] [int] NULL
) ON [PRIMARY]
create table tblCommentNetwork (
commentNetworkId int identity(1,1),networkId int,
commentBoxID int )
Create Store Procedure to insert :
create proc [dbo].[bsp_commentBox_InsertUpdate_ByXML]
@RetrieveXMLDoc xml
AS
declare @docHandler int
exec sp_xml_preparedocument @docHandler output,@RetrieveXMLDoc
begin tran
declare @isError nvarchar(max)='0'
declare @isCount int=0
/* tblCommentBox */
declare @AddEdit nvarchar(1)
declare @commentBoxID int
declare @commentBoxTitle varchar(max)
declare @commentBoxDesc nvarchar(max)=''
declare @commentStatus bit
declare @BoxPositionX int
declare @BoxPositionY int
declare @boxHeight int
declare @boxWidth int
select @commentBoxID = _commentBoxID,
@commentBoxTitle = _commentBoxTitle,
@commentBoxDesc = _commentBoxDesc,
@commentStatus = _commentStatus,
@BoxPositionX = _BoxPositionX,
@BoxPositionY = _BoxPositionY,
@boxHeight = _boxHeight,
@boxWidth = _boxWidth
from openxml (@docHandler,'NewDataSet/CommentBox',2)
with(_commentBoxID int,_commentBoxTitle varchar(max),_commentBoxDesc nvarchar(max), _commentStatus int,
_BoxPositionX int, _BoxPositionY int, _boxHeight int, _boxWidth int)
if (@commentBoxID = 0)
begin
insert into tblCommentBox (commentBoxTitle,commentBoxDesc,created,modified,commentStatus,BoxPositionX,BoxPositionY,boxHeight,boxWidth)
values (@commentBoxTitle,@commentBoxDesc,getdate(),getdate(),@commentStatus,@BoxPositionX,@BoxPositionY,@boxHeight,@boxWidth)
set @commentBoxID = @@identity
set @isError = @@ERROR
end
else
begin
update tblCommentBox set commentBoxTitle = @commentBoxTitle, commentBoxDesc = @commentBoxDesc, commentStatus = @commentStatus,
BoxPositionX = @BoxPositionX, BoxPositionY = @BoxPositionY, boxHeight = @boxHeight, boxWidth = @boxWidth
where commentBoxID = @commentBoxID
set @isError = @@ERROR
end
if @isError = '0'
begin /* Comment-Network */
delete from tblCommentNetwork where commentBoxID = @commentBoxID
declare @networkId int
select identity(int,1,1)SNo,_networkId into #tempCommentNetwork
from openxml (@docHandler,'NewDataSet/CommentBox/networkCommentBox',2) with(_networkId int)
declare @NewSNo int
set @NewSNo =1
select @networkId = _networkId from #tempCommentNetwork where SNo = @NewSNo
while @@rowcount > 0
begin
Insert into tblCommentNetwork(networkId,commentBoxID) Values(@networkId,@commentBoxID)
set @isError = @@error
if @isError <> '0'
begin
break
end
set @NewSNo = @NewSNo + 1
select @networkId = _networkId from #tempCommentNetwork where SNo = @NewSNo
end /* While End */
end /* Comment-Network End */
if @isError = '0'
begin
select 'DS102'
commit tran
end
else
begin
select @isError
rollback tran
end
XML :
bsp_commentBox_InsertUpdate_ByXML
'<NewDataSet>
<CommentBox>
<_commentBoxID>0</_commentBoxID>
<_commentBoxTitle>Frontech Mouse</_commentBoxTitle>
<_commentStatus>1</_commentStatus>
<_boxHeight>100</_boxHeight>
<_boxWidth>100</_boxWidth>
<_BoxPositionX>0</_BoxPositionX>
<_BoxPositionY>0</_BoxPositionY>
<_commentBoxDesc>Test Description</_commentBoxDesc>
<networkCommentBox>
<_commentNetworkId>0</_commentNetworkId>
<_networkId>11</_networkId>
<_commentBoxID>0</_commentBoxID>
</networkCommentBox>
<networkCommentBox>
<_commentNetworkId>0</_commentNetworkId>
<_networkId>22</_networkId>
<_commentBoxID>0</_commentBoxID>
</networkCommentBox>
</CommentBox>
</NewDataSet>'
No comments:
Post a Comment