必须声明标量变量/变量已经声明

Question

我想创建一个函数，它接受一个查询并且它的参数并返回一个SQLDataSource进一步绑定。

功能是：

public static SqlDataSource getSqlSource(string sql, Dictionary<string, string> parameters) 
    { 
     SqlConnection con = new SqlConnection(ConnectionString); 

     using (SqlCommand cmd = con.CreateCommand()) 
     { 
      con.Open(); 
      cmd.CommandText = sql; 
      foreach (KeyValuePair<string, string> item in parameters) 
      { 

       cmd.Parameters.Add(new SqlParameter(item.Key, item.Value)); 

      } 
      SqlDataSource source = new SqlDataSource(ConnectionString, cmd.CommandText); 

      con.Close(); 

      return source; 
     } 
    } 

我试图创建的查询是：

string sql = 
      // "declare @from date ; " + 
      "with cte as " + 
"(" + 
"select username,count(username) as cc from SaleReport " + 
"group by username " + 
") " + 
"select count(SaleReport.username) as numerKlientesh,distributorname from SaleReport " + 
"inner join cte on SaleReport.username=cte.username " + 
"where (SaleReport.saledate) <=CONVERT(date,@from) " + 
    "and (SaleReport.saledate)>=(dateadd(DAY,-10,CONVERT(date,@from))) " + 
"and cc>1 and SaleReport.comboid not in (43,44,46,47) " + 
"group by distributorname "; 
     Dictionary<String, String> Params = new Dictionary<String, String>(); 
     Params.Add("@from", from); 
     SqlDataSource source = QueryManager.getSqlSource(sql, Params); 
     GridView1.DataSource = source; 
     GridView1.DataBind(); 

当我运行应用程序，我得到以下错误：

"Must declare the variable @from"

但是，当我尝试声明它时（我已将该声明作为注释），我收到以下消息：

The variable name '@from' has already been declared.

Variable names must be unique within a query batch or stored procedure.

我在查询中做错了什么？

EDIT1： 随着SabinBio的建议，我已经输出的查询的结果如下：

declare @from date; 
with cte as (
    select username 
     ,count(username) as cc 
    from SaleReport 
    group by username 
    ) 
select count(SaleReport.username) as numerKlientesh 
    ,distributorname 
from SaleReport 
    inner join cte on SaleReport.username=cte.username 
where (SaleReport.saledate) <= CONVERT(date,@from) 
    and (SaleReport.saledate) >= (dateadd(DAY,-10,CONVERT(date,@from))) 
    and cc>1 
    and SaleReport.comboid not in (43,44,46,47) 
group by distributorname 

EDIT2

如果我把查询在存储过程中，我把它称为：

DECLARE @return_value int EXEC @return_value = [dbo].[ProcedureName] @from ="+from+" 

它工作正常，bu T I想避免可能的SQL注入，如果我宣布它想：

DECLARE @return_value int EXEC @return_value = [dbo].[ProcedureName] @from [email protected] 

，并把saledate在我的字典中的项目还是有错误

Must declare the variable @saledate

Answer 1

研究了一段时间后，我改变了功能：

public static SqlDataSource getSqlSource(string sql, Dictionary<string, string> parameters) 
{ 
    SqlConnection con = new SqlConnection(ConnectionString); 
    SqlDataSource source = new SqlDataSource(ConnectionString, sql); 

    foreach (KeyValuePair<string,string> pair in parameters) 
    { 

     source.SelectParameters.Add(pair.Key, pair.Value); 
    } 

    return source; 
} 

，并宣布在代码字典时落后，关键项目不应该用“@”字符宣布，将“@”字符有只有在查询中声明，那么，该声明是：

Dictionary<String, String> Params = new Dictionary<String, String>(); 
    Params.Add("from", from); 

建议采取HERE 谢谢大家的帮助:)

Answer 2

你在错误的有cmd.ExecuteReader()发生在这里：

 foreach (KeyValuePair<string, string> item in parameters) 
     { 

      cmd.Parameters.Add(new SqlParameter(item.Key, item.Value)); 

      cmd.ExecuteReader(); 

     } 

我希望这需要像：

 foreach (KeyValuePair<string, string> item in parameters) 
     { 

      cmd.Parameters.Add(new SqlParameter(item.Key, item.Value)); 

     } 
     cmd.ExecuteReader();