asp.net-core - 如何使用 csharp 修改函数 GetSelectStatement 以生成内部连接选择语句?
问题描述
问题
如何使用 csharp 修改函数 GetSelectStatement 以生成内部连接选择语句?
我有如下的json字符串数据
{
"Details":{
"table":[
"MasterTable",
"FooterTable"
],
"fields":{
"ItemCode":"string",
"Quantity":"int",
"Price":"decimal"
},
"keys":{
"BranchCode":1,
"Year":2019,
"Serial":2
}
}
}
如何修改 csharp 函数 GetSelectStatement 以生成内部连接选择语句:
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;
}
选择语句所需的结果是:
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
解决方案
这是一个简单的解决方法,如下所示:
public string GetSelectStatement(string JsonDataForSelect)
{
var root = (JObject)JsonConvert.DeserializeObject(JsonDataForSelect);
var query = "";
var query1 = "";
var query2 = "";
string[] tableName = new string[] { };
var items = root.SelectToken("Details").Children().OfType<JProperty>().ToDictionary(p => p.Name, p => p.Value);
foreach (var item in items)
{
if (item.Key == "table")
{
tableName = JsonConvert.DeserializeObject<string[]>(item.Value.ToString());
}
else if (item.Key == "fields")
{
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())
{
query1 += string.Format("{0}.{1}", tableName[1], id.Key);
}
else
{
query1 += string.Format("{0}.{1},", tableName[1], id.Key);
}
query = string.Format("select {0} from {1} inner join {2} on {3} where ", query1, tableName[0], tableName[1], query2);
}
}
else if (item.Key == "keys")
{
var key = item.Value.SelectToken("").OfType<JProperty>().ToDictionary(p => p.Name, p => p.Value);
var count = 0;
if (query2 == "")
{
foreach (var id in key)
{
count++;
if (count == key.Count())
{
query2 += string.Format("{0}.{1}={2}.{3}", tableName[0], id.Key, tableName[1], id.Key);
}
else
{
query2 += string.Format("{0}.{1}={2}.{3},", tableName[0], id.Key, tableName[1], id.Key);
}
query = string.Format("select {0} from {1} inner join {2} on {3} where ", query1, tableName[0], tableName[1], query2);
}
}
count = 0;
foreach (var id in key)
{
count++;
if (count == key.Count())
{
query += string.Format("{0}.{1}={2} ", tableName[0], id.Key, id.Value);
}
else
{
query += string.Format("{0}.{1}={2} and ", tableName[0], id.Key, id.Value);
}
}
}
}
return query;
}
结果:
推荐阅读
- machine-learning - 如何使用强化学习进行产品推荐?
- android - 使用 API>24 在 Eclipse 上构建 Android Wear 应用程序
- python - 通过python动态更新表
- sql - SQL 在 Union 语句中使用 Order By
- ios - Switch case 语句 - 通用关联类型
- android - OAuth2 回调在某些设备上的浏览器内启动活动
- java - Sping .jms.listener 阻止另一个侦听器读取 JMS 消息
- sql - 想要在单个查询表单多个数据中选择所有提到的数据
- python - 在 IPython 中为 Windows 设置文件路径
- javascript - scalajs-bundler 包含在 jQuery 插件中