解決ASP.NET的SqlCommand,利用SqlParameter來下SQL指令"Where In"的問題

2008-04-08

60107
0
ASP.NET

前陣子在討論區看到這方面的問題...小弟去測了一下..有以下心得...

資料表內容如下：

一般作法,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();

執行結果：

回首頁