none
How to convert these function fill grid to json formate ? RRS feed

  • Question

  • I have function name FillGrids this function fill grid

    i try to design json formate  as following :

    { 
       "Details":{ 
          "table":[ 
             "MasterTable",
             "FooterTable"
          ],
          "fields":{ 
             "ItemCode":"string",
             "Quantity":"int",
             "Price":"decimal"
             
          },
          "keys":{ 
             "BranchCode":1,
             "Year":2019,
             "Serial":2
          }
       }
    }
    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

    function i do by csharp generate SQL statement above .

    my business by csharp  :

    private static void FillGrids(Control.ControlCollection Controls, xForm frm)
        {
          
            foreach (Control co in Controls)
            {
                if (co is xDataGrid && (!String.IsNullOrEmpty(((xDataGrid)co).TableName) || !String.IsNullOrEmpty(((xDataGrid)co).SelectQuery)))
                {
                    Binder.DataToGrid(frm.Controls, frm, (xDataGrid)co);
                }
                else
                {
    
                }
                if (co.Controls.Count > 0 && co.Name != "pnlGridPanel" && co.Name != "pnlMasterDetailsGrid")
                {
                    FillGrids(co.Controls, frm);
                }
            }
            
        }
     public static void DataToGrid(Control.ControlCollection Controls, xForm frm, xDataGrid Grid)
        {
            
            string SQL = "";
    
            string Criteria = "";
            DataTable Relations = new DataTable();
    
            if (Grid.SelectQuery != "")
    
                SQL = Grid.SelectQuery;
            else xExceptions.DataGirdTableNameOrSelectQueryIsMissing();
    
            Relations = DataAccess.ExecuteDataTable(SqlFactory.Queries.Tables_GetRelations(frm.TableName, Grid.TableName));
    
            if (Grid.SelectQuery.IndexOf(" WHERE ", StringComparison.OrdinalIgnoreCase) == -1)
            {
                Criteria = SqlFactory.Queries.GetGlobalCriteriaForGrid(frm, Grid.TableName, Relations);
                SQL += " WHERE ";
                SQL += Criteria != "" ? Criteria : " 1 = 1 ";
            }
            else
            {
                
                Criteria = SqlFactory.Queries.GetGlobalCriteriaForGrid(frm, Grid.TableName, Relations, false);
                if (Criteria != "") SQL += " AND " + Criteria;
            }
    
            if (!String.IsNullOrEmpty(Grid.Filter) && !SQL.Contains(" 1 = 1 ")) SQL += " AND " + Grid.Filter;
            
            DataTable dt = DataAccess.ExecuteDataTable(SQL);
    
            
            if (frm.IsSaveAndCopy)
            {
                Grid.ChangeAllRowsState(RowStates.Inserted); //Because of F6                
            }
            else
            {
                Grid.DataBinding(dt);
               
            }
           
        }


    Saturday, September 21, 2019 3:43 PM

All replies

  • What database do you use? Many RDBMS support JSON generation. E.g. SQL Server and the FOR JSON clause.
    Sunday, September 22, 2019 3:40 PM
  • sql server database
    Monday, September 23, 2019 12:56 AM
  • Hi 

    Thank you for posting here.

    According to your description, I want to a question with you.

    Do you mean that you want to execute the below sql so that datagridview shows data?

    I hope that you could describe your question more clearly. which will help us to solve the problem.

    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 1:55 AM
    Moderator
  • Hi 

    Thanks for the feedback.

    I read your reply from this thread.

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

    I want to know what the final datagridview you need is. You could give me a demonstration.

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

    Could you give a example about the relation that you want to get?

    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:36 AM
    Moderator
  • Thank you for reply

    According point of relation this is example :

    PrimaryTable field ForignTable ForignKey Example MasterTable Serial pk Year pk BranchCode pk DateTransaction Trxinvf Serial fk Year fk

    BranchCode fk DateTransaction result must be as following PrimaryTable primarykey ForignTable ForignKey trxinvh serial trxinvf serial trxinvh year trxinvf year trxinvh BranchCode trxinvf BranchCode


    Monday, September 23, 2019 8:12 AM
  • statement of SQL already above 

    to clear more 

    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

    this statement after (

    inner join FooterTable on

    ) i will get from relation from example above by this statement

    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='trxinvh'  and tab.name ='trxinvf'

    Monday, September 23, 2019 8:20 AM
  • What does JSON have to do with your UI grid? Please clarify why you need JSON. Are you trying to take the data stored in the grid and write it out to JSON? The discussion so far seems wrapped up in getting data from the DB into the grid. How does JSON have anything to do with that?

    Michael Taylor http://www.michaeltaylorp3.net

    Monday, September 23, 2019 1:57 PM
    Moderator
  • { 
       "Details":{ 
          "table":[ 
             "MasterTable",
             "FooterTable"
          ],
          "fields":{ 
             "ItemCode":"string",
             "Quantity":"int",
             "Price":"decimal"
             
          },
          "keys":{ 
             "BranchCode":1,
             "Year":2019,
             "Serial":2
          }
       }
    }
    Tuesday, September 24, 2019 1:25 AM
  • I try json because in the future i will use on mobile and web then in this case i will do not make effort more

    actually i can do it by data by i do all project to json this structure i build full project based on that 

    Tuesday, September 24, 2019 1:28 AM
  • Hi 

    Thanks for the feedback.

    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

    Let me confirm it again, Do you mean that you want to get the following string from the above sql statement?

    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='trxinvh'  and tab.name ='trxinvf'

    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.

    Tuesday, September 24, 2019 5:18 AM
    Moderator
  • JSON has absolutely nothing to do with your UI so eliminate that from the conversation. Take the types you're reading from your database (or whatever) and convert that to JSON. It is easy to do. If you're using JSON.NET then it is a line of code. If you're using the newer JSON serializer in .NET it is about the same. In either case you either set up your business objects to match the format you want (generally) or you create a custom serialization type that represents the structure you want in JSON and then just convert your business data to that.

    Post the code you're using to populate your business objects with the data you want to serialize. If you're storing it directly in the UI then that is the first thing you need to fix.


    Michael Taylor http://www.michaeltaylorp3.net

    Tuesday, September 24, 2019 1:36 PM
    Moderator