Fluent interfaces in C#

Question

I have a question with fluent interfaces.

We have some objects that are used as parameter objects for a SQL interface, here's an example:

using (DatabaseCommand cmd = conn.CreateCommand(
    "SELECT A, B, C FROM tablename WHERE ID = :ID",
    SqlParameter.Int32(":ID", 1234)))
{
    ...
}

For some of these parameters, I'd like to enable some specialized options, but instead of adding more properties to the Int32 method (which is just one of many), I thought I'd look into fluent interfaces.

Here's an example where I've added what I am looking into:

SqlParameter.Int32(":ID", 1234).With(SqlParameterOption
    .Substitute
    .Precision(15)
)

I know these two options doesn't make sense for this type of parameter, but that's not what the question is about.

In the above case, Substitute would have to be a static property (or method if I just add some parenthesis) on the SqlParameterOption class, whereas Precision would have to be an instance method.

What if I reorder them?

SqlParameter.Int32(":ID", 1234).With(SqlParameterOption
    .Precision(15)
    .Substitute
)

Then Substitute would have to be the instance property and Precision the static method. This won't compile of course, I can't have both a static and a non-static property or method with the same name.

How do I do this? Am I completely on the wrong track here?

While re-reading the question, I had an idea, would this different syntax below make more sense?

SqlParameter.Int32(":ID", 1234).With
    .Precision(15)
    .Substitute

In this case both would be instance methods on whatever With returns, which would be a specialized class or interface for SqlParameter options like this. I'm not sure I'd like to dump the .With part, as this would expose all methods of the object, instead of just the fluent ones.

Advice and some good url's would be most welcome, I've scoured over many examples, but they tend to show examples like this:

order
    .AddFreeShipping()
    .IncludeItem(15)
        .SuppressTax();

(lifted from this page)

---

Edit: Followup after responses From @marxidad:

class SqlParameterOption
{
    public SqlParameterOption Precision(int p) {/* ... */; return this;}
    public SqlParameterOption Substitute() {/* ... */; return this;}
    /* ... */       
}

/* ... */
SqlParameter.Int32(":ID", 1234).With(new SqlParameterOption()
                                           .Precision(15)
                                           .Substitute());

With this approach, With would have to take the object, and apply it to the parameter. I'm fine with that.

If I used the syntax I added as an example, it would be like this:

SqlParameter.Int32(":ID", 1234).With
                               .Precision(15)
                               .Substitute());

In this case, With wouldn't know when the chain ended, so each option would have to apply its effect directly.

What is preferred? That the options build up an effect object that will have to be applied later, or that each effect applies its effect directly?

My decision: As @marxidad says, if the changes are irreversible, and could potentially be subject to a reversal, building up state and failing at some point with an exception is the way I'll go.

However, in this case, I'm going with a simpler approach that modifies the SqlParameter object directly.

In this case, my code will look like this:

SqlParameter.Int32(":ID", 1234).With
                               .Precision(15)
                               .Substitute());

Edit: Gah, that's how it goes when I focus on just one thing.

I can't use that syntax, I'll go with the following, as suggested by @marxidad:

SqlParameter.Int32(":ID", 1234).With(new SqlParameterOption()
                                           .Precision(15)
                                           .Substitute());

The reason is of course that the method that takes the SqlParameter object as an argument is unable to cope with the object returned by With, so although the SqlParameter object is constructed and set up properly, it became incompatible with the intended usage.

Answer 1

SqlParameterOption's methods can all be instance methods that returns the same object:

class SqlParameterOption
 {
    public SqlParameterOption Precision(int p) {/* ... */; return this;}
    public SqlParameterOption Substitute() {/* ... */; return this;}
    /* ... */       
 }

/* ... */
SqlParameter.Int32(":ID", 1234).With(new SqlParameterOption()
                                           .Precision(15)
                                           .Substitute());

Re: building up state to be applied later vs. applying directly with each call, if there's no real irreverisible side-effects in either case, then it doesn't matter and it's up to your personal taste. If the options are commited with each method call and there's a chance you might want to undo that, then you might want to build up the state first and then apply it. If the parameter object does validation between properties for you as you apply them then it might be better to go with direct application so you'll get validation feedback right way.

Answer 2

You can have overloaded methods though. For example, if it was Substitute(). You can't normally have both static and instance versions of a method, but extension methods might be of some use... but if the two versions of Substitute have different meanings, it would be cleaner to simply have different types being returned, so that the two variants of Substitute() can't conflict.