Convert Complex JSON Data To SQL Table | RAJESH GAMI

 Below Query to Convert Complex JSON data to SQL table,

Step 1

Create complex JSON & Convert complex JSON into SQL Table , for this we are using Openjson() for Converting JSON data into SQL,

declare @Jsonstring nvarchar(max)=
'{
"BankID":"00001",
"BankName":"Sbank7",
"BankType":"National",
"BankAccount":"021356458777",
"BankContacts":"0213258777",
"BankAccounts":{

                  "Account":
				  [
				  {"Acid":"1001","Name":"Sai pathrikar"},
				  {"Acid":"1002","Name":"Swaraj pathrikar"},
				  {"Acid":"1003","Name":"Sharayu pathrikar"},
				  {"Acid":"1004","Name":"Kartik Ingle"}
				  ]},
"BankTransaction":
[
{"Trid":"5001","TrAmount":1500,"TrTypr":"CR","Acid":"1001","TrDate":"2022-01-01"},
{"Trid":"5002","TrAmount":2000,"TrTypr":"DR","Acid":"1002","TrDate":"2022-01-01"},
{"Trid":"5003","TrAmount":2500,"TrTypr":"CR","Acid":"1003","TrDate":"2022-01-01"},
{"Trid":"5004","TrAmount":3000,"TrTypr":"DR","Acid":"1004","TrDate":"2022-01-01"}
]
}'

select Tbl_Bank.BankId,Tbl_Bank.BankName,Tbl_Bank.BankType,Tbl_Bank.BankAccount,
Tbl_Bank.BankContacts,Tbl_Account.Acid,Tbl_Account.Name,
Tbl_Tr.Trid ,Format(Tbl_Tr.TrAmount,'C','en-in') Tramount, Case when TrTypr='CR' then 'Credited' else 'Debited'
end TranType ,Format(Tbl_Tr.TrDate,'dd-MMM-yyyy') TrDate
from openJson(@Jsonstring)
with
(
BankID int , BankName varchar(max),BankType varchar(max),BankAccount varchar(max),
BankContacts varchar(max), BankAccounts nvarchar(max) as json,BankTransaction nvarchar(max) as json
)AS Tbl_Bank
Cross apply openjson (Tbl_Bank.BankAccounts)
with(
Account nvarchar(max) as json
) AS Tbl_BankAccounts cross apply openjson (Tbl_BankAccounts.Account)
with
(
Acid int , Name nvarchar(max)
) Tbl_Account
cross apply openjson(Tbl_Bank.BankTransaction)
with(
Trid int,TrAmount decimal(18,2),TrTypr varchar(max),Acid int,TrDate date
) Tbl_Tr where Tbl_Tr.Acid=Tbl_Account.Acid
SQL

By - No comments:

SQL Database Performance Tuning Using SQL Profiler | RAJESH GAMI

 Step 1

SSMS go to tools & click SQL profiler 

Step 2

Trace property, Add Trace Name="Database Query Tunning" & Use the template="Tuning"

Step 3

Send Request to databases from application, API or Execute DML query Or Procedure in Database for trace Query in profiler

Step 4

Now Stop trace & Save Trace file as "{FileName}.trc"

Step 5

SSMS - go to tools menu & select Database engine tuning advisor

Step 6

SQL server authentication & Login into Server for Database tune advisor

Step 7

Add Session Name="DbQueryTune" & Select Workload File. We already have saved from SQL profiler & go to Tuning Option, select Advanced options & define max. space for recommendation in (MB)

Step 8

Now select Database for workload analysis & select multiple Databases & Table for Tuning

Step 9

Click on Start analysis Button to start workload & Tune Db objects

Step 10

Tuning progress & generating report & checking the index, Statistics for tables

Step 11

Recommendation (in our case there is no recommendation from database tuning advisor because we already tune & Created required statistics for table & we also set AUTO_CREATE_STATISTICS ON & AUTO_UPDATE_STATISTICS ON at Database level)

Step 12

All types of report are generated. You can check all report Select Report from Dropdown & Also check tuning summary

Note: To perform all the above steps in Production Server you need to get permission from DBA 

    By - No comments:
    Labels:

    Main Difference Between ViewData Vs ViewBag Vs TempData In ASP.NET MVC | RAJESH GAMI

      In ASP.NET MVC there are three ways - ViewData, ViewBag, and TempData - to pass data from the controller to view the next request. Like WebForm, you can also use Session to persist data during a user session. Now, the question is when to use ViewData, VieBag, TempData, and Session. Each of them has their own importance. In this article, I am trying to explain the differences among these four.


    ViewData
    1. ViewData is derived from the ViewDataDictionary class and is basically a Dictionary object i.e., Keys and Values where Keys are String while Values will be objects.
    2. Data is stored as Object in ViewData.
    3. While retrieving the data it needs to be Type Cast to its original type as the data is stored as the object and it also requires NULL checks while retrieving.
    4. ViewData is used for passing value from Controller to View.
    5. ViewData is available only for Current Request. It will be destroyed on redirection.
    Example

    In the below example, a string value is set in the ViewData object in Controller and it is then displayed in View.

    Controller
    1. public class FirstController: Controller {  
    2.     // GET: First  
    3.     public ActionResult Index() {  
    4.         ViewData["Message"] = "Hello RAJESH!";  
    5.         return View();  
    6.     }  
    7. }  
     View
    1. <html>  
    2.   
    3. <head>  
    4.     <meta name="viewport" content="width=device-width" />  
    5.     <title>Index</title>  
    6. </head>  
    7.   
    8. <body>  
    9.     <div> @ViewData["Message"] </div>  
    10. </body>  
    11.   
    12. </html> 
    ViewBag
    1. ViewBag is a Wrapper built around ViewData.
    2. ViewBag is a dynamic property and it makes use of the C# 4.0 dynamic features.
    3. While retrieving, there is no need for Type Casting data.
    4. ViewBag is used for passing value from Controller to View.
    5. ViewBag is available only for Current Request. It will be destroyed on redirection.
    Example

    In the below example, a string value is set in the ViewBag object in Controller and it is then displayed in View.

    Controller
    1. public class FirstController: Controller {  
    2.     // GET: First  
    3.     public ActionResult Index() {  
    4.         ViewBag.Message = "Hello RAJESH!";  
    5.         return View();  
    6.     }  
    7. }  
     View
    1. <html>  
    2.   
    3. <head>  
    4.     <meta name="viewport" content="width=device-width" />  
    5.     <title>Index</title>  
    6. </head>  
    7.   
    8. <body>  
    9.     <div> @ViewBag.Message </div>  
    10. </body>  
    11.   
    12. </html>  
    TempData
    1. TempData is derived from the TempDataDictionary class and is basically a Dictionary object i.e. Keys and Values where Keys are String while Values will be objects.
    2. Data is stored as Object in TempData.
    3. While retrieving the data it needs to be Type Cast to its original type as the data is stored as objects and it also requires NULL checks while retrieving.
    4. TempData can be used for passing value from Controller to View and also from Controller to Controller.
    5. TempData is available for Current and Subsequent Requests. It will not be destroyed on redirection.
    Example

    In the below example, a string value is a set of the TempData objects in Controller and they have redirected to another Controller and finally, it is displayed in View.

    First Controller
    1. public class FirstController: Controller {  
    2.         // GET: First  
    3.         public ActionResult Index() {  
    4.             TempData["Message"] = "Hello RAJESH!";  
    5.             return new RedirectResult(@ "~\Second\");  
    6.             }  
    7.         }  
     Second Controller
    1. <html>  
    2.   
    3. <head>  
    4.     <meta name="viewport" content="width=device-width" />  
    5.     <title>Index</title>  
    6. </head>  
    7.   
    8. <body>  
    9.     <div> @TempData["Message"]; </div>  
    10. </body>  
    11.   
    12. </html>  
    Session
    1. In ASP.NET MVC, Session is a property of Controller class whose type is HttpSessionStateBase.

      public HttpSessionStateBase Session { get; }

    2. The Session is also used to pass data within the ASP.NET MVC application and Unlike TempData, it persists for its expiration time (by default session expiration time is 20 minutes but it can be increased).

    3. The Session is valid for all requests, not for a single redirect.

    4.  It also requires typecasting for getting data and checks for null values to avoid error.
     
    By - No comments:

    Call Any Web API & Web Service From SQL Server | RAJESH GAMI

     Enable configuration in SQL

    sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
    SQL

    Sample API link

    -- Sample API Link
http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile
    BASIC

    Sample JSON Request

    @Mobile varchar(max),
@Flag varchar(max)

{
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '
    SQL

    Content Type

    application/json
    C#

    Create Store Procedure for Calling Web API.

    --//========================
    -- if you learn more please visit my blog

	-- https://saipathrikar.blogspot.com/
--//========================
--================ execute this 1st for 1st time use only

Create Proc Proc_CallApiFromSQL
(
@Mobile varchar(max),
@Flag varchar(max)
)
as
Declare @Object as Int;
Declare @ResponseText as Varchar(8000);
 declare @json table (Json_Table nvarchar(max))
 declare @body varchar(max)
 declare @Apilink varchar(max)
 set @Apilink='http://sbankapi7.somee.com:80/api/Member/GetDataFromServerByMobile';
 set @body='

 {
  "CID": 0,
  "CName": "",
  "AC_Balance": "",
  "AC_No": "",
  "AC_ID": "",
  "CDOB": {},
  "CEmail": "",
  "ReciverEmail": "",
  "CMObile": "'+@Mobile+'",
  "CGender": "",
  "CPan": "",
  "CAdhaar": "",
  "CNationality": "",
  "CAddress": "",
  "City": "",
  "State": "",
  "Country": "",
  "PinCode": "",
  "Cisdelete": 0,
  "CreatedBy": 0,
  "CreatedDate": {},
  "ModifiedBy": 0,
  "ModifiedDate": {},
  "UID": 0,
  "CustImgPath": "",
  "CustAdaarPath": "",
  "CustPanPath": "",
  "Flag": "'+@Flag+'",
  "OpMsg": "",
  "Pass": ""
} '

Exec sp_OACreate 'MSXML2.XMLHTTP', @Object OUT;
Exec sp_OAMethod @Object, 'open', NULL, 'post',@Apilink,'false'
EXEC sp_OAMethod @Object, 'setRequestHeader', null, 'Content-Type', 'application/json'
Exec sp_OAMethod @Object, 'send', null, @body
Exec sp_OAMethod @Object, 'responseText', @ResponseText OUTPUT

  INSERT into @json (Json_Table) exec sp_OAGetProperty @Object, 'responseText'
-- select the JSON string
--select * from @json
-- Parse the JSON string
SELECT * FROM OPENJSON((select * from @json))
WITH (
CID bigint,CName varchar(max),AC_Balance varchar(max),AC_No varchar(max),AC_ID bigint,CDOB datetime,
CEmail varchar(max),ReciverEmail varchar(max),CMObile varchar(max),CGender varchar(max),CPan varchar(max),
CAdhaar varchar(max),CNationality varchar(max),CAddress varchar(max),City varchar(max),State varchar(max),
Country  varchar(max),PinCode varchar(max),Cisdelete bit,CreatedBy varchar(max),CreatedDate datetime,ModifiedBy varchar(max),
ModifiedDate datetime,UID bigint,CustImgPath varchar(max),CustAdaarPath varchar(max),CustPanPath varchar(max),
Flag varchar(max),OpMsg varchar(max),Pass varchar(max)
)
return
    SQL

    Execute Stored Procedure 

    EXEC  Proc_CallApiFromSQL '8541254874','SE'
    SQL

    Output

    CIDCNameAC_BalanceAC_NoAC_IDCDOBCEmailReciverEmailCMObile
    40030Bank Charges? 16,47,29,357.0017591208887991401415-May-1992bankcharges@gmail.combankcharges@gmail.com8541254874
    By - No comments:
    Subscribe to: Posts (Atom)

    RAJESH GAMI - Blog

    Digital Signature Pad in Angular | RAJESH GAMI

      What is Signature Pad? Signature Pad could be a JavaScript library for drawing fancy signatures. It supports HTML5 canvas and uses variabl...