Skip to content

Instantly share code, notes, and snippets.

@ritacse

ritacse/Insert ntext in TempT

Last active June 1, 2022 03:54
Show Gist options
  • Save ritacse/3ad71f9f25393c3750765eb5fc004769 to your computer and use it in GitHub Desktop.
Save ritacse/3ad71f9f25393c3750765eb5fc004769 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
Insert ntext in TempT
---in controller code
Int64 creator = Convert.ToInt64(HttpContext.User.Identity.Name.Split('#')[0]);
var xmlChild = new XElement("ArrayOfSecuritySubject", view.SectorwiseSurveyList.Select(x => new XElement("ESecuritySubject",
new XElement("ChildID", x.ChildID),
new XElement("inspectionSubjectId", x.SubjectID),
new XElement("obtainedMarks", x.Marks),
new XElement("isNotApplicable", x.notApplicable),
new XElement("remarks", x.InspectionRemarks)
)));
var InspectionId = new SqlParameter {ParameterName = "InspectionId", Value = view.InspectionId };
var EstablishmentId = new SqlParameter { ParameterName = "EstablishmentId", Value = view.establishmentId };
var inspectionDate = new SqlParameter { ParameterName = "inspectionDate", Value = view.inspectionDate };
var user = new SqlParameter { ParameterName = "creator", Value = creator };
var lstOfChildDataParm = new SqlParameter
{
ParameterName = "lstOfSecuritySubjectData", //same as SP parameter name
Value = xmlChild.ToString(),
SqlDbType = SqlDbType.NVarChar
};
var SP = "InsertUpdateSubjectWiseInspectionSP " + view.InspectionId + "," + view.establishmentId + ",' " + view.inspectionDate + "'," + xmlChild + "'," + creator;
var Query = "InsertUpdateSubjectWiseInspectionSP @InspectionId, @EstablishmentId, @inspectionDate ,@lstOfChildData ,@creator " ;
var result = DBContext.Database.SqlQuery<ResultSaveVM>(Query, InspectionId, EstablishmentId, inspectionDate, lstOfChildDataParm, user).FirstOrDefault();
--- In SP
@lstOfChildData ntext /// parameter
DECLARE @IDout bigint,@Code as varchar(20),@OutlstOfData int
------------------------------------- INSERT XML INTO TABLE ------------------------------------------
CREATE TABLE #TempTeamMember_T
(
teamId int
,userId int
,positioningTeam nvarchar(50)
)
EXEC sp_xml_preparedocument @OutlstOfData OUTPUT, @lstOfChildData
INSERT INTO #TempTeamMember_T
SELECT
teamId,
userId,
positioningTeam
FROM Openxml( @OutlstOfData,'/ArrayOfTeamMemberInfo/ETeamMemberInfo', 3)
WITH
(
teamId int
,userId int
,positioningTeam nvarchar(50)
)
Raw
InsertUpdate SP with versioning
Alter PROCEDURE [dbo].[Purchase_Comparative _Statement_Insert_Update_SP]
@ID bigint ,
@CompanyCode varchar(20) ,
@BuyerCode varchar(20) ,
@CurrencyCode varchar(20) ,
@ProductCode varchar(50) ,
@UnitCode varchar(20) ,
@Specification varchar(500) ,
@MinOrderQty int ,
@IsRecommended bit ,
@Justification varchar(500) ,
@Note varchar(500) ,
@User bigint,
@lstOfChildData xml
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @IDOut varchar(20) ,@Code as Varchar(20)
DECLARE @OutChildData int
CREATE TABLE #PCSChild_T(
childId bigint,
SupplierCode varchar(50) ,
QuotationDate date ,
BrandName varchar(150) ,
ProposedPrice decimal(18,2) ,
CountryOfOrigin varchar(50) ,
LeadTime datetime ,
PaymentTermCode int ,
PaymentModeCode varchar(50) ,
OfferValidity date ,
TermsConditions varchar(500) ,
Warranty varchar(150) ,
VatTax varchar(150) ,
Remarks varchar(250) ,
RecommendationSerial int
)
------------------------------------- INSERT XML INTO TABLE ------------------------------------------
INSERT INTO #PCSChild_T
SELECT T.Item.query('./childId').value('.','[BIGINT]') childId,
T.Item.query('./SupplierCode').value('.','varchar(50)') SupplierCode,
T.Item.query('./QuotationDate').value('.','[date]') QuotationDate,
T.Item.query('./BrandName').value('.','varchar(150)') BrandName,
T.Item.query('./ProposedPrice').value('.','decimal(18,2)') ProposedPrice,
T.Item.query('./CountryOfOrigin').value('.','varchar(50)') CountryOfOrigin,
T.Item.query('./LeadTime').value('.','[datetime]') LeadTime,
T.Item.query('./PaymentTermCode').value('.','[int]') PaymentTermCode,
T.Item.query('./PaymentModeCode').value('.','varchar(50)') PaymentModeCode,
T.Item.query('./OfferValidity').value('.','[date]') OfferValidity,
T.Item.query('./TermsConditions').value('.','varchar(500)') TermsConditions,
T.Item.query('./Warranty').value('.','varchar(150)') Warranty,
T.Item.query('./VatTax').value('.','varchar(150)') VatTax,
T.Item.query('./Remarks').value('.','varchar(250)') Remarks,
T.Item.query('./RecommendationSerial').value('.','[int]') RecommendationSerial
FROM @lstOfChildData.nodes('/ArrayOfPurchaseCS_Child/EPurchaseCS_Child') AS T(Item)
----DELETE FROM #PCSChild_T WHERE childId =''
---------- //// Validation required if exist ////----------
--SET @isExist= ISNULL((SELECT Top(1)[ID] FROM [Purchase_Comparative _Statement_Parent_T_X] with (nolock) WHERE CompanyID=@CompanyCode
-- AND GeneralDutyDate=@GeneralDutyDate ),'' )
-- IF(@isExist <> 0 AND @ID = 0) ---- //// validation-1
-- Select -9 as ID, @Code as Code, 0 as Version, 'False' as IsSuccess, 'Data already exist against this employee for the selected month.' as Message
---------- ////---------- END validation ---------- ////----------
BEGIN
IF ( @ID = 0 )
BEGIN
Declare @ParentIdNew as int
Set @ParentIdNew =Isnull(( Select Top(1) [ID] from [Purchase_Comparative _Statement_Parent_T_X] group by [ID] order by [ID] desc),0)+1
SET @Code = 'PCS'+ @ParentIdNew
print @ParentIdNew
INSERT INTO [Purchase_Comparative _Statement_Parent_T_X]
([ID],[Code],[CompanyCode],[BuyerCode],[CurrencyCode],[ProductCode],[UnitCode],[Specification],[MinOrderQty],[IsRecommended],[Justification],[Note],[ApprovalCode],[Version],[Creator],[CreationDate])
VALUES
(@ParentIdNew,@Code , @CompanyCode , @BuyerCode,@CurrencyCode,@ProductCode,@UnitCode,@Specification,@MinOrderQty, @IsRecommended,@Justification , @Note , NULL , 0 , @User,GETDATE())
SET @IDOut = convert(varchar(10), @ParentIdNew) +'/'+@Code +'/'+'0'
------------------------------------------INSERT XML DATA INTO CHILD TABLE ------------------------------------------
INSERT INTO [dbo].[Purchase_Comparative _Statement_Child_T_X]
( [ParentId], [SupplierCode], [QuotationDate], [BrandName], [ProposedPrice], [CountryOfOrigin], [LeadTime], [PaymentTermCode], [PaymentModeCode], [OfferValidity], [TermsConditions], [Warranty], [VatTax], [Remarks], [RecommendationSerial], [Version])
SELECT @ParentIdNew, SupplierCode , QuotationDate , BrandName , ProposedPrice , CountryOfOrigin , LeadTime , PaymentTermCode , PaymentModeCode , OfferValidity , TermsConditions , Warranty , VatTax , Remarks , RecommendationSerial , 0
FROM #PCSChild_T
------------------------------------------END INSERT ------------------------------------------
Select @ParentIdNew as ID, @Code as Code, 0 as Version, 'True' as IsSuccess, 'Data Save Success' as Message
END
ELSE IF ( @ID >0 )
BEGIN
print 'Edit'
DECLARE @VERSION INT = Isnull((Select MAX([VERSION]) from [Purchase_Comparative _Statement_Parent_T_X] WITH (NOLOCK) WHERE ID=@ID),0)
DECLARE @ApproalCode INT = (Select ApprovalCode from [Purchase_Comparative _Statement_Parent_T_X] WITH (NOLOCK) WHERE ID=@ID AND [Version]= @Version)
SET @CODE = (Select Code from [Purchase_Comparative _Statement_Parent_T_X] WITH (NOLOCK) WHERE ID=@ID AND [Version]= @Version)
IF(@ApproalCode = 7 OR @ApproalCode IS NULL)
BEGIN
UPDATE [Purchase_Comparative _Statement_Parent_T_X]
SET
[CompanyCode] = @CompanyCode
,[BuyerCode] = @BuyerCode
,[CurrencyCode] = @CurrencyCode
,[ProductCode] = @ProductCode
,[UnitCode] = @UnitCode
,[Specification] = @Specification
,[MinOrderQty] = @MinOrderQty
,[IsRecommended] = @IsRecommended
,[Justification] = @Justification
,[Note] = @Note
,[Modifier]=@User
,[ModificationDate]=GETDATE()
WHERE [ID] = @ID and [Version]= @Version
SET @IDOut = cast(@ID as varchar(20))
---- **** 1. Delete from child_T
DELETE Child
FROM [Purchase_Comparative _Statement_Child_T_X] Child with (nolock)
LEFT OUTER JOIN #PCSChild_T temp ON Child.Id = temp.childId
WHERE temp.childId IS NULL AND Child.ParentID=@ID AND Child.Version=@VERSION
---- **** 2. Update child_T
print 'Update childId'
UPDATE PCSc SET
PCSc.[QuotationDate] = Temp.QuotationDate
,PCSc.[BrandName] = Temp.BrandName
,PCSc.[ProposedPrice] = Temp.ProposedPrice
,PCSc.[CountryOfOrigin] = Temp.CountryOfOrigin
,PCSc.[LeadTime] = Temp.LeadTime
,PCSc.[PaymentTermCode] = Temp.PaymentTermCode
,PCSc.[PaymentModeCode] = Temp.PaymentModeCode
,PCSc.[OfferValidity] = Temp.OfferValidity
,PCSc.[TermsConditions] = Temp.TermsConditions
,PCSc.[Warranty] = Temp.Warranty
,PCSc.[VatTax] = Temp.VatTax
,PCSc.[Remarks] = Temp.Remarks
,PCSc.[RecommendationSerial] = Temp.RecommendationSerial
FROM #PCSChild_T Temp
INNER JOIN [dbo].[Purchase_Comparative _Statement_Child_T_X] PCSc WITH (NOLOCK) ON Temp.childId= PCSc.Id
and PCSc.SupplierCode = Temp.SupplierCode
WHERE PCSc.ParentID= @ID and PCSc.VERSION=@VERSION
---- **** 3. New Insert in child_T
INSERT INTO [dbo].[Purchase_Comparative _Statement_Child_T_X]
( [ParentId], [SupplierCode], [QuotationDate], [BrandName], [ProposedPrice], [CountryOfOrigin], [LeadTime], [PaymentTermCode], [PaymentModeCode], [OfferValidity], [TermsConditions], [Warranty], [VatTax], [Remarks], [RecommendationSerial], [Version])
SELECT @ID, SupplierCode , QuotationDate , BrandName , ProposedPrice , CountryOfOrigin , LeadTime , PaymentTermCode , PaymentModeCode , OfferValidity , TermsConditions , Warranty , VatTax , Remarks , RecommendationSerial , @VERSION
FROM #PCSChild_T where t.childId=0 AND @ID > 0
SELECT @ID AS ID,'' as Code, 0 AS Version, 'True' AS IsSuccess, 'Data Updated Successfully' AS [Message];
END
IF(@ApproalCode = 5 )
BEGIN
declare @NewVersion int = 0
print 'New Version'
set @NewVersion = @VERSION+1
----===========~~~~~~~ THIS SECTION FOR VERSIONING ~~~~~~~===========
INSERT INTO [Purchase_Comparative _Statement_Parent_T_X]
([ID],[Code],[CompanyCode],[BuyerCode],[CurrencyCode],[ProductCode],[UnitCode],[Specification],[MinOrderQty],[IsRecommended],[Justification],[Note],[ApprovalCode],[Version],[Creator],[CreationDate])
VALUES
(@ParentIdNew,@Code , @CompanyCode , @BuyerCode,@CurrencyCode,@ProductCode,@UnitCode,@Specification,@MinOrderQty, @IsRecommended,@Justification , @Note , NULL , @VERSION , @User,GETDATE())
SET @IDOut = convert(varchar(10), @ParentIdNew) +'/'+@Code +'/'+'0'
------------------------------ INSERT Version INTO CHILD TABLE ------------------------------
INSERT INTO [dbo].[Purchase_Comparative _Statement_Child_T_X]
( [ParentId], [SupplierCode], [QuotationDate], [BrandName], [ProposedPrice], [CountryOfOrigin], [LeadTime], [PaymentTermCode], [PaymentModeCode], [OfferValidity], [TermsConditions], [Warranty], [VatTax], [Remarks], [RecommendationSerial], [Version])
SELECT @ParentIdNew, SupplierCode , QuotationDate , BrandName , ProposedPrice , CountryOfOrigin , LeadTime , PaymentTermCode , PaymentModeCode , OfferValidity , TermsConditions , Warranty , VatTax , Remarks , RecommendationSerial , @VERSION
FROM #PCSChild_T
------------------------------------------ END Versioning ------------------------------------------
SELECT @ID AS ID,'' as Code, @VERSION AS Version, 'True' AS IsSuccess, 'New version created Successfully' AS [Message];
END
END
DROP TABLE #PCSChild_T
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
SELECT
@ErrorMessage_NVC = ERROR_MESSAGE(),
@ErrorSeverity_INT = ERROR_SEVERITY(),
@ErrorNumber_INT = ERROR_NUMBER(),
@ErrorProcedure_VC = ERROR_PROCEDURE(),
@ErrorLine_INT = ERROR_LINE()
Select -3 as ID, @ErrorNumber_INT as Code, 0 as Version, 'False' as IsSuccess, @ErrorMessage_NVC as Message
RETURN
END CATCH
END
Raw
InsertXML Full SP
--Query: [General_Duty_T_X_Insert_Update_SP] '0','00','2021-01-08','ok','False','31725','<ArrayOfGeneralDutyChild>
-- <EGeneralDutyChild>
-- <EmpId>100590</EmpId>
-- </EGeneralDutyChild>
-- <EGeneralDutyChild>
-- <EmpId>113786</EmpId>
-- </EGeneralDutyChild>
-- <EGeneralDutyChild>
-- <EmpId>99658</EmpId>
-- </EGeneralDutyChild>
--</ArrayOfGeneralDutyChild
ALTER PROCEDURE [dbo].[General_Duty_T_X_Insert_Update_SP]
@ID bigint,
@CompanyID varchar (10),
@GeneralDutyDate varchar (20),
@Note varchar (250),
@IsAbsent bit,
@User bigint,
@lstOfChildData xml
AS
BEGIN
BEGIN TRY
BEGIN TRANSACTION
DECLARE @IDOut varchar(20)
DECLARE @OutChildData int, @isExist int=0 , @isGenSalary varchar(10)=''
DECLARE @Code as Varchar(20)
---------- //// Validation required if exist ////----------
SET @isExist= ISNULL((SELECT Top(1)[ID] FROM [General_Duty_Parent_T_X] with (nolock) WHERE CompanyID=@CompanyID
AND GeneralDutyDate=@GeneralDutyDate ),'' )
IF(@isExist <> 0 AND @ID = 0) ---- //// validation-1
Select -9 as ID, @Code as Code, 0 as Version, 'False' as IsSuccess, 'Data already exist against this employee for the selected month.' as Message
---------- ////---------- END validation ---------- ////----------
ELSE
BEGIN
IF ( @ID = 0 )
BEGIN
Declare @ParentIdNew as int
Set @ParentIdNew =Isnull(( Select Top(1) [ID] from [General_Duty_Parent_T_X] group by [ID] order by [ID] desc),0)+1
print @ParentIdNew
INSERT INTO [General_Duty_Parent_T_X]
([ID] ,[CompanyID] ,[GeneralDutyDate] ,[Note] ,[IsAbsent] ,[ApprovalCode] ,[Varsion] ,[Creator] ,[CreationDtae])
VALUES
(@ParentIdNew, @CompanyID , @GeneralDutyDate , @Note , @IsAbsent , NULL , 0 , @User,GETDATE())
SET @IDOut = convert(varchar(10), @ParentIdNew) +'/'+@Code +'/'+'0'
------------------------------------------INSERT XML DATA INTO CHILD TABLE ------------------------------------------
INSERT INTO [dbo].[General_Duty_Child_T_X]
([ParentID]
,[EmployeeID]
,[FirstPresentDate]
,[SecondPresentDate]
,[FirstDateOT]
,[Varsion]
)
SELECT
@ParentIdNew,
--T.Item.query('./XMLPropertyName').value('.','[DataType]') TableColunmName,
T.Item.query('./EmpId').value('.','[BIGINT]') EmployeeID,
NULL,
NULL,
0,
0
FROM @lstOfChildData.nodes('/ArrayOfGeneralDutyChild/EGeneralDutyChild') AS T(Item)
------------------------------------------END INSERT ------------------------------------------
Select @ParentIdNew as ID, @Code as Code, 0 as Version, 'True' as IsSuccess, 'Data Save Success' as Message
END
END
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
DECLARE @ErrorNumber_INT INT;
DECLARE @ErrorSeverity_INT INT;
DECLARE @ErrorProcedure_VC VARCHAR(200);
DECLARE @ErrorLine_INT INT;
DECLARE @ErrorMessage_NVC NVARCHAR(4000);
SELECT
@ErrorMessage_NVC = ERROR_MESSAGE(),
@ErrorSeverity_INT = ERROR_SEVERITY(),
@ErrorNumber_INT = ERROR_NUMBER(),
@ErrorProcedure_VC = ERROR_PROCEDURE(),
@ErrorLine_INT = ERROR_LINE()
Select -3 as ID, @ErrorNumber_INT as Code, 0 as Version, 'False' as IsSuccess, @ErrorMessage_NVC as Message
RETURN
END CATCH
END
Raw
InsertXML In Temp_T
DEclare @lstOfChildData xml='<ArrayOfGeneralDutyChild>
<EGeneralDutyChild>
<EmpId>100590</EmpId>
</EGeneralDutyChild>
<EGeneralDutyChild>
<EmpId>113786</EmpId>
</EGeneralDutyChild>
<EGeneralDutyChild>
<EmpId>99658</EmpId>
</EGeneralDutyChild>
</ArrayOfGeneralDutyChild
--T.Item.query('./XMLPropertyName').value('.','[DataType]') TableColunmName,
SELECT T.Item.query('./EmpId').value('.','[BIGINT]') EmployeeID INTO #EmpIds_T
FROM @lstOfChildData.nodes('/ArrayOfGeneralDutyChild/EGeneralDutyChild') AS T(Item)
SELECT * FROM #EmpIds_T
DROP TABLE #EmpIds_T
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment