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

发布于 2024-11-03 11:25:40 字数 805 浏览 5 评论 0原文

我发现通过 mysqli_stmt_prepare() 函数在 PHP 中使用准备好的语句。 SQL-Server 的 C# 是什么样的？我找到了这个代码示例（使用参数化命令）。这就是我要找的吗？

        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;

如果否，那么怎么办？
如果是，请告诉我如何使用字符“？”而不是在命令文本中写入@id。
谢谢

原文

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

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

一曲爱恨情仇 2024-11-10 11:25:40

SQL Server（至少通过 SqlClient）使用命名参数。该代码确实会执行参数化查询，但有一些注意事项：

它尚未正式“准备好”（请参阅.Prepare())，但您几乎永远不需要
这些对象中的几个是 IDisposable;您应该为它们使用 using
DataTable （和适配器等）将工作，但正在下降（首选映射类，IMO )
在同一方法中看到 DataGridView 和 SqlCommand 可能意味着您的 UI 代码与数据访问代码太接近；我个人会将数据访问内容推低一个级别，

例如：（

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;

并将其绑定到 UI 上的 DataGridView）

当然，如果参数值始终为 4 code> 您可以直接将其编码到 TSQL 中。

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.

回复收藏 0 原文