将参数值从 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];