c# 在 SqlDataAdapter 中使用 Parameters.AddWithValue [英] c# Using Parameters.AddWithValue in SqlDataAdapter

问题描述

如何将 Parameters.AddWithValue 与 SqlDataAdapter 一起使用.在搜索代码下方.

How can I use Parameters.AddWithValue with an SqlDataAdapter. Below searching codes.

var da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE '%"+txtSearch.Text+"%'", _mssqlCon.connection);
var dt = new DataTable();
da.Fill(dt);

我改写了这样的代码:

SqlDataAdapter da;
da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE '%@search%'", _mssqlCon.connection);
da.SelectCommand.Parameters.AddWithValue("@search",txtSearch.Text);
var dt = new DataTable();
da.Fill(dt);

但失败了.

推荐答案

用于初始化SqlDataAdapter的字符串成为SelectCommand SqlDataAdapter 属性.
您可以使用此代码向该命令添加参数

The string used to initialize the SqlDataAdapter becomes the CommandText of the SelectCommand property of the SqlDataAdapter.
You could add parameters to that command with this code

da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
                        _mssqlCon.connection);
da.SelectCommand.Parameters.AddWithValue("@search","%" + txtSearch.Text + "%");

• 首先，删除参数占位符周围的单引号.
• 二、在Value参数中直接添加通配符添加值

您已要求使用 AddWithValue，但请记住，虽然它是一个有用的快捷方式，但也存在许多缺点并且都有详细记录.

You have asked to use AddWithValue, but remember that, while it is a useful shortcut, there are also numerous drawbacks and all well documented.

• 第一:可以我们已经停止使用 AddWithValue() 了吗?作者讨论了 AddWithValue 如何在您的查询
• 第二:数据访问代码如何影响数据库性能作者提供了强大的性能问题的证据AddWithValue
• First: Can we stop using AddWithValue() already? where the author discuss how AddWithValue could give back wrong results in your queries
• Second: How Data Access Code Affects Database Performance where the author presents evidences of strong performance problems for AddWithValue

因此，没有 AddWithValue 和使用 Object 和 Collection Initializers 语法可以写成

So, the same code without AddWithValue and using the Object and Collection Initializers syntax could be written as

da = new SqlDataAdapter("SELECT * FROM annotations WHERE annotation LIKE @search",
                        _mssqlCon.connection);
da.SelectCommand.Parameters.Add(new SqlParameter
{
    ParameterName = "@search",
    Value = "%" + txtSearch.Text + "%",
    SqlDbType = SqlDbType.NVarChar,
    Size = 2000  // Assuming a 2000 char size of the field annotation (-1 for MAX)
});

而且，上面更简化的单行版本是:

and, an even more simplified and one liner version of the above is:

da.SelectCommand.Parameters.Add("@search",SqlDbType.NVarChar,2000).Value = "%" + txtSearch.Text + "%";

这篇关于c# 在 SqlDataAdapter 中使用 Parameters.AddWithValue的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！