AX2009 连接外部Orcal与SQL区别

在09中有时需要与公司现有的其他系统做集成,其他的系统可能使用Orcal,sql server等数据库。

在实际应用中,操作这两个DB的写法都类似,只是有些细节上的地方需要注意

1,在与Orcal做连接时需要注意两点,首先AX2009默认没有引用关于Orcal的一些dll引用,我们需要手工去增加这些reference,如图

 

2,在方法的调用中,我们并不能需要在每个方法上加server 关键字,因为Orcal是在客户端进行调用的。而在SQL SERVER必须的带上Server关键字

具体的代码如下;

//orcal read
static System.Data.OracleClient.OracleDataReader executeoracleStatic(str _sql,System.Data.OracleClient.OracleConnection _sqlConnection)
{
    InteropPermission                       interop = new InteropPermission(InteropKind::ClrInterop);
    System.Data.OracleClient.OracleCommand        oracleCommand;
    System.Data.OracleClient.OracleDataReader     oracleDataReader;
    ;
    interop.assert();
    oracleCommand = new System.Data.OracleClient.OracleCommand();
    oracleCommand.set_Connection(_sqlConnection);
    oracleCommand.set_CommandText(_sql);
    oracleCommand.set_CommandTimeout(999999999);

    oracleDataReader =  oracleCommand.ExecuteReader();
    CodeAccessPermission::revertAssert();
    return oracleDataReader;
}
//sql server read
server static System.Data.SqlClient.SqlDataReader executeSqlStatic(str _sql,System.Data.SqlClient.SqlConnection _sqlConnection)
{
    InteropPermission                       interop = new InteropPermission(InteropKind::ClrInterop);
    System.Data.SqlClient.SqlCommand        sqlCommand;
    System.Data.SqlClient.SqlDataReader     sqlDataReader;
    ;
    interop.assert();
    sqlCommand = new System.Data.SqlClient.SqlCommand();
    sqlCommand.set_Connection(_sqlConnection);
    sqlCommand.set_CommandText(_sql);
    sqlCommand.set_CommandTimeout(999999999);

    sqlDataReader =  sqlCommand.ExecuteReader();
    CodeAccessPermission::revertAssert();
    return sqlDataReader;
}

orcal Connection
Static System.Data.OracleClient.OracleConnection getOracleConnection()
{
    IWS_POSDatabaseParameter                                para = IWS_POSDatabaseParameter::find();
    System.Data.OracleClient.OracleConnection               oracleConnection;
    System.Data.OracleClient.OracleConnectionStringBuilder  connectionStringBuilder;
    InteropPermission                                       interop = new InteropPermission(InteropKind::DllInterop);
    str                                                     connectionString;


    interop.assert();

    connectionStringBuilder = new System.Data.OracleClient.OracleConnectionStringBuilder();
    connectionStringBuilder.set_DataSource(para.DataSourceName);
    connectionStringBuilder.set_UserID(para.Sa);
    connectionStringBuilder.set_Password(para.Pwd);
    connectionStringBuilder.set_IntegratedSecurity(FALSE);
   // connectionStringBuilder.set_IntegratedSecurity(true);

     oracleConnection = new System.Data.OracleClient.OracleConnection(connectionStringBuilder.get_ConnectionString());
    //connectionString    = "Provider=OraOLEDB.Oracle.1;Password=ENZOTEST;Persist Security Info=false;User ID=ENZOTEST;Data Source=ENZOTEST";
   // oracleConnection = new System.Data.OracleClient.OracleConnection(connectionString);
    oracleConnection.Open();
    CodeAccessPermission::revertAssert();
    return oracleConnection;

}

//sql server connection
Server Static System.Data.SqlClient.SqlConnection getSqlConnection_Pos()
{
    IWS_POSDatabaseParameter                                para = IWS_POSDatabaseParameter::find();

    System.Data.SqlClient.SqlConnection                     sqlConnection;
    System.Data.SqlClient.SqlConnectionStringBuilder        connectionStringBuilder;
    InteropPermission                                       interop = new InteropPermission(InteropKind::ClrInterop);
    interop.assert();
    connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
  //  connectionStringBuilder.set_DataSource(para.DatabaseServer);
   // connectionStringBuilder.set_InitialCatalog(para.Database);
    connectionStringBuilder.set_UserID(para.Sa);
    connectionStringBuilder.set_Password(para.Pwd);
    connectionStringBuilder.set_IntegratedSecurity(false);
   // connectionStringBuilder.set_IntegratedSecurity(true);

    sqlConnection = new System.Data.SqlClient.SqlConnection(connectionStringBuilder.get_ConnectionString());
    sqlConnection.Open();
    CodeAccessPermission::revertAssert();
    return sqlConnection;



}

orcal execute
static void executeNonOracle(str _sql,System.Data.OracleClient.OracleConnection _sqlConnection)
{
    InteropPermission                       interop = new InteropPermission(InteropKind::ClrInterop);
    System.Data.OracleClient.OracleCommand        oracleCommand;
    ;
    interop.assert();
    if(_sqlConnection == null)
        _sqlConnection = IWS_DotNetSqlProvider::getOracleConnection();

    oracleCommand = new System.Data.OracleClient.OracleCommand();
    oracleCommand.set_Connection(_sqlConnection);
    oracleCommand.set_CommandText(_sql);
    oracleCommand.set_CommandTimeout(999999999);

    oracleCommand.ExecuteNonQuery();
    _sqlConnection.Close();
    CodeAccessPermission::revertAssert();
}

sql server execute
server static void executeNonSql(str _sql,System.Data.SqlClient.SqlConnection _sqlConnection)
{
    InteropPermission                       interop = new InteropPermission(InteropKind::ClrInterop);
    System.Data.SqlClient.SqlCommand        sqlCommand;
    System.Data.SqlClient.SqlDataReader     sqlDataReader;
    ;
    interop.assert();
    //if(_sqlConnection == null)
       // _sqlConnection = IWS_DotNetSqlProvider::getSqlConnection_Barcode();

    sqlCommand = new System.Data.SqlClient.SqlCommand();
    sqlCommand.set_Connection(_sqlConnection);
    sqlCommand.set_CommandText(_sql);
    sqlCommand.set_CommandTimeout(999999999);

    sqlCommand.ExecuteNonQuery();
    _sqlConnection.Close();
    CodeAccessPermission::revertAssert();
}