1.常规查询语句书写
StringBuilder sb = new StringBuilder();
//加空格或者\n解决两个条件粘连的问题
//用where 1=1解决where该不该加的问题
sb.Append("select * from T_Customers where 1=1\n");
if (cbQueryByName.Checked)
{
sb.Append("and Name like @name\n");
}
if (cbQueryByMobile.Checked)
{
sb.Append("and MobilePhone like @mobile\n");
}
if (cbQueryByCarNum.Checked)
{
sb.Append("and CarNum like @carnum\n");
}
string strSQL=sb.ToString();
2.利用泛型书写查询
List<string> listWhere = new List<string>();
List<SqlParameter> listParameters = new List<SqlParameter>();
if (cbQueryByName.Checked)
{
listWhere.Add("Name like @name");
listParameters.Add(new SqlParameter("name","%"+txtQueryName.Text+"%"));
}
if (cbQueryByMobile.Checked)
{
listWhere.Add("MobilePhone like @mobile");
listParameters.Add(new SqlParameter("mobile", "%" + txtQueryMobile.Text + "%"));
}
if (cbQueryByCarNum.Checked)
{
listWhere.Add("CarNum like @carnum");
listParameters.Add(new SqlParameter("carnum", "%" + txtQueryCarNum.Text + "%"));
}
string sql = "select * from T_Customers\n";
if (listWhere.Count > 0)
{
string sqlWhere = string.Join(" and ", listWhere.ToArray());
sql =sql+" where "+sqlWhere;
}
dataGridView1.DataSource = SqlHelper.ExecuteDataTable(sql, listParameters.ToArray());
public static DataTable ExecuteDataTable(string cmdText, params SqlParameter[] parameters)
{
using (SqlConnection conn = new SqlConnection(connstr))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = cmdText;
cmd.Parameters.AddRange(parameters);
using (SqlDataAdapter adapter = new SqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
}
}
}
3.拼音检索[利用Visual studio International pack开发包]
数据库中有存汉字的字段及对应的汉字拼音的字段,汉字拼音可编写程序利用开发包添加进去,无需人工录入。
static string GetPinyin(string sname)
{
StringBuilder sb = new StringBuilder();
foreach (char ch in sname)
{
if (!ChineseChar.IsValidChar(ch))
{
continue;
}
ChineseChar chineseChar = new ChineseChar(ch);
string pinyin = chineseChar.Pinyins[0]; //暂时只考虑多音字的第一个
pinyin = pinyin.Substring(0, pinyin.Length - 1); //去掉最后一位的声调
sb.Append(pinyin);
}
return sb.ToString();
}
C#数据检索
最新推荐文章于 2023-08-17 23:13:38 发布