CODEWITHSUNDEEP
c#phpsql-serverprepared-statementsqlcommand
ahoo
ahoo

Reputation: 1361

Prepared Statements in SQL-Server through C#

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

Upvotes: 2

Views: 3363

Answers (2)

mirana
mirana

Reputation: 1

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

Upvotes: -1

Marc Gravell
Marc Gravell

Reputation: 1062494

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.

Upvotes: 2

Related Questions