双11狂欢大促

盲盒赢 定制羽绒服

开启惊喜盲盒，抽取你的专属好礼

— 惊喜奖品抢先看 —

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

超级年卡50元券

周边70元券

手机支架

一拖三数据线

雨伞

运动毛巾

电动牙刷

VIP月卡

机械键盘

定制羽绒服

多条件查询数据库并显示（c#与ASP.NET）

2008-04-28 02:42:04 1.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

shoumangli 墨衍会员 · AI 创作全网分发关注 + 加入社区

AIGC在游戏行业的应用：革新游戏开发与体验 AIGC（人工智能生成内容）技术正在深刻改变游戏行业，从开发到体验全面革新。在游戏开发中，AIGC可用于自动生成关卡、角色和剧情，提升开发效率；在资源制作中，通过AI生成场景、角色模型等，降低成本。阅读详情

我从文本框里读出多个数据,我要在数据库里面查询是否符合多个数据条件的数据并显示
string loginName = TextBoxLoginName.Text;
string。。。
string。。。。。
。。。
string Title = TextBoxTitle.Text;

这多个条件中随便满足多少条件多可以(即，一个，二个....或全部满足)；
我已经实现两个条件的查询并显示了，但是条件一多的话，那我那个代码效率是非常低的，请高手指点我该怎么改进我的代码。谢谢

我写的代码如下：

string loginName = TextBoxLoginName.Text;
string Title = TextBoxTitle.Text;

if (TextBoxTitle.Text == "" && TextBoxLoginName.Text == "")
{
Response.Write("<Script Language=JavaScript>alert('请输入查询条件！');</Script>");
}

else
{
try
{
//连接数据库
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=SERVER;Database=mydata;User Id=sa;Pwd=";

conn.Open();

if (TextBoxTitle.Text != "" && TextBoxLoginName.Text != "")
{

string strSql = "select * from [Topic] where [UserLoginName] =" + SqlStringFormat.GetQuotedString(loginName) + "and Title = " + SqlStringFormat.GetQuotedString(Title);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");

GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();

}

else
{
if (TextBoxTitle.Text != "")
{

string strSql = "select * from [Topic] where Title = " + SqlStringFormat.GetQuotedString(Title);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");

GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();
}

if (TextBoxLoginName.Text != "")
{
string strSql = "select * from [Topic] where [UserLoginName] =" + SqlStringFormat.GetQuotedString(loginName);
SqlDataAdapter da = new SqlDataAdapter(strSql, conn);

DataSet ds = new DataSet();
da.Fill(ds, "Topic");

GV.DataSource = ds.Tables["Topic"].DefaultView;
GV.DataBind();
conn.Close();
}
}
}

catch
{
Response.Write("<FONT STYLE='Z-INDEX:100;LEFT:80;POSITION:absolute;TOP:60;'>该用户或该文章不存在</FONT>");
}
}

}

...全文

惊呆了！Oracle 11g从入门到放弃的保姆级指南（新手必看）本文为Oracle 11g数据库新手提供了一份详尽的入门指南。内容涵盖从安装配置、SQL*Plus工具使用、核心的SQL增删改查操作，到事务、索引等进阶概念的解析。文章旨在帮助初学者避开常见陷阱，快速掌握这一经典关系型数据库管理系统的基本操作与核心技能，实现从入门到上手。阅读详情

shoumangli

C# 111,129 成员 642,540 内容

+ 关注 + 加入社区

切换为时间正序

whynot2012 2011-04-24

打赏
举报

都是高手啊

wellst 2010-04-24

打赏
举报

2楼第三种方法是不是数据多了不好用呢？

tqqlyl 2009-09-14

打赏
举报

您好，问题解决了吗

andylauhai 2008-04-29

打赏
举报

方法总比问题多

shoumangli 2008-04-28

打赏
举报

谢谢各位，准备结贴了

yunfeng007 2008-04-28

打赏
举报

如何使用？

/// <summary>

        /// 为查询添加条件

        /// <example>

        /// 用法一：

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);

        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// 

        /// 用法二：AddCondition函数可以串起来添加多个条件

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// </example>

        /// </summary>

        /// <param name="fielName">字段名称</param>

        /// <param name="fieldValue">字段值</param>

        /// <param name="sqlOperator">SqlOperator枚举类型</param>

        /// <returns>增加条件后的Hashtable</returns>

yunfeng007 2008-04-28

打赏
举报

3、



using System;

using System.Collections.Generic;

using System.Text;

using System.Data.Common;

using System.Data;

using System.Collections;



namespace DBUtility

{

    public class SearchCondition

    {

        private Hashtable conditionTable = new Hashtable();

        public Hashtable ConditionTable

        {

            get { return this.conditionTable; }

        }

        /// <summary>

        /// 为查询添加条件

        /// <example>

        /// 用法一：

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual);

        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// 

        /// 用法二：AddCondition函数可以串起来添加多个条件

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual).AddCondition("Test2", "Test2Value", SqlOperator.Like);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// </example>

        /// </summary>

        /// <param name="fielName">字段名称</param>

        /// <param name="fieldValue">字段值</param>

        /// <param name="sqlOperator">SqlOperator枚举类型</param>

        /// <returns>增加条件后的Hashtable</returns>

        public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator)

        {

            this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue, sqlOperator));

            return this;

        }



        /// <summary>

        /// 为查询添加条件

        /// <example>

        /// 用法一：

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false);

        /// searchObj.AddCondition("Test2", "Test2Value", SqlOperator.Like, true);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// 

        /// 用法二：AddCondition函数可以串起来添加多个条件

        /// SearchCondition searchObj = new SearchCondition();

        /// searchObj.AddCondition("Test", 1, SqlOperator.NotEqual, false).AddCondition("Test2", "Test2Value", SqlOperator.Like, true);

        /// string conditionSql = searchObj.BuildConditionSql();

        /// </example>

        /// </summary>

        /// <param name="fielName">字段名称</param>

        /// <param name="fieldValue">字段值</param>

        /// <param name="sqlOperator">SqlOperator枚举类型</param>

        /// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>

        /// <returns></returns>

        public SearchCondition AddCondition(string fielName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty)

        {

            this.conditionTable.Add(fielName, new SearchInfo(fielName, fieldValue, sqlOperator, excludeIfEmpty));

            return this;

        }



        /// <summary>

        /// 根据对象构造相关的条件语句（不使用参数），如返回的语句是:

        /// <![CDATA[

        /// Where (1=1)  AND Test4  <  'Value4' AND Test6  >=  'Value6' AND Test7  <=  'value7' AND Test  <>  '1' AND Test5  >  'Value5' AND Test2  Like  '%Value2%' AND Test3  =  'Value3'

        /// ]]>

        /// </summary>

        /// <returns></returns> 

        public string BuildConditionSql()

        {

            string sql = " Where (1=1) ";

            string fieldName = string.Empty;

            SearchInfo searchInfo = null;



            StringBuilder sb = new StringBuilder();

            foreach (DictionaryEntry de in this.conditionTable)

            {

                searchInfo = (SearchInfo)de.Value;



                //如果选择ExcludeIfEmpty为True,并且该字段为空值的话,跳过

                if (searchInfo.ExcludeIfEmpty && string.IsNullOrEmpty((string)searchInfo.FieldValue))

                {

                    continue;

                }



                if (searchInfo.SqlOperator == SqlOperator.Like)

                {

                    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,

                        this.ConvertSqlOperator(searchInfo.SqlOperator), string.Format("%{0}%", searchInfo.FieldValue));

                }

                else

                {

                    sb.AppendFormat(" AND {0} {1} '{2}'", searchInfo.FieldName,

                        this.ConvertSqlOperator(searchInfo.SqlOperator), searchInfo.FieldValue);

                }

            }



            sql += sb.ToString();



            return sql;

        }



        #region 辅助函数



        /// <summary>

        /// 转换枚举类型为对应的Sql语句操作符号

        /// </summary>

        /// <param name="sqlOperator">SqlOperator枚举对象</param>

        /// <returns><![CDATA[对应的Sql语句操作符号（如 ">" "<>" ">=")]]></returns>

        private string ConvertSqlOperator(SqlOperator sqlOperator)

        {

            string stringOperator = " = ";

            switch (sqlOperator)

            {

                case SqlOperator.Equal:

                    stringOperator = " = ";

                    break;

                case SqlOperator.LessThan:

                    stringOperator = " < ";

                    break;

                case SqlOperator.LessThanOrEqual:

                    stringOperator = " <= ";

                    break;

                case SqlOperator.Like:

                    stringOperator = " Like ";

                    break;

                case SqlOperator.MoreThan:

                    stringOperator = " > ";

                    break;

                case SqlOperator.MoreThanOrEqual:

                    stringOperator = " >= ";

                    break;

                case SqlOperator.NotEqual:

                    stringOperator = " <> ";

                    break;

                default:

                    break;

            }



            return stringOperator;

        }



        /// <summary>

        /// 根据传入对象的值类型获取其对应的DbType类型

        /// </summary>

        /// <param name="fieldValue">对象的值</param>

        /// <returns>DbType类型</returns>

        private DbType GetFieldDbType(object fieldValue)

        {

            DbType type = DbType.String;



            switch (fieldValue.GetType().ToString())

            {

                case "System.Int16":

                    type = DbType.Int16;

                    break;

                case "System.UInt16":

                    type = DbType.UInt16;

                    break;

                case "System.Single":

                    type = DbType.Single;

                    break;

                case "System.UInt32":

                    type = DbType.UInt32;

                    break;

                case "System.Int32":

                    type = DbType.Int32;

                    break;

                case "System.UInt64":

                    type = DbType.UInt64;

                    break;

                case "System.Int64":

                    type = DbType.Int64;

                    break;

                case "System.String":

                    type = DbType.String;

                    break;

                case "System.Double":

                    type = DbType.Double;

                    break;

                case "System.Decimal":

                    type = DbType.Decimal;

                    break;

                case "System.Byte":

                    type = DbType.Byte;

                    break;

                case "System.Boolean":

                    type = DbType.Boolean;

                    break;

                case "System.DateTime":

                    type = DbType.DateTime;

                    break;

                case "System.Guid":

                    type = DbType.Guid;

                    break;

                default:

                    break;

            }

            return type;

        }

        #endregion

    }

}

yunfeng007 2008-04-28

打赏
举报

2、



using System;

using System.Collections.Generic;

using System.Text;



namespace DBUtility

{

    /// <summary>

    /// 查询信息实体类

    /// </summary>

    public class SearchInfo

    {

        public SearchInfo() {}



        /// <summary>

        /// 构造函数

        /// </summary>

        /// <param name="fieldName">字段名称</param>

        /// <param name="fieldValue">字段的值</param>

        /// <param name="sqlOperator">字段的Sql操作符号</param>

        public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator)

            : this(fieldName, fieldValue, sqlOperator, false)

        { }



        /// <summary>

        /// 构造函数

        /// </summary>

        /// <param name="fieldName">字段名称</param>

        /// <param name="fieldValue">字段的值</param>

        /// <param name="sqlOperator">字段的Sql操作符号</param>

        /// <param name="excludeIfEmpty">如果字段为空或者Null则不作为查询条件</param>

        public SearchInfo(string fieldName, object fieldValue, SqlOperator sqlOperator, bool excludeIfEmpty) 

        {

            this.fieldName = fieldName;

            this.fieldValue = fieldValue;

            this.sqlOperator = sqlOperator; 

            this.excludeIfEmpty = excludeIfEmpty;

        }



        private string fieldName;

        private object fieldValue;

        private SqlOperator sqlOperator;

        private bool excludeIfEmpty = false;





        /// <summary>

        /// 字段名称

        /// </summary>

        public string FieldName

        {

            get { return fieldName; }

            set { fieldName = value; }

        }



        /// <summary>

        /// 字段的值

        /// </summary>

        public object FieldValue

        {

            get { return fieldValue; }

            set { fieldValue = value; }

        }



        /// <summary>

        /// 字段的Sql操作符号

        /// </summary>

        public SqlOperator SqlOperator

        {

            get { return sqlOperator; }

            set { sqlOperator = value; }

        }



        /// <summary>

        /// 如果字段为空或者Null则不作为查询条件

        /// </summary>

        public bool ExcludeIfEmpty

        {

            get { return excludeIfEmpty; }

            set { excludeIfEmpty = value; }

        }

    }

}

yunfeng007 2008-04-28

打赏
举报

多条件查询组合类
1、



using System;

using System.Collections.Generic;

using System.Text;



namespace DBUtility

{

    public enum SqlOperator

    {

         /// <summary>

        /// Like 模糊查询

        /// </summary>

        Like,



        /// <summary>

        /// ＝ is equal to 等于号 

        /// </summary>

        Equal,



        /// <summary>

        /// <> (≠) is not equal to 不等于号

        /// </summary>

        NotEqual,



        /// <summary>

        /// ＞ is more than 大于号

        /// </summary>

        MoreThan,



        /// <summary>

        /// ＜ is less than 小于号 

        /// </summary>

        LessThan,



        /// <summary>

        /// ≥ is more than or equal to 大于或等于号 

        /// </summary>

        MoreThanOrEqual,



        /// <summary>

        /// ≤ is less than or equal to 小于或等于号

        /// </summary>

        LessThanOrEqual,



        /*

        不支持下面两个符号

        

        /// <summary>

        /// 在某个值的中间，拆成两个符号 >= 和 <=

        /// </summary>

        Between,



        /// <summary>

        /// 在某个字符串值中

        /// </summary>

        In

        */

    }

}

makel 2008-04-28

打赏
举报

再简单不过的一个问题了，直接根据输入值用字符串组合成一个where条件，组合完后再一次送进sqlserver里去查一把就可以了，象你这样有几个条件值就查几次，肯定慢了。。。

kakajya 2008-04-28

打赏
举报

写查询的sql语句用stringBuilder的对象，别用string
也能在一定程度上提高性能。

kakajya 2008-04-28

打赏
举报

多条件查询？
那就要涉及到算法的问题了。

wang520d 2008-04-28

打赏
举报

你可以写个方法先拼接SQL条件如：



/// <summary>

    /// 获取DG显示控件的数据集SQL字符串

    /// </summary>

    /// <returns></returns>

    ///  

    private void BuildSearch()

    {

        StringBuilder sql = new StringBuilder();

        sql.AppendFormat(" where 1 = 1  ");

        //类型

        if (!String.IsNullOrEmpty(CatchType))

        {

            sql.Append(" and Type=" + CatchType + "");

        }



        //状态

        if (!String.IsNullOrEmpty(Status))

        {

            sql.Append(" and status=" + Status + "");

        }

       

        this.setFormParm("sqlWhere", sql.ToString());//用VIEWSTATE保存

    }

shoumangli 2008-04-28

打赏
举报

[Quote=引用 1 楼 zccmy22 的回复:]
你的是效率慢．还是报错啊．感觉你写的语句有问题．
[/Quote]

shoumangli 2008-04-28

打赏
举报

没有报错的，我感觉如果条件一多，我这个东西肯定是不行的

zhenglimv 2008-04-28

打赏
举报

力争成为中国最大的架构师群联盟，架构师1群：43545919已满，2群：59048426的招募也近尾声，现3群：17303696正式开放，各群内容基本相同，高手也经过群主进行了调整，希望大家不必重复加入！培训经过筛选才会进行，请大家不必着急！！！

已经上传的顶级软件产品的架构分析,本群资料仅供研究学习，不得商用！！！
google 、
eBay、
Youtube、
淘宝等
......
技术文章包括：
《自己动手写操作系统》
《搜索引擎-原理、技术与系统》
《企业应用架构模式》
......
重要的RUP实例
设计模式精解
......
资料陆续上传中

conan304 2008-04-28

打赏
举报

看看邹老大写的:

--1. 使用 EXEC 实现的动态参数存储过程

CREATE PROC p_test

@para1 varchar(10)=null,

@para2 varchar(10)=null,

@para3 varchar(10)=null,

@para4 varchar(10)=null

AS

SET NOCOUNT ON

DECLARE @sql varchar(8000)

SET @sql='SELECT * FROM tbname WHERE 1=1'

IF @para1 IS NOT NULL

	SET @sql=@sql+' AND col1='''+@para1+''''

IF @para2 IS NOT NULL

	SET @sql=@sql+' AND col2='''+@para2+''''

IF @para3 IS NOT NULL

	SET @sql=@sql+' AND col3='''+@para3+''''

IF @para4 IS NOT NULL

	SET @sql=@sql+' AND col4='''+@para4+''''

EXEC(@sql)

GO





/*======================================================*/



--2. 使用 sp_executesql 实现的动态参数存储过程

CREATE PROC p_test

@para1 varchar(10)=null,

@para2 datetime=null,

@para3 varchar(10)=null,

@para4 int=null

AS

SET NOCOUNT ON

DECLARE @sql nvarchar(4000)

SET @sql='SELECT * FROM tbname WHERE 1=1'

	+CASE WHEN @para1 IS NULL THEN '' ELSE ' AND col1=@para1' END

	+CASE WHEN @para2 IS NULL THEN '' ELSE ' AND col2=@para2' END

	+CASE WHEN @para3 IS NULL THEN '' ELSE ' AND col3=@para3' END

	+CASE WHEN @para4 IS NULL THEN '' ELSE ' AND col4=@para4' END

EXEC sp_executesql @sql,N'

	@para1 varchar(10)=null,

	@para2 datetime=null,

	@para3 varchar(10)=null,

	@para4 int=null

',@para1,@para2,@para3,@para4

GO





/*======================================================*/



--3. 不使用动态 Transact-SQL 语句实现的动态参数存储过程

CREATE PROC p_test

@para1 varchar(10)=null,

@para2 datetime=null,

@para3 varchar(10)=null,

@para4 int=null

AS

SET NOCOUNT ON

SELECT * FROM tbname 

WHERE (@para1 IS NULL OR col1=@para1)

	AND (@para2 IS NULL OR col2=@para2)

	AND (@para3 IS NULL OR col3=@para3)

	AND (@para4 IS NULL OR col4=@para4)

zccmy22 2008-04-28

打赏
举报

你的是效率慢．还是报错啊．感觉你写的语句有问题．

✦双11狂欢大促✦

多条件查询数据库并显示（c#与ASP.NET）

相关推荐

双11狂欢大促