Easy way to avoid sql strings?

Question

In my code I have it everywhere

command.Parameters.Add("@name", DbType.String).Value = name;

Is there an easier way? I would like to do something like

command.command.CommandTextFn("insert into tbl(key,val) values(?, ?);", key, value);

and find out if the key / value is a string or int. I would not mind if I had to use {0}instead?

Answer 1

Use the AddWithValue method :

command.Parameters.AddWithValue( "@name", name );

You can use this in combination with the extension method:

public static SqlCommand CreateCommand(this SqlConnection connection, string command, string[] names, object[] values )
{
     if (names.Length != values.Length)
     {
          throw new ArgumentException("name/value mismatch");
     }

     var cmd = connection.CreateCommand();
     cmd.CommandText = command;
     for (int i = 0; i < names.Length; ++i )
     {
         cmd.Parameters.AddWithValue(names[i], values[i]);
     }

     return cmd;
}

used as

var command = connection.CreateCommand( "insert into tbl (key,val) values(@key,@val)",
                                        new string[] { "@key", "@val" },
                                        new object[] { key, val } );

Answer 2

Using parameterized queries protects your system against SQL injection attacks.

, SQL-, ? , .

, , .

.

Answer 3

- - , SQL-. , !

( ), , , - , , , .

Answer 4

, . .

Answer 5

Try replacing such linq calls with sql. You will get type checking at compile time, and you will no longer need to avoid strings and think about SQL injections.

Example:

Dim newCustomer = New Customer With {.CustomerID = "MCSFT", .CompanyName = "Microsoft", .ContactName = "John Doe", .ContactTitle = "Sales Manager", .Address = "1 Microsoft Way", .City = "Redmond", .Region = "WA", .PostalCode = "98052", .Country = "USA", .Phone = "[phone]", .Fax = Nothing}
db.Customers.Add(newCustomer)
db.SubmitChanges()