某表含有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 = 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>
/// 简单的条件查询
/// </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;
}
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>
/// 获取表中所有字段
/// </summary>
/// <param name="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 = ls.QueryForMoreField(field, page);
foreach (var d in data)
{
Console.WriteLine("ID:"+d.Id+" Name"+d.Name);
}
}
注：这只是个简陋粗糙的实现而已，后续精简程序，拓展Linq 这类方法，程序无法提供下载，因为不知道博客园编辑器如何上传，见谅。
注意：本程序支持sql2008 R2以上的数据库
