前陣子在討論區看到這方面的問題...小弟去測了一下..有以下心得...
資料表內容如下：
一般作法,sql where in字串,全由使用者定義
string queryString =
"SELECT id,gender,name FROM [user] Where id in(19,21,24)";
SqlCommand cmd =
new SqlCommand(queryString, conn);
SqlDataAdapter adapter =
new SqlDataAdapter(cmd);
DataTable dt =
new DataTable();
adapter.Fill(dt);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
參數作法,sql where in字串,使用@IdGroup方式,@IdGroup = 19,這樣是正常可以run的
string queryString =
"SELECT id,gender,name FROM [user] Where id in(@IdGroup)";
SqlCommand cmd =
new SqlCommand(queryString, conn);
cmd.Parameters.Add(
"@IdGroup",
"19");
SqlDataAdapter adapter =
new SqlDataAdapter(cmd);
DataTable dt =
new DataTable();
adapter.Fill(dt);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
參數作法,sql where in字串,使用@IdGroup方式,@IdGroup = 19,21,24,這樣會產生錯誤
string queryString =
"SELECT id,gender,name FROM [user] Where id in(@IdGroup)";
SqlCommand cmd =
new SqlCommand(queryString, conn);
cmd.Parameters.Add(
"@IdGroup",
"19,21,24");
SqlDataAdapter adapter =
new SqlDataAdapter(cmd);
DataTable dt =
new DataTable();
adapter.Fill(dt);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
解決方法,將參數分成多個,例如 where id in(@param1,@param2,@param3),然後設定每一個參數的值
string query =
"19,21,24";
string[] param = query.Split(',');
string temp = "";
for (
int i = 0; i < param.Length; i++)
...{
temp += "@param" + i.ToString() + ",";
}
string queryString =
"SELECT id,gender,name FROM [user] Where id in(" + temp.Substring(0, temp.Length - 1) +
")";
SqlCommand cmd =
new SqlCommand(queryString, conn);
for (
int i = 0; i < param.Length; i++)
...{
cmd.Parameters.Add("@param" + i.ToString(), SqlDbType.Int).Value = param[i];
}
SqlDataAdapter adapter =
new SqlDataAdapter(cmd);
DataTable dt =
new DataTable();
adapter.Fill(dt);
this.GridView1.DataSource = dt;
this.GridView1.DataBind();
執行結果：
推到 Twitter!
推到 Plurk!
