有時候需要操作Axapta資料庫以外的資料庫裡的資訊,這個時候無論用X++裡的select還是Query都沒辦法做到,只能採用Direct SQL.Axapta提供了Connection,UserConnection和ODBCConnection三個對象用於執行Direct SQL.
下面是協助文檔中對這三個對象的描述:
1.Connection
A Connection represents the current session with the SQL database. Within the context of a Connection, SQL statements are executed and results are returned.
這個串連是Axapta與SQL資料庫的當前會話.在這個串連的上下文,可以執行SQL語句並得到結果.
2.UserConnection
A UserConnection represents an auxiliary connection to the SQL database, based on the same login properties as the main connection. Within the context of a UserConnection, SQL statements are executed and results are returned.UserConnections can be used to obtain a separate transaction scope.
基於原主串連的屬性建立的輔助串連,可以獲得一個單獨的事物範圍.
3.ODBCConnection
An OdbcConnection establishes a database connection using ODBC (Open Database Connectivity). Within the context of an OdbcConnection, SQL statements are executed and results are returned. In order to work, the proper ODBC drivers must have been installed and configured in the ODBC Manager in the Control Panel.
通過ODBC建立連結.當然適當的ODCB驅動必須通過控制台裡的ODBC管理器安裝和配置.
我在做的時候採用了Connection,使用原有的資料庫連接會話.
當然有時候資料庫伺服器有可能跟Axapta資料庫伺服器不是一台,需要通過連結的伺服器把需要訪問的資料庫伺服器連結到Axapta資料庫伺服器,在SQL語句中用serverName.databsaeName.dbo.TableName的方式訪問.
以下是範例程式碼:static server void DirectSQL()
{
Connection Con;
Statement Stmt;
ResultSet R;
str sqlString;
;
sqlString ='SELECT * FROM [dbServer].[axapta2].dbo.userInfo';
Con = new Connection();
Stmt = Con.createStatement();
R = Stmt.executeQuery(sqlString);
while ( R.next() )
{
print R.getString(1);
}
pause;
}
上面的代碼在Axapta3.0中可以順利運行,不管上面的代碼是運行在client還是server端.但在Axapta4.0中,Axapta不允許client直接存取資料庫,並且Direct SQL被視為危險的API,所以必須用SqlStatementExecutePermission的asset()來限制SQL語句的執行,否則上述代碼將不能執行,這點在Writing Secure X++ Code(一)中有介紹,Axapta4.0的代碼如下:static server void DirectSQL()
{
Connection Con;
Statement Stmt;
ResultSet R;
str sqlString;
;
sqlString ='SELECT * FROM [dbServer].[axapta2].dbo.userInfo';
new SqlStatementExecutePermission(sqlString).assert();
Con = new Connection();
Stmt = Con.createStatement();
R = Stmt.executeQuery(sqlString);
while ( R.next() )
{
print R.getString(1);
}
pause;
}
