某表含有N個(gè)字段超精簡(jiǎn)模糊查詢方法

我們?cè)谧龆鄠€(gè)字段模糊查詢時(shí)，是不是覺(jué)得非常麻煩？比如我要模糊查詢某表多個(gè)字段存在某數(shù)據(jù)時(shí)，如下

select * from table where a like '%key%' or b like '%key%'orc like '%key%'..........

上面的語(yǔ)句不但長(zhǎng)，而且寫(xiě)起來(lái)好麻煩。我們是不是有更好的辦法呢？

答案是肯定的。我們可以這樣寫(xiě)：

SELECT * FROM table where CONCAT(a,b,c......） like '%key%'

這樣不就顯得很簡(jiǎn)單，很簡(jiǎn)潔？

如果存在N個(gè)字段，而你又不情愿一個(gè)一個(gè)的手寫(xiě)每個(gè)字段,你又該如何呢？

我的思路是這樣的，首先讀取某表所有的字段，比如讀出來(lái)某表含有a,b,c,d....等字段（select name from syscolumns where id=object_id(TableName)語(yǔ)句可以讀取某表字段信息），

然后將這些字段拼接到concat中，拼接的結(jié)果像這樣的：SELECT * FROM table where CONCAT(a,b,c......） like '%key%'

這樣一來(lái)，簡(jiǎn)單了很多，減少了繁瑣不必要的sql拼接操作。

本人為了這個(gè)問(wèn)題，也做了一些程序demo，以便大家互相學(xué)習(xí)。

0.列名實(shí)體類

  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.分頁(yè)實(shí)體類

public class Paging    {        /// <summary>        /// 總數(shù)        /// </summary>        public int TotalItems { get; set; }        /// <summary>        /// 每頁(yè)多少條        /// </summary>        public int ItemsPerPage { get; set; }        /// <summary>        /// 當(dāng)前頁(yè)        /// </summary>        public int CurrentPage { get; set; }        /// <summary>        /// 總共多少頁(yè)        /// </summary>        public int TotalPages        {            get { return (int)Math.Ceiling((decimal)TotalItems / ItemsPerPage); }        }

3.帥選條件

  public class SelectField    {        /// <summary>        /// 表名        /// </summary>        public string TableName { get; set; }        /// <summary>        /// 查找的關(guān)鍵字        /// </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>        /// 帶有分頁(yè)的多字段查詢        /// </summary>        /// <param name="TableName">表名</param>        /// <param name="KeyWord">查詢關(guān)鍵字</param>        /// <param name="page">當(dāng)前頁(yè)號(hào)</param>        /// <param name="take">每頁(yè)顯示行數(shù) </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 = reader[tempName];                                if (value != DBNull.Value)                                {                                    pi.SetValue(t, value, null);                                }                            }                        }                        list.Add(t);                    }                    sb = null;                    sql = null;                    conn = null;                    return list;                }            }            return null;        }        /// <summary>        /// 簡(jiǎn)單的條件查詢        /// </summary>        /// <param name="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;