Skip to the content

Dynamische query met pagination in een .NET API

In een aantal grote API projecten rees de vraag van een klant hoe deze de data uit de API zou kunnen gebruiken in Qlikview. Goede vraag zeker gezien de hoeveelheid entiteiten zou dit een flinke programmeerklus opleveren met voor iedere specifieke dataset een wirwar aan controllers en modellen.

De klant heeft een ruime ervaring in het maken van SQL Server query’s dus het zou mooi zijn als deze methode gebruikt zou kunnen worden in de aanroep naar de API.

Let wel op gebruik onderstaande blog nooit in een open API omdat je het mogelijk maakt om uit allerlei tabellen, zonder rechten op de verschillende tabellen, de data op te vragen.

Opgeslagen procedure voor dynamische query en pagination

Met onderstaande script kan ik op de SQL Server een dynamische query uitvoeren. Hiermee heb ik de basis voor de vraag klaar immers ik kan op een query achtige wijze de data ophalen uit alle tabellen in de API en daarbij ook nog gebruik maken van een pagination functie bij grote datasets.

CREATE PROCEDURE [dbo].[Usp_Paging_Cursor] (
@Tables varchar(1000),
@PK varchar(100),
@Sort varchar(200) = NULL,
@PageNumber int = 1,
@PageSize int = 10,
@Fields varchar(1000) = '*',
@Filter varchar(1000) = NULL,
@Group varchar(1000) = NULL)
AS

/*Find the @PK type*/
DECLARE @PKTable varchar(100)
DECLARE @PKName varchar(100)
DECLARE @type varchar(100)
DECLARE @prec int

IF CHARINDEX('.', @PK) > 0
	BEGIN
		SET @PKTable = SUBSTRING(@PK, 0, CHARINDEX('.',@PK))
		SET @PKName = SUBSTRING(@PK, CHARINDEX('.',@PK) + 1, LEN(@PK))
	END
ELSE
	BEGIN
		SET @PKTable = @Tables
		SET @PKName = @PK
	END

SELECT @type=t.name, @prec=c.prec
FROM sysobjects o 
JOIN syscolumns c on o.id=c.id
JOIN systypes t on c.xusertype=t.xusertype
WHERE o.name = @PKTable AND c.name = @PKName

IF CHARINDEX('char', @type) > 0
   SET @type = @type + '(' + CAST(@prec AS varchar) + ')'

DECLARE @strPageSize varchar(50)
DECLARE @strStartRow varchar(50)
DECLARE @strFilter varchar(1000)
DECLARE @strGroup varchar(1000)

/*Default Sorting*/
IF @Sort IS NULL OR @Sort = ''
	SET @Sort = @PK

/*Default Page Number*/
IF @PageNumber < 1
	SET @PageNumber = 1

/*Set paging variables.*/
SET @strPageSize = CAST(@PageSize AS varchar(50))
SET @strStartRow = CAST(((@PageNumber - 1)*@PageSize + 1) AS varchar(50))

/*Set filter & group variables.*/
IF @Filter IS NOT NULL AND @Filter != ''
	SET @strFilter = ' WHERE ' + @Filter + ' '
ELSE
	SET @strFilter = ''
IF @Group IS NOT NULL AND @Group != ''
	SET @strGroup = ' GROUP BY ' + @Group + ' '
ELSE
	SET @strGroup = ''
	
/*Execute dynamic query*/	
EXEC(
'DECLARE @PageSize int
SET @PageSize = ' + @strPageSize + '

DECLARE @PK ' + @type + '
DECLARE @tblPK TABLE (
            pk  ' + @type + ' NOT NULL PRIMARY KEY
            )

DECLARE PagingCursor CURSOR DYNAMIC READ_ONLY FOR
SELECT '  + @PK + ' FROM ' + @Tables + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort + '

OPEN PagingCursor
FETCH RELATIVE ' + @strStartRow + ' FROM PagingCursor INTO @PK

SET NOCOUNT ON

WHILE @PageSize > 0 AND @@FETCH_STATUS = 0
BEGIN
            INSERT @tblPK (pk)  VALUES (@PK)
            FETCH NEXT FROM PagingCursor INTO @PK
            SET @PageSize = @PageSize - 1
END

CLOSE       PagingCursor
DEALLOCATE  PagingCursor

SELECT ' + @Fields + ' FROM ' + @Tables + ' JOIN @tblPK tblPK ON ' + @PK + ' = tblPK.pk ' + @strFilter + ' ' + @strGroup + ' ORDER BY ' + @Sort
)

GO

 

Vervolgens heb ik in de API een model aangemaakt waarin de parameters voor de controller kunnen worden opgeslagen. Omdat ik de klant de keuze wil geven om gebruik te maken van JSON of XML zijn beide methodes in het model opgenomen.

 

using System.Xml.Serialization;
using Newtonsoft.Json;
namespace FilterConcept
{
    public class PaginationFilterConceptModel
    {
        [XmlElement("tables")]
        [JsonProperty("tables")]
        public string Tables { get; set; } = "";
        [XmlElement("primairyKey")]
        [JsonProperty("primairyKey")]
        public string PrimairyKey { get; set; } = "";
        [XmlElement("pageNumber")]
        [JsonProperty("pageNumber")]
        public string PageNumber { get; set; } = "1";
        [XmlElement("pageSize")]
        [JsonProperty("pageSize")]
        public string PageSize { get; set; } = "1000";
        [XmlElement("fields")]
        [JsonProperty("fields")]
        public string Fields { get; set; } = "*";
        [XmlElement("sort")]
        [JsonProperty("sort")]
        public string Sort { get; set; } = "";
        [XmlElement("filter")]
        [JsonProperty("filter")]
        public string Filter { get; set; } = "";
        [XmlElement("group")]
        [JsonProperty("group")]
        public string Group { get; set; } = "";
    }
}

 

JSON Body

De JSON voor de body zal er dan als volgt uit kunnen zien;

Je kan in dit script geen alias gebruiken voor de tabelnaam. Dit kan wel voor de veldnamen waarmee je de output aan kan sluiten op de property namen in je eigen applicatie of de BI. Als je alle velden uit een tabel of een query terug wilt zien dan kan je ook een astriks (*) gebruiken.

Een primairy key is in dit script noodzakelijk. Je krijgt de waarde van de primairy key terug als property [pk] in je JSON result.

Pagenumber is de pagina die je opvraagt te beginnen bij pagina 1. Dus als je 100.000 records verwacht en je hebt de pagesize ingesteld op 1000 dan zullen er 1000 pagina's beschikbaar zijn met ieder 1000 records.

Je gebruikt vrijwel dezelfde opdracht dan zoals je die zou gebruiken in SQL Server waarbij de tabel alias één van de weinige beperkingen is in het gebruik van dit script.

{
"tables" : "SalesOrderDetail INNER JOIN SalesOrderHeader ON SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID AND SalesOrderDetail.SalesOrderID = SalesOrderHeader.SalesOrderID",
"primairyKey" : "SalesOrderHeader.SalesOrderID",
"pageNumber" : "1",
"pageSize" : "1000",
"fields" : "SalesOrderHeader.SalesOrderID, SalesOrderHeader.OrderDate, SalesOrderHeader.DueDate, SalesOrderHeader.ShipDate, SalesOrderHeader.Status, SalesOrderDetail.ProductID, SalesOrderDetail.UnitPrice",
"sort" : "",
"filter" : "(SalesOrderHeader.SalesOrderID = 43659)",
"group" : ""
}

 

Data serializer

Dit blog gaat niet over de implementatie van SQL server commands dus dat deel sla ik hier over. Ik lees het resultaat in een datatable en gebruik vervolgens de Javascript serializer om de data in de tabel om te zetten naar JSON.

{
    Script.Serialization.JavaScriptSerializer serializer = new Script.Serialization.JavaScriptSerializer();
    List<Dictionary<string, object>> rows = new List<Dictionary<string, object>>();
    Dictionary<string, object> row;
    foreach (DataRow dr in objDt.Rows)
    {
        row = new Dictionary<string, object>();
        foreach (DataColumn col in objDt.Columns)
            row.Add(col.ColumnName, dr(col));
        rows.Add(row);
    }
    objResult = serializer.Serialize(rows);
}

Have fun met de code en feedback is welkom via het contact formulier.