CODEWITHSUNDEEP
c#vb.netado.net
Jason Bayldon
Jason Bayldon

Reputation: 1296

Clarifying SQL and ADO.net Literal Parameter specifications

So I am reading a book on ADO, and I have reached the end of the chapter describing parameters and queries in ADO. In the book it provides the following code examples to pass a parameter to SQL server:

Dim paramValue As New SqlParameter("@NewSalary", SqlDbType.Money)
paramValue.Value = 50000@
salaryUpdate.Parameters.Add(paramValue)
paramValue = New SqlParameter("@EmployeeID", SqlDbType.BigInt)
paramValue.Value = 25&
salaryUpdate.Parameters.Add(paramValue)

salaryUpdate.Parameters.AddWithValue("@NewSalary", 50000@)
salaryUpdate.Parameters.AddWithValue("@EmployeeID", 25&)

For C# it shows 

salaryUpdate.Parameters.AddWithValue("@NewSalary", 50000m);
salaryUpdate.Parameters.AddWithValue("@EmployeeID", 25L);

What the book doesnt really go into is why the values being defined have and M,L,@,& characters appended to them. Why is this? And the difference between vb and c# is perhaps just syntax?

Tried doing some research on MSDN, but these types of examples dont appear there, or so it seems. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx

Thanks for any clarification

Upvotes: 2

Views: 260

Answers (2)

Steve
Steve

Reputation: 216313

In VB.NET and C# you could add a postfix character to a constant value to explain its datatype. It is a leftover from the VB6 days to help with portability issues. Nowadays you should try to use an explicit constant Const NAME As Type = value declaration and use the suffix only when needed.

See VB.NET Type Characters on MSDN

For the AddWithValue vs the new SqlParameter syntax the latter is preferable because you could exactly select the datatype and the size of the parameter passed to the database engine. In this way the database engine could better optimize the query and reuse the already prepared statement when you reexecute it. However the AddWithValue has its advantages in the simplicity and ease of use. So if you don't have worries about performance you could also use it.

By the way, the two syntax could be used in VB.NET and C#. They are part of the NET library that could be called from every managed language

Upvotes: 4

Mez
Mez

Reputation: 4726

When adding the parameter value, it depends on the data type being specified in the parameter. A normal example would be like the one which you can find on MSDN http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.parameters.aspx.

So with regards to the extra M,L,@,& you do not need them any longer. Whenever I used AddWithValue, or adding a parameter, I always pass the values in double quotes.

Upvotes: 0

Related Questions