dcsimg
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 4 of 4

Thread: How modify function GetSelectStatement to generate sql select statement ?

  1. #1
    Join Date
    Aug 2019
    Posts
    5

    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

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,108

    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).

  3. #3
    Join Date
    Aug 2019
    Posts
    5

    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

  4. #4
    Arjay's Avatar
    Arjay is offline Moderator / MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,108

    Re: How modify function GetSelectStatement to generate sql select statement ?

    Quote Originally Posted by ahmed.salah View Post
    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
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width




On-Demand Webinars (sponsored)