Monthly Archives: September 2012

LINQ Queries: handling nulls

Posted on 27/09/2012 by Paul Fox

Quite often when querying databases using data from text boxes, you can run into problems dealing with a blank string and fields containing a null. A field in a table may contain a null which could also mean a blank … Continue reading →

Posted in C# Development | Comments Off

Left Outer Join LINQ Query

Posted on 27/09/2012 by Paul Fox

Here is a good example for creating a left outer join in LINQ for entities;

var myQuery = (from x in db.tAXModelKitXref
                           join p in db.vPRODUCTSTKDBs on x.ProdCode equals p.ProdCode into kits let k = kits.FirstOrDefault()
                           orderby x.UniqueId
                           select new
                           {
                               UniqueId = x.UniqueId,
                               ProdCode = x.ProdCode,
                               ProdCodeDesc = k.ProdCodeDesc, 
                               KitCode = x.KitCode,
                               KitCodeDesc = "",
                               ModelKitStatus = x.ModelKitStatus,
                               ModelKitNotes = x.ModelKitNotes
                           }).ToList();

var myQuery = (from x in db.tAXModelKitXref

join p in db.vPRODUCTSTKDBs on x.ProdCode equals p.ProdCode into kits let k = kits.FirstOrDefault()

orderby x.UniqueId

select new

{

UniqueId = x.UniqueId,

ProdCode = x.ProdCode,

ProdCodeDesc = k.ProdCodeDesc,

KitCode = x.KitCode,

KitCodeDesc = "",

ModelKitStatus = x.ModelKitStatus,

ModelKitNotes = x.ModelKitNotes

}).ToList();

If you need to perform other queries to get extra data for your main query list, first perform your main query first and … Continue reading →

Posted in C# Development | Comments Off

Serialize a List to XML

Posted on 18/09/2012 by Paul Fox

Here is a very good simple way to convert a list of objects to XML. First, create a class for your list of objects. This class could be declared next to your routine if it is just to capture query … Continue reading →

Posted in C# Development | Comments Off

AX BC – Another Example for Reading Records

Posted on 12/09/2012 by Paul Fox

Axapta ax;
            AxaptaRecord record;
            Object carId, carBrand, model, modelYear, mileage;
            try
            {
                // Create AX object and logon to AX
                ax = new Axapta();
                ax.Logon(null, null, null, null);
                // Create an AxaptaRecord object from the
                // table that will be used
                using (record = ax.CreateAxaptaRecord("CarTable"))
                {
                    // Execute the statement entered as parameter
                    record.ExecuteStmt("select * from %1
                      where %1.CarBrand like 'BMW'");
                    // Loop through the result of the statement.
                    while (record.Found)
                    {
                        // Set our local variables to be
                        // equal to the fields in the table
                        // for the current record.
                        carId = record.get_Field("CARID");
                        carBrand = record.get_Field("CARBRAND");
                        model = record.get_Field("MODEL");
                        modelYear = record.get_Field("MODELYEAR");
                        mileage = record.get_Field("MILEAGE");
                        // Write the result to the console
                        Console.WriteLine(carId + "t" +
                                          carBrand + "t" +
                                          model + "t" +
                                          modelYear + "t" +
                                          mileage);
                        // Go to the next record in the result set
                        record.Next();
                    }
                    // End the AX session
                    ax.Logoff();
                    // Make sure the console stays up
                    // until a key is pressed
                    Console.ReadKey();
                }
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

Axapta ax;

AxaptaRecord record;

Object carId, carBrand, model, modelYear, mileage;

try

{

// Create AX object and logon to AX

ax = new Axapta();

ax.Logon(null, null, null, null);

// Create an AxaptaRecord object from the

// table that will be used

using (record = ax.CreateAxaptaRecord("CarTable"))

{

// Execute the statement entered as parameter

record.ExecuteStmt("select * from %1

where %1.CarBrand like 'BMW'");

// Loop through the result of the statement.

while (record.Found)

{

// Set our local variables to be

// equal to the fields in the table

// for the current record.

carId = record.get_Field("CARID");

carBrand = record.get_Field("CARBRAND");

model = record.get_Field("MODEL");

modelYear = record.get_Field("MODELYEAR");

mileage = record.get_Field("MILEAGE");

// Write the result to the console

Console.WriteLine(carId + "t" +

carBrand + "t" +

model + "t" +

modelYear + "t" +

mileage);

// Go to the next record in the result set

record.Next();

}

// End the AX session

ax.Logoff();

// Make sure the console stays up

// until a key is pressed

Console.ReadKey();

}

catch (Exception e)

{

Console.WriteLine(e.Message);

}

Posted in C# Development | Comments Off

AX BC – Insert Records

Posted on 12/09/2012 by Paul Fox

 Axapta ax;
            AxaptaRecord record;
            try
            {
                // Create AX object and logon to AX
                ax = new Axapta();
                ax.Logon(null, null, null, null);
                // Create a new AxaptaRecord object with
                // the name of the table as input parameter
                using (record = ax.CreateAxaptaRecord("CarTable"))
                {
                    // Remember to clear the tablebuffer if
                    // you are inserting inside a loop
                    record.Clear();
                    record.InitValue();
                    // Set the fields in the table
                    record.set_Field("CARID", "XXX1");
                    record.set_Field("MODELYEAR", 1998);
                    record.set_Field("CARBRAND", "FORD");
                    record.set_Field("MODEL", "F1");
                    record.set_Field("MILEAGE", 89378);
                    // Insert the record
                    record.Insert();
                }
                // End the AX session
                ax.Logoff();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

Axapta ax;

AxaptaRecord record;

try

{

// Create AX object and logon to AX

ax = new Axapta();

ax.Logon(null, null, null, null);

// Create a new AxaptaRecord object with

// the name of the table as input parameter

using (record = ax.CreateAxaptaRecord("CarTable"))

{

// Remember to clear the tablebuffer if

// you are inserting inside a loop

record.Clear();

record.InitValue();

// Set the fields in the table

record.set_Field("CARID", "XXX1");

record.set_Field("MODELYEAR", 1998);

record.set_Field("CARBRAND", "FORD");

record.set_Field("MODEL", "F1");

record.set_Field("MILEAGE", 89378);

// Insert the record

record.Insert();

}

// End the AX session

ax.Logoff();

}

catch (Exception e)

{

Console.WriteLine(e.Message);

}

Posted in C# Development | Comments Off

AX BC – Call a Static Method

Posted on 12/09/2012 by Paul Fox

	Axapta ax;
            Object axObject;
            try
            {
                // Create a new Axapta object.
                ax = new Axapta();
                // Logon using the default settings
                // in the AX configuration for the .NET
                // Business Connector
                ax.Logon(null, null, null, null);
                // Call the static method and return the
                // result to the axObject variable
                axObject =
                  ax.CallStaticClassMethod("Global", "AxHelloWorld");
                // Print the result to the console
                Console.WriteLine("The message from AX is {0}",
                  axObject.ToString());
                ax.Logoff();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
            }

Axapta ax;

Object axObject;

try

{

// Create a new Axapta object.

ax = new Axapta();

// Logon using the default settings

// in the AX configuration for the .NET

// Business Connector

ax.Logon(null, null, null, null);

// Call the static method and return the

// result to the axObject variable

axObject =

ax.CallStaticClassMethod("Global", "AxHelloWorld");

// Print the result to the console

Console.WriteLine("The message from AX is {0}",

axObject.ToString());

ax.Logoff();

}

catch (Exception e)

{

Console.WriteLine(e.Message);

}

Another good example for finding a record ID by calling the standing find method from a table

ax.CallStaticClassMethod("xUserInfo", "find") as AxaptaRecord).get_Field("Id").ToString()

1	ax.CallStaticClassMethod("xUserInfo", "find") as AxaptaRecord).get_Field("Id").ToString()

And another…

    public static int GetFieldId(this Axapta ax, string tableName, string fieldName)  
    {  
        AxaptaObject dictionary = ax.CreateAxaptaObject("Dictionary");  
        int fieldId = 0;  
        if (ax.TableExists(tableName))  
        {  
            int tableId = ax.GetTableId(tableName);  
            AxaptaObject dictTable = (AxaptaObject)dictionary.Call("tableObject", tableId);  
            fieldId = (int)dictTable.Call("fieldName2Id", fieldName);  
        }  
        return fieldId;  
    }

public static int GetFieldId(this Axapta ax, string tableName, string fieldName)

{

AxaptaObject dictionary = ax.CreateAxaptaObject("Dictionary");

int fieldId = 0;

if (ax.TableExists(tableName))

{

int tableId = ax.GetTableId(tableName);

AxaptaObject dictTable = (AxaptaObject)dictionary.Call("tableObject", tableId);

fieldId = (int)dictTable.Call("fieldName2Id", fieldName);

}

return fieldId;

}

Posted in C# Development | Comments Off