Question Cannot modify function GetSelectStatement to generate select statement from string json?

ahmedsalah

Active member
Joined
Sep 26, 2018
Messages
32
Programming Experience
3-5
I work on csharp and i need to generate select statement based on inner join select statement but i cannot modify it
I need to get fields and keys and table to generate inner join select statement as below :
select FooterTable.ItemCode,FooterTable.Quantity,FooterTable.UniPrice from

MasterTable inner join FooterTable on MasterTable.Serial=FooterTable.Serial,MasterTable.BranchCode=FooterTable.BranchCode,MasterTable.Year=FooterTable.Year

where MasterTable.Serial=10 AND MasterTable.Year=2019 AND MasterTable.BranchCode=1
What i try to get result above by csharp as following :

public string GetSelectStatement(string JsonDataForSelect)
{
var root = (JObject)JsonConvert.DeserializeObject(JsonDataForSelect);
var query = "";
var items = root.SelectToken("Details").Children().OfType<JProperty>().ToDictionary(p => p.Name, p => p.Value);
foreach (var item in items)
{
if (item.Key == "table")
{
var tableName = item.Value;
query = string.Format("select from table {0} inner join table{1} where", tableName);
}
else if (item.Key == "keys")
{
var key = item.Value.SelectToken("").OfType<JProperty>().ToDictionary(p => p.Name, p => p.Value);
var count = 0;
foreach (var id in key)
{
count++;
if (count == key.Count())
{
query += string.Format("{0} = {1}", id.Key, id.Value);
}
else
{
query += string.Format("{0} = {1} and ", id.Key, id.Value);
}
}

}
}
return query;
}
json string i generate sql from it
{
"Details":{
"table":[
"MasterTable",
"FooterTable"
],
"fields":{
"ItemCode":"string",
"Quantity":"int",
"Price":"decimal"

},
"keys":{
"BranchCode":1,
"Year":2019,
"Serial":2
}
}
}
 
Last edited:
Over in Dream In Code, he used code tags for a similar post.
 
It looked like you were already getting help over at CodeProject. Why should we help here and just waste our time?
 
There are likely a number of factors why people are refusing to help you.
  • You are to brief with your explanations, and lack to ask a DIRECT question ie. - Why do I get formatException error on line 15 etc
  • You do not put in enough effort into explaining your problem with your current code, or you are not elaborate enough with regards what you are trying to do
  • You do not explain why your code does not work for you
  • You do not explain if you are receiving any errors, if you are, you don't list them nor state if you receive any, yet at all
  • You post on multiple forums, and speaking for myself here, people like me read those other forums every few hours. Posting the same issue on all forums sets the wrong tone. It also makes you look like a non-contributing help vampire
  • You do not post your code in code tags, while you can manage to do it correctly on most other forums. You have failed do correct your habits on this website despite me telling you previously to use code tags.
With a combination of the above, you only have yourself to blame. Give your topic some proper structure, and spend time writing it out properly and in English. While we do know English is not everyone's first language, you have no excuse for a poorly worded topic when you can avail or many online language translators today. Now that you've read all that, I hope you remember for the next time you post a question.


Now, a few questions about what you're doing. Lets use this piece of xml. Do you need the WORDS like; Year ie. "Year":2019, or do you only need the VALUES ie. 2019 beside your placeholder text?

What does your current code do?
Does your current code throw any errors?
If you get errors, what are they?
What is your code doing that you do not want it to do?
If you have problems with the current code, post it in code tags first, before quoting line numbers where your issue may reside.

I would consider rewriting what you have above anyway. Using the JavaScriptSerializer - If you want the placeholder ie. Year, and the value, you can use a Dictionary by specifying <Dictionary<string, string>> between Deserialize and your (json); object. Just as I've done below :
C#:
            var serializer = new JavaScriptSerializer(); /* Create a new JavaScriptSerializer */
            var receivedjson = serializer.Deserialize<Dictionary<string, string>>(json); /* Return a dictionary */
You can also return an array of values :
C#:
            string[] result = receivedjson.Select(func => func.Value.ToString()).ToArray(); /* Or Convert to an array and use only the values */
Both of these methods provide you with smoother ways to interact with the code values as well as the placeholders if you consider using a dictionary. Then you can build onto them with a little Linq too.


C#:
           public static readonly string json = @"{'Name': 'Avatar','ReleaseDate': '2009-8-7','Genres': 'Action'}"; /* This is my json file */
If I run this in a method :
C#:
            var serializer = new JavaScriptSerializer(); /* Create a new JavaScriptSerializer */
            var receivedjson = serializer.Deserialize<Dictionary<string, string>>(json); /* Return a dictionary */
            string[] result = receivedjson.Select(func => func.Value.ToString()).ToArray(); /* Or Convert to an array and use only the values */
            int i = 0;
            foreach (KeyValuePair<string, string> kvp in receivedjson)
            {
                i++;
                Console.WriteLine($"Key {i} is : {kvp.Key} while value {i} is : {kvp.Value}");
            }
The output from this is :
C#:
Key 1 is : Name while value 1 is : Avatar
Key 2 is : ReleaseDate while value 2 is : 2009-8-7
Key 3 is : Genres while value 3 is : Action
 
Notice that all of the bullet points that Sheepings had above are covered by How to Ask Questions the Smart Way. I gave you a link to that same page when I responded in the other forum when you were asking about the rude practice of cross-posting. Take the 10-15 minutes to read the recommendations on that page about how to put together a good question.
 
With what I posted on post 6, you should also take on board what you were told on codeproject by Richard Deeming :

Screenshot_21.jpg


That's why I would ditch your approach above to the nearest bin and learn to do things properly.

sorry I recognize that and i will fix it
With a half ass effortless reply like that, I can see now what Skydiver was talking about when he called you out as a taker, and somebody who does not contribute back to the community forums you aimlessly exploit for answers. Your lack of responses across all forums really shows what type of user you are, and I say that after reviewing your posts across those other forums.

I will conclude by assuming that your attempted apology and disregard for post 6, and the replies you received on code project and the five various other boards you posted too, are likely due to the fact that you are more concentrated with that bullshit answer you received on SO? Mind you; as a side note. Its wrong...

I dread to read your horror story on your next question on how to secure queries using parameters after your database likely suffers from SQL injection. Since I did read in your other topic that you're forming json to SQL directly. Any good developer would know to use parameters and not concatenated query strings which leave you vulnerable to injection attack.

By not carefully scrutinising their answer for vulnerabilities and inadequacy regarding the correct approach for your needs, (especially from a rag amateur websites like Stackoverflow.) You can deal with the consequences later when it'll be to late. Small piece of advice as a developer; question everything and don't take advice on a whim.

Good luck

More Crossposts here : https://www.tek-tips.com/viewthread.cfm?qid and here How modify function GetSelectStatement to generate sql select statement ? and there are others too....
 
Last edited:
Back
Top Bottom