none
How modify function GetSelectStatement to generate sql select statement ? RRS feed

  • Question

  • 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;
            }

    { 
               "Details":{ 
                  "table":[ 
                     "MasterTable",
                     "FooterTable"
                  ],
                  "fields":{ 
                     "ItemCode":"string",
                     "Quantity":"int",
                     "Price":"decimal"
    
                  },
                  "keys":{ 
                     "BranchCode":1,
                     "Year":2019,
                     "Serial":2
                  }
               }
        }



    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







    • Edited by engahmedbarbary Saturday, September 21, 2019 9:31 PM more details explain
    Saturday, September 21, 2019 6:57 PM

Answers

  • Hi 

    Thanks for the feedback.

    It seems that you replying this thread. Your current problem is that you need to achieve correct sql statement from the json string. Therefore, I hope that you could check if it is correct. Of course, I will make a reply to another thread.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 5:23 AM
    Moderator

All replies

  • Hi engahmedbarbary,

    Thank you for posting here.

    You could try the following code to get the sql select statement.

            private void button1_Click(object sender, EventArgs e)
            {
                string path = "D:\\test.json";
                string text = File.ReadAllText(path);
                string result = GetSelectStatement(text);
            }
            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);
                List<JToken> list = new List<JToken>();
                foreach (var item in items)
                {
                    if (item.Key == "table")
                    {
                        var tableName = item.Value.ToList();
                        list = tableName;
                        query = string.Format("select  from table {0} inner join table {1} where ", tableName[0], tableName[1]);
                    }
                    if (item.Key == "fields")
                    {
                        var key = item.Value.SelectToken("").OfType<JProperty>().ToList(); ;
                        string text = string.Format("{0}.{1},{2}.{3},{4}.{5} ", list[1], key[0].Name, list[1], key[1].Name, list[1], key[2].Name);
                        query = query.Insert(query.IndexOf("from"), text);
                    }
                    if (item.Key == "keys")
                    {
                        var key = item.Value.SelectToken("").OfType<JProperty>().ToDictionary(p => p.Name, p => p.Value);
                        var count = 0;
                        string data = "on ";
                        foreach (var id in key)
                        {
                            count++;
                            if (count == key.Count())
                            {
                                query += string.Format("{0}.{1} = {2}", list[0],id.Key, id.Value);
                               
                            }
                            else
                            {
                                query += string.Format("{0}.{1} = {2} and ", list[0], id.Key, id.Value);
                            }
                            data += string.Format("{0}.{1}={2}.{3}, ", list[0], id.Key, list[1], id.Key);
    
                        }
                        query = query.Insert(query.IndexOf("where"), data);
    
                    }
                    
                    
                }
                query=query.Remove(query.LastIndexOf(','), 1);
                return query;
            }
    Result:

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 3:16 AM
    Moderator
  • Sorry, but why join MasterTable at all?

    You're not selecting any field from master, and your "where" fields are the same for both master and footer tables. Unless you foresee data integrity problem, you can skip the master table and just perform lookup on the footer.

    Monday, September 23, 2019 3:22 AM
    Answerer
  • thank you for reply

    I make this function to Fill data grid so that fields on json represent fields on footertable

    fields exist on footer table because i need to show details from one table.

    what I need above is 

    I need to modify code above to get relation from database .

    meaning part after ON (mastertable.BranchCode=FooterTable.BranchCode and mastertable.Serial=FooterTable.Serial and mastertable.Year=FooterTable.Year)

    this part i need to get it from database 

    public string GetFieldsRelation (string MasterTable,string FooterTable)

    {

    }

    return Fields related on two tables 

    and then append to code above you modify to me .

    i try but statement may be wrong it not get me any result and get null

    public string Tables_GetRelations(string PrimaryTable, string ForeignTable, string PrimaryField = "")
            {
                return $"select  pk_col.name as MasterColumnName, col.name as ChildColumnName from sys.tables tab inner join sys.columns col on col.object_id = tab.object_id INNER join sys.foreign_key_columns fk_cols on fk_cols.parent_object_id = tab.object_id and fk_cols.parent_column_id = col.column_id INNER join sys.foreign_keys fk on fk.object_id = fk_cols.constraint_object_id INNER join sys.tables pk_tab on pk_tab.object_id = fk_cols.referenced_object_id INNER join sys.columns pk_col on pk_col.column_id = fk_cols.referenced_column_id and pk_col.object_id = fk_cols.referenced_object_id where pk_tab.name = '{PrimaryField}' and tab.name = '{ForeignTable}' {(string.IsNullOrWhiteSpace(PrimaryField) ? string.Empty : $" and pk_col.name = '{PrimaryField}'")}";
            }


    Monday, September 23, 2019 4:35 AM
  • Hi 

    Thanks for the feedback.

    It seems that you replying this thread. Your current problem is that you need to achieve correct sql statement from the json string. Therefore, I hope that you could check if it is correct. Of course, I will make a reply to another thread.

    Best Regards,

    Jack


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, September 23, 2019 5:23 AM
    Moderator