For several reasons we need to get the data from SQL db and insert it into AX table.
below the code snippet to do read data from db and insert into LedgerJournalTrans table:
public static server void GetDataandInsertToLedger()
{
str serverName;
str catalogName;
str ConnectionString;
str sqlQuery;
Table2 t;
real p;
System.Data.SqlClient.SqlConnectionStringBuilder connectionStringBuilder;
System.Data.SqlClient.SqlConnection connection;
System.Data.SqlClient.SqlCommand command;
System.Data.SqlClient.SqlParameterCollection parameterCollection;
System.Data.SqlClient.SqlDataReader dataReader;
;
new InteropPermission( InteropKind::ClrInterop ).assert();
sqlQuery = "SELECT TOP 10 T.Txt , T.Amount FROM TestTable T";
serverName = SysSQLSystemInfo::construct().getLoginServer();
catalogName = SysSQLSystemInfo::construct().getloginDatabase();
connectionStringBuilder = new System.Data.SqlClient.SqlConnectionStringBuilder();
connectionStringBuilder.set_DataSource(serverName);
connectionStringBuilder.set_IntegratedSecurity(true);
connectionStringBuilder.set_InitialCatalog(catalogName);
ConnectionString = "Data Source=Essam-PC;Initial Catalog=Test;Integrated Security=True";
connection = new System.Data.SqlClient.SqlConnection(ConnectionString);
command = new System.Data.SqlClient.SqlCommand(sqlQuery);
command.set_Connection(connection);
parameterCollection = command.get_Parameters();
try
{
connection.Open();
try
{
dataReader = command.ExecuteReader();
while(dataReader.Read())
{
t.Txt= dataReader.get_Item("Txt");
p=dataReader.get_Item("Amount");
t.Amount=p;
t.insert();
EssamIntegration::InsertLedgerJournalTrans(dataReader.get_Item("Txt"),p,0);
}
dataReader.Dispose();
}
catch
{
dataReader.Dispose();
}
catch(Exception::CLRError)
{
dataReader.Dispose();
}
connection.Dispose();
}
catch
{
connection.Dispose();
}
catch(Exception::CLRError)
{
connection.Dispose();
}
command.Dispose();
CodeAccessPermission::revertAssert();
}
and the below job to insert into AX table
public static server void InsertLedgerJournalTrans(LedgerJournalTransTxt Txt,
AmountCurDebit AmountDebit,
AmountCurCredit AmountCredit
)
{
LedgerJournalTrans journalTrans;
;
journalTrans.clear();
journalTrans.initValue();
journalTrans.Txt = StrFmt(Txt);
journalTrans.Voucher = "VOU-000008815";
journalTrans.JournalNum = "JOU_001473";
journalTrans.LineNum = LedgerJournalTrans::lastLineNum(journalTrans.JournalNum)+1;
journalTrans.transDate = systemDateGet();
journalTrans.AccountType = LedgerJournalACType::Ledger;
journalTrans.AmountCurDebit = AmountDebit;
journaltrans.AmountCurCredit=AmountCredit;
journalTrans.OffsetAccountType = LedgerJournalACType::Ledger;
journalTrans.OffsetLedgerDimension = 0002; //Customized field
journalTrans.LedgerDimension = 5637172467;
journalTrans.CurrencyCode = "USD";
journalTrans.insert();
// info("done");
}
*This post is locked for comments
I have the same question (0)
