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

Blazor Export Excel based on template excel file C#

 Target:

i have mudtable contains data from model object.

i need when user press on export excel button to load object data for mudtable to ready excel file template and write data for this template then download it for end user on the browser.

Used Nuget: NPOI version 2.6.0

Create custom component for export excel ExportExcel.razor

@inherits BaseComponentCustom

<a href="" @onclick="Export" class="btn btn-white btn-sm  shadow-sm fw-bold" data-bs-toggle="modal" data-bs-target="#kt_share">

    @trans["ExportData"]

    <!--begin:: Icon-->

    <img src="assets/media/svg/icon/share-qoute.svg" class="h-25px">

    <!--end:: Icon-->

</a>

*-------------------------------------------------------------------------------

using NPOI.SS.UserModel;

using NPOI.XSSF.UserModel;

namespace YourNameSpace;

public partial class ExportExcel

{

    private FileStream _file;

    [Parameter] public ExcelDataObject? ExcelDataObject{ get; set; }

    private async void Export()

    {

        if (ExcelDataObject!= null)

        {

            string rootpath = Path.Combine(System.IO.Directory.GetCurrentDirectory(), "wwwroot/assets/Templates/");

            string name = "TemplateFile.xlsx";

            string FileName = Path.Combine(rootpath, name);

            IWorkbook _workbook;

            var memoryStream = new MemoryStream();

            ISheet excelSheet;

            _file?.Close();

            var bytes = new byte[0];

            _file = new FileStream(FileName, FileMode.Open, FileAccess.Read);

            {

                    _workbook = new XSSFWorkbook(_file);

                    _workbook.MissingCellPolicy = MissingCellPolicy.RETURN_NULL_AND_BLANK;

                _file.Close();

            }

            excelSheet = _workbook.GetSheetAt(0);

            IWorkbook workbook = excelSheet.Workbook;

            int i = 1;

            foreach (var member in ExcelDataObject.ListToGetColumnsData)

            {

                IRow row = excelSheet.CreateRow(i);

                row.CreateCell(0).SetCellValue(member.cell0Value);

                row.CreateCell(1).SetCellValue(member.cell2Value);

                row.CreateCell(2).SetCellValue(member.cell3Value);

               );

                i++;

            }

            using (var ms = new MemoryStream())

            {

                workbook.Write(ms, false);

                bytes = ms.ToArray();

            }

            await SaveAs(JS, FileName + ".xlsx", bytes);

        }

        async Task SaveAs(IJSRuntime js, string fileName, byte[] data)

        {

            await js.InvokeAsync<object>(

                "BlazorDownloadFile",

                fileName,

                Convert.ToBase64String(data)

            );

        }

    }

}

---------------------------------------------------------------------------------------------

Javascript Function

function BlazorDownloadFile(filename, bytesBase64) {

    var link = document.createElement('a');

    link.download = filename;

    link.href = "data:application/octet-stream;base64," + bytesBase64;

    document.body.appendChild(link); // Needed for Firefox

    link.click();

    document.body.removeChild(link);

}

C# linq Queries Tips and Tricks

 update multiple columns inside complex object

-------------------------------------------

  _policies = SharedFunctions.PoliciesFilterForTenant(policyTenants, _policies, _selectedTenant);

                foreach (TPolicy item in _policies)

                {

                    item.Roles = item.Roles.Select(r => { r.IsChecked = false; r.AssignmentId = 0; return r; }).ToList();

                }

---------------------------------------------------------------------

join two lists with multiple on condition

join table with custom list and get result using linq query.

 public bool UserTenantDelete(int TenantId, int GovId)

        {

            //get custom list to compare later

            List<UserTenant> TenantsToDelete = _ctx.UserTenants.Where(d => d.TenantId == TenantId && d.GovId == GovId).ToList();

            if (TenantsToDelete.Any())

            {

               List<SubjectAssignment> subjectAssignment = (from sa in _ctxPolicyServerDbContext.SubjectAssignments.ToList()

                                                             join ut in TenantsToDelete

                                                             on new {A=sa.TenantId, B=sa.SubjectId } equals new {A=ut.TenantId,B=ut.UserId.ToString()}

                                                             select  sa).ToList();

             //   _ctxPolicyServerDbContext.SubjectAssignments.RemoveRange(subjectAssignment);

               // _ctxPolicyServerDbContext.SaveChanges();

               // _ctx.SaveChanges();

                return true;

            }

            return false;

        }

-------------------------------------------------------------------------------------

Filter List using another List

   List<int> GovsNotAuthorized = _ctx.GovAgencyTenants.Where(a => a.TenantId != TenantId).Select(a=>a.GovId).ToList();
                List<int> GovsAuthorized = _ctx.GovAgencyTenants.Where(a => a.TenantId == TenantId).Select(a => a.GovId).ToList();
                List<int> result = GovsNotAuthorized.Where(x => !GovsAuthorized.Any(a => a == x)).Distinct().ToList(); 

-------------------------------------------------------------------------------------------------