sql server query to json

 sql server query result to json object

      
	SELECT [SettingKey]
		   ,[SettingValue]
	FROM Config.ServiceSettings
	WHERE ServiceID =15
	FOR JSON PATH 
       
 

sql server JSON_MODIFY and OpenJson Tips

 Json Modify Mulitple json properties at the same time

       
declare @OrderBody nvarchar(max) = (select  sme.fnOrderBodyGet(@OrderID))
declare @OrderBodyNew nvarchar(max) 
set @OrderBodyNew =JSON_MODIFY(
		               JSON_MODIFY(JSON_MODIFY(@OrderBody, 'strict$.Details.TransactionNumber', cast(@paymentID as varchar))
								,'strict$.Details.PaymentReferenceNo',@PaymentReferenceNo )
					 ,'strict$.Details.PaymentMethodID',cast(@paymentMethodID as varchar))
       
 

Select Json Array as Table

       
 DECLARE @JSONINPUT nvarchar(4000) =
    '{
        "OUTER": [{
                    "ABC":"TEST_WB_New","XYZ":"9085"
                 },
                 {
                    "ABC":"TEST_WB1_New","XYZ":"9086"
                 },
                 {
                    "ABC":"TEST_WB2_New","XYZ":"9087"
                 }]
    }';

    SELECT ABC, XYZ 
    FROM OPENJSON(@JSONINPUT,'$.OUTER') 
    WITH (ABC NVARCHAR(50), XYZ NVARCHAR(50))
       
 

Select Single Property from json field

       
DECLARE @result nvarchar(max)
	declare @paymentResponse nvarchar(500)
select @paymentResponse = sme.fnPaymentResponseBodySTDGet(@PaymentID) 
	SELECT @result=PaymentProviderReferenceNo
FROM OPENJSON ( @paymentResponse )  
WITH (   
              PaymentProviderReferenceNo   VARCHAR(200)   '$.PaymentProviderReferenceNo' 
 )
       
 

automapper map destination object to two sources objects

 problem:

i Have two models for child Components in blazor hosted app,

i need to bind two models to parent component model after validation for each one.

solution:

create map for two models with destination one

       
public class AutoMapper : Profile

    {

        public AutoMapper()

        {

  CreateMap();

        CreateMap();

} 

parent component declarations

       
[Inject] public IMapper? _mapper { get; set; }

 [Inject] protected IJSRuntime JS { get; set; }

[Inject] protected IWebAssemblyHostEnvironment host { get; set; }

 public ReceiptFormModel? FormModel { get; set; } = new();

  public VATTaxNumberModel? VATTaxNumberModel { get; set; }=new();

   public PolicyOwnerModel? PolicyOwnerModel { get; set; } =new();



 private async Task prepareModel()

        {

            var MapFirstModel = _mapper.Map(VATTaxNumberModel);

            FormModel= _mapper.Map(PolicyOwnerModel, MapFirstModel);



            LogToConsole($"FormModel=={JsonSerializer.Serialize(FormModel)}");

        }

protected void LogToConsole(string message)

    {

        if (host.IsDevelopment())

            JS.InvokeAsync("console.log", message);

    }

       
 

 

sql server update column to primary key in another table to prepare data for insert

Problem:

i have excel file need to export it in more than table into sql server database .

so after import data for temp table i need to select every primary table data and insert it.

so i need to create mapping columns for primary keys for master table to be able to insert detail table data with primary key for table.

example : i have this excel file data 

i have two tables :

RegionsTable

CityTable

i need after export to create insert into these two tables .

Region Table

City Table

solution:

1- select distinct regions from temp table and insert into Region Table:

         INSERT INTO [Lookups].[Regions]

           ([RegionId]

           ,[RegionNameAR]

           ,[RegionNameEN]

           ,[RegionPostalCode]

           ,[GeoRegionId])

    select distinct

          [RegionId]

           ,[RegionNameAR]

           ,[RegionNameEN]

           ,[RegionPostalCode]

           ,[GeoRegionId]

from dbo.Cities$      
 

2- create new column in temp table to save RegionId into .

ALTER TABLE dbo.Cities$ ADD
RegionRefID tinyint NULL
GO 
     
 

update dbo.Cities$

update dbo.Cities$  
set RegionRefID = r.RegionId
from dbo.Cities$ c
inner join  Lookups.Regions r
on
r.RegionId=c.RegionId
and r.RegionNameAR=c.RegionNameAR
and r.RegionNameEn=c.RegionNameEn
and r.RegionPostalCode=c.RegionPostalCode
and r.GeoRegionId =c.GeoRegionId
       
 

3-update city Table with city and mapp new regionid with query to keep reference to Region Table.

	INSERT INTO [Lookups].[City]

			   ([CityId]

			   ,[CityNameAR]

			   ,[CityNameEN]

			   ,[RegionId])

			 select Id,

	CityNameAr

	,CityNameEn

	,RegionIdNew

			 from dbo.Cities$

	GO

done.

 

Collapse region when press on CTR+M CTR+O visual studio 2022

 option to collapse 

I have to activate 'Collapse #regions when collapsing to definitions' in

Tools -> Options -> Text Editor -> C# -> Advanced