一般网站数据搜索的功能实现

1.首先写出搜索条件类

例如以下代码：

        /// <summary>
        /// 搜索条件
        /// </summary>
        public class SearchBookCriterion
        {
            public string title = null;
            public DateTime? date1 = null, date2 = null;
            public string author = null;
            public string publisher = null;
            public double? price1 = null, price2 = null;
        }

2.写出搜索条件（where语句）

        /// <summary>
        /// 根据查询条件构建SQL语句中的Where语句
        /// </summary>
        /// <param name="search"></param>
        /// <returns></returns>
        private static string bulidWhereString(SearchBookCriterion search)
        {
            string sql = "";
            string temp = "";
            if (!string.IsNullOrEmpty(search.title))
            {
                temp = string.Format("and (bookTitle like '%{0}%')", search.title);
                sql += temp;
            }
            if (!string.IsNullOrEmpty(search.author))
            {
                temp = string.Format("and (author1 like '%{0}%'" + "or author2 like '%{0}%'" + "or author3 like '%{0}%')", search.author);
                sql += temp;
            }
            if (!string.IsNullOrEmpty(search.publisher))
            {
                temp = string.Format("and (PublisherName like '%{0}%')", search.publisher);
                sql += temp;
            }
            if (search.date1.HasValue && search.date2.HasValue)
            {
                temp = string.Format("and (publisherDate between '{0}' and '{1}')", search.date1.Value, search.date2.Value);
                sql += temp;
            }
            if (search.price1.HasValue && search.price2.HasValue)
            {
                temp = string.Format("and (price between '{0}' and '{1}')", search.price1.Value, search.price2.Value);
                sql += temp;
            }
            return sql;
        }

 

3.根据条件搜索图书并返回分页结果

        /// <summary>
        /// 根据条件搜索图书并分页返回
        /// </summary>
        /// <param name="search">搜索条件</param>
        /// <param name="pageIndex">显示当前页</param>
        /// <param name="pageSize">页面大小</param>
        /// <param name="count">符合条件的总数</param>
        /// <returns></returns>
        public static DataTable GetsearchBooks(SearchBookCriterion search, int pageIndex, int pageSize, out int count)
        {
            string where = "";
            if (null != search)
            {
                where = bulidWhereString(search);
            }
            string sql1= "select count(*) from View_BookInfo where1=1" + where;
            count = Convert.ToInt32(SqlDBHelper.ExecuteScalar(SqlDBHelper.ConnectionString, CommandType.Text, sql1, null));

            string sql2 = "select id,bookTitle,price,discount,realPrice,author1,author2,author3," + "smallImage,PublisherName,publisherDate," +
                "Row_Number() over (order by id desc) as rownum" + "from View_BookInfo where (1=1)";
            sql2 += where;
            string temp = "";
            sql2 = "with tempTable as(" + sql2 + ")";
            temp = string.Format("select * from tempTable where rownum between {0} and {1}", (pageIndex - 1) * pageSize + 1, pageIndex * pageSize);
            sql2 += temp;
            DataTable table = SqlDBHelper.ExecuteDataSet(SqlDBHelper.ConnectionString, CommandType.Text, sql2, null).Tables[0];
            return table;
        }

注：函数Row_Number支持MSSQL2005版本以上。如果是MSSQL可以利用Not In和Select top分页