前言
业务需求:由于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');
}
}
}