Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1189675%22%20slang%3D%22en-US%22%3E10K%20Request%20per%20Second%3A%20REST%20API%20with%20Azure%20SQL%2C%20Dapper%20and%26nbsp%3BJSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1189675%22%20slang%3D%22en-US%22%3E%3CP%3EIn%20the%20previous%20article%20I%20described%20how%20easy%20is%20to%20create%20a%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sql-database%2Fbuilding-rest-api-with-python-flask-and-azure-sql%2Fba-p%2F1056637%22%20target%3D%22_self%22%3EREST%20API%20with%20Python%20and%20Azure%20SQL%3C%2FA%3E.%20But%20what%20about%26nbsp%3B.NET%3F%20Would%20that%20be%20as%20easy%20as%20well%3F%20Absolutely%20yes%3A%20the%20native%20support%20to%20JSON%20offered%20by%20Azure%20SQL%20will%20also%20help%20a%20lot%20again.%20In%20addition%20to%20that%2C%20the%20Micro-ORM%20Dapper%20will%20make%20the%20interaction%20with%20database%20a%20breeze%2C%20and%20the%20new%20%3CEM%3ESystem.Text.Json%3C%2FEM%3E%20will%20take%20care%20of%20manipulating%20that%20developer-friendly%20format.%20Sounds%20too%20good%20and%20easy%20to%20be%20true%3F%20Well%2C%20let's%20prove%20it!%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EI%20started%20creating%20a%20WebAPI%20project%20using%26nbsp%3B.NET%20CLI%20(I'm%20using%26nbsp%3B.NET%20Core%203)%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Edotnet%20new%20webapi%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eand%20then%20I%20added%20the%20following%20packages%26nbsp%3B%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EDapper%3C%2FLI%3E%0A%3CLI%3EMicrosoft.Data.SqlClient%3C%2FLI%3E%0A%3CLI%3ESystem.Text.Json%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3Evia%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Edotnet%20add%20package%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3Eto%20add%20the%20libraries%20mentioned%20before.%20Dapper%20is%20an%20amazing%20MicroORM%2C%20that%20removes%20all%20the%20plumbing%20code%20needed%20to%20connect%20and%20query%20a%20database%2C%20so%20that%20you%20can%20focus%20more%20one%20the%20really%20important%20code.%20It%20is%20battle%20tested%2C%20as%20it%20is%20used%20by%20StackOverflow%20itself%2C%20and%20very%20recommended.%20If%20you%20want%20to%20have%20a%20kickstart%2C%20there%20is%20a%2010%20post%20tutorial%20here%3A%20Dapper.NET%20%40%20Medium%2C%20along%20with%20full%20source%20code%20for%20all%20described%20features.%3CBR%20%2F%3E%26nbsp%3B%3CBR%20%2F%3EFrom%20a%20database%20perspective%20I'm%20using%20almost%20the%20same%20code%20I%20used%20to%20build%20the%20same%20sample%20with%20Python%2C%20leveraging%20as%20much%20as%20possible%20the%20native%20support%20to%20JSON%20the%20Azure%20SQL%20provides%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3ECREATE%20OR%20ALTER%20PROCEDURE%20web.get_customer%3CBR%20%2F%3E%40Id%20INT%3CBR%20%2F%3EAS%3CBR%20%2F%3ESET%20NOCOUNT%20ON%3B%3CBR%20%2F%3ESELECT%20%3CBR%20%2F%3E%20%20%5BCustomerID%5D%2C%20%3CBR%20%2F%3E%20%20%5BCustomerName%5D%2C%20%3CBR%20%2F%3E%20%20%5BPhoneNumber%5D%2C%20%3CBR%20%2F%3E%20%20%5BFaxNumber%5D%2C%20%3CBR%20%2F%3E%20%20%5BWebsiteURL%5D%2C%3CBR%20%2F%3E%20%20%5BDeliveryAddressLine1%5D%20AS%20'Delivery.AddressLine1'%2C%3CBR%20%2F%3E%20%20%5BDeliveryAddressLine2%5D%20AS%20'Delivery.AddressLine2'%2C%3CBR%20%2F%3E%20%20%5BDeliveryPostalCode%5D%20AS%20'Delivery.PostalCode'%20%3CBR%20%2F%3EFROM%20%3CBR%20%2F%3E%20%20%5BSales%5D.%5BCustomers%5D%20%3CBR%20%2F%3EWHERE%20%3CBR%20%2F%3E%20%20%5BCustomerID%5D%20%3D%20%40Id%3CBR%20%2F%3EFOR%20JSON%20PATH%3CBR%20%2F%3EGO%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3EThanks%20to%20the%20FOR%20JSON%20PATH%20clause%2C%20the%20result%20will%20be%20like%20the%20following%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%5B%7B%3CBR%20%2F%3E%20%20%22CustomerID%22%3A%20123%2C%3CBR%20%2F%3E%20%20%22CustomerName%22%3A%20%22Tailspin%20Toys%20(Roe%20Park%2C%20NY)%22%2C%3CBR%20%2F%3E%20%20%22PhoneNumber%22%3A%20%22(212)%20555%E2%80%930100%22%2C%3CBR%20%2F%3E%20%20%22FaxNumber%22%3A%20%22(212)%20555%E2%80%930101%22%2C%3CBR%20%2F%3E%20%20%22WebsiteURL%22%3A%20%22http%3A%2F%2Fwww.tailspintoys.com%5C%2FRoePark%22%2C%3CBR%20%2F%3E%20%20%22Delivery%22%3A%20%7B%3CBR%20%2F%3E%20%20%20%22AddressLine1%22%3A%20%22Shop%20219%22%2C%3CBR%20%2F%3E%20%20%20%20%22AddressLine2%22%3A%20%22528%20Persson%20Road%22%2C%3CBR%20%2F%3E%20%20%20%20%22PostalCode%22%3A%20%2290775%22%3CBR%20%2F%3E%20%20%7D%3CBR%20%2F%3E%7D%5D%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3ESince%20the%20result%20is%20a%20JSON%20string%2C%20all%20it's%20needed%20to%20turn%20it%20into%20an%20object%20is%20to%20deserialize%20it.%20In%20the%20sample%20on%20GitHub%20I%20decided%20to%20keep%20the%20JSON%20as%20JSON%20as%20I%20want%20to%20take%20advantage%20of%20its%20flexibility%20as%20much%20as%20possible%2C%20but%20that's%20of%20course%20up%20to%20you.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EUsing%20Dapper%20you%20literally%20need%20just%20two%20lines%20of%20code%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Evar%20qr%20%3D%20await%20conn.ExecuteScalarAsync%3CSTRING%3E(%3CBR%20%2F%3E%20%20sql%3A%20procedure%2C%20%3CBR%20%2F%3E%20%20param%3A%20parameters%2C%20%3CBR%20%2F%3E%20%20commandType%3A%20CommandType.StoredProcedure%3CBR%20%2F%3E)%3B%3CBR%20%2F%3Eif%20(qr%20!%3D%20null)%3CBR%20%2F%3E%20%20result%20%3D%20JsonDocument.Parse(qr)%3B%3C%2FSTRING%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20you're%20done.%20In%20the%20result%20variable%20you'll%20have%20the%20JSON%20ready%20to%20be%20used.%20If%20you%20are%20creating%20a%20REST%20API%2C%20you%20can%20just%20return%20it%20to%20the%20client%20and%E2%80%A6well%2C%20nothing%20else%3A%20done!%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EAs%20usual%20the%20full%20source%20code%20is%20available%20on%20GitHub%20for%20you%20to%20play%20with%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FAzure-Samples%2Fazure-sql-db-dotnet-rest-api%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure-Samples%2Fazure-sql-db-dotnet-rest-api%3C%2FA%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ETo%20make%20the%20sample%20easily%20reusable%2C%20I%20wrapped%20the%20logic%20that%20execute%20the%20request%20to%20the%20database%20into%20the%20%3CEM%3EControllerQuery%3C%2FEM%3E%20base%20class%2C%20so%20it%20will%20be%20very%20easy%20to%20derive%20from%20there%20to%20create%20more%20specialized%20controller%20classes.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22class-diagram.jfif%22%20style%3D%22width%3A%20880px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172843iF554A34331ED86F7%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22class-diagram.jfif%22%20alt%3D%22class-diagram.jfif%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EOn%20the%20database%20side%2C%20I%20created%20a%20convention%20for%20stored%20procedure%20naming%20so%20that%20code%20can%20automatically%20call%20the%20correct%20procedure%20for%20the%20correct%20verb%20and%20controller%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3Eweb.%26lt%3Bverb%26gt%3B_%26lt%3Bcontroller%26gt%3B%20%5B%40id%5D%2C%20%5B%40payload%5D%3C%2FPRE%3E%0A%3CP%3E%3CBR%20%2F%3EFor%20example%2C%20the%20%3CEM%3EGet%3C%2FEM%3E%20method%20for%20the%20%3CEM%3ECustomersController%3C%2FEM%3E%20will%20call%20the%20stored%20procedure%20%3CEM%3Eweb.get_customers%3C%2FEM%3E%20behind%20the%20scenes%2C%20and%20is%20something%20like%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3E%5BHttpGet%5D%3CBR%20%2F%3Epublic%20async%20Task%3CJSONELEMENT%3E%20Get()%3CBR%20%2F%3E%7B%3CBR%20%2F%3E%20%20return%20await%20this.Query(%22get%22%2C%20this.GetType())%3B%3CBR%20%2F%3E%7D%3C%2FJSONELEMENT%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CEM%3Eid%3C%2FEM%3E%20and%20%3CEM%3Epayload%3C%2FEM%3E%20are%20two%20optional%20parameters%20that%20can%20be%20used%20to%20target%20a%20specific%20row%20via%20its%20own%20id%20and%20the%20payload%20is%20the%20JSON%20document%20that%20will%20be%20passed%20to%20the%20database%20for%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fyorek%2Fazure-sql-db-samples%2Ftree%2Fmaster%2Fsamples%2F03-json-support%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Efurther%20manipulation%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EHonestly%2C%20that's%20amazing.%20Clean%20code%2C%20very%20lean%2C%20maintainable%2C%20flexible%20and%20fast.%20Fast%3F%20Yeah%2C%20what%20about%20performances%3F%20There%20will%20be%20a%20dedicated%20post%20on%20this%20subject%2C%20but%20let's%20just%20say%20that%20with%20this%20configuration%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3EAzure%20SQL%20Hyperscale%20HS_Gen5_2%3C%2FLI%3E%0A%3CLI%3EWeb%20App%20Linux%20DotNet%20Core%203.1%2C%20P1V1%2C%201%20node%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EI%20was%20able%20to%20execute%20%3CSTRONG%3E1100%3C%2FSTRONG%3E%20Requests%20Per%20Seconds%20with%20a%20median%20response%20time%20of%2020msec.%20If%20you%20can%20accept%20a%20bit%20higher%20latency%2C%20you%20can%20also%20reach%20%3CSTRONG%3E1500%3C%2FSTRONG%3E%20RPS%20but%20the%20median%20response%20time%20becomes%2040msec%20and%20the%2095%20percentile%20is%20set%20at%2095msec.%20Database%20usage%20never%20goes%20above%2020%25%20in%20such%20cases%E2%80%A6and%20in%20fact%20the%20bottleneck%20is%20the%20Web%20App%20(better%2C%20the%20Web%20App%20Plan)%20and%20more%20specifically%20the%20CPU.%20Time%20to%20scale%20up%20or%20out%20the%20Web%20App%20Plan.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20scaling%20up%20and%20out%20a%20bit%2C%20I%20was%20able%20to%20reach%20almost%20%3CSTRONG%3E10.000%3C%2FSTRONG%3E%20request%20per%20second%20with%20just%20an%20HS_Gen5_4.%20Quite%20impressive.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22requests-per-seconds.png%22%20style%3D%22width%3A%20880px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F172844iE335166AF88C9DF1%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22requests-per-seconds.png%22%20alt%3D%22requests-per-seconds.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EIf%20you're%20wondering%20what%20solution%20I%20used%20to%20create%20load%20test%2C%20you%20can%20find%20all%20details%2C%20along%20with%20deployable%20code%2C%20here%3A%20%3CA%20href%3D%22https%3A%2F%2Fdev.to%2Fyorek%2Frunning-locust-on-azure-2k40%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3ERunning%20Locust%20On%20Azure%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3ENow%2C%20the%20solution%20could%20seem%20complete%20already%E2%80%A6but%20it%20really%20is%3F%20Not%20really%2C%20when%20working%20with%20the%20cloud%2C%20we%20always%20need%20to%20take%20think%20about%20resiliency.%20Luckily%20for%20us%2C%20the%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2Fdotnet%2FSqlClient%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3E.NET%20SqlClient%3C%2FA%3E%20driver%20already%20does%20quite%20a%20good%20job%20for%20us%20as%20it%20automatically%20handles%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-connectivity-issues%23net-sqlconnection-parameters-for-connection-retry%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EIdle%20Connection%20Resiliency%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EFor%20now%2C%20it's%20more%20than%20enough.%20I'll%20discuss%20Connection%20Resiliency%20in%20detail%20in%20a%20future%20post%2C%20as%20it%20is%20a%20topic%20that%20deserves%20an%20article%20on%20its%20own.%3C%2FP%3E%0A%3CP%3E%3CBR%20%2F%3EIn%20the%20meantime%2C%20have%20fun%2C%20with%20Azure%20SQL%2C%26nbsp%3B.NET%2C%20Python%20or%20any%20language%20you%20want%20to%20use.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1189675%22%20slang%3D%22en-US%22%3E%3CP%3ECreate%20and%20deploy%20scalable%20REST%20API%20in%20just%20a%20few%20lines%20of%26nbsp%3Bcode%2C%20thanks%20to%20Azure%20SQL%20and%20it's%20native%20support%20to%20JSON.%20And%20use%20Locust%20to%20load%20test%20the%20REST%20API.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1189675%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3Edevelopment%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279596%22%20slang%3D%22en-US%22%3ERe%3A%2010K%20Request%20per%20Second%3A%20REST%20API%20with%20Azure%20SQL%2C%20Dapper%20and%26nbsp%3BJSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279596%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Davide%2C%3C%2FP%3E%3CP%3Egreat%20article%2C%20thanks.%20However%2C%20I%20am%20not%20sure%20if%26nbsp%3BExecuteScalarAsync%20is%20the%20best%20method%20for%20retrieving%20data%20in%20that%20case.%20Dapper%20can%20only%20retrieve%202033%20characters%20with%20a%20single%20query.%20In%20our%20case%2C%20if%20the%20dataset%20is%20large%20enough%2C%20JSON%20will%20be%20truncated%20which%20will%20lead%20to%20an%20exception%20when%20trying%20to%20parse%20it.%20My%20proposition%20is%20to%20use%26nbsp%3BQueryAsync%3CSTRING%3E%20or%20something%20else%20which%20returns%20collection%20which%20one%20we%20can%20later%20join%20into%20one%20string%20before%20trying%20to%20call%26nbsp%3BJsonDocument.Parse().%20Let%20me%20know%20what%20you%20think.%26nbsp%3B%3C%2FSTRING%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1279633%22%20slang%3D%22en-US%22%3ERe%3A%2010K%20Request%20per%20Second%3A%20REST%20API%20with%20Azure%20SQL%2C%20Dapper%20and%26nbsp%3BJSON%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1279633%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F606770%22%20target%3D%22_blank%22%3E%40marekott%3C%2FA%3E%20%2C%3C%2FP%3E%0A%3CP%3Eyou're%20absolutely%20right%2C%20if%20you%20have%20to%20return%20a%20big%20JSON%2C%20you%20need%20to%20manually%20concatenate%20the%20results%20as%20they%20will%20be%20split%20in%20more%20than%20one%20row.%20I'll%20make%20sure%20to%20call%20this%20out%20in%20the%20GitHub%20repo%2C%20so%20everyone%20will%20be%20aware.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere's%20the%20doc%20page%20where%20this%20behavior%2C%20along%20with%20the%20technique%20to%20deal%20with%20this%2C%20is%20explained%3A%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fjson%2Fuse-for-json-output-in-sql-server-and-in-client-apps-sql-server%3Fview%3Dsql-server-ver15%23use-for-json-output-in-a-c-client-app%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fjson%2Fuse-for-json-output-in-sql-server-and-in-client-apps-sql-server%3Fview%3Dsql-server-ver15%23use-for-json-output-in-a-c-client-app%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnother%20option%20is%20to%20use%20a%20Dapper%20extension%20that%20my%20colleague%20Jovan%20created%3A%20%3CA%20href%3D%22https%3A%2F%2Fgithub.com%2FJocaPC%2FDapper.Stream%23creating-json-rest-api%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgithub.com%2FJocaPC%2FDapper.Stream%23creating-json-rest-api%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ELast%20option%20(or%20maybe%20%22trick%22)%20is%20to%20put%20the%20JSON%20into%20a%20variable%20right%20on%20the%20server%20side%2C%20so%20you%20will%20not%20have%20to%20deal%20with%20JSON%20concatenation%3A%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%3EDECLARE%20%40j%20NVARCHAR(MAX)%20%3D%20(SELECT%20*%20FROM%20%26lt%3BTable%26gt%3B%20FOR%20JSON%20AUTO)%0ASELECT%20%40j%20AS%20JSON%3C%2FPRE%3E%3C%2FLINGO-BODY%3E
Microsoft

In the previous article I described how easy is to create a REST API with Python and Azure SQL. But what about .NET? Would that be as easy as well? Absolutely yes: the native support to JSON offered by Azure SQL will also help a lot again. In addition to that, the Micro-ORM Dapper will make the interaction with database a breeze, and the new System.Text.Json will take care of manipulating that developer-friendly format. Sounds too good and easy to be true? Well, let's prove it!


I started creating a WebAPI project using .NET CLI (I'm using .NET Core 3):

 

dotnet new webapi

 

and then I added the following packages 

  • Dapper
  • Microsoft.Data.SqlClient
  • System.Text.Json

via

 

dotnet add package

 

to add the libraries mentioned before. Dapper is an amazing MicroORM, that removes all the plumbing code needed to connect and query a database, so that you can focus more one the really important code. It is battle tested, as it is used by StackOverflow itself, and very recommended. If you want to have a kickstart, there is a 10 post tutorial here: Dapper.NET @ Medium, along with full source code for all described features.
 
From a database perspective I'm using almost the same code I used to build the same sample with Python, leveraging as much as possible the native support to JSON the Azure SQL provides:

 

CREATE OR ALTER PROCEDURE web.get_customer
@Id INT
AS
SET NOCOUNT ON;
SELECT
[CustomerID],
[CustomerName],
[PhoneNumber],
[FaxNumber],
[WebsiteURL],
[DeliveryAddressLine1] AS 'Delivery.AddressLine1',
[DeliveryAddressLine2] AS 'Delivery.AddressLine2',
[DeliveryPostalCode] AS 'Delivery.PostalCode'
FROM
[Sales].[Customers]
WHERE
[CustomerID] = @Id
FOR JSON PATH
GO


Thanks to the FOR JSON PATH clause, the result will be like the following:

 

[{
"CustomerID": 123,
"CustomerName": "Tailspin Toys (Roe Park, NY)",
"PhoneNumber": "(212) 555–0100",
"FaxNumber": "(212) 555–0101",
"WebsiteURL": "http://www.tailspintoys.com\/RoePark",
"Delivery": {
"AddressLine1": "Shop 219",
"AddressLine2": "528 Persson Road",
"PostalCode": "90775"
}
}]


Since the result is a JSON string, all it's needed to turn it into an object is to deserialize it. In the sample on GitHub I decided to keep the JSON as JSON as I want to take advantage of its flexibility as much as possible, but that's of course up to you.


Using Dapper you literally need just two lines of code:

 

var qr = await conn.ExecuteScalarAsync<string>(
sql: procedure,
param: parameters,
commandType: CommandType.StoredProcedure
);
if (qr != null)
result = JsonDocument.Parse(qr);

 

And you're done. In the result variable you'll have the JSON ready to be used. If you are creating a REST API, you can just return it to the client and…well, nothing else: done!


As usual the full source code is available on GitHub for you to play with:

 

Azure-Samples/azure-sql-db-dotnet-rest-api

 

To make the sample easily reusable, I wrapped the logic that execute the request to the database into the ControllerQuery base class, so it will be very easy to derive from there to create more specialized controller classes.

 

class-diagram.jfif

On the database side, I created a convention for stored procedure naming so that code can automatically call the correct procedure for the correct verb and controller:

 

web.<verb>_<controller> [@id], [@payload]


For example, the Get method for the CustomersController will call the stored procedure web.get_customers behind the scenes, and is something like:

 

[HttpGet]
public async Task<JsonElement> Get()
{
return await this.Query("get", this.GetType());
}

 

id and payload are two optional parameters that can be used to target a specific row via its own id and the payload is the JSON document that will be passed to the database for further manipulation.


Honestly, that's amazing. Clean code, very lean, maintainable, flexible and fast. Fast? Yeah, what about performances? There will be a dedicated post on this subject, but let's just say that with this configuration:

  • Azure SQL Hyperscale HS_Gen5_2
  • Web App Linux DotNet Core 3.1, P1V1, 1 node

I was able to execute 1100 Requests Per Seconds with a median response time of 20msec. If you can accept a bit higher latency, you can also reach 1500 RPS but the median response time becomes 40msec and the 95 percentile is set at 95msec. Database usage never goes above 20% in such cases…and in fact the bottleneck is the Web App (better, the Web App Plan) and more specifically the CPU. Time to scale up or out the Web App Plan.

 

By scaling up and out a bit, I was able to reach almost 10.000 request per second with just an HS_Gen5_4. Quite impressive.

 

requests-per-seconds.png


If you're wondering what solution I used to create load test, you can find all details, along with deployable code, here: Running Locust On Azure.


Now, the solution could seem complete already…but it really is? Not really, when working with the cloud, we always need to take think about resiliency. Luckily for us, the .NET SqlClient driver already does quite a good job for us as it automatically handles Idle Connection Resiliency.


For now, it's more than enough. I'll discuss Connection Resiliency in detail in a future post, as it is a topic that deserves an article on its own.


In the meantime, have fun, with Azure SQL, .NET, Python or any language you want to use.

2 Comments
Frequent Visitor

Hi Davide,

great article, thanks. However, I am not sure if ExecuteScalarAsync is the best method for retrieving data in that case. Dapper can only retrieve 2033 characters with a single query. In our case, if the dataset is large enough, JSON will be truncated which will lead to an exception when trying to parse it. My proposition is to use QueryAsync<string> or something else which returns collection which one we can later join into one string before trying to call JsonDocument.Parse(). Let me know what you think. 

Microsoft

Hi @marekott ,

you're absolutely right, if you have to return a big JSON, you need to manually concatenate the results as they will be split in more than one row. I'll make sure to call this out in the GitHub repo, so everyone will be aware.

 

Here's the doc page where this behavior, along with the technique to deal with this, is explained: https://docs.microsoft.com/en-us/sql/relational-databases/json/use-for-json-output-in-sql-server-and....

 

Another option is to use a Dapper extension that my colleague Jovan created: https://github.com/JocaPC/Dapper.Stream#creating-json-rest-api.

 

Last option (or maybe "trick") is to put the JSON into a variable right on the server side, so you will not have to deal with JSON concatenation:

 

DECLARE @j NVARCHAR(MAX) = (SELECT * FROM <Table> FOR JSON AUTO)
SELECT @j AS JSON