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,
 
Back
Top Bottom