Erp Objects

Ok, here comes most important part, that is explanation of “Adapter” class, which actually executes queries and following section enlists all possible ways.

The most important part, as && and || operators are not overridable, the SQL AND is replaced by single “&” operand and OR is replaced by single “|” operand in order to build the query expression

Throught this section, we will use following schema,

Table: Customers

Fields: CustomerID, FirstName, LastName, IsActive

Table: Invoices

Fields: InvoiceID, InvoiceDate, Amount, CustomerID

and now note that, CustomerID of Invoices table refers to CustomerID of Customers.

Query Single Object

Invoice.Adapter.Get( 2312 );

Just by specifying the “Long” ID.

Query Single Object by “WHERE”

Invoice.Adapter.Get( Invoice.Schema.InvoiceID == 2312 );

This is an expression tree, and it is built by schema access.

Invoice.Adapter.Get(
   Customer.Schema.IsActive == true ,
   Customer.Schema.CustomerID.SortDesc());

This expression creates an Auto Join and also sorts CustomerID in descending order, giving you the invoice of last added customer

Compound WHERE Expression by “AND”

Invoice.Adapter.GetAll(
   Invoice.Schema.InvoiceDate >= new DateTime(2001,1,1) &
   Invoice.Schema.Status != "PAID");

This compond expression contains two expressions and “AND” operator.

Dynamic Expression Tree for WHERE

Consider following sample of searching customer,

private CustomerList Search(string name, string email, string phone,
    int start, int size)
{
    ErpExpression exp = null;
    if(!String.IsNullOrEmpty(name))
        exp &= Customer.Schema.FirstName.Contains(name);
    if(!String.IsNullOrEmpty(email))
        exp &= Customer.Schema.EmailAddress.Contains(email);
    if(!String.IsNullOrEmpty(phone))
        exp &= Customer.Schema.Phone.Contains(phone);

    // note this exp Expression is dynamic depending upon
    // values specified at run time
    return Customer.Adapter.GetAll(exp, start, size);
}

The above function returns all Customers if all parameters are either null or empty. This search functionality is very essential in order to build dynamic query expression, which is not offered in any of the ORML software existing till date.

You can sure use & or | respectively for AND or OR operators, however please try to visualize the expression tree in order to avoid any confusion.

Documentation

Well here comes the most intricate parts of joins handling, most of the time we require queries to be able to pull different data from different tables.

This section could be little lengthy, but its worth spending time on.

Lets assume following case.

Table: Customers

Fields: CustomerID, FirstName, LastName, IsActive

Table: Invoices

Fields: InvoiceID, InvoiceDate, Amount, CustomerID

and now note that, CustomerID of Invoices table refers to CustomerID of Customers.

Specify Relations

In Erp Objects Editor, when you click on item “CustomerID” in the “Invoice” object in the tree view of left side.

As specified above, you can see, we specify Related Object and Related Object Property. Related Object Property is essentially Primary Key of the related object. And we also specify “CreateRelatedObjectProperty” true. Which encapsulates related Customer object and you can access values as specified below.

Invoice inv = Invoice.Adapter.Get(12212);
Console.WriteLine(inv.Customer.FirstName);

Please note, the Customer property is cached in invoice object, and if its related customer object has been deleted in the database, it still returns an Empty Customer object, in order to avoid null exception errors.

Related Object’s Children

If you specify “CreatePropertyInRelatedObject” to be true and relation type to be Children, then it creates “InvoiceList” property in Customer class. So that you can get master detail relations.

Customer c = Customer.Adapter.Get(121212);
foreach(Invoice inv in c.InvoiceList)
{
    Console.WriteLine(inv.Amount);
}

Auto Join

Once you have established the relations specified above, Erp Objects prepares a default “Join Dictionary”. Which enables you to query related object properties without specifying joins. For example,

InvoiceList invoices =
    Invoice.Adapter.GetAll(
         Customer.Schema.IsActive == true );

Note above query carefully, as Customer and Invoice are related, a default join of type “LEFT JOIN CUSTOMER ON CUSTOMERS.CUSTOMERID=INVOICES.CUSTOMERID” is automatically added in the SQL. Erp Objects is smart enough to determine the shortest path for the Joins. And for this the code generation takes little longer time for bigger databases and for bigger relations.

Explicit Join

You can specify explicit join also, and in this case, the Auto Join is completely ignored. Following is the syntax.

InvoiceList invoices =
    Invoice.Adapter.GetAll( Customer.Schema.IsActive == true,
        Sql.LeftJoin("Customer",
             Customer.Schema.CustomerID == Invoice.Schema.CustomerID ));

The second parameter could be any valid Erp Expression.

Documentation Detail, Joins, Master, Relations

The core of Erp Objects only provides simple SQL connectivity, but the code is not usable in any form unless you generate the code based on your target database.

There are some conventions we maintain and we would like you to maintain these as well. In your application project, please create folder called “Data” and create a text file called “Data.erd”, as soon as you create “Data.erd” file, the Erp Objects Editor will open automatically.

Well please note the application is kind of still under development, the menus etc, dont expect it to work anything other then specified below.

For all methods specified below, please use following,

Specify Connection

Please specify “ConnectionString” and “DatabaseType” to connect to your database.

Generating Simple Code

1. Once you specify the connection details, click on “Refresh”.
2. This will populate, Table and its corresponding fields on the left side in tree view.
3. Now you can click on “Generate Code” button which will create “Data.erd.cs” file.
4. Please note you must import this “Data.erd.cs” using “Add Existing Item” menu in your C# project.

Generate Updatable Code

Often you require to store a timestamp value in your table to store when it was last updated. However please note, by clicking the button “Refresh Updatable”, you can create auto updatable code for all the table which defines “LastUpdate” Column name with type “DateTime”.

For all the tables, that contain column “LastUpdate”, the generated code automatically inserts, modifies LastUpdate whenever you modify them and its done automatically.

For example, table “Products” does not have LastUpdate column but table “Invoices” has LastUpdate column.

Product p = Product.Adapter.Get(1211);
p.Price = 2.1;
p.Update(); //<-- no timestamp update...

Invoice inv = Invoice.Adapter.Get(121122);
inv.Amount = 2.1;
inv.Update(); // <-- column LastUpdate is automatically set.

It is manditory for now that the column name must be “LastUpdate” only.

Generate Slave Code

This button ignores “LastUpdate” and by default inserts “Auto Number Values” in “Insert” method as well, this provides facility to reinsert auto number values correctly in “Slave” databases.

Documentation Code Generation, CodeGen

You can use Erp Objects only when the following conditions are met.

C# Target Language:
Your project’s target language should be C# only and it should support C# 2.0 onwards syntax.

Must have Primary Key:
Every table, or only the tables you access through Erp Objects, must have a “Primary Key” as a single column. Note that you can have some other Unique Index Constraint defined for multi column primary key values, but Erp Objects require every object to be able to be accessed via only one primary key.

LONG AutoNumber Only:
Auto Number Primary Key can only be of type “LONG”, for example, BIGINT in MySQL and in MS SQL. Integer is not accepted.

Only One Primary Key:
If you have any relation table between two other tables and have two columns that constitute primary key then you should define a new “ID” to be Primary Key and Auto Number and specify a unique index over other two columns.

No Column with name “ID”:
There can not be any column name “ID”, since ID is defined in Erp Objects, you can not specify any column name in table as “ID”, you should always prefix it as “ProductID”, “CustomerID” etc etc.

Documentation ID, Primary Key

Transactions are important parts of modern Business Application and every RDBMS provides inbuilt support for transactions.

In Erp Objects, the transactions are very simple and you do not have to specify anything to initialize, create, commit or rollback. Please observe following code very carefully.

public void TransferFunds(long fromAccountID, long toAccountID, decimal amount)
{
     ErpContext.ExecuteTransaction(
          delegate()
          {
               BankAccount fromAct =
                    BankAccount.Adapter.Get(fromAccountID);
               BankAccount toAct =
                    BankAccount.Adapter.Get(toAccountID);
               if(fromAct.Amount < amount)
                    throw new Exception("Insufficient funds.");
                    // above exception automatically rollbacks
                    // the current transaction
               fromAct.Amount -= amount;
               fromAct.Update();
               toAct.Amount += amount;
               toAct.Update();
               // successful processing commits the transaction
          }
     );
}

The logic here is pretty simple, all you need to do is, just execute all methods inside a delegate passed through “ErpContext.ExecuteTransaction”, however you practically never access any transaction at all, but dont worry, it works correctly. Any part of code inside delegate from anywhere raises any exception, then the transaction is automatically rollbacked. If you want to rollback the transaction then you can simply throw any exception, however your thrown exception will also be thrown further in the stack so that your top most exception catcher can get this exception. But dont worry, the transaction is rollbacked too.

Successful execution of the statements inside delegate automatically commits the transaction at the end of it.

Other overload of Execute Transaction method provides some parameters which can be used to isolate the transaction accordingly however that has nothing to do with Erp Objects, as it is entirely and independently passed through the underlying database provider.

Documentation Transaction

Currently Erp Objects offers database connectivity for following database servers.

1. MS SQL SERVER 2005 onwards (default)
2. MySQL 3.23 onwards

If you do not specify any provider type to be used in your code, MS SQL will be used as default.

However you can change the provider at runtime as well. Consider following cases.

Initializing app.config or web.config

You can initialize connectivity by specifying following lines in your app.config or web.config files respectively for your projects.

<appSettings>
<add key="Erp.Objects.CodeDOM.Class" 
value="Erp.Objects.CodeDOM.SqlCodeDOMProvider"/>
</appSettings>

<connectionStrings>
<add name="ApplicationConnectionString" 
connectionString=".. fill your values.."/>
</connectionStrings>

Default name of ConnectionString is reserved to be “ApplicationConnectionString”, because it is assumed that most of the time you will be connecting to only one database throught your application life time. At run time you can still define different connection and you will learn it below.

Please note, there are two values for key “Erp.Objects.CodeDOM.Class“,

1. "Erp.Objects.CodeDOM.SqlCodeDOMProvider", connects to MS SQL Database

2. "Erp.Objects.CodeDOM.MySqlCodeDOMProvider", connects to MySQL Database

It is important that you must provide this value correctly, otherwise if you assume just specifying mysql database connection string, it will not connect correctly.

Initialization in Application or Global.asax init methods

In your WinForm or XAML application, at some point just after the Main method gets called, or in Global.asax in case of Web Application, you can use following code to initialize Provider.

For MS SQL Database,

Using Erp.Objects.CodeDOM;

SqlCodeDOMProvider.Provider =
   new SqlCodeDOMProvider("connectionString");

For MySQL Database,

Using Erp.Objects.CodeDOM;

SqlCodeDOMProvider.Provider =
   new MySqlCodeDOMProvider("connectionString");

Please note, SqlCodeDOMProvider.Provider <– is the static provider used all accorss your code. MySqlCodeDOMProvider is derived from SqlCodeDOMProvider and it just provides some syntactical differences.

Please note, this method should only be used first time, from next time, if you want to use the different connection just for small amount of time, you must use next method.

Execute Remote Connection

Well I didnt get any great name of this topic so I just left it to be “Execute Remote Connection” which means that the provider you specified in the methods above, the static one, “SqlCodeDOMProvider.Provider” is the provider used for your entire application and it is assumed to be the local provider for your entire application. But assume you might want to connect to some different database (it is thats why assumed to be “Remote database”, although it may be anywhere, but just for convention we will call it “Remote”). Then following is the method to do it.

private void ProcessRemoteStatements()
{

     // Note that, the SqlCodeDOMProvider.Provider
     // is used for following code

     Product pl = new Product();
     pl.ProductName = "plocal";
     pl.Price = 1.2;
     pl.Insert();

     SqlCodeDOMProvider remoteProvider =
          new SqlCodeDOMProvider( "some remote db" );
     ErpContext.ExecuteRemoteConnection(
          remoteProvider,
          delegate()
          {
               // new provider is used between these braces only
               Product p = new Product();
               p.ProductName = "P1";
               p.Price = 2.3;
               p.Insert();

          }
     );

     // from now onwards, the default provider is used...
}

As you can use any number of statements inside the delegate for “ExecuteRemoteConnection” method, all the database related calls will be routed through the specified provider only.

Documentation Connectivity, MS SQL

Installation of Erp Objects is fairly simple, just run the installer and it binds “.erd” files to Erp Objects Editor. And “C:\Program Files\NeuroSpeech Technologies\Erp Objects\” here in this folder, you will find two important files.

1. Erp.Objects.Dll
2. NeuroMySql.Data.dll

The NeuroMySql.Data.dll is replacement for MySql.Data.dll due to an annoying bug that when Date Time is defined null, MySql throws exception instead of returning DateTime.MinVal.

Documentation Editor, Installation, MySQL, NeuroMySQL