How to modify function GetSelectStatement to generate inner join select statement by using csharp?
How to modify function GetSelectStatement to generate inner join select statement by using csharp?
(OP)
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 :
What i try to get result above by csharp as following :
return query;
}
I need to modify code above to generate select statement as above
to simple syntax i need to make
select FooterTable.fields from MasterTable inner join FooterTable on MasterTable.key1=FooterTable.key1 and MasterTable.key2=FooterTable.key2 and MasterTable.key3=FooterTable.key3 where key1 =value and key2=value and key3=value
How to do that please by modify code above
I need to get fields and keys and table to generate inner join select statement as below :
CODE --> sqlserver
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
CODE --> csharp
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); } } } }
}
I need to modify code above to generate select statement as above
to simple syntax i need to make
select FooterTable.fields from MasterTable inner join FooterTable on MasterTable.key1=FooterTable.key1 and MasterTable.key2=FooterTable.key2 and MasterTable.key3=FooterTable.key3 where key1 =value and key2=value and key3=value
How to do that please by modify code above
RE: How to modify function GetSelectStatement to generate inner join select statement by using csharp?
e.g. query = string.Format("select from table {0} inner join table{1} where", tableName);
one part of your issue might be that you seem to be using the where clause for the join columns,
When sql sees the phrase inner join/join it expects an "On" clause.
If you are planning on adding the join columns via the where clause the syntax would be
query = string.Format("select from {0},{1} where", tableName);
also query = string.Format("select from table {0} inner join table{1} where", tableName);
why do you have the word table in there?
plus you only have on tablename in the format statement, but your code is looking for 2 {0} and {1}
shouldn't it be more like
string.Format("select from {0} a inner join {1} b on a.{2} = b.{3} where", table1, table2, col1, col2);
also your original query has table {0} table{1}
table {0} has a space between the word table and the placeholder.
RE: How to modify function GetSelectStatement to generate inner join select statement by using csharp?