A table contains N fields, which are ultra-Simplified fuzzy queries.

A table contains N fields, which are ultra-Simplified fuzzy queries.

Is it very troublesome for us to perform fuzzy queries on multiple fields? For example, if I want to perform a fuzzy query on the existence of a data in multiple fields in a table

 

Select * from table where a like '% key %' or B like '% key %' or c like '% key % '..........

 

The preceding statements are not only long, but also difficult to write. Is there a better solution?

The answer is yes. We can write as follows:

SELECT * FROM table where CONCAT (a, B, c...) like '% key %'

This is simple and concise?

 

If there are N fields and you are reluctant to write each field one by one, what should you do?

My idea is to read all the fields in a table first. For example, a table contains a, B, c, d .... field (select name from syscolumns where id = object_id (TableName) statement can read the field information of a table ),

Then splice these fields into concat. The result is as follows: SELECT * FROM table where CONCAT (a, B, c ......) like '% key %'

In this way, the SQL concatenation operations are much simpler and less cumbersome and unnecessary.

I have also made some program demos to help you learn from each other.

0. Column name entity class

  public class SysColumns    {        public string Key { get; set; }        public string ColumnName { get; set; }    }

 

1. Reverse and sort enumeration queries

Public enum OrderType {// <summary> /// Reverse Order // </summary> Desc = 0, /// <summary> /// sequence // </summary> ASC = 1 ,}

2. Paging entity class

Public class Paging {/// <summary> /// total // </summary> public int TotalItems {get; set ;} /// <summary> /// number of entries per page /// </summary> public int ItemsPerPage {get; set ;} /// <summary> /// current page /// </summary> public int CurrentPage {get; set ;} /// <summary> /// total number of pages /// </summary> public int TotalPages {get {return (int) Math. ceiling (decimal) TotalItems/ItemsPerPage );}}

3. Handsome Selection Conditions

Public class SelectField {// <summary> /// table name // </summary> public string TableName {get; set ;} /// <summary> /// search keyword /// </summary> public string Key {get; set ;} /// <summary> /// other conditions /// </summary> public string OtherWhere {get; set ;} /// <summary> /// sorting field /// </summary> public string OrderField {get; set ;} /// <summary> /// sorting type /// </summary> public string OrderType {get; set ;}}

4. Encapsulation Method

Public class SelectForMoreField <T> where T: new () {private string conn = null; /// <summary> /// connection string /// </summary> public SelectForMoreField () {conn = ConfigurationManager. connectionStrings ["Conn"]. connectionString ;} /// <summary> /// determine whether a Column exists in the SqlDataReader // </summary> /// <param name = "dr"> SqlDataReader </param> // /<param name = "columnName"> column name </param> // <returns> </returns> private bool re AderExists (SqlDataReader dr, string columnName) {dr. getSchemaTable (). defaultView. rowFilter = "ColumnName = '" + columnName + "'"; return (dr. getSchemaTable (). defaultView. count> 0 );} /// <summary> /// multi-field query with pagination /// </summary> /// <param name = "TableName"> table name </param>/ // <param name = "KeyWord"> query KeyWord </param> /// <param name = "page"> current page number </param> /// <param name = "take"> Number of lines per page </param> /// <returns> </ret Urns> 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 typ E = 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> /// simple condition query /// </summary> /// <param name = "field"> query condition </param> /// <returns> </returns> public IList <T> QueryForMoreField (SelectField field) {IList <SysColumns> ls = GetTableField (field. tableName); StringBuilder sb = new StringBuilder (); sb. append ("SE LECT * 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> /// obtain all the fields in the table /// </summary> /// <param name = "TableName"> table name </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. Call demo

  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);            }        }

 

 

 

 

 

Note: This is just a rough implementation. In the future, the program will streamline the program and expand the methods such as Linq. The program cannot be downloaded because it does not know how to upload the blog editor. Sorry.

Note: This program supports databases above sql2008

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Multi-Table multi-field fuzzy query SQL statement writing

Select distinct t. from (select number, company, ticket number, order number, date, null as item name, null as specification, null as quantity, null as unit price from a) union all (select header, null, item name, specification, quantity, unit price from B) t where t. company like '% variable %'

Multi-table, multi-field fuzzy query

The script is okay. Is the data size too large? Can you change the rollback segment of the library? If you can, try to change it to a larger one. How can you change it to the one with the highest permissions as the DB system administrator?