named parameters

  • Thread starter Thread starter A.J
  • Start date Start date
A

A.J

As I got to know that name parameters are used in sql server and oledb
data provider uses question mark (?) literal. Could you please give me
detailed information regarding these? How they work, when they are used
and why we use them.
 
ALthough you can technically use Named parameters in OleDb - I belive that
they still are resolved by the order in which they were added so the real
benefit of using named params isnt' there.

For SqlClient though - it's simple:

System.String sql = "SELECT * FROM MYTable WHERE FirstName = @FirstName";

SqlCommand cmd = new SqlCommand(sql, ConectionObject);
cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 20);
cmd.Parameters("@FirstName").Value = "William";

HTH,

Bill
 
cmd.Parameters.Add("@FirstName­", SqlDbType.VarChar, 20);
cmd.Parameters("@FirstName").V­alue = "William";
Click to expand...
these statements are not clear could you please provide me with some
articles related to them.
 
AJ,

these statements are not clear could you please provide me with some
articles related to them.

http://www.google.com/

In my opinion was Bill answering you completly correct with a nice answer.
For OleDB as Bill stated is this as well possible.

System.String sql = "SELECT * FROM MYTable WHERE FirstName =?;

OleDbCommand cmd = new SqlCommand(sql, ConectionObject);
cmd.Parameters.Add("", SqlDbType.VarChar, 20);
cmd.Parameters[0].Value = "William";

In my opinion is with this in Addition to Bills message everything showed.

I hope this helps,

Cor
 
AJ - What isn't clear specifically - perhaps with more information I could
be more helpful.. The first line creates a Simple string that will be used
as the CommandText property of the command object. The next line creates a
command object using the commandtext referenced above, and a connection
object.

The next line adds a Parameter to the command's parameters colllection -
first specifying the paramaeter name, then the type, then the size). The
following line sets the value of that parameter.

The example Cor uses is better than mine in that he uses the index - which
will always correspond to the position in which the parameter was added. If
you look at the parameter object though - there are multiple overloads for
it and fortunately you get intellisense with it, but if you can tell us
exactly which item is giving you the confusion, we can probably be of more
help.

Cheers,

Bill

--
W.G. Ryan MVP (Windows Embedded)

TiBA Solutions
www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com
A.J said:
cmd.Parameters.Add("@FirstName­", SqlDbType.VarChar, 20);
cmd.Parameters("@FirstName").V­alue = "William";
Click to expand...
these statements are not clear could you please provide me with some
articles related to them.
 
I am sorry I was not specific to my problem. I am using access as the
database and I am a newbie. Queries are:

1. What is the need of using named parameters or literal (?)
2. In cmd.Parameters.ADD(); is it necessary that the datatype and size
must be same as that in the database.

Things are getting a bit clear. Thanks a lot for assisting me
 
Named parameters let you refernce/add them irrespective of where they appear
in the query. If you did "SELECT * FROM SOMETABLE WHERE FirstField = ? and
SecondField = ?"

Then the first value added to the collection would map to the first ? and
the second to the second.

"SELECT * FROM SOMETABLE WHERE FirstField = @First and SecondField =
@Second"


would allow you to do this
cmd.Parameters.Add("@Second", SqlDbType.Varchar);
cmd.Parameters.Add("@First", SqlDbType.VarChar);

Also, you Can add the datatype and value to add more precision, force strong
typing etc but you don't have to. You may want to, so if the proc is taking
an INT value, you can't pass it the literal "Bill" or something like that
but you don't have to specify type or length. Whenever you have overloads
like that remember that the differences will be filled in with default
values in just about every case.
 
This has been discussed any number of times on this list. See google groups
for the archives. I've also written a number of articles on this as well.
See www.betav.com\articles.htm (handling gazintas and gazoutas).

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
 
Back
Top