[C#]輕鬆搞定動態欄位和條件
因為舊專案查詢和更新需求，
所有欄位都由使用者自行選擇，
所以舊專案中充滿串字串的處理(如下面程式碼)，
string select = @"select ";
string from = " from orders ";
StringBuilder sb = new StringBuilder();
foreach (object itemChecked in checkedListBox1.CheckedItems)
{
sb.Append(string.Format("{0},", itemChecked.ToString()));
}
select += sb.ToString().TrimEnd(',') + from;
sb = new StringBuilder();
string where = @" where ";
if (textBox1.Text.Length > 0)
{
sb.AppendLine(string.Format(" c1 ='{0}' ", textBox1.Text));
}
if (textBox2.Text.Length > 0)
{
sb.AppendLine(string.Format(" and c2 !='{0}' ", textBox2.Text));
}
if (textBox3.Text.Length > 0)
{
sb.AppendLine(string.Format(" and c3 ='{0}' ", textBox3.Text));
}
if (textBox4.Text.Length > 0)
{
sb.AppendLine(string.Format(" and c4 ='{0}' ", textBox4.Text));
}
if (textBox5.Text.Length > 0)
{
sb.AppendLine(string.Format(" and c5 <='{0}' ", textBox4.Text));
}
where += sb.ToString();
string sqlstatement = select + where;
SqlCommand command = new SqlCommand(sqlstatement);
command.ExecuteNonQuery();
雖然這樣寫程式可以達到使用者的需求，
但整體程式碼不易閱讀、不好維護同時也不夠安全(Sql injection)，
早期EF還沒現身時，我大多使用 CodeEngine.Framework 來實做ORM，
而且該Framework針對動態欄位處理真的很方便，
下面我簡單介紹一下，大家可以自己玩玩看。
List<string> columns = new List<string>();
foreach (object itemChecked in checkedListBox1.CheckedItems)
{
columns.Add(itemChecked.ToString());
}
SelectQueryBuilder query = new SelectQueryBuilder();
query.SelectColumns(columns.ToArray());
if (textBox1.Text.Length > 0)
{
query.AddWhere("c1", Comparison.Equals, textBox1.Text);
}
if (textBox2.Text.Length > 0)
{
query.AddWhere("c2", Comparison.NotEquals, textBox2.Text);
}
if (textBox3.Text.Length > 0)
{
query.AddWhere("c3", Comparison.Equals, textBox3.Text);
}
if (textBox4.Text.Length > 0)
{
query.AddWhere("c4", Comparison.Equals, textBox4.Text);
}
if (textBox5.Text.Length > 0)
{
query.AddWhere("c5", Comparison.LessOrEquals, textBox4.Text);
}
query.SetDbProviderFactory(DbProviderFactories.GetFactory("System.Data.SqlClient"));
DbCommand command = query.BuildCommand();
產生的CommandText(參數化處理)
參考
SelectQueryBuilder: Building complex and flexible SQL queries/commands from C#