0

In a seperate post, Is it necessary to add a @ in front of an SqlParameter name?, a discussion is had about prefixing the ParameterName with the "@" sign.

If you are abstracting all of your ADO access behind interfaces such as IDbCommand and using IDbCommand.CreateParameter() to return instances of IDbDataParameter, is it still correct to prefix the ParameterName with "@".

My gut feeling is no, since the @ is required by SqlServer and the point of using interfaces to remove the implementation details.

I'd also suggest this is perhaps why the undocumented feature of automatically checking for the prefix character exists, if you are only using ADO.NET via interfaces and are removed from knowing exactly what kind of database you are using ?

Community
  • 1
  • 1
nrjohnstone
  • 778
  • 10
  • 17
  • You will still need to prefix that @ sign. – Rahul May 19 '14 at 01:44
  • 1
    If you look at Microsoft's [Data Access Block](http://daab.codeplex.com/) which has provided abstraction using the `System.Data.Common` base classes for years, they include a provider specific abstract method `BuildParameterName` (or similar) that is always called by all implementations, and allow a prefix char or any other provider specific formatting to be applied to the parameter name if needed. – mdisibio May 19 '14 at 16:13
  • I disagree, if I prefix the @ sign but the underlying concrete instance is an Oracle database provider, it will spit the dummy since Oracle uses a different character (# I think) to identify named parameters – nrjohnstone May 21 '14 at 21:57
  • @mdisibio can you please submit your comment as the answer as this is bang on I think. Excellent, I missed that abstraction completely. Thankyou – nrjohnstone May 21 '14 at 22:00

1 Answers1

0

Simply as a demonstration that you need to take this little detail into account even when abstracting, if you look at Microsoft's Data Access Block which for years has provided ADO abstraction using the System.Data.Common base, you'll see that they address this very issue by including virtual method in the abstract base class Database that is then overridden by the provider specific derived classes.

So the base class Database.cs has this method:

/// <summary>Builds a value parameter name for the current database.</summary>
/// <param name="name">The name of the parameter.</param>
/// <returns>A correctly formated parameter name.</returns>
public virtual string BuildParameterName(string name){ return name; }

(if the provider uses positional parameters or has no need of a prefix, there is nothing more to override)

and then the SqlClient specific provider implementation SqlDatabase.cs overrides it as such:

/// <summary>Gets the parameter token used to delimit parameters for the SQL Server database.</summary>
protected char ParameterToken{ get { return '@'; } }    

public override string BuildParameterName(string name)
{
    if (name == null) throw new ArgumentNullException("name");
    if (name[0] != ParameterToken)
        return name.Insert(0, new string(ParameterToken, 1));

    return name;
}

Notice that this implementation allows the calling code to use sql parameter names with the '@' prefix or not, thus freeing the devs from having to know/remember what the api actually does to the name under the covers.

I don't use the DAAB directly, but their overall approach to abstracting behind the System.Data and System.Data.Common interfaces and classes is a great guideline for small data access api's.

mdisibio
  • 3,148
  • 31
  • 47