-
September 21st, 2019, 04:55 PM
#1
How modify function GetSelectStatement to generate sql select statement ?
I need to modify GetSelectStatement to generate select statment below :
Code:
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
How to modify csharp function GetSelectStatement to generate inner join select statement as :
Code:
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;
}
my json file as
Code:
{
"Details":{
"table":[
"MasterTable",
"FooterTable"
],
"fields":{
"ItemCode":"string",
"Quantity":"int",
"Price":"decimal"
},
"keys":{
"BranchCode":1,
"Year":2019,
"Serial":2
}
}
}
All fields exist on json from FooterTable only
-
September 21st, 2019, 07:55 PM
#2
Re: How modify function GetSelectStatement to generate sql select statement ?
Before I offer an opinion, can you help me figure out what you are trying to do with the query?
I ask because you are joining the master table to the footer table by 3 columns and they are the same columns in the where clause to filter the master table.
If this is the case, then why join to the master table (i.e., just filter the footer table directly without a join).
-
September 21st, 2019, 10:24 PM
#3
Re: How modify function GetSelectStatement to generate sql select statement ?
thank you for reply but suppose some of fields or one field exist on two tables it will not work and it will give ambigous column name can you please help me generating inner join select statement by modify function csharp getselectstatment
-
September 22nd, 2019, 12:15 AM
#4
Re: How modify function GetSelectStatement to generate sql select statement ?
Originally Posted by ahmed.salah
thank you for reply but suppose some of fields or one field exist on two tables it will not work and it will give ambigous column name can you please help me generating inner join select statement by modify function csharp getselectstatment
So your example was bad?
At any rate:
Will master table always be the first table and be the table the where clause is applied to?
Will the footer table always be the table from which the colums are selected from?
What defines the join clause? It doesn't make sense for it to be the same as the where clause columns.
You may wonder why I'm focusing in on the sql - it's because extracting data from the json is trivial, but won't help you if the json doesn't contain enough info to form a correct sql query.
I'm trying to get to the point where the json is flexible enough to form a generic query. If there are restrictions to this (and it doesn't have to be as generic), please let me know.
Last edited by Arjay; September 22nd, 2019 at 12:19 AM.
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|