2

i found using of prepared statements in PHP by mysqli_stmt_prepare() Function. what is like it in C# for SQL-Server? i found this code example(using parameterize command). is this what i am looking for?

        SqlConnection conn = new SqlConnection();
        SqlCommand com = new SqlCommand();
        SqlDataAdapter dap = new SqlDataAdapter();
        DataTable tbl = new DataTable();
        SqlParameter param = new SqlParameter();
        conn.ConnectionString = @"Data Source=...";
        com.Connection = conn;
        com.CommandText = "select * from tbl1 where id<@id";
        com.Parameters.AddWithValue("@id",4);
        com.CommandType = CommandType.Text;
        dap.SelectCommand = com;
        conn.Open();
        dap.Fill(tbl);
        conn.Close();
        dataGridView1.DataSource = tbl;

if NO, then what?
if YES, tell me how to using character '?' instead of writing @id in command text.
thanks

ahoo
  • 1,321
  • 2
  • 17
  • 37

2 Answers2

2

SQL Server (at least, via SqlClient) uses named parameters. That code will indeed execute a parameterised query, but a few notes:

  • it hasn't been formally "prepared" (see .Prepare()), but you pretty much never need to anyway
  • several of those objects are IDisposable; you should have usings for them
  • DataTable (and adapter, etc) will work, but is in decline (with mapped classes being preferred, IMO)
  • seeing a DataGridView and a SqlCommand in the same method probably means your UI code is too close to the data access code; I would push the data-access stuff down a level, personally

For example:

DataTable tbl = new DataTable();
using(var conn = new SqlConnection(@"Data Source=..."))
using(var com = conn.CreateCommand())
{
    com.CommandText = "select * from tbl1 where id<@id";
    com.Parameters.AddWithValue("@id",4);
    com.CommandType = CommandType.Text;        

    SqlDataAdapter dap = new SqlDataAdapter();   
    dap.SelectCommand = com;
    conn.Open();
    dap.Fill(tbl);
    conn.Close();     
}
return tbl;

(and bind it to the DataGridView back at the UI)

Of course, if the parameter value is always 4 you could code that into the TSQL directly.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • what is difference between using .Prepare() or not? – ahoo Apr 26 '11 at 06:25
  • @ahoo now that query plan cache/re-use is very good, *not a lot* to be honest; but if you are going to be executing the same command on the same connection in a loop, it cuts out the query-plan lookup. Which frankly is almost zero now anyway. Put it this way: I don't use it. It used to matter more, back in the day. – Marc Gravell Apr 26 '11 at 06:37
  • thanks, but how to using character '?' instead of writing @id in command text – ahoo Apr 26 '11 at 07:13
  • i means '?' placeholder without name – ahoo Apr 26 '11 at 07:15
  • @ahoo yes; and I'm telling you that *it doesn't work that way* with SqlClient; it uses named parameters. – Marc Gravell Apr 26 '11 at 07:16
-1

Yes, but it is no way to use '?' mark.

mirana
  • 1