日期:2014-05-18  浏览次数:21290 次

将参数值从 SqlParameter[] 转换到 String 失败,对象必须实现 IConvertible。哪里错了啊
public IList<PetShop.Model.Product> GetProductsBySerch(string[] keywords)
  {
  //定义模糊查询的sql语句,一般模糊查询的sql语句都由4部分组成,一下为范例
  string sql1 = "select productid, categoryid, name, descn, image where ((";
  //{0}是占位符
  string sql2 = "categoryid like '%'+{0}+'%' or name like '%'+{0}+'%'";
  string sql3 = ")or(";
  string sql4="))";

  string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ssa"].ConnectionString;

  string parm_keyword = "@Keyword";

  //可以但是sql1,sql2都没有变化,又制造了一个新的字符串
  //sql1 = sql1 + sql2;

  //学习一个新的字符串类型(StringBuilder),动态字符串PK string,非常适合经常需要修改的字符串情况下
  System.Text.StringBuilder builder = new StringBuilder();
  //利用builder的Append把sql传进去
  builder.Append(sql1);

  //算出字符串的长度
  int count= keywords.Length;
  //利用for循环把sql1,sql2,sql3,sq4连接起来成一个SQL语句
  for (int i = 0; i < count; i++)
  {
  string keyword=keywords[i];
  //string parm_keyword = "@Keyword";
  string cond = string.Format(sql2, parm_keyword + i);
  builder.Append(cond);
  builder.Append(i + 1 < count ? sql3 : sql4);
  }

  //现在拼接出了一个含有SQL参数的SQL语句
  //因为上面含有变量@Keyword,所以要先定义一些
  System.Data.SqlClient.SqlParameter[] paras=
  new System.Data.SqlClient.SqlParameter[count];
  for (int i = 0; i < count; i++)
  {
  //创建一个个的SQL参数对象
  paras[i] = new System.Data.SqlClient.SqlParameter(
  parm_keyword + i, System.Data.SqlDbType.VarChar, 80);
  paras[i].Value=keywords[i];
  }

  List<PetShop.Model.Product> list=new List<PetShop.Model.Product>();
  using (SqlConnection connection = new SqlConnection(connectionString))
  {
  //因为此时有多个SQL语句,所以要利用builder的ToString方法转化成SQL语句
  SqlCommand command = new SqlCommand(builder.ToString(), connection);
  connection.Open();

  command.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = paras;

  SqlDataReader reader= command.ExecuteReader();
  while (reader.Read())
  {
  PetShop.Model.Product product = new PetShop.Model.Product()
  {
  ProductId = reader.GetString(0),
  CategoryId = reader.GetString(1),
  Name = reader.GetString(2),
  Descn = reader.GetString(3),
  Image = reader.GetString(4)
  };
  list.Add(product);
  }
  return list;
  }
   


  }


------解决方案--------------------
C# code

command.Parameters.Add("@Keyword", SqlDbType.NVarChar).Value = paras[i];