c# 在 SqlDataAdapter 中使用 Parameters.AddWithValue

c# Using Parameters.AddWithValue in SqlDataAdapter

提问by

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

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

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

I rewrote the code like this:

我重写了这样的代码：

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);

but it failed.

但它失败了。

采纳答案by Steve

The string used to initialize the SqlDataAdapterbecomes the CommandTextof the SelectCommandproperty of the SqlDataAdapter.
You could add parameters to that command with this code

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

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

• First, remove the single quote around the parameter placeholder.
• Second, add the wildcard character directly in the Value parameter of AddWithValue

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

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，但请记住，虽然它是一个有用的快捷方式，但也有许多缺点并且都有详细记录。

• First: Can we stop using AddWithValue() already?where the author discuss how AddWithValuecould give back wrong results in your queries
• Second: How Data Access Code Affects Database Performancewhere the author presents evidences of strong performance problems for AddWithValue

• 第一：我们可以停止使用 AddWithValue() 了吗？作者讨论AddWithValue如何在您的查询中返回错误结果的地方
• 第二：数据访问代码如何影响数据库性能，其中作者提出了AddWithValue存在严重性能问题的 证据

So, the same code without AddWithValueand using the Object and Collection Initializerssyntax could be written as

因此，没有AddWithValue并使用Object 和 Collection Initializers语法的相同代码可以写成

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 + "%";

回答by mis2000lab

Try this:

尝试这个：

mySearchString = "Select * From test Where ([title] LIKE '%' + @title + '%')";
cmd.Parameters.Add("@title", SqlDbType.VarChar, 120);
cmd.Parameters("@title").Value = TextBox1.Text;

回答by dragonal

I use Repeater for show data

我使用中继器显示数据

int queryString =int.Parse(Request.QueryString["Id"]);

SqlConnection conn =new SqlConnection("server=.; Database=Northwind; 
Integrated Security=true;");

try{
conn.Open();

SqlDataAdapter dataAdapter = new SqlDataAdapter("SELECT ProductID, ProductName, UnitPrice, CategoryID FROM Products WHERE CategoryID =@CategoryID", conn);

dataAdapter.SelectCommand.Parameters.Add("@CategoryID", queryString);
                        DataSet dataSet = new DataSet();
                        dataAdapter.Fill(dataSet);
                        QueryStringProductListRepeater.DataSource = dataSet;
                        QueryStringProductListRepeater.DataBind();
}

catch{
Response.Write("QueryStringProductListRepeater");
}

finally{
conn.Close();
}

回答by Mohammad Musavi

Use da.SelectCommand.Parameters.Add()instead of cmd.Parameters.Add(), here's a sample for dealing with a stored procedure which takes two parameters and second one is a nullable int parameter:

使用da.SelectCommand.Parameters.Add()代替cmd.Parameters.Add()，这里有一个处理带有两个参数的存储过程的示例，第二个是一个可为空的 int 参数：

public DataTable GetData(int par1, int? par2)
{
    using (SqlConnection conn = new SqlConnection(connectionString))
    {
        using (SqlDataAdapter da = new SqlDataAdapter())
        {
            string sql = "StoredProcedure_name";
            da.SelectCommand = new SqlCommand(sql, conn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            da.SelectCommand.Parameters.Add("@Par1", SqlDbType.Int).Value = par1;
            da.SelectCommand.Parameters.Add("@Par2", SqlDbType.Int).Value = (object)par2?? DBNull.Value;

            DataSet ds = new DataSet();
            da.Fill(ds, "SourceTable_Name");

            DataTable dt = ds.Tables["SourceTable_Name"];

            //foreach (DataRow row in dt.Rows)
            //{
            //You can even manipulate your data here
            //}
            return dt;
        }
    }
}