If you are comparing two strings for equality then the culture settings don’t make much difference (though it affects, for example, Turkish, which has dotted and undotted i’s).
If you are sorting a list of strings there’s a big difference; different cultures often sort in different orders.
CurrentCulture sorts strings according to, erm, the current culture (i.e. the current locale). So this changes depending on where your software is run.
InvariantCulture is basically US English settings. It’s invariant because it’s the same wherever your software runs.
Ordinal comparisons are based on the values of the Unicode code points. This is usually the best choice for comparing equality, but not a good choice if you are sorting a list of strings to display to the user.