DataSet2JSON 1.0.3

This will convert the DataSet to JSON output as the name suggests.

Let's begin. Its just 1 steps process after adding the reference.

Create object for formatter

Code side changes

DataSet2JSON.Formatter convertToJSON = new DataSet2JSON.Formatter();
convertToJSON.FormatDataSet(ds);  // Your Dataset

This will return the output as JObject

Database side changes

First line of the select query should always be a config file.

select '1:Books';   // This is our configuration which will be explained
select id,booktitle from tbl_books;

Output

{
    "Books":[{
        "id":1,
        "booktitle":"Some text"
    },{
        "id":2,
        "booktitle":"Some text2"
    }]
}

By this you can understand that the config is converted to JSON. Now let us see how to change "id" of the Books to "BookID".

select '1:Books';
select id as BookID,booktitle as Title from tbl_books;

Output

{
    "Books":[{
        "BookID":1,
        "Title":"Some text"
    },{
        "BookID":2,
        "Title":"Some text2"
    }]
}

Now lets get only single book as output. The above Books is a list now I need a single object.

select '1:Books:single';

Output

{
    "Books":{
        "BookID":1,
        "Title":"Some text"
    }
}

If there is no book, then we might want to show a different message, and when book is found we might want to give the output of total books found with that matching title

IF EXISTS(SELECT id from tbl_books where booktitle like '%Hi%') THEN
	select '1:Status,2::singlevar';
	select '1' ResponseCode,'Success' ResponseDescription;
	select count(id) as TotalBooks from tbl_books where booktitle like '%Hi%'; -- Query B
ELSE
	select '1:Status';
	select '0' ResponseCode,'No Book Found' ResponseDescription;
END IF;

Output

--- If book is found with title
{
    "Status":{
        "ResponseCode":1,
        "ResponseDescription":"Success"
    },
	"TotalBooks":10 --- Here you can see the single value returned from Query B is a single object
}

--- If book is not found
{
    "Status":{
        "ResponseCode":0,
        "ResponseDescription":"No Book Found"
    }
}	

Let's consider you have a page with "Genre's" and you should the display the books under each Genre

Let us see how we can do this, to achieve the output i might need JSON in a Parent and child levels.

-- Scifi
----- Scifi book 1
----- Scifi book 2
-- Thriller
----- Thriller book 1
----- Thriller book 2

We should create a relation between the two tables here, tbl_genres and tbl_books. Lets assume that genreid is the column in tbl_books, id is the column in tbl_genres

select '1:Genres!1^id:2^genreid~1^Books';  --- we are using a seperator '!' here

--- If we convert the above to sql this is how it will be
--- tbl_genres.id = tbl_boards.genreid

select id,GenreName from tbl_genres;
select genreid,id as BookID,booktitle as Title from tbl_books;

Output

{
    "Genres":[{
				"GenreName":"Scifi",
				"Books":[{
					"BookID":3,
					"Title":"Scifi book 1"
				},{
					"BookID":4,
					"Title":"Scifi book 2"
				}]
			},
			{
				"GenreName":"Thriller",
				"Books":[{
					"BookID":5,
					"Title":"Thriller book 1"
				},{
					"BookID":6,
					"Title":"Thriller book 2"
				}]
			}]
}

There are some other feature which you can figure out what the output will be.

select '1:Genres,4:UserDetails!1^id:2^genreid~1^Books|2^authorid:3^id~2^Author:single'; 

Multiple relations are seperated by "|" in the config. To achieve this output. The relations are from right to left means "Leaf condition"|"Last leaf condition"

Use 😒ingle and see output and see without 😒ingle what is the difference.

Try 😒inglevar , this is not fully functionaly as of now.

A --- B

---B1 ---------C1