dosc9472
dosc9472
2011-04-26 05:13
浏览 58

通过C#在SQL-Server中准备语句

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

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

2条回答 默认 最新

  • dongshicuo4844
    dongshicuo4844 2011-04-26 05:21
    已采纳

    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.

    点赞 评论
  • doujiao6872888
    doujiao6872888 2013-02-08 21:50

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

    点赞 评论

相关推荐