首页 > 代码库 > 某表含有N个字段超精简模糊查询方法
某表含有N个字段超精简模糊查询方法
我们在做多个字段模糊查询时，是不是觉得非常麻烦？比如我要模糊查询某表多个字段存在某数据时，如下
select * from table where a like ‘%key%‘ or b like ‘%key%‘ or c like ‘%key%‘..........
上面的语句不但长，而且写起来好麻烦。我们是不是有更好的办法呢？
答案是肯定的。我们可以这样写：
SELECT * FROM table where CONCAT(a,b,c......） like ‘%key%‘
这样不就显得很简单，很简洁？
如果存在N个字段，而你又不情愿一个一个的手写每个字段,你又该如何呢？
我的思路是这样的，首先读取某表所有的字段，比如读出来某表含有a,b,c,d....等字段（select name from syscolumns where id=object_id(TableName)语句可以读取某表字段信息），
然后将这些字段拼接到concat中，拼接的结果像这样的：SELECT * FROM table where CONCAT(a,b,c......） like ‘%key%‘
这样一来，简单了很多，减少了繁琐不必要的sql拼接操作。
本人为了这个问题，也做了一些程序demo，以便大家互相学习。
0.列名实体类
public class SysColumns { public string Key { get; set; } public string ColumnName { get; set; } }
1，枚举查询倒序，排序
public enum OrderType { /// <summary> /// 倒序 /// </summary> Desc = 0, /// <summary> /// 顺序 /// </summary> ASC = 1, }2.分页实体类
public class Paging { /// <summary> /// 总数 /// </summary> public int TotalItems { get; set; } /// <summary> /// 每页多少条 /// </summary> public int ItemsPerPage { get; set; } /// <summary> /// 当前页 /// </summary> public int CurrentPage { get; set; } /// <summary> /// 总共多少页 /// </summary> public int TotalPages { get { return (int)Math.Ceiling((decimal)TotalItems / ItemsPerPage); } }
3.帅选条件
public class SelectField { /// <summary> /// 表名 /// </summary> public string TableName { get; set; } /// <summary> /// 查找的关键字 /// </summary> public string Key { get; set; } /// <summary> /// 其他条件 /// </summary> public string OtherWhere { get; set; } /// <summary> /// 排序字段 /// </summary> public string OrderField { get; set; } /// <summary> /// 排序类型 /// </summary> public string OrderType { get; set; } }
4.封装的方法
public class SelectForMoreField<T> where T : new() { private string conn = null; /// <summary> /// 连接字符串 /// </summary> public SelectForMoreField() { conn = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString; } /// <summary> /// 判断SqlDataReader是否存在某列 /// </summary> /// <param name="dr">SqlDataReader</param> /// <param name="columnName">列名</param> /// <returns></returns> private bool readerExists(SqlDataReader dr, string columnName) { dr.GetSchemaTable().DefaultView.RowFilter = "ColumnName= ‘" + columnName + "‘"; return (dr.GetSchemaTable().DefaultView.Count > 0); } /// <summary> /// 带有分页的多字段查询 /// </summary> /// <param name="TableName">表名</param> /// <param name="KeyWord">查询关键字</param> /// <param name="page">当前页号</param> /// <param name="take">每页显示行数 </param> /// <returns></returns> public IList<T> QueryForMoreField(SelectField field, Paging page) { IList<SysColumns> ls = GetTableField(field.TableName); StringBuilder sb = new StringBuilder(); sb.Append("SELECT [t1].* FROM (SELECT ROW_NUMBER() OVER (ORDER BY [t0]." + field.OrderField + " " + field.OrderType + ") AS [ROW_NUMBER], [t0].* FROM "); sb.Append(field.TableName); sb.Append(" AS [t0]"); sb.Append(" where "); int i = 0; sb.Append("CONCAT("); foreach (SysColumns sysc in ls) { sb.Append(" cast(" + sysc.ColumnName + " as nvarchar(max)),"); if ((ls.Count - 1) == i) { sb.Append(" cast(" + sysc.ColumnName + " as nvarchar(max)))"); sb.Append(" like ‘%" + field.Key + "%‘"); } i++; } if (!String.IsNullOrEmpty(field.OtherWhere)) { sb.Append("and "); sb.Append(field.OtherWhere); } sb.Append(" ) AS [t1] "); sb.Append("WHERE [t1].[ROW_NUMBER] BETWEEN ((" + page.CurrentPage + "*" + page.ItemsPerPage + ") - (" + page.ItemsPerPage + " -1)) AND (" + page.CurrentPage + "*" + page.ItemsPerPage + ")"); sb.Append("ORDER BY [t1].[ROW_NUMBER]"); string sql = sb.ToString(); IList<T> list; Type type = typeof(T); string tempName = string.Empty; using (SqlDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql)) { if (reader.HasRows) { list = new List<T>(); while (reader.Read()) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { tempName = pi.Name; if (readerExists(reader, tempName)) { if (!pi.CanWrite) { continue; } var value = http://www.mamicode.com/reader[tempName];"field">查询条件</param> /// <returns></returns> public IList<T> QueryForMoreField(SelectField field) { IList<SysColumns> ls = GetTableField(field.TableName); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM " + field.TableName); sb.Append(" where "); int i = 0; sb.Append("CONCAT("); foreach (SysColumns sysc in ls) { sb.Append(" cast(" + sysc.ColumnName + " as nvarchar(max)),"); if ((ls.Count - 1) == i) { sb.Append(" cast(" + sysc.ColumnName + " as nvarchar(max)))"); sb.Append(" like ‘%" + field.Key + "%‘"); } i++; } string sql = sb.ToString(); IList<T> list; Type type = typeof(T); string tempName = string.Empty; using (SqlDataReader reader = SqlHelper.ExecuteReader(conn, CommandType.Text, sql)) { if (reader.HasRows) { list = new List<T>(); while (reader.Read()) { T t = new T(); PropertyInfo[] propertys = t.GetType().GetProperties(); foreach (PropertyInfo pi in propertys) { tempName = pi.Name; if (readerExists(reader, tempName)) { if (!pi.CanWrite) { continue; } var value = http://www.mamicode.com/reader[tempName];"TableName">表名</param> /// <returns></returns> public IList<SysColumns> GetTableField(string TableName) { SqlDataReader read = SqlHelper.ExecuteReader(conn, CommandType.Text, "select name from syscolumns where id=object_id(‘" + TableName + "‘)"); IList<SysColumns> ls = new List<SysColumns>(); while (read.Read()) { SysColumns co = new SysColumns(); co.Key = Guid.NewGuid().ToString().Replace("-", ""); co.ColumnName = read.GetString(0); ls.Add(co); } read.Close(); return ls; } }5.调用演示
class Program { // static string conn = ConfigurationManager.ConnectionStrings["Conn"].ConnectionString; static void Main(string[] args) { SelectForMoreField<Product> ls = new SelectForMoreField<Product>(); SelectField field = new SelectField(); field.TableName = "Product"; field.Key = "1"; field.OtherWhere = "Id > 12281"; int y = (int)OrderType.Desc; field.OrderType = ((OrderType)y).ToString(); field.OrderField = "Id"; Paging page = new Paging(); page.CurrentPage = 1; page.ItemsPerPage = 10; IList<Product> data = http://www.mamicode.com/ls.QueryForMoreField(field, page);"ID:"+d.Id+" Name"+d.Name); } }
注：这只是个简陋粗糙的实现而已，后续精简程序，拓展Linq 这类方法，程序无法提供下载，因为不知道博客园编辑器如何上传，见谅。
注意：本程序支持sql2008以上的数据库
声明：以上内容来自用户投稿及互联网公开渠道收集整理发布，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任，若内容有误或涉及侵权可进行投诉： 投诉/举报 工作人员会在5个工作日内联系你，一经查实，本站将立刻删除涉嫌侵权内容。