What is the difference between those two SQL command parameter declaration methods?

Question

I am new to SQL and I am using Windows forms and C#.

The following 2 SQL queries work fine with no issues but I am concerned that one of them might cause problems later and I do not know which one is safer to use.

I just want to know what is the difference between the two command parameters deceleration and value assigning in both first and second methods:

The first command parameters deceleration method:

SqlConnection MyConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);

SqlCommand MyCommand = new SqlCommand();
DataTable DataTable = new DataTable();

SqlDataAdapter Sql_Data_Adapter = new SqlDataAdapter();

long PhoneNumber = 07429114523;

MyConnection.Open();
MyCommand.CommandText = "SELECT * FROM List_of_All_Orders WHERE Phone= @PhoneNumber ";
MyCommand.Connection = MyConnection;

// command parameters declaration and value assigning
MyCommand.Parameters.Add("@PhoneNumber", SqlDbType.BigInt).Value = PhoneNumber;

Sql_Data_Adapter.SelectCommand = MyCommand;
Sql_Data_Adapter.Fill(DataTable);

dataGridView1.DataSource = DataTable;            
MyCommand.Parameters.Clear();
MyConnection.Close();

The second command parameters declaration method:

long PhoneNumber = 07429114523;
MyConnection.Open();
MyCommand.CommandText = "SELECT * FROM List_of_All_Orders WHERE Phone= @PhoneNumber ";
MyCommand.Connection = MyConnection;

// command parameters declaration and value assigning
MyCommand.Parameters.Add("@PhoneNumber", SqlDbType.BigInt);
MyCommand.Parameters["@PhoneNumber"].Value = PhoneNumber;

Sql_Data_Adapter.SelectCommand = MyCommand;
Sql_Data_Adapter.Fill(DataTable);

dataGridView1.DataSource = DataTable;

MyCommand.Parameters.Clear();
MyConnection.Close();

Anyone knows which method to choose first or second in terms of command parameters declaration and value assigning and which one is safer.

Please help. Thank you

They are the same, the latter is just more verbose. Note that phone numbers are strings, not integers, so you will lose the leading 0. Save the hassle of calling `.Dispose` later by using a `using () {}` for the connection and command and adapter. — Alex K., Feb 26 '16 at 16:59
@Alex K. I use PhoneNumber = Convert.ToInt64(textBox1.Text); because the phone number wont fit into "int32" . so I have to use "long" . is there other data type apart from "long" to carry 11 digits? thank you — Kate, Feb 26 '16 at 17:10
Yes, a string in C# and a VARCHAR(32) in SQL, you must use a string if you want to keep leading zeros. — Alex K., Feb 26 '16 at 17:11
@AlexK. It's possible that the OP is not dealing with phone numbers that will start with a leading 0. — juharr, Feb 26 '16 at 17:55

score 1 · Answer 1 · answered Feb 26 '16 at 17:06

  MyCommand.Parameters.Add("@PhoneNumber", SqlDbType.BigInt).Value = PhoneNumber;

The one-line version is better, because

Everything is done in a single line,
No risk of misspelling the parameter name in the second line,

But there's still a lot to go wrong.
I would make a little function which does it all for you, and prevents common mistakes:

   // Returns a new BigInt parameter
   public SqlParameter ParamBigInt(string paramName, long v)
   {
       // Fixup parameter name
       string name = paramName.Trim();

       if (!name.StartsWith("@"))
           name = "@" + name;

       // Make the new parameter
       return new SqlParameter(name, SqlDbType.BigInt)
       {
           Value = p;
       };
   }

Then to use it:

  MyCommand.Parameters.Add(ParamBigInt("@PhoneNumber", PhoneNumber));

score -2 · Answer 2 · answered Feb 26 '16 at 17:00

-2

There is no problem with any of the two methods above. In fact there is a shorter syntax where you do not need to worry about the datatype.

MyCommand.Parameters.Add("@PhoneNumber", PhoneNumber);

Imagine you have 20 Parameters you want to Add, you will prefer something that hastens your project completion time

answered Feb 26 '16 at 17:00

mayowa ogundele

475
2
6
19

If you are marking down, at least give the reason of it – mayowa ogundele Feb 26 '16 at 17:26
That overload of `Add` has been marked as obsolete. You probably meant `AddWithValue`, but there are reasons to not use that as well http://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/ – juharr Feb 26 '16 at 18:04

What is the difference between those two SQL command parameter declaration methods?

2 Answers2