Sql command parameters

jay8anks

Active member
Joined
May 6, 2022
Messages
31
Programming Experience
5-10
Stupid question, but I have never been able to find an answer to this. When I first started doing Asp.Net in C# or VB, all the examples to add SQL command parameters look like:

cmd.Parameters.AddWithValue("@Id", rowId);

When going back through my code, I sometimes find I did this: cmd.Parameters.AddWithValue("Id", rowId);

It works just fine. Also, after using Dapper a little lately, a lot of the examples don't add the ampersand, either.

What exactly does the ampersand do with SQL parameters? Is it a .net thing or does it do something in the sql server.

This is yet another one of those things that is hard to search for, because it pulls up all kinds of things except what I'm wondering here.

Thanks,
 
Solution
Stupid question, but I have never been able to find an answer to this. When I first started doing Asp.Net in C# or VB, all the examples to add SQL command parameters look like:

cmd.Parameters.AddWithValue("@Id", rowId);

When going back through my code, I sometimes find I did this: cmd.Parameters.AddWithValue("Id", rowId);

It works just fine. Also, after using Dapper a little lately, a lot of the examples don't add the ampersand, either.

What exactly does the ampersand do with SQL parameters? Is it a .net thing or does it do something in the sql server.

This is yet another one of those things that is hard to search for, because it pulls up all kinds of things except what I'm wondering here.

Thanks,

The ampersand at symbol is a parameterized query, which helps prevent (among other things) against SQL injection.
If you are using Dapper (as you say you are), it automatically parameterizes your query for you.


Dapper Example:
int someMemberID = 3;

string sql = @"
INSERT INTO [dbo].[SomeTableName]          
([member_date],[first_name],[last_name],[email],[phone],[address1],[address2],[city],[state],[zip])
SELECT [t_member_date],[t_first_name],[t_last_name],[t_email],[t_phone],[t_address1],[t_address2],[t_city],[t_state],[t_zip]
FROM [#TempCBIntake]
LEFT OUTER JOIN [dbo].[CBRosterInfo] WITH (NOLOCK) ON [member_date] = [t_member_date]
WHERE [member_key] = @MemberID;
";

var output = conn.Execute(sql, new {MemberID = someMemberID});

Corrected cause I don't want to look like a complete idiot :ROFLMAO:
 
Last edited:
Ampersand: &
Commercial At/At sign: @
 

Internally it will prepend @ to the name if not set.

Given the great lengths that programmers go to in order to save a few keystrokes, it's weird that more examples don't leave them out. Since almost all examples put them in, I thought maybe it did something extra when it was there.

Thanks.
 
The ampersand at symbol is a parameterized query, which helps prevent (among other things) against SQL injection.
If you are using Dapper (as you say you are), it automatically parameterizes your query for you.

Apparently just .net also automatically parameterizes your query for you. And if I needed to worry about this if I ran across it in some old code, was why I asked.

Also, I wasn't sure that the at symbol was what it was really called. I looked it up and wikipedia said:

The absence of a single English word for the symbol has prompted some writers to use the French arobase[2] or Spanish and Portuguese arroba, or to coin new words such as ampersat[3] and asperand,[4]

Somewhere in that, with my spellcheck highlighting everything, asperand got changed to ampersand and I didn't even notice.

When you go out of your way to make an attempt to call something the right name and it is still wrong. !&%^ :)

Thanks.

 
Given the great lengths that programmers go to in order to save a few keystrokes, it's weird that more examples don't leave them out. Since almost all examples put them in, I thought maybe it did something extra when it was there.

Thanks.
You're supposed to put it there according to documentation. The internal "autofix" is not documented, but something I found while researching.
 
You're supposed to put it there according to documentation. The internal "autofix" is not documented, but something I found while researching.

That does explain why I couldn't really turn anything up when DuckDuckGo(ing) on the subject, then. Like I said, a few times I was redoing some old code I wrote and would occasionally run into a line here or there where I forgot the @. I just wondered why it worked, and if it didn't matter, should I worry about it.

Some things with general terminology are a bitcharoonydoony to research with a search engine. For example, Microsoft called dotnet, ".net", you know, a top-level domain.

Thanks,
 
It's behavior particular to SqlClient; other databases support other characters as prefixes to parameter names and do complain if you omit them

Apparently just .net also automatically parameterizes your query for you

I'm not sure this is true; parameterization is something one must do directly and explicitly. No database driver I'm aware of will take an unparameterized query like

C#:
SELECT * FROM T WHERE X = 1

and turn it into

C#:
SELECT * FROM T WHERE X = @x

command.Parameters.Add("@x", 1);

Things like Dapper do help with small details like array expansion where a query like

C#:
SELECT * FROM T WHERE X IN @x

command.Parameters.Add("@x", myArrayOfThreeX);

Becomes

C#:
SELECT * FROM T WHERE X IN (@x0,@x1,@x2)

command.Parameters.Add("@x0", myArrayOfThreeX[0]);
command.Parameters.Add("@x1", myArrayOfThreeX[1]);
command.Parameters.Add("@x2", myArrayOfThreeX[2);

But base level SqlCommand doesn't manipulate the query you give it to add parameters that don't exist supplied by you
 
I'm not sure this is true; parameterization is something one must do directly and explicitly. No database driver I'm aware of will take an unparameterized query like

Absolutely, several time I have been going over some old code and found something exactly like this:

cmd.Parameters.AddWithValue("Id", rowId);

It works without throwing any errors.

That's why I started this thread. I wanted to know why it was working. I couldn't find any info on it, so I thought maybe it was something like a .net verbatim string in some way, where the "@" was signifying something different about the parameter internally on the database server (I use MySQL).

If I was really good, I could probably compile some test code and use Reflector or something to see what it was really doing. I was curious but not that curious.

Thanks,
 
See my post #9 which points to the source code where the at sign is inserted for the user by the .NET Framework for the SQL driver. I'm pretty sure that has also been ported into .NET Core.
 
use Reflector or something
If you haven't used it before, check out Reference Source for .net FW and Source Browser for core, plus these days you'll find the source code for other libs somewhere on GitHub.. no need to decompile so much (but on that note you can also configure VS so it decompiles if you navigate to code that isn't yours/is a member of a compiled reference)

Long story short, you can sometimes get away with skipping parameter placeholder marks because the client lib you're using adds them for you(but you'll have to use them properly in the SQL itself) - skydiver linked to reference source, but on my cell it seem to open at the wrong line. Check out line 877 at Reference Source - it's a "if the param doesn't start with @, add it in",


Side note, why MS wrote "if 0 is less than the name length" rather than "if name length is greater than 0" I've no idea.. Seems a backwards expression of identical logic to me
 
Last edited:
I think I had line 545 correct:
Screenshot_2.png


Line 877 and the code surrounding it looks like this:
Screenshot_1.png


Side note, why MS wrote "if 0 is less than the name length" rather than "if name length is greater than 0" I've no idea.. Seems a backwards expression of identical logic to me
Those are known as Yoda conditions. It's a carry over from C/C++ programmers as a way to prevent accidental assignments by putting the constant to the left of a boolean expression.
 
Back
Top Bottom