SqlCommand Parameters vs String.Format

Question

I searched on the Internet, but I just can’t find anything that explains my question (maybe I am not using the correct search bar), so I am posting here, hoping that someone can help me with this. (My program is written in C # using Visual Studio 2010)

I noticed that in C # there are several ways to build an SQL command.

SqlConnection connection = GetAndOpenConnection(); //function containing connection string and open connection
SqlCommand command = connection.CreateCommand();

Until this moment I have no questions. I have a problem with CommandText. I use several different commands in my code ( SELECT/ INSERT/ UPDATE/ DELETE), but it allows SELECTfor example.

//Example 1:
command.CommandText = String.Format("SELECT * FROM myTable WHERE name = '{0}'", "bob");

//Example 2:
command.CommandText = "SELECT * FROM myTable WHERE name = @myName";
command.Parameters.Add(new SqlParameter("myName", "bob"));

What are the differences between the above examples? (performance wise / structure wise / etc.)

, , , .cs, 2, - , - , , 1, .

/ ? ?

, , 2 .

, 2.

, List<string> names. , 2 , , , .

?

List<string> names = new List<string> {"adam", "bob", "john"};
foreach(string name in names)
{
    command.CommandText = "SELECT * FROM myTable WHERE name = @myName";
    command.Parameters.Add(new SqlParameter("myName", name));
    reader = command.ExecuteReader();

    while(reader.Read())
    {
        //loop through each cell and print on the Console
    }
}

, , , "@myName" "myName". , , , , . "@myName" , "myName" , , 2. .Net 4.0, , .

Answer 1

SQL. , , string.Format, bob TextBox1.Text, 1';DROP TABLE myTable;'.

SQL Injection , , , . , , , , .

- , , , , . , @myName @, , SqlParameter.

. - @myName:

command.Parameters.Add(new SqlParameter("@myName", name));

:

command.Parameters.Clear();

, - .

Answer 2

, () SQL-. , , , ( ).

() SQL-. , , , . , .

Answer 3

1: SQL-

2: SQL

, command.Parameters.Add(new SqlParameter("@myName", "bob"));

Answer 4

All of the above answers are correct, but answer your edit:

You need to get rid of the command after using it at each iteration:

using (var conn = new SqlConnection(connectionString)
{
    conn.open(); //You only need to open the connection once, so we do it outside the loop
    foreach (var name in names)
    {
        using (var cmd = new SqlCommand("SELECT * FROM myTable where name = @MyName", conn)
        {
            cmd.Parameters.AddWithValue("@MyName", name);
            //Do something with the command
        }
        //The command is disposed of here
    }
}
//The connection is disposed of here.