Discussion of article "Integrating MQL-based Expert Advisors and databases (SQL Server, .NET and C#)"

 

New article Integrating MQL-based Expert Advisors and databases (SQL Server, .NET and C#) has been published:

The article describes how to add the ability to work with Microsoft SQL Server database server to MQL5-based Expert Advisors. Import of functions from a DLL is used. The DLL is created using the Microsoft .NET platform and the C# language. The methods used in the article are also suitable for experts written in MQL4, with minor adjustments.

Questions related to integrating the work with databased into Expert Advisors written in MQL5 often appear on the forums. Interest in this topic is not surprising. Databases are very good as a means of saving data. Unlike the terminal logs, the data do not disappear from the databases. They are easy to sort and filter, choosing only the required ones. A database can be used to pass the necessary information to an expert — for example, certain commands. And most importantly — the obtained data can be analyzed from different perspectives and processed statistically. For example, writing a one-line query is enough to find out the average and total profit for a specified time for each currency pair. And now imagine how long it takes to manually calculate this for the account history in the trading terminal.

Unfortunately, MetaTrader does not provide built-in tools for interacting with database servers. The problem can only be solved by importing functions from DLL files. The task is not simple, but feasible.

Start the expert, changing the connection string values to your database server access parameters. If everything is done correctly, the expert will output the following to the log:

2018.07.10 20:36:21.428    MqlSqlDemo (EURUSD,H1)    Connected to database.
2018.07.10 20:36:22.187    MqlSqlDemo (EURUSD,H1)    Created table in database.
2018.07.10 20:36:22.427    MqlSqlDemo (EURUSD,H1)    Data written to table.
2018.07.10 20:36:22.569    MqlSqlDemo (EURUSD,H1)    Number read from database: 1
2018.07.10 20:36:22.586    MqlSqlDemo (EURUSD,H1)    String read from database: Test

Connecting to the database, executing SQL commands, writing and reading data — everything is executed successfully.

Author: Сергей Ткаченко

 
MetaQuotes Software Corp.:

The article Integration of MQL Expert Advisor and Databases (SQL Server, .NET and C#) has been published:

Author: Sergey Tkachenko

Thanks for the article, just recently we discussed on the forum how to connect C# DLL to MQL5 directly. Before that I used a C++ DLL. Sergey, can Entity Framework be used with this technology? Especially CodeFirst is of interest.

 

Unfortunately, I can't say anything about Entity Framework - I've never worked with it. And I have little experience with the relatively new C# and .NET technologies.

If I had to do it, I would try it. Presumably, it will work. Especially if you don't use advanced features inside exported static functions, but wrap them in private functions of some auxiliary classes and apply them there. In one of my projects I used classes from System.Collections.Generic.

 
Сергей Ткаченко:

Unfortunately, I can't say anything about Entity Framework - I've never worked with it. And I have little experience with the relatively new C# and .NET technologies in general.

If I had to do it, I would try it. Presumably, it will work. Especially if you don't use advanced features inside exported static functions, but wrap them in private functions of some auxiliary classes and apply them there. I used classes from System.Collections.Generic in one of my projects.

I see, I have experience, I'll try it if I have a chance. In general, have you noticed any disadvantages of exporting functions from .NET DLL-files to unmanaged code? I mean that Renat F. swears a lot about such things. He gives the most general arguments at the level of scary stories.

Have you noticed the disadvantages? For example, reduced performance, increased memory consumption, etc.

 

We can only talk about the disadvantages in comparison with something else.

If we compare EAs that use export of functions from DLL with those EAs that do not and work on pure MQL - then it all depends on the implementation of a particular EA and what tasks it performs. If there are two EAs that do exactly the same thing - then it is hard to say. I assume that the one that uses DLL will be faster because of better code optimisation by compilers when building DLLs. But that's just an assumption, because I haven't compared directly. I usually did in DLL only what is more difficult or impossible to do in MQL (for example, the work with the database described in the article). Therefore, my Expert Advisors made with and without DLL access performed different tasks.

Expert Advisors using DLLs have one disadvantage - less reliability. At times, though rarely, they sometimes get the error "Access violation at 0x08364576" (memory address digits are different). Robots based on pure MQL do not have such errors. Of course, everything depends on a particular DLL - how it is implemented, how complex it is, whether all potentially dangerous places in terms of memory errors have been checked. But my Expert Advisors have a situation when everything works well for two or three months, and then when restarting one of the five Expert Advisors running on one MT has this error in the log. In the case of pure MQL this does not happen.

If we compare exporting functions from DLLs in C# with exporting from regular DLLs - say, in C++ - then each approach has its own advantages and disadvantages. I made another DLL in C++ and Qt. It was also working with a database, but SQLite, not SQL Server. There were memory access errors too, and much more often than in the .NET DLL. However, if the project is "cleaned up", if pointers to null are checked everywhere where necessary, memory is freed and so on - then maybe the opposite will be more reliable. But in C# it is somehow easier, everything is automatically checked and freed there. I haven't noticed any differences in performance. But, however, my C++/Qt project was not exploited much.

 
Сергей Ткаченко:

The minuses here can only be talked about in comparison to anything else.

If we compare EAs that use export of functions from DLL with those EAs that do not and work on pure MQL - then it all depends on the implementation of a particular EA and what tasks it performs. If there are two EAs that do exactly the same thing - then it is hard to say. I assume that the one that uses DLL will be faster because of better code optimisation by compilers when building DLLs. But that's just an assumption, because I haven't compared directly. I usually did in DLL only what is more difficult or impossible to do in MQL (for example, the work with the database described in the article). Therefore, my Expert Advisors made with and without DLL access performed different tasks.

Expert Advisors using DLLs have one disadvantage - less reliability. At times, though rarely, they sometimes get the error "Access violation at 0x08364576" (memory address digits are different). Robots based on pure MQL do not have such errors. Of course, everything depends on a particular DLL - how it is implemented, how complex it is, whether all potentially dangerous places in terms of memory errors have been checked. But my Expert Advisors have a situation when everything works well for two or three months, and then when restarting one of the five Expert Advisors running on one MT has this error in the log. In the case of pure MQL, this does not happen.

If we compare exporting functions from DLLs in C# with exporting from regular DLLs - say, in C++ - then each approach has its own advantages and disadvantages. I made another DLL in C++ and Qt. It was also working with a database, but SQLite, not SQL Server. There were memory access errors too, and much more often than in the .NET DLL. However, if the project is "cleaned up", if pointers to null are checked everywhere where necessary, memory is freed and so on - then maybe the opposite will be more reliable. But in C# it is somehow easier, everything is automatically checked and freed there. I haven't noticed any differences in performance. But, however, my C++/Qt project was not exploited much.

And another question, maybe you have done such things. Is it possible to launch an interactive control panel from a C# DLL or do you have to make the panel as a separate programme and provide communication with the DLL in some way, for example, through Memory Mapping or WCF?

I am talking about one local computer now.

 

No, I haven't worked with interactive panels, unfortunately. I can only speculate. And I don't quite understand what is meant by "interactive panel".

Is it necessary to open some MetaTrader window? Here I don't know how it could be done or if it can be done at all.

Should I just open some window and receive input from the user? I haven't done this either, but I think it's probably not difficult. Make a class that defines a regular Windows Forms window. In that class, make a static exportable function that creates the window, shows it to the user in dialogue mode, and then releases it. That should work.

 
Amazing article! Exactly what I was looking for! Thank you Sergiey!
 
mark.
 
Yes, very nice work Sergy, much appreciated. 
 

Love the article - thanks very much for the in-depth discussion.


Hoping someone can help me with a snag during Build time.

C:\Users\user\source\repos\mql\MqlSqlDemo\packages\UnmanagedExports.1.2.7\tools\RGiesecke.DllExport.targets(58,3): error : Microsoft.Build.Utilities.ToolLocationHelper could not find ildasm.exe


Essentially, the UnmanagedExports utility is unable to find the disassembler executable required to perform it's work.


I know for a fact that ildasm.exe exists, and it's various locations... but not sure how to get DllExport to recognize the proper path.