D365Fo：在窗体中输入sql语句，展示查询结果

前言

业务需求：由于365是云上的版本，无法访问数据库，提出需求，在一个窗体中，输入sql语句，把执行的结果展示出来

---

实现思路如下：
1.窗体设计：
string控件：用来输入sql语句
button按钮：一个用于查询
一个Temptable：用来承载查询出来的数据
一个Grid：用来展示查询出来的数据
2.由于每次执行的查询sql语句都不一样，没办法固定临时表的字段数量，只能够一次创建足够多的字段，去给它赋值，我个人创建了32个列，一般查询感觉足够用了
2.逻辑部分
画面效果为模仿SQL Server数据库画面效果，整体思路如下：
1.画面初始化的时候显示效果
2.查询成功后的画面
3.失败的画面

一、窗体设计

1.画面效果

窗体画面如下：

2.VS设计

二、代码部分

1.窗体代码

：

[Form]
public class ExecuteSQLScript extends FormRun
{
   ExecuteSQLScriptTmp    executeSQLScriptTmp;

    /// <summary>
    ///
    /// </summary>
    public void init()
    {
        super();
        //tmptable link to form datasource
        ExecuteSQLScriptTmp.linkPhysicalTableInstance(executeSQLScriptTmp);
    }

    [Control("Button")]
    class ExecuteSelect
    {
        /// <summary>
        /// Select Button
        /// </summary>
        public void clicked()
        {
            super();
            SysInfologEnumerator        infologEnum;
            SysInfologMessageStruct     infoMessageStruct;
            FormControl                 fieldControl;
            ExecuteSQLScript   execute = new ExecuteSQLScript();
            int                         i = 1;
           
            try
            {
                ErrorMessageGroup.visible(false);
           
                execute.parmFormRun(element);
                execute.run();
                executeSQLScriptTmp = execute.parmExecuteSQLScript();
                ExecuteSQLScriptTmp.linkPhysicalTableInstance(executeSQLScriptTmp);
                ExecuteSQLScriptTmp_ds.executeQuery();
                FormGrid.visible(true);
           
            }
            
            catch
            {
                // 处理错误信息
                str errorMessage;
                infologEnum = SysInfologEnumerator::newData(infolog.infologData());
                while(infologEnum.moveNext())
                {
                    switch(infologEnum.currentException())
                    {
                        case Exception::Error:
                            infoMessageStruct =  SysInfologMessageStruct::construct(infologEnum.currentMessage());
                            errorMessage += infoMessageStruct.message();
                            errorMessage += '\n';
                            break;
                        case Exception::Warning:
                            infoMessageStruct =  SysInfologMessageStruct::construct(infologEnum.currentMessage());
                            warning(infoMessageStruct.message());
                            break;
                    }
                }
                //Error Message 
                ErrorMessageControl.text(strFmt('%1',errorMessage));
                ErrorMessageControl.colorScheme(FormColorScheme::RGB);
                ErrorMessageControl.foregroundColor(WinAPI::RGB2int(255,0,0));
                ErrorMessageControl.backgroundColor(WinAPI::RGB2int(255,255,255));
                ErrorMessageControl.border(10);
                ErrorMessageGroup.visible(true);
            
            }
           
        }

    }

}

2.逻辑代码

 class SQLScript
{
    FormRun formRun;
    ExecuteSQLScriptTmp             executeSQLScript_T;

    public FormRun parmFormRun(FormRun _formRun = formRun)
    {
        formRun = _formRun;
        return formRun;
    }

    public ExecuteSQLScriptTmp parmExecuteSQLScript(ExecuteSQLScriptTmp _executeSQLScript_T = executeSQLScript_T)
    {
        executeSQLScript_T = _executeSQLScript_T;
        return executeSQLScript_T;
    }

    void run()
    {
       
        FormStringControl   sqlScriptCtrl;
        Connection          Con = new Connection();
        Statement           Stmt = Con.createStatement();
        FormStringControl   sQLScript = formRun.design(0).controlName('SQLText');
        Map                 paramMap = SqlParams::create();
        ResultSet           R =Stmt.executeQueryWithParameters(sQLScript.valueStr(), paramMap);
        ResultSetMetaData   resultSetMetaData = R.getMetaData();
        int                 i;
        int                 many = 1;
        str                 experssion = subStr(sQLScript.valueStr(),0,6);
       
        if(experssion == 'select')
        {
            ttsbegin;
            while (R.next())
            {
                i = 1;
                executeSQLScript_T.clear();
                while(i <= resultSetMetaData.getColumnCount() && i <= 32)
                {
                    //get TmpTable assignment
                    executeSQLScript_T.(fieldName2Id(executeSQLScript_T.TableId, strfmt('%1%2','Field', i))) = R.getString(i);
                    if(many == 1)
                    {
                        //Modify form column labels
                        sqlScriptCtrl = formRun.design(0).controlName(strFmt('ExecuteSQLScriptTmp_Field%1',i)) as FormStringControl;
                        sqlScriptCtrl.label(resultSetMetaData.getColumnName(i));
                        sqlScriptCtrl.visible(true);
                    }
                    i++;
                }
            
                many++;
                executeSQLScript_T.insert();
            }
            // limit the scope of the assert call
            CodeAccessPermission::revertAssert();
            ttscommit;
        }
        else
        {
            info('Enter a correct executable query statement');
        }
        
    }

}

总结

目前的画面只有32列可以显示，当查询的数据量比较大的时候效率太慢，做过一次测试，查询383064行数据，执行时间用了5:21