473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SqlParameters

Gav
Hi All,

Can somebody tell me the advantage of using SqlParameters over simple
putting the paratmeters in the sql string: ie

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = " +
nSomeNumber.ToString();
SqlCommand ....etc
}

vs

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = @Number";
SqlCommand ....etc
SqlParameter prmNumber = new SqlParameter (...etc (well you know how the
rest goes anyway))

}

Cheers
Gav

PS if I'm using SqlParameters how easy is it to change the values and rerun
the command?
Nov 15 '05 #1
4 6280
Gav,

By using the parameters, you get a number of things:

- Type safety
- Length checking
- Proper conversion to the representation of the type in the query language
- Guards against SQL injection attacks

All of these things make paramters a much better alternative. If you
are going to use parameters, changing the values is a snap, just change the
Value property on the parameter to the new value, and execute the command.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Gav" <sp**@spam.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
Hi All,

Can somebody tell me the advantage of using SqlParameters over simple
putting the paratmeters in the sql string: ie

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = " +
nSomeNumber.ToString();
SqlCommand ....etc
}

vs

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = @Number";
SqlCommand ....etc
SqlParameter prmNumber = new SqlParameter (...etc (well you know how the
rest goes anyway))

}

Cheers
Gav

PS if I'm using SqlParameters how easy is it to change the values and rerun the command?

Nov 15 '05 #2
Gav
Great info, thanks for the reply.

Regards
Gav

"Nicholas Paldino [.NET/C# MVP]" <mv*@spam.guard.caspershouse.com> wrote in
message news:OV**************@TK2MSFTNGP11.phx.gbl...
Gav,

By using the parameters, you get a number of things:

- Type safety
- Length checking
- Proper conversion to the representation of the type in the query language - Guards against SQL injection attacks

All of these things make paramters a much better alternative. If you
are going to use parameters, changing the values is a snap, just change the Value property on the parameter to the new value, and execute the command.

Hope this helps.

--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"Gav" <sp**@spam.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
Hi All,

Can somebody tell me the advantage of using SqlParameters over simple
putting the paratmeters in the sql string: ie

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = " +
nSomeNumber.ToString();
SqlCommand ....etc
}

vs

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = @Number"; SqlCommand ....etc
SqlParameter prmNumber = new SqlParameter (...etc (well you know how the
rest goes anyway))

}

Cheers
Gav

PS if I'm using SqlParameters how easy is it to change the values and

rerun
the command?


Nov 15 '05 #3
In addition to what Nic mentioned, I believe that they allow you to use a
cached execution plan as well.
"Gav" <sp**@spam.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
Hi All,

Can somebody tell me the advantage of using SqlParameters over simple
putting the paratmeters in the sql string: ie

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = " +
nSomeNumber.ToString();
SqlCommand ....etc
}

vs

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = @Number";
SqlCommand ....etc
SqlParameter prmNumber = new SqlParameter (...etc (well you know how the
rest goes anyway))

}

Cheers
Gav

PS if I'm using SqlParameters how easy is it to change the values and rerun the command?

Nov 15 '05 #4
William,

Actually, calling the Prepare method will cause the execution plan to be
cached.
--
- Nicholas Paldino [.NET/C# MVP]
- mv*@spam.guard.caspershouse.com

"William Ryan" <do********@comcast.nospam.net> wrote in message
news:uS**************@TK2MSFTNGP10.phx.gbl...
In addition to what Nic mentioned, I believe that they allow you to use a
cached execution plan as well.
"Gav" <sp**@spam.com> wrote in message
news:OR**************@TK2MSFTNGP11.phx.gbl...
Hi All,

Can somebody tell me the advantage of using SqlParameters over simple
putting the paratmeters in the sql string: ie

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = " +
nSomeNumber.ToString();
SqlCommand ....etc
}

vs

Getsomething(int nSomeNumber)
{
string sSqlStatement= "Select * From SomeTable Where index = @Number"; SqlCommand ....etc
SqlParameter prmNumber = new SqlParameter (...etc (well you know how the
rest goes anyway))

}

Cheers
Gav

PS if I'm using SqlParameters how easy is it to change the values and

rerun
the command?


Nov 15 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: nbohana | last post by:
I am trying use the following code to update sql. The problem is it only works once, then I get a message that the '@Param1' can only be used once. I need to know how to reuse these things or...
1
by: Tim::.. | last post by:
Can someone please tell me how I build an array with all my SQLParameters! I want to do something like the example shown below... (" I know it doesn't work!") I would like to generate all the...
1
by: Tim::.. | last post by:
Is there an easy why to build this in a loop rather than having to declare every single SQLparameter, value etc etc I would be really grateful for any advice! Thanks ... CODE ...
1
by: Patrick Olurotimi Ige | last post by:
Is it possible to pass a UserID parameter to a SProcedure and retrieve only the fields the UserID is associated with. I have a table with fields for example:- userid | Code 1 | erf344 2 ...
10
by: Patrick Olurotimi Ige | last post by:
I have a checkbox and i want to input Char "Y" or "N" to the Table In C# we could use for example :- ptrTest.Value = chkYN.Checked ? "Y" : "N"; Whats the equivalent in VB.NET?
2
by: tshad | last post by:
I have an SqlParameter array that I want to reuse after I have used it. For example, I have the following code that calls my generic db routines: ***********************************************...
5
by: Patrick.O.Ige | last post by:
I have a parameter below and i'm passing the value via Store procedure Cmd.Parameters.Add(New SqlParameter("@ProductID", SqlDbType.Int, 1)).Value = 104 But as you can see the value "104" is hard...
2
by: John Kotuby | last post by:
Hi All, I am trying to move my experience with VB6 and ADO over to VB.NET. I am having difficulty with assigning values to SQL parameters. In classic ADO a value could be assigned to a parameter...
2
by: Dynamo | last post by:
Hi there, I have a curious problem I can't seem to solve. I have a list box that gets filled with data, the user selects an item in this list, then a form view displays the full details from...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.