2012-11-15

[ASP.NET] 在Parameters使用SQL語法like和in

常常忘記SQL語法like和in要怎麼使用在Parameters裡,在此做個記錄~

like:
...
SqlCommand cmd = new SqlComand();
cmd.CommandText = "select * from tblUsers where UserName like @Name + '%'";
cmd.Parameters.Add("@Name",txtNAME.Text.Trim());
...

in:(要把每個條件各自獨立一個Parameter)
...
SqlCommand cmd = new SqlComand();
StringBuilder tempSql = new StringBuilder();

string[] aryName = "Tim,John,Ken".Split(",");

for (int I = 0 ; I < aryName.Length ; I++)
{
     if (tempSql.ToString() != "") tempSql.Append(",");
     tempSql.Append("@Name" + I.ToString());
     cmd.Parameters.Add("@Name" + I.ToString(), aryName[I]);
}

cmd.CommandText = string.Format("select * from tblUsers where UserName in ({0})", tempSql.ToString());
...

沒有留言:

張貼留言