First a warning, this is a difficult article which goes really deep inside the .Net machinery so if you don’t get it the first time (or even the second or third time…) don’t worry and come back later. 🙂
For a training session I’ve taught at the end of last year I wanted to demonstrate some subtleties of multi-threading, and more specifically some memory visibility issues that should cause a program to hang.
So I developed a small sample that I expected would be showing the issue, but instead of hanging as expected the program completed!
After manipulating the program further I obtained the behavior I wanted, the program was hanging, but it still didn’t explained why it managed to complete with my original version.
I suspected some JITter optimizations, and indeed it was the case, but I needed more information to completely explain this strange behavior.
As often, the StackOverflow platform was of great help; if you’re curious you can have a look at the original SO thread.
In this article I’ll “build” and explain the issue step by step, trying to make it more understandable than the SO thread which is indeed quite dry.
As you may know event handlers are a common source of memory leaks caused by the persistence of objects that are not used anymore, and you may think should have been collected, but are not, and for good reason.
In this (hopefully) short article, I’ll present the issue with event handlers in the context of the .Net framework, then I’ll show you how you can implement the standard solution to this issue, the weak event pattern, in two ways, either using:
the “legacy” (well, before .Net 4.5, so not that old) approach which is quite cumbersome to implement
the new approach provided by the .Net 4.5 framework which is as simple as it can be
Today if you ever need to consume a web API, or produce one, chances are you’ll need to use JSON.
And there is good reasons why JSON has become so prevalent in the web world:
secondly JSON is less verbose than XML (see my other article on the subject) and can be used for most scenarios where XML was historically used
So whatever the language and platform you’ll use you’ll need a strong JSON parsing component.
In the Java world there is at least two good candidates: Gson and Jackson.
In this article I’ll illustrate how to use Gson: I’ll start with a (not so) simple use case that just works, then show how you can handle less standard situations, like naming discrepancies, data represented in a format not handled natively by Gson or types preservation.
More and more JSON is becoming the data interchange format of the web and even starts to leak outside of this world, replacing XML wherever it can, and there is really good reasons for that.
But often people are driven towards JSON for other reasons, not necessarily bad reasons, but based on more anecdotal facts, like the so-called verbosity of XML.
Indeed this is the argument you’ll hear the most often, e.g. just have a look at this nice comparison of the two formats: the first cons is of course “verbosity“.
And it’s a factual argument: the size gains can be important if your values are small, typically for representing business objects like customers because the markup overhead (all the closing tags) will become important relatively to the carried information (e.g. the names and zip codes of your customers).
But you rarely send big chunk of data in a raw text format as XML or JSON, because nowadays servers and clients (e.g. web browsers) supports live gzipping of the workloads, and use it transparently.
So the size advantage of JSON over XML should reduce because GZIP knows how to factorize redundant information like markups.
At least this seems a reasonable speculation, but while intuition is good hard numbers are better to be definitely convinced and to have a numerical idea of the impact.
So I’ve written a small Javabenchmark that I’ll present, along with its results, in this article.
Sometimes we need to modify some global state, but when we do that it’s critical to ensure we leave things as we’ve found them; you know like when you lift the toilet seat: forgetting to put it down afterwards could get you in trouble!
It’s similar in programming and you need to use the right tools and practices.
In this article I’ll show you how they can be used to scope global state changes in a fluent manner, a pattern I’ve used for years to enhance reusability and readability of this kind of code.
In the first part I’ll illustrate this pattern with a small and hopefully fun example, and then in the second part I’ll describe how it can be applied to other situations like culture settings and Excel development. Continue reading →
Note to pedantic guys: yes Access is not a database engine, only the graphical front-end to Jet/ACE, but we’ll stand with this simplification. 🙂
The sample application (Excel + VBA) and the SQL source code are available in this ZIP archive: VBA_Databases_Source.zip.
If you are an advanced VBA developer who builds applications that manage a non trivial amount of data odds are good you are using an Access database to store them.
If this setup perfectly fits your current needs, you completely master it, you’re not experiencing any issue and your needs won’t evolve in the near future you can skip this article and continue enjoying your system. 😉
Indeed, do you really need a new database management system (DBMS)?
Often the only argument in favor of migrating to other DBMS is they are “better”; while it’s true for some technical capabilities, it may not be regarding other “metrics” like simplicity: Access is easy to understand and manage for non IT staff and is often installed with default business workstation setup along with the rest of the Office suite.
So let’s say you have strong reasons to migrate to a new DBMS because you’ve come to the point where you feel you need support for at least one of the following features: interoperability, big storage, high concurrency (hundreds of users) and/or high performance, and Access starts to become a part of the problem.
So what can you do if you want to enhance the quality of your database without making your total cost of ownership (TCO) explode?
Your TCO is essentially made of:
licensing costs: limiting them is quite simple: using a free, usually open-source, database and paying only for support
management costs: they are by far bigger than the licensing costs and are directly impacted by the complexity of the DBMS; so you need a simple DBMS that you’ll be able to setup and manage yourself as you used to do with Access without the help of system or database administrators
development costs: every additional change to your current schema or VBA implementation to fit the new DBMS will have a cost; so we want things to be transparent with zero additional development, which in particular means a standard SQL-based DBMS.
While the equation may seem a little complex it has at least three solutions:
SQLite is the ideal candidate if you’re happy with the “single-file” model, you don’t have high concurrency constraints, and your only needs are interoperability (with Mac OS, Linux, Unix…), bigger storage and/or costs savings,
MySQL and PostgreSQL: if you need support for high-concurrency, really big storage (e.g. tens of GBs), advanced user-management, performance fine tuning and other advanced features you’ll have to jump out of the single-file world.
If you don’t have specific requirements then MySQL and PostgreSQL will appear similar to you and equally do the job. However, in this kind of situation, I have a preference for MySQL, not because its inherent capabilities would be superior (as I’ve said MySQL and PostgreSQL are roughly equivalent for simple setups), but because, as the reference open-source DBMS for years, MySQL benefits from a huge community and toolbox. Moreover, while you’ll sure find the tools to work in good conditions with PostgreSQL, if you ever need to justify your choice to your hierarchy you’ll be in a better position if you choose the standard solution instead of the challenger.
But as I’m not sectarian, and for completeness, I’ll cover both.
In this article I’ll quickly cover the setup of these three DBMS (with links to other resources for more extensive instructions) and illustrate their usage with a small VBA application, a revolutionary todo-list manager, that uses Access too.
This article replaces the previous one with the same title.
Indeed the previous C++/CLI wrapper implementation had a flaw that created memory corruption. This issue has been fixed in the following implementation.
Moreover I’ve used it as an opportunity to greatly enhance the content, especially the description of the C++/CLI wrapper implementation, design rationales being now included.
The article has been updated on the 19th of June 2016 to integrate 3 fixes:
the YahooAPIWrapper‘s destructor is correctly declared in the header file to avoid memory leaks,
the YahooAPIWrapper‘s definition/cpp file does not redefine the class and the __declspec(dllexport) metadata has been moved to the header file to avoid compilation errors,
the YahooAPIWrapper‘s and native C++ program’s implementations have been updated to take into account the new fields names of Yahoo API to avoid runtime exceptions.
When it comes to software development in a professional environment, heterogeneity is the rule not the exception: you often need to interact with systems developed with other technologies.
I’ve been recently faced with such a situation: a team that uses only native C++ needed to retrieve data using the object-oriented API of another team that develops only in .Net with C#.
This is a relatively uncommon scenario (just look at the number of articles on the subject), the standard case being new systems based on the .Net platform, developed in C# or VB.Net, needing to interact with legacy systems developed in native C++.
I’ve used the C++/CLI platform due to its unique ability to mix managed (.Net) and native code in one place and is then the ideal tool for building bridges between these two worlds using simple wrappers: the native face of the wrapper can be consumed by the legacy components and its managed face can directly use the C# API.
In this article I’ll illustrate how I’ve tackled the issue by building a simple C++/CLI wrapper, using a similar use-case: market-data retrieval from Yahoo.
If you have an important VBA code base you know how difficult it is to maintain it, not because VBA is inherently a bad or poor platform but because the developers are often either end-users not aware of good development practices or professional developers not skilled enough in VBA development who have learnt it on the job. In both cases, more often than not, you end up with poor quality source code.
There is other forces that make VBA development less appealing, as the pressure of software vendors like Microsoft who’d like to sell you not just Office but Office+Visual Studio and then promotes other technologies like .Net with languages such as C# and VB.Net. Just have a look at the evolution of the VBA editor design and capabilities since 10 years and you’ll understand that it does not benefit from fanatic development and promotion efforts.
It’s why you should avoid the headlong rush and restrict your VBA development efforts to the bare minimum: for new developments you should consider other languages and platforms like C# and VB.Net with the .Net framework as they seamlessly integrate with the Office suite, with little overhead compared to the direct integration of VBA, and give you access to a wealth of modern technologies. But don’t be fooled by the FUD about the dangers of keeping a legacy VBA code base and do your due diligence: does the guy who suggest you a full migration will do it for free or is he paid for the job? 😉 A full migration may be a necessity: not because the platform is VBA but because the application is buggy, undocumented, out of control and using it creates a true operational risk, and this is true for any application and technology including the newest.
Then, if you have a VBA application that is working perfectly, is documented and controlled, an alternative to both the headlong rush and the full migration is the integration of .Net components with VBA, you then use a future-proof technology to develop new features and/or replace legacy features as you go along, avoiding the big-bang effect.
So now you know what to do and right now I’ll show you how to do this seamless transition from VBA to greener pastures by implementing a simple API with three popular .Net languages: C# (the king), VB.Net (the prince) and C++/CLI (the Jack of all trades, master of none).